Snowflake中按Item ID分组获取最常见商品描述的SQL查询方法

问题场景 #

假设你的表名为product_data，存在如下数据：

需要为每个唯一Item ID获取出现次数最多的商品描述，避免关联查询时产生重复记录。

解决方案1：分组统计+窗口函数（灵活可控） #

这种方式能明确控制排序逻辑，比如当多个描述出现次数相同时，可指定兜底规则（如下示例按描述字典序取第一个）：

WITH desc_counts AS (
    SELECT
        item_id,
        商品描述 AS product_desc,
        COUNT(*) AS desc_count
    FROM product_data
    GROUP BY item_id, 商品描述
),
ranked_descs AS (
    SELECT
        item_id,
        product_desc,
        ROW_NUMBER() OVER (
            PARTITION BY item_id 
            ORDER BY desc_count DESC, product_desc ASC
        ) AS rn
    FROM desc_counts
)
SELECT item_id, product_desc AS 最常见商品描述
FROM ranked_descs
WHERE rn = 1;

逻辑拆解：

• desc_counts：按Item ID和商品描述分组，统计每个组合的出现频次。
• ranked_descs：通过ROW_NUMBER()窗口函数，按Item ID分区，先按频次降序排序，频次相同时按描述字典序升序排序，为每组内的记录分配排名。
• 最后筛选排名为1的记录，即为每个Item ID对应的最常见商品描述。

解决方案2：使用Snowflake内置MODE()函数（简洁高效） #

Snowflake原生支持MODE()聚合函数，可直接返回分组内出现频率最高的值，适合简单场景：

SELECT
    item_id,
    MODE(商品描述) AS 最常见商品描述
FROM product_data
GROUP BY item_id;

注意：若某个Item ID下存在多个描述出现次数并列最多，MODE()会自动返回字典序最小的那个值，和方案1的兜底规则一致。

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