Snowflake中使用Scaffold识别缺失值失效求助

1. 先确认Scaffold表的完整性 #

Scaffold表必须包含所有日期 + Category + Sub-category的完整笛卡尔积，否则连接后自然不会出现缺失行。验证方法：

-- 按日期分组，检查每个日期的分类组合数是否等于总分类数（比如示例中是4组：A1/A2/B1/B2）
SELECT 
    date,
    COUNT(*) AS combo_count
FROM scaffold
GROUP BY date
HAVING combo_count != 4; -- 替换为你的预期总分类数

如果返回结果不为空，说明Scaffold表缺少对应日期的分类组合，需要重新生成：

-- 示例：生成日期序列 + 分类列表的交叉连接
WITH date_series AS (
    -- 生成指定范围的日期序列，这里以2023全年为例
    SELECT DATEADD(day, seq4(), '2023-01-01') AS date
    FROM TABLE(GENERATOR(ROWCOUNT => 365))
),
category_master AS (
    SELECT 'A' AS category, 'A1' AS sub_category UNION ALL
    SELECT 'A' AS category, 'A2' AS sub_category UNION ALL
    SELECT 'B' AS category, 'B1' AS sub_category UNION ALL
    SELECT 'B' AS category, 'B2' AS sub_category
)
CREATE OR REPLACE TABLE scaffold AS
SELECT ds.date, cm.category, cm.sub_category
FROM date_series ds
CROSS JOIN category_master cm;

2. 修正连接条件与查询逻辑 #

连接必须同时匹配日期、Category、Sub-category三个字段，且避免在WHERE子句过滤掉NULL行：

推荐用左连接（以Scaffold为主表，更直观）

SELECT
    s.date,
    s.category,
    s.sub_category,
    ad.value -- 缺失时自动显示NULL
FROM scaffold s
LEFT JOIN actual_data ad
    ON s.date = ad.date
    AND s.category = ad.category
    AND s.sub_category = ad.sub_category
ORDER BY s.date, s.category, s.sub_category;

避免的错误写法

• 漏写日期连接条件：仅匹配Category/Sub-category会导致日期不对应
• WHERE子句过滤实际表字段：比如WHERE ad.value IS NOT NULL会直接剔除缺失行
• 数据类型不匹配：比如Scaffold的date是DATE类型，实际表是TIMESTAMP，需转换：CAST(ad.date AS DATE) = s.date

3. 排查数据格式问题 #

如果连接后仍有缺失，检查是否存在大小写、空格差异：

-- 验证分类字段是否有格式不一致
SELECT DISTINCT category, sub_category FROM actual_data
EXCEPT
SELECT DISTINCT category, sub_category FROM scaffold;

如果返回结果，说明实际表存在Scaffold没有的分类值，或格式不同（比如大写/小写、前后空格），需统一格式后再连接：

-- 示例：统一字段格式后连接
SELECT
    s.date,
    s.category,
    s.sub_category,
    ad.value
FROM scaffold s
LEFT JOIN actual_data ad
    ON s.date = CAST(ad.date AS DATE)
    AND TRIM(UPPER(s.category)) = TRIM(UPPER(ad.category))
    AND TRIM(UPPER(s.sub_category)) = TRIM(UPPER(ad.sub_category));

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