Presto SQL按ID和食品分类group by时空时间字段填充未来日期问题

需求核心 #

补全ID为2的记录中缺失的食品分类，缺失记录的time字段使用date_add('year',1, now())函数生成的距今1年的未来日期填充，最终每个ID都要覆盖全部存在的食品分类。

原始表结构（表名：food_records） #

实现SQL #

WITH all_categories AS (
    -- 取所有存在的食品分类
    SELECT DISTINCT "food category" 
    FROM food_records
),
all_ids AS (
    -- 取所有存在的ID
    SELECT DISTINCT ID 
    FROM food_records
),
full_combination AS (
    -- 生成ID和食品分类的全量组合
    SELECT a.ID, b."food category"
    FROM all_ids a
    CROSS JOIN all_categories b
)
SELECT 
    fc.ID,
    fc."food category",
    COALESCE(fr.time, date_add('year', 1, NOW())) AS time
FROM full_combination fc
LEFT JOIN food_records fr 
    ON fc.ID = fr.ID 
    AND fc."food category" = fr."food category"
ORDER BY fc.ID, fc."food category"

说明 #

• 逻辑核心是先通过笛卡尔积生成每个ID对应全量食品分类的基准组合，再左关联原始表匹配已有记录
• 缺失的time字段通过COALESCE函数判空后用指定规则填充，最终输出结果和要求的期望结构完全一致
• 如果原始表中存在其他ID，也会自动补全对应缺失的食品分类，可扩展性更强

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