Snowflake中基于时间戳获取分组最新值并实现行转列的方案

问题根因 #

你现有代码仅能提取当日有更新的字段值，当日无更新的字段会返回空值。若需要实现类似示例中8月12日自动继承8月1日Date of SKU live取值的效果，需要额外补充历史值继承逻辑。此前使用last_value返回重复行，是因为没有搭配去重逻辑，且未添加IGNORE NULLS参数跳过空值。

调整方案 #

核心是利用Snowflake原生支持IGNORE NULLS参数的LAST_VALUE窗口函数，跳过空值直接取截止到当前日期的最新非空记录，完整调整后的SQL如下：

WITH daily_field_latest AS (
    -- 取每个issue+日期+字段的当日最新值，保留原有逻辑不变
    SELECT 
        fh.ISSUE_ID,
        i.issue_name,
        DATE(i.created_date) as created_date,
        DATE(fh.TIME) as field_date,
        f.name as field_name,
        fh.value as field_value,
        i.status,
        i.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.created as created_date,
            r.name as resolution, 
            i0.id, 
            i0.key as issue_name, 
            s.name as status
        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
),
daily_pivot AS (
    -- 行转列生成当日各字段的取值
    SELECT
        ISSUE_ID, 
        issue_name,
        field_date,
        MAX(CASE WHEN field_name = 'Number of Products' THEN field_value END) AS Number_of_Products,
        MAX(CASE WHEN field_name = 'Number of SKU live' THEN field_value END) AS Number_of_SKU_Live,
        MAX(CASE WHEN field_name = 'Number of SKU not created' THEN field_value END) AS Number_of_SKU_Not_Created,
        MAX(CASE WHEN field_name = 'Date of SKU live' THEN field_value END) AS Date_of_SKU_Live,
        MAX(CASE WHEN field_value = '10020' THEN field_date END) AS Work_In_Progress_Date,
        MAX(CASE WHEN field_value = '10010' THEN field_date END) AS Pending_Date,
        status, 
        resolution
    FROM daily_field_latest
    GROUP BY ISSUE_ID, issue_name, field_date, status, resolution
)
-- 补充历史值继承逻辑，空值自动取最近一次的非空值
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,
    LAST_VALUE(Work_In_Progress_Date IGNORE NULLS) OVER (
        PARTITION BY ISSUE_ID ORDER BY field_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Work_In_Progress_Date,
    LAST_VALUE(Pending_Date IGNORE NULLS) OVER (
        PARTITION BY ISSUE_ID ORDER BY field_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Pending_Date,
    status,
    resolution
FROM daily_pivot
ORDER BY ISSUE_ID, field_date;

注意事项 #

• 如果你的需求就是仅展示当日更新的字段值，无更新就留空，你现有的代码已经符合要求，返回的3行结果和你贴出的示例输出一致。
• IGNORE NULLS是实现历史值继承的核心参数，会跳过所有空值取截止到当前日期的最后一次有效更新。
• 显式指定窗口范围ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，避免Snowflake默认窗口范围引发的计算偏差。

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