Trino(PrestoSQL) 单窗口函数实现Prior Year to Date指标计算方法

我此前一直在尝试找到仅通过单个窗口函数计算「去年同期至今（Prior Year to Date）」时段内指标总和与平均值的方法，由于现有代码中已经存在大量窗口函数逻辑，所以希望尽可能简化代码实现。这里分享我的解决方案供大家参考。

术语说明 #

• 去年同期至今也可叫做 last year to date、prior ytd、last ytd 等，不同企业对该术语的叫法各有差异。

核心实现逻辑 #

无需嵌套子查询、无需关联同期时间维度表，仅通过窗口函数内嵌时间区间判断的方式完成计算，适用大部分支持标准SQL的计算引擎（如Spark SQL、BigQuery、Snowflake等）。
示例代码如下：

-- 示例表结构：sales(销售日期sale_date, 部门department, 销售额sale_amount)
SELECT
  sale_date,
  department,
  sale_amount,
  -- 计算对应部门的去年年初至去年今日累计销售额
  SUM(
    CASE 
      WHEN sale_date >= DATEADD('year', -1, DATE_TRUNC('year', CURRENT_DATE)) 
        AND sale_date <= DATEADD('year', -1, CURRENT_DATE) 
      THEN sale_amount 
      ELSE 0 
    END
  ) OVER (PARTITION BY department) AS prior_ytd_total_sales,
  -- 计算对应部门的去年年初至去年今日平均日销售额
  AVG(
    CASE 
      WHEN sale_date >= DATEADD('year', -1, DATE_TRUNC('year', CURRENT_DATE)) 
        AND sale_date <= DATEADD('year', -1, CURRENT_DATE) 
      THEN sale_amount 
      ELSE NULL 
    END
  ) OVER (PARTITION BY department) AS prior_ytd_avg_daily_sales
FROM sales

若需计算相对于当前行日期的去年同期至今而非全局固定的去年同期区间，只需将代码中的CURRENT_DATE替换为当前行的日期字段sale_date即可，无需调整窗口函数的整体结构。

方案优势 #

• 仅使用单个窗口函数完成计算，代码结构简洁，便于维护
• 不需要额外关联维度表，计算性能更高
• 时间区间判断可灵活调整，适配不同业务的时间口径定义

内容的提问来源于stack exchange，提问作者G Taylor Bentz