Snowflake中按日期取分组最后值并基于字段值生成透视表问题

问题原因 #

你的现有代码只取了每个字段当日的最后更新值，如果某个日期对应字段没有更新，就会返回空值，且无法携带此前最后一次更新的有效值到后续日期，另外你之前使用last_value时没有添加IGNORE NULLS参数和正确的窗口帧配置，导致结果不符合预期。

解决方案 #

修改后的SQL如下，逻辑是先取每个字段每日的最后更新值，透视后用带忽略空值的窗口函数携带历史有效值到后续无更新的日期：

WITH daily_field_last AS (
    -- 第一步：取每个ISSUE、每个日期、每个字段的当日最后更新值
    SELECT 
        fh.ISSUE_ID,
        i.issue_name,
        DATE(fh.TIME) AS field_date,
        f.name AS field_name,
        fh.value AS field_value,
        i.status AS current_status,
        i.resolution AS current_resolution
    FROM JIRA.ISSUE_FIELD_HISTORY fh
    LEFT JOIN JIRA.FIELD f 
        ON fh.FIELD_ID = f.ID 
        AND f._FIVETRAN_DELETED = 0
    LEFT JOIN (
        SELECT 
            i0.id, 
            i0.key AS issue_name,
            s.name AS status,
            r.name AS resolution
        FROM JIRA.issue i0
        LEFT JOIN JIRA.status s ON i0.status = s.ID
        LEFT JOIN JIRA.RESOLUTION r ON i0.RESOLUTION = r.ID
        WHERE i0._FIVETRAN_DELETED = 0
        AND i0.key LIKE 'PIM%'
    ) i ON i.id = fh.ISSUE_ID
    WHERE fh.ISSUE_ID IN (SELECT ID FROM ISSUE WHERE PROJECT = 10041)
    AND fh.FIELD_ID IN ('customfield_10067', 'customfield_10063', 'customfield_10066', 'customfield_10068', 'status', 'resolution')
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ISSUE_ID, DATE(fh.TIME), field_name ORDER BY fh.TIME DESC) = 1
),
issue_date_list AS (
    -- 第二步：生成每个ISSUE的所有涉及的日期列表，保证无更新的日期也有行
    SELECT DISTINCT ISSUE_ID, issue_name, field_date
    FROM daily_field_last
),
pivot_daily AS (
    -- 第三步：按日期透视字段值
    SELECT 
        dl.ISSUE_ID,
        dl.issue_name,
        dl.field_date,
        MAX(CASE WHEN dfl.field_name = 'Number of Products' THEN dfl.field_value END) AS Number_of_Products,
        MAX(CASE WHEN dfl.field_name = 'Number of SKU live' THEN dfl.field_value END) AS Number_of_SKU_Live,
        MAX(CASE WHEN dfl.field_name = 'Number of SKU not created' THEN dfl.field_value END) AS Number_of_SKU_Not_Created,
        MAX(CASE WHEN dfl.field_name = 'Date of SKU live' THEN dfl.field_value END) AS Date_of_SKU_Live,
        MAX(CASE WHEN dfl.field_value = '10020' THEN dfl.field_date END) AS Work_In_Progress_Date,
        MAX(CASE WHEN dfl.field_value = '10010' THEN dfl.field_date END) AS Pending_Date,
        MAX(dfl.current_status) AS status,
        MAX(dfl.current_resolution) AS resolution
    FROM issue_date_list dl
    LEFT JOIN daily_field_last dfl
        ON dl.ISSUE_ID = dfl.ISSUE_ID
        AND dl.field_date = dfl.field_date
    GROUP BY dl.ISSUE_ID, dl.issue_name, dl.field_date
)
-- 第四步：用last_value携带历史最后一个非空值到后续日期
SELECT 
    ISSUE_ID,
    issue_name,
    field_date AS FIELD_TIME,
    LAST_VALUE(Number_of_Products IGNORE NULLS) OVER (PARTITION BY ISSUE_ID ORDER BY field_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Number of Products",
    LAST_VALUE(Number_of_SKU_Live IGNORE NULLS) OVER (PARTITION BY ISSUE_ID ORDER BY field_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Number of SKU live",
    LAST_VALUE(Date_of_SKU_Live IGNORE NULLS) OVER (PARTITION BY ISSUE_ID ORDER BY field_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Date of SKU Live",
    LAST_VALUE(Number_of_SKU_Not_Created IGNORE NULLS) OVER (PARTITION BY ISSUE_ID ORDER BY field_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Number of SKU Not Created",
    Work_In_Progress_Date,
    Pending_Date,
    LAST_VALUE(status IGNORE NULLS) OVER (PARTITION BY ISSUE_ID ORDER BY field_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Status",
    LAST_VALUE(resolution IGNORE NULLS) OVER (PARTITION BY ISSUE_ID ORDER BY field_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Resolution"
FROM pivot_daily
ORDER BY ISSUE_ID, field_date

核心优化点 #

• 新增IGNORE NULLS参数让last_value跳过空值，取到最近一次更新的有效值
• 明确指定窗口帧ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，避免默认窗口帧导致的取值错误
• 先生成每个ISSUE的全量日期列表，保证没有字段更新的日期也会保留行

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