Snowflake SQL中如何统计时间序列里ID的历史出现次数

需求说明 #

需要在Snowflake的SQL查询中新增一列Count，统计每行数据里ID在当前行之前的所有历史出现次数。同时数据集规模较大，需保证查询低负载、高性能。

输入输出示例 #

输入数据

输出数据

高效解决方案 #

使用Snowflake原生窗口函数ROW_NUMBER()即可实现需求，该方法无需自连接或嵌套子查询，执行效率极高，适合大规模数据集：

SELECT
    Date,
    ID,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) - 1 AS Count
FROM
    your_table_name
ORDER BY
    Date;

方案说明 #

• PARTITION BY ID：按ID分组，确保仅统计同一ID的历史出现次数
• ORDER BY Date：按时间排序，保证统计范围严格限定为当前行之前的历史数据
• ROW_NUMBER() - 1：ROW_NUMBER()对每组内的行从1开始编号，减去1后正好得到当前行之前的出现次数（首次出现时结果为0）

该方案依托Snowflake对窗口函数的优化执行计划，避免了高负载的笛卡尔积或重复扫描，在大表上的性能远优于自连接等传统方法。

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