如何用Snowflake SQL实现按城市分区的30天滚动中位数计算

你的date-spine日期轴表完全适配该场景，核心思路是先补全每个城市的连续日期序列，再基于日期范围计算滑动中位数，避免因日期缺失导致的行数统计错误。

步骤说明 #

• 生成全量城市+连续日期的基准表：取业务表所有去重城市，和date-spine表中覆盖业务表日期范围的日期做笛卡尔积，得到每个城市的连续日期序列
• 关联业务表补全数值：将业务表的VALUE关联到基准表，无数据的日期VALUE记为NULL
• 计算30天滑动中位数：使用Snowflake内置MEDIAN()窗口函数，基于日期范围限定回溯窗口
• 对齐原表输出：关联回原业务表过滤掉补位的无数据日期行，匹配要求的输出字段

完整Snowflake SQL代码 #

WITH 
-- 提取业务表的日期范围和去重城市列表
biz_meta AS (
    SELECT 
        MIN(CREATED_DATE) AS min_biz_dt,
        MAX(CREATED_DATE) AS max_biz_dt,
        ARRAY_AGG(DISTINCT CITY) AS distinct_cities
    FROM your_business_table -- 替换为你的业务表名
),
-- 生成每个城市的连续日期基准
city_date_full AS (
    SELECT 
        city_val.value::STRING AS CITY,
        dt.DATE AS CREATED_DATE
    FROM biz_meta b
    JOIN TABLE(FLATTEN(input => b.distinct_cities)) city_val
    JOIN your_date_spine_table dt -- 替换为你的日期轴表名，字段名不一致自行调整
        ON dt.DATE BETWEEN b.min_biz_dt AND b.max_biz_dt
),
-- 关联业务表补全VALUE字段
full_data_with_value AS (
    SELECT 
        cd.CITY,
        cd.CREATED_DATE,
        biz.VALUE
    FROM city_date_full cd
    LEFT JOIN your_business_table biz
        ON cd.CITY = biz.CITY 
        AND cd.CREATED_DATE = biz.CREATED_DATE
),
-- 计算30天滑动中位数
moving_median_cal AS (
    SELECT 
        CITY,
        CREATED_DATE,
        VALUE,
        MEDIAN(VALUE) OVER(
            PARTITION BY CITY 
            ORDER BY CREATED_DATE
            -- 回溯30天包含当前日期，所以向前取29天
            RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
        ) AS MOVING_MEDIAN_30_DAY
    FROM full_data_with_value
)
-- 关联回原业务表，仅保留原表存在的业务行
SELECT 
    biz.CREATED_DATE,
    biz.CITY,
    biz.VALUE,
    cal.MOVING_MEDIAN_30_DAY
FROM your_business_table biz
LEFT JOIN moving_median_cal cal
    ON biz.CITY = cal.CITY 
    AND biz.CREATED_DATE = cal.CREATED_DATE
ORDER BY biz.CITY, biz.CREATED_DATE;

关键逻辑说明 #

• 窗口范围使用RANGE BETWEEN INTERVAL而非ROWS PRECEDING：直接基于日期值做窗口筛选，即使不补全日期也能正确识别30天范围，搭配日期轴补全后可覆盖所有业务日期区间
• 中位数计算自动忽略NULL值：补位日期的空VALUE不会参与统计，不影响结果准确性
• 最后关联回原业务表：过滤掉补全的无业务数据的日期行，完全匹配要求的输出字段

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