Teradata中特定条件下日期差计算的窗口函数优化方案咨询

现有包含ID、SDate、EDate和Sts字段的数据集：

ID     SDate         EDate       Sts
101     7/18/2023      7/18/2023  01  
101     7/19/2023      7/18/2023  01
101     7/20/2023      7/20/2023  02
101     7/21/2023      7/21/2023  03

需要计算两类记录的日期差：

• 满足SDate=EDate且Sts≠'01'的日期
• 满足SDate=EDate且Sts='01'的日期

示例中结果为 7/20/2023 - 7/18/2023 = 3天。目前通过拆分Sts='01'和Sts≠'01'的数据集并关联ID来计算，希望了解是否有使用窗口函数的高效实现方式。

可以通过窗口函数+条件聚合的方式实现，无需拆分数据集关联，只需要一次数据扫描即可完成计算，具体SQL示例如下：

WITH filtered_records AS (
    SELECT
        ID,
        SDate AS valid_date,
        Sts,
        -- 按ID分组，提取同组内Sts='01'且SDate=EDate的日期（示例取最早的用MIN，取最新的用MAX）
        MIN(CASE WHEN Sts = '01' THEN SDate END) OVER (PARTITION BY ID) AS sts01_base_date
    FROM your_table
    WHERE SDate = EDate -- 仅保留SDate与EDate相等的有效记录
)
SELECT
    ID,
    valid_date AS non_sts01_date,
    sts01_base_date,
    DATEDIFF(day, sts01_base_date, valid_date) AS date_diff_days
FROM filtered_records
WHERE Sts != '01'; -- 仅输出Sts≠'01'的记录及其日期差

逻辑说明： #

1. 过滤有效记录：先筛选出SDate=EDate的记录，因为只有这类记录参与日期差计算；
2. 窗口函数提取基准日期：通过MIN(CASE...) OVER (PARTITION BY ID)，在每个ID分组内提取符合Sts='01'的基准日期（如果同ID下有多条Sts='01'的有效记录，可根据需求用MIN取最早日期或MAX取最新日期）；
3. 计算日期差：最后筛选出Sts≠'01'的记录，用DATEDIFF计算与基准日期的天数差。

这种方式避免了数据集拆分和关联操作，在数据量较大时性能更优。

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