Redshift中UNIX时间戳转换、累计计数及分组问题求助

问题根源 #

你的SQL存在两个核心问题，导致单日多条记录仅得到1个计数：

1. 错误的分组字段：用time_col（原始秒级时间戳）分组，会把同一天的不同时间戳拆成独立分组，每个分组仅对应1条原始记录，窗口函数的count每次只能统计1条。
2. 窗口函数排序逻辑偏差：按time_col排序而非转换后的日期列，无法保证按天维度统一计算累计计数。

解决方案 #

根据你的需求，分两种场景提供修正方案：

场景1：按天+分类统计当日总数，再计算累计计数

先聚合单日每个分类的总记录数，再基于聚合结果计算累计：

WITH daily_counts AS (
    SELECT 
        cat_col_1,
        cat_col_2,
        date_trunc('day', TIMESTAMP 'epoch' + time_col * INTERVAL '1 second') AS date_day,
        COUNT(*) AS daily_count
    FROM example_table
    GROUP BY cat_col_1, cat_col_2, date_day
)
SELECT 
    cat_col_1,
    cat_col_2,
    date_day,
    SUM(daily_count) OVER (
        PARTITION BY cat_col_1 
        ORDER BY date_day 
        ROWS UNBOUNDED PRECEDING
    ) AS cumulative_count
FROM daily_counts
ORDER BY cat_col_1, date_day;

场景2：保留原始记录，每条记录显示到当日为止的累计计数

如果不需要按天聚合，仅需每条记录展示到当前日期的累计数：

SELECT 
    cat_col_1,
    cat_col_2,
    date_trunc('day', TIMESTAMP 'epoch' + time_col * INTERVAL '1 second') AS date_day,
    COUNT(*) OVER (
        PARTITION BY cat_col_1 
        ORDER BY date_day 
        ROWS UNBOUNDED PRECEDING
    ) AS cumulative_count
FROM example_table
ORDER BY cat_col_1, date_day;

关键说明 #

• 分组时必须使用转换后的date_day而非原始time_col，确保同一天的记录被归为同一组。
• 窗口函数排序字段改为date_day，保证累计逻辑按天维度有序计算。

内容的提问来源于stack exchange，提问作者kduf