Teradata SQL实现跨两个月引用上月最差持仓值的方法

核心思路 #

先按月份计算每个ID的最大持仓值（即你所说的"最差持仓值"），再确定该值的生效起始日期（当前月份+2个月的第一天），最后将原始数据与这些生效值关联，为每条日期记录匹配对应的生效值。

标准SQL实现（适用于PostgreSQL、BigQuery等） #

WITH monthly_worst AS (
    SELECT
        ID,
        DATE_TRUNC(CALENDAR, MONTH) AS month_start,
        MAX(POSITION) AS worst_position,
        -- 计算生效起始日：当前月份往后推2个月的第一天
        DATE_ADD(DATE_TRUNC(CALENDAR, MONTH), INTERVAL 2 MONTH) AS effective_start_date
    FROM your_table
    GROUP BY ID, DATE_TRUNC(CALENDAR, MONTH)
),
ranked_worst AS (
    SELECT
        t.ID,
        t.CALENDAR,
        t.POSITION,
        t.VALUE,
        mw.worst_position,
        -- 为每个日期筛选最新生效的最差持仓值
        ROW_NUMBER() OVER (PARTITION BY t.ID, t.CALENDAR ORDER BY mw.effective_start_date DESC) AS rn
    FROM your_table t
    LEFT JOIN monthly_worst mw
        ON t.ID = mw.ID
        AND mw.effective_start_date <= t.CALENDAR
)
SELECT
    ID,
    CALENDAR,
    POSITION,
    VALUE,
    CASE WHEN rn = 1 THEN worst_position ELSE NULL END AS WORST_POSITION
FROM ranked_worst
ORDER BY ID, CALENDAR;

SQL Server适配版本 #

WITH monthly_worst AS (
    SELECT
        ID,
        DATEFROMPARTS(YEAR(CALENDAR), MONTH(CALENDAR), 1) AS month_start,
        MAX(POSITION) AS worst_position,
        DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(CALENDAR), MONTH(CALENDAR), 1)) AS effective_start_date
    FROM your_table
    GROUP BY ID, DATEFROMPARTS(YEAR(CALENDAR), MONTH(CALENDAR), 1)
),
ranked_worst AS (
    SELECT
        t.ID,
        t.CALENDAR,
        t.POSITION,
        t.VALUE,
        mw.worst_position,
        ROW_NUMBER() OVER (PARTITION BY t.ID, t.CALENDAR ORDER BY mw.effective_start_date DESC) AS rn
    FROM your_table t
    LEFT JOIN monthly_worst mw
        ON t.ID = mw.ID
        AND mw.effective_start_date <= t.CALENDAR
)
SELECT
    ID,
    CALENDAR,
    POSITION,
    VALUE,
    CASE WHEN rn = 1 THEN worst_position ELSE NULL END AS WORST_POSITION
FROM ranked_worst
ORDER BY ID, CALENDAR;

逻辑说明 #

1. 计算月度最差持仓：通过日期截断分组，提取每个ID每月的POSITION最大值，同时生成该值的生效起始日期。
2. 匹配生效值：用左连接将原始数据与月度最差持仓关联，只保留生效日期早于等于当前记录日期的条目，再通过窗口函数筛选出每个日期对应的最新生效值。
3. 输出结果：仅保留排名第一的生效值，其余日期显示NULL，完全符合你给出的示例输出。

这个方案无需递归，能自动处理任意数量的月份循环，新增月份数据时会自动计算生效日期并关联到后续记录。

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