如何在Snowflake中提取按Code分组的全Item共享日期？

我们需要实现按Code分组，筛选出每个分组下所有Item都存在的日期，保留这些日期对应的全部记录。比如示例中：

• Code=1时，仅2022-03-01同时存在Item a、b、c，只保留该日期的记录
• Code=2时，仅2022-01-01同时存在Item a、b、c，只保留该日期的记录

原始数据表结构及数据 #

期望结果 #

实现方案 #

核心思路是先统计分组内的总Item数，再对比每个日期下的Item数，筛选出两者相等的日期，最后关联回原表获取完整记录。对应的Snowflake SQL代码如下：

WITH code_item_count AS (
    -- 统计每个Code下的唯一Item总数
    SELECT 
        Code,
        COUNT(DISTINCT item) AS total_items
    FROM your_table_name
    GROUP BY Code
),
date_item_count AS (
    -- 统计每个Code+Date下的唯一Item数
    SELECT 
        Code,
        Date,
        COUNT(DISTINCT item) AS date_items
    FROM your_table_name
    GROUP BY Code, Date
),
valid_dates AS (
    -- 筛选出包含所有Item的日期
    SELECT 
        dic.Code,
        dic.Date
    FROM date_item_count dic
    JOIN code_item_count cic ON dic.Code = cic.Code
    WHERE dic.date_items = cic.total_items
)
-- 关联回原表，获取最终记录
SELECT 
    t.Code,
    t.Date,
    t.item
FROM your_table_name t
JOIN valid_dates vd ON t.Code = vd.Code AND t.Date = vd.Date
ORDER BY t.Code, t.Date, t.item;

代码说明 #

• 替换your_table_name为你的实际表名
• code_item_count：计算每个Code分组下的不同Item总数
• date_item_count：计算每个Code在单个日期下的不同Item数量
• valid_dates：通过对比两个计数，筛选出该日期包含分组内所有Item的Code+Date组合
• 最后关联原表，仅保留符合条件的日期记录

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