Teradata窗口分析函数：按分区合并连续相同金额日期区间

问题分析 #

你当前用min(strt_dt) OVER (PARTITION by id, amt ORDER BY strt_dt)的问题在于：同一个ID和Amt下，如果存在非连续的日期段，这个窗口函数会把所有同ID同Amt的记录归为一组，导致原本不连续的区间被错误合并，出现日期重叠或不准确的结果。

比如：ID=1，Amt=100的记录有两段：2023-01-01至2023-01-03，和2023-01-05至2023-01-07。按id+amt分区的话，min(strt_dt)会取2023-01-01，合并后变成2023-01-01至2023-01-07，这显然不对，因为中间2023-01-04是断开的。

正确实现思路（以SQL为例） #

核心是先识别出连续的相同Amt分组，也就是给每个连续的“岛屿”分配一个唯一标识，再基于这个标识分组合并日期。具体步骤：

1. 按ID排序，计算相邻记录的Amt变化
   用窗口函数LAG(Amt) OVER (PARTITION BY ID ORDER BY strt_dt)获取上一条记录的Amt，对比当前Amt，判断是否属于同一个连续组。

2. 生成连续组的标识
   通过累计求和（SUM）给每个新的连续组分配递增的分组ID：当当前Amt和上一条不同时，标记为1，否则为0，然后累计求和得到分组号。

3. 基于分组号合并日期
   按ID、Amt和分组号分区，取最小的strt_dt和最大的end_dt。

示例代码 #

假设你的数据集表名为your_table，SQL语句如下：

WITH grouped_data AS (
    SELECT 
        ID,
        Amt,
        strt_dt,
        end_dt,
        -- 标记是否为新的连续组：当前Amt与上一条不同则为1，否则0
        CASE 
            WHEN LAG(Amt) OVER (PARTITION BY ID ORDER BY strt_dt) != Amt 
            THEN 1 
            ELSE 0 
        END AS is_new_group,
        -- 累计求和生成连续组ID
        SUM(CASE 
            WHEN LAG(Amt) OVER (PARTITION BY ID ORDER BY strt_dt) != Amt 
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY ID ORDER BY strt_dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM your_table
)
SELECT 
    ID,
    Amt,
    MIN(strt_dt) AS merged_strt_dt,
    MAX(end_dt) AS merged_end_dt
FROM grouped_data
GROUP BY ID, Amt, group_id
ORDER BY ID, merged_strt_dt;

关键说明 #

• LAG(Amt)用于获取同一ID下前一条记录的Amt，判断当前记录是否属于新的连续区间。
• 累计求和SUM(...) OVER (...)生成的group_id，会给每个连续的相同Amt区间分配唯一的ID，确保同一个ID下不连续的相同Amt区间被分成不同组。
• 最后按ID、Amt、group_id分组，取最小开始日期和最大结束日期，就能得到正确的合并后区间。

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