如何选取数据集分组内字段值变更后的首条Type为P的记录

需求规则 #

• 数据集按id分组，每组内按时间字段升序排列
• 当同组内Type字段值发生变化时，筛选出值为'P'的第一条记录

示例数据 #

id  startdate   enddate   Type  status
123 1/01/2021   5/05/2021   C   A
123 6/05/2021   30/06/2021  C   A
123 1/07/2021   30/08/2021  P   A
123 31/08/2021  31/12/2021  P   A

预期输出 #

id  startdate   enddate   Type  status
123 1/07/2021   30/08/2021  P   A

实现方案（通用SQL窗口函数写法，支持MySQL8+、PostgreSQL、Hive、Spark SQL等主流引擎） #

核心逻辑是通过LAG()窗口函数获取同分组内上一条记录的Type值，匹配「当前Type为P，且上一条Type不为P」的记录即可：

SELECT id, startdate, enddate, Type, status
FROM (
    SELECT
        *,
        LAG(Type, 1) OVER (PARTITION BY id ORDER BY startdate ASC) AS last_type
    FROM your_dataset_table
) tmp
WHERE Type = 'P'
  AND (last_type <> 'P' OR last_type IS NULL);

补充说明：如果同个ID存在多次非P→P的切换（例如序列为C→P→C→P），上述语句会返回每一次切换为P的首条记录；如果只需要全序列中第一次出现P的记录，在外层查询增加按id分组取startdate最小值的筛选逻辑即可。

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