如何用窗口函数替换自连接按日维度计算DOD、WOW等环比同比指标

需要将原使用自连接的SQL查询转换为窗口分析函数实现，按日维度计算各平台Amount指标的以下环比/同比值，且输出结果与原查询完全一致：

• DOD：日环比（前一日数值）
• WOW：周环比（前一周同日数值）
• MOM：月环比（前一月同日数值）
• QOQ：季环比（前一季度同日数值）
• YOY：同比（前一年同日数值）

原查询代码 #

with metric_table as (
  select date'2022-09-08' as date, 'iOs'     as platform, 100 as amount union all
  select date'2022-09-08' as date, 'Android' as platform, 105 as amount union all
  select date'2022-09-07' as date, 'iOs'     as platform, 99  as amount union all
  select date'2022-09-07' as date, 'Android' as platform, 86  as amount union all
  select date'2022-09-01' as date, 'iOs'     as platform, 98  as amount union all
  select date'2022-09-01' as date, 'Android' as platform, 88  as amount union all
  select date'2022-08-08' as date, 'iOs'     as platform, 105 as amount union all
  select date'2022-08-08' as date, 'Android' as platform, 106 as amount union all
  select date'2022-06-08' as date, 'iOs'     as platform, 88  as amount union all
  select date'2022-06-08' as date, 'Android' as platform, 85  as amount union all
  select date'2021-09-08' as date, 'iOs'     as platform, 84  as amount union all
  select date'2021-09-08' as date, 'Android' as platform, 83  as amount
)
, metric_by_platform_daily as (
  select
    date,
    platform,
    amount,

    --Add last periods dates
    date_sub(date, interval 1 day)     as last_day_date,
    date_sub(date, interval 1 week)    as last_week_date,
    date_sub(date, interval 1 month)   as last_month_date,
    date_sub(date, interval 1 quarter) as last_quarter_date,
    date_sub(date, interval 1 year)    as last_year_date
  from
    metric_table
)
select
  --Add Current Period calculations
  mpd.date,
  mpd.platform,
  mpd.amount    as amount_current,

  --Add Period-Over-Period calculations
  dod.amount    as amount_last_day,
  wow.amount    as amount_last_week,
  mom.amount    as amount_last_month,
  qoq.amount    as amount_last_quarter,
  yoy.amount    as amount_last_year
from
  metric_by_platform_daily         as mpd
left join metric_by_platform_daily as dod
  on dod.date = mpd.last_day_date
  and dod.platform = mpd.platform
left join metric_by_platform_daily as wow
  on wow.date = mpd.last_week_date
  and wow.platform = mpd.platform
left join metric_by_platform_daily as mom
  on mom.date = mpd.last_month_date
  and mom.platform = mpd.platform
left join metric_by_platform_daily as qoq
  on qoq.date = mpd.last_quarter_date
  and qoq.platform = mpd.platform
left join metric_by_platform_daily as yoy
  on yoy.date = mpd.last_year_date
  and yoy.platform = mpd.platform

转换后的窗口函数实现代码 #

WITH metric_table AS (
  SELECT date'2022-09-08' AS date, 'iOs'     AS platform, 100 AS amount UNION ALL
  SELECT date'2022-09-08' AS date, 'Android' AS platform, 105 AS amount UNION ALL
  SELECT date'2022-09-07' AS date, 'iOs'     AS platform, 99  AS amount UNION ALL
  SELECT date'2022-09-07' AS date, 'Android' AS platform, 86  AS amount UNION ALL
  SELECT date'2022-09-01' AS date, 'iOs'     AS platform, 98  AS amount UNION ALL
  SELECT date'2022-09-01' AS date, 'Android' AS platform, 88  AS amount UNION ALL
  SELECT date'2022-08-08' AS date, 'iOs'     AS platform, 105 AS amount UNION ALL
  SELECT date'2022-08-08' AS date, 'Android' AS platform, 106 AS amount UNION ALL
  SELECT date'2022-06-08' AS date, 'iOs'     AS platform, 88  AS amount UNION ALL
  SELECT date'2022-06-08' AS date, 'Android' AS platform, 85  AS amount UNION ALL
  SELECT date'2021-09-08' AS date, 'iOs'     AS platform, 84  AS amount UNION ALL
  SELECT date'2021-09-08' AS date, 'Android' AS platform, 83  AS amount
)
SELECT
  date,
  platform,
  amount AS amount_current,
  -- 日环比：匹配前一日同平台的amount
  MAX(amount) OVER (PARTITION BY platform) FILTER (WHERE date = date_sub(metric_table.date, INTERVAL 1 DAY)) AS amount_last_day,
  -- 周环比：匹配前一周同日同平台的amount
  MAX(amount) OVER (PARTITION BY platform) FILTER (WHERE date = date_sub(metric_table.date, INTERVAL 1 WEEK)) AS amount_last_week,
  -- 月环比：匹配前一月同日同平台的amount
  MAX(amount) OVER (PARTITION BY platform) FILTER (WHERE date = date_sub(metric_table.date, INTERVAL 1 MONTH)) AS amount_last_month,
  -- 季环比：匹配前一季度同日同平台的amount
  MAX(amount) OVER (PARTITION BY platform) FILTER (WHERE date = date_sub(metric_table.date, INTERVAL 1 QUARTER)) AS amount_last_quarter,
  -- 同比：匹配前一年同日同平台的amount
  MAX(amount) OVER (PARTITION BY platform) FILTER (WHERE date = date_sub(metric_table.date, INTERVAL 1 YEAR)) AS amount_last_year
FROM metric_table
ORDER BY date DESC, platform;

转换说明 #

• 移除原查询的多层自连接，通过窗口函数直接在单表中完成多维度数值匹配
• PARTITION BY platform保证仅在同一平台内关联数据，避免跨平台干扰
• FILTER子句精准定位对应偏移日期的记录，结合MAX聚合（因每个date+platform组合唯一，MAX等价于取目标值）
• 输出字段与原查询完全一致，排序逻辑对齐原查询结果

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