Snowflake SQL中按ID分组统计高频tag并处理平局的实现方法

问题描述 #

对给定的业务表按ID分组，统计每个ID出现次数最多的tag；如果同一ID下多个tag出现次数相同，取该ID下对应timestamp最新的tag作为最终结果。

样例数据 #

ID    tag     data            timestamp
001     A   walter  2021-06-04 09:46:25
005     F   junior  2021-06-05 09:47:25
001     B   junior  2021-06-04 09:47:25
002     C  soprano  2021-06-04 09:48:25
002     C     alto  2021-06-04 09:49:25
001     A    brown  2021-06-04 09:50:25
003     A   cleave  2021-06-04 09:51:25
003     B     land  2021-06-04 09:52:25
004     C   before  2021-06-04 09:53:25
005     H   junior  2021-06-04 09:47:25

实现思路 #

通过两层统计+窗口函数即可完成需求：

1. 先按ID+tag分组，统计每个tag在对应ID下的出现次数，同时记录每个ID+tag组合的最新时间戳，用于处理次数相同的平局场景
2. 对每个ID下的所有tag，按照「出现次数降序、最新时间戳降序」排序，取排序第一位的tag即为最终结果

可用SQL代码 #

请将代码中你的表名替换为实际业务表名即可直接在Snowflake中运行：

WITH tag_stat AS (
    -- 统计每个ID下各tag的出现次数、对应最新时间
    SELECT 
        ID,
        tag,
        COUNT(*) AS tag_cnt,
        MAX(timestamp) AS latest_ts
    FROM 你的表名
    GROUP BY ID, tag
),
rank_result AS (
    -- 按规则排序取每个ID的最优tag
    SELECT 
        ID,
        tag,
        ROW_NUMBER() OVER (
            PARTITION BY ID 
            ORDER BY tag_cnt DESC, latest_ts DESC
        ) AS rn
    FROM tag_stat
)
SELECT ID, tag
FROM rank_result
WHERE rn = 1
ORDER BY ID;

输出结果 #

ID   tag
001    A
002    C
003    B
004    C
005    F

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