求助：使用Window Functions实现交易数据的复杂日期字段计算

可以通过分组计算+窗口函数的组合实现需求，以下是标准SQL的实现代码（语法细节可根据使用的数据库调整）：

WITH spend_year_groups AS (
    SELECT
        spend_year,
        CASE
            WHEN spend_year = 0 THEN MIN(transaction_date)
            ELSE DATEADD(month, -3, MIN(transaction_date))
        END AS spend_year_start_date
    FROM transactions
    GROUP BY spend_year
),
spend_year_dates AS (
    SELECT
        spend_year,
        spend_year_start_date,
        LEAD(spend_year_start_date) OVER (ORDER BY spend_year) AS spend_year_end_date
    FROM spend_year_groups
)
SELECT
    t.transaction_id,
    t.transaction_type,
    t.spend_year,
    t.transaction_date,
    d.spend_year_start_date,
    d.spend_year_end_date
FROM transactions t
JOIN spend_year_dates d ON t.spend_year = d.spend_year
ORDER BY t.transaction_id;

逻辑拆解 #

1. 计算每个spend_year组的起始日期 #

• 第一个CTE spend_year_groups 按spend_year分组处理：
  • 对spend_year=0的组，直接取组内交易日期的最小值（首次交易日期）作为起始日期；
  • 对spend_year>0的组，取组内交易日期的最小值，再向前偏移3个月（通过DATEADD函数实现）。

2. 计算每个组的结束日期 #

• 第二个CTE spend_year_dates 利用LEAD窗口函数：
  • 按spend_year升序排列，获取下一个组的起始日期作为当前组的结束日期；
  • 最后一组无后续分组，因此结束日期为NULL。

3. 关联原表输出结果 #

• 将原交易表与计算好的日期分组表通过spend_year关联，为每笔交易匹配对应的起始和结束日期，最终按交易ID排序得到目标输出。

数据库语法适配说明 #

• PostgreSQL：将DATEADD(month, -3, MIN(transaction_date))替换为MIN(transaction_date) - INTERVAL '3 months'；
• MySQL/SQL Server：直接使用上述代码中的DATEADD语法即可。

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