Redshift将UNIX时间戳转换为YYYYMMDD，收集累积计数，并按日期分组

Question

这里发生了一些事情。我将在下面列出我的逻辑:

1. 选择分类列

2. 转换unix时间戳:

   date_trunc('day'， TIMESPAMP 'epoch' + time_column * INTERVAL '1秒'

3. 收集分类列的累计计数

   count(categorical_column) OVER (PARTITION BY categorical_column ORDER BY date)

4. GROUP BY date_column, categorical_column

我遇到的问题是，当一天中有多个事件发生时，我每天只得到1个计数。有什么建议吗?

SELECT cat_col_1, 
cat_col_2, 
date_trunc('day', TIMESTAMP 'epoch' + time_col * INTERVAL '1 second'), 
count(cat_col_1) OVER (PARTITION BY cat_col_1 ORDER BY time_col ROW UNBOUNDED PRECEEDING)
FROM example_table
GROUP BY time_col, cat_col_1, cat_col_2

Answer 1

答案:Wrap count(cat_col_1) in sum()

SELECT cat_col_1, 
cat_col_2, 
date_trunc('day', TIMESTAMP 'epoch' + time_col * INTERVAL '1 second'), 
sum(count(cat_col_1)) OVER (PARTITION BY cat_col_1 ORDER BY time_col ROW UNBOUNDED PRECEEDING)
FROM example_table
GROUP BY time_col, cat_col_1, cat_col_2