SQL/Presto按日查询历史存在但当日不存在的消失记录实现方案

核心逻辑 #

利用Presto的数组函数做集合差集计算，步骤如下：

• 提取所有需要统计的目标日期
• 计算每个日期当日出现的item集合
• 计算每个日期之前（不含当日）所有出现过的item的累计集合
• 两个集合取差集即为符合要求的消失条目

完整SQL代码 #

WITH all_days AS (
    -- 提取所有存在的业务日期，若需补全连续日期可替换为sequence生成
    SELECT DISTINCT day
    FROM your_table
),
day_items AS (
    -- 聚合每个日期当日出现的所有item
    SELECT 
        day,
        array_agg(DISTINCT item) AS cur_day_items
    FROM your_table
    GROUP BY day
),
cumulative_prev_items AS (
    -- 聚合每个日期之前（不含当日）所有出现过的item
    SELECT 
        a.day,
        array_agg(DISTINCT t.item) AS all_history_items
    FROM all_days a
    LEFT JOIN your_table t ON t.day < a.day
    GROUP BY a.day
)
SELECT 
    -- 将差集数组转为逗号分隔的字符串匹配输出格式
    array_join(array_except(c.all_history_items, d.cur_day_items), ',') AS disappeared_item,
    c.day AS day_of_interest
FROM cumulative_prev_items c
LEFT JOIN day_items d ON c.day = d.day
-- 过滤第一天（无历史数据，差集为空）
WHERE cardinality(array_except(c.all_history_items, d.cur_day_items)) > 0
ORDER BY c.day;

关键函数说明 #

• array_agg(DISTINCT item)：将多行item值聚合成去重后的数组
• array_except(arr1, arr2)：返回仅在arr1中存在的元素，即两个集合的差集
• array_join(arr, ',')：将数组元素按指定分隔符拼接为字符串
• cardinality(arr)：返回数组长度，用于过滤无消失条目的日期

如果你的业务日期存在断层，需要统计连续日期的结果，可以将all_days部分替换为连续日期生成逻辑：

all_days AS (
    -- 生成从最小日期到最大日期的连续整数日期
    SELECT explode(sequence((SELECT min(day) FROM your_table), (SELECT max(day) FROM your_table))) AS day
)

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