Snowflake中补全keyword缺失的device类别数据且避免重复的方法

你当前的自连接写法存在逻辑问题：当同一个keyword+rating组合同时存在mobile、desktop两类device的记录时，自连接会生成2*2=4条匹配结果，这就是重复数据的来源。

可以通过枚举全量设备维度再左关联原表的方式实现需求，该写法性能稳定，适配绝大多数SQL引擎：

SELECT 
    t.keyword,
    d.device,
    COALESCE(k.rating, t.rating) AS rating
FROM 
    -- 提取所有唯一的关键词+评分组合
    (SELECT DISTINCT keyword, rating FROM kw) t
-- 交叉连接生成两类设备的全量组合，确保每个关键词都对应两类设备
CROSS JOIN 
    (SELECT 'mobile' AS device UNION ALL SELECT 'desktop' AS device) d
-- 左关联原表，匹配已存在的记录，不存在的字段会自动保留维度值
LEFT JOIN kw k 
    ON t.keyword = k.keyword 
    AND t.rating = k.rating 
    AND d.device = k.device

如果你的数据量很小，也可以用UNION补全的方式，逻辑更直观：

SELECT DISTINCT keyword, device, rating FROM (
    -- 原表已有数据
    SELECT keyword, device, rating FROM kw
    UNION ALL
    -- 补全缺失mobile的记录
    SELECT keyword, 'mobile' AS device, rating FROM kw
    UNION ALL
    -- 补全缺失desktop的记录
    SELECT keyword, 'desktop' AS device, rating FROM kw
) t

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