如何使用Oracle分析函数为无序值列表计算day_wid_min与day_wid_max

首先创建测试表并插入测试数据，SQL语句如下：

CREATE TABLE TEST 
(
    ID NUMBER, 
    TECH VARCHAR2(200), 
    DAY_WID NUMBER
);

Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210428);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210412);
Insert into TEST (ID,TECH,DAY_WID) values (999,'EXCLUDE',20210402);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210415);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210408);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210430);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210414);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210427);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210422);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210426);
Insert into TEST (ID,TECH,DAY_WID) values (999,'EXCLUDE',20210419);
Insert into TEST (ID,TECH,DAY_WID) values (999,'EXCLUDE',20210401);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210413);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210429);
Insert into TEST (ID,TECH,DAY_WID) values (999,'EXCLUDE',20210406);
Insert into TEST (ID,TECH,DAY_WID) values (999,'EXCLUDE',20210416);
Insert into TEST (ID,TECH,DAY_WID) values (999,'EXCLUDE',20210420);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210409);
Insert into TEST (ID,TECH,DAY_WID) values (999,'EXCLUDE',20210421);
Insert into TEST (ID,TECH,DAY_WID) values (999,'EXCLUDE',20210407);
Insert into TEST (ID,TECH,DAY_WID) values (999,'EXCLUDE',20210405);
Insert into TEST (ID,TECH,DAY_WID) values (999,'INCLUDE',20210423);

需求是按TECH列的连续取值分组，计算每个分组对应的最小DAY_WID和最大DAY_WID，实现分段区间统计效果。这里的分组不是全局按TECH聚合，而是将排序后连续相邻的相同TECH划分为同一组，属于典型的数据孤岛问题，可通过Oracle分析函数实现，完整SQL如下：

WITH t_mark AS (
    -- 按日期升序排序，对比当前行和上一行TECH值，不同则标记为1，相同标记为0
    SELECT 
        ID,
        TECH,
        DAY_WID,
        CASE WHEN LAG(TECH, 1) OVER(ORDER BY DAY_WID) = TECH THEN 0 ELSE 1 END AS diff_flag
    FROM TEST
),
t_group AS (
    -- 对标记值累加，生成连续相同TECH的专属组号
    SELECT 
        ID,
        TECH,
        DAY_WID,
        SUM(diff_flag) OVER(ORDER BY DAY_WID) AS group_id
    FROM t_mark
)
-- 按组号和TECH聚合，计算每个区间的起止日期
SELECT 
    TECH,
    MIN(DAY_WID) AS day_wid_min,
    MAX(DAY_WID) AS day_wid_max
FROM t_group
GROUP BY group_id, TECH
ORDER BY day_wid_min;

执行后即可得到所有连续TECH区间的起止日期，和预期输出效果一致。

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