Snowflake SQL实现季度缺失值填充与季度最新类别获取

原始数据表 #

user_id  date_at          quarter    type_class
123     2022-01-13 8:30:25   1       beginner
123     2022-02-13 7:29:25   1       beginner to mid
123     2022-04-26 17:30:59  2       mid
123     2022-12-15 12:30:33  4       expert

需求 #

• 提取每个季度的最新type_class（如Q1取beginner to mid）
• 用前一季度的有效值填充缺失季度的type_class（如Q3无数据，填充Q2的mid）

预期结果 #

user_id  quarter  type_class
123      1        beginner to mid
123      2        mid
123      3        mid
123      4        expert

当前问题 #

现有查询仅返回存在数据的季度，无法补全缺失季度并完成值填充。

解决方案 #

核心思路 #

1. 生成全年4个季度的完整序列（单/多用户场景分别处理）
2. 筛选每个用户+季度的最新type_class
3. 通过左连接关联完整季度序列与最新数据，使用窗口函数向前填充缺失值

单用户场景查询语句 #

WITH all_quarters AS (
    -- 生成2022年所有4个季度
    SELECT generate_series(1,4) AS quarter
),
latest_class_per_quarter AS (
    SELECT
        user_id,
        EXTRACT(QUARTER FROM date_at)::INT AS quarter,
        type_class,
        ROW_NUMBER() OVER (PARTITION BY user_id, EXTRACT(QUARTER FROM date_at) ORDER BY date_at DESC) AS row_num
    FROM class
    WHERE DATE(date_at) BETWEEN '2022-01-01' AND '2022-12-31'
),
filtered_latest AS (
    SELECT user_id, quarter, type_class
    FROM latest_class_per_quarter
    WHERE row_num = 1
)
SELECT
    '123' AS user_id,
    aq.quarter,
    -- 向前填充缺失的type_class值
    LAST_VALUE(fl.type_class) OVER (ORDER BY aq.quarter ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS type_class
FROM all_quarters aq
LEFT JOIN filtered_latest fl ON aq.quarter = fl.quarter AND fl.user_id = '123'
ORDER BY aq.quarter;

多用户场景查询语句 #

如果需要处理多个用户，先生成所有用户与季度的组合：

WITH all_users AS (
    SELECT DISTINCT user_id FROM class WHERE DATE(date_at) BETWEEN '2022-01-01' AND '2022-12-31'
),
all_quarters AS (
    SELECT generate_series(1,4) AS quarter
),
user_quarters AS (
    -- 生成所有用户+季度的完整组合
    SELECT au.user_id, aq.quarter
    FROM all_users au, all_quarters aq
),
latest_class_per_quarter AS (
    SELECT
        user_id,
        EXTRACT(QUARTER FROM date_at)::INT AS quarter,
        type_class,
        ROW_NUMBER() OVER (PARTITION BY user_id, EXTRACT(QUARTER FROM date_at) ORDER BY date_at DESC) AS row_num
    FROM class
    WHERE DATE(date_at) BETWEEN '2022-01-01' AND '2022-12-31'
),
filtered_latest AS (
    SELECT user_id, quarter, type_class
    FROM latest_class_per_quarter
    WHERE row_num = 1
)
SELECT
    uq.user_id,
    uq.quarter,
    -- 按用户分组，向前填充缺失值
    LAST_VALUE(fl.type_class) OVER (PARTITION BY uq.user_id ORDER BY uq.quarter ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS type_class
FROM user_quarters uq
LEFT JOIN filtered_latest fl ON uq.user_id = fl.user_id AND uq.quarter = fl.quarter
ORDER BY uq.user_id, uq.quarter;

关键说明 #

• all_quarters生成完整季度序列，确保每个季度都出现在结果中
• ROW_NUMBER()用于筛选每个用户+季度组内的最新记录
• LAST_VALUE()窗口函数按季度排序，自动将前序非空的type_class填充到当前缺失的位置

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