如何按type分组并聚合时间区间及提取特定行的eatingDate值

咱们先来明确需求的核心：

• 按type分组，把连续的时间区间（即前一行的endDate等于当前行的startDate）合并成一个区间
• 合并后的eatingDate取该连续区间最后一行的eatingDate值（不管前面行有没有非NULL值，以最后一行为准）

实现思路 #

这是典型的「连续区间分组」问题，核心步骤是：

1. 给每个type下的行按startDate排序，用窗口函数判断当前行是否和上一行属于同一个连续区间
2. 基于判断结果生成每个连续区间的唯一分组ID
3. 按分组ID聚合，得到合并后的起止日期，并取对应区间最后一行的eatingDate

通用SQL实现 #

下面的代码适用于大多数支持窗口函数的SQL方言（比如PostgreSQL、MySQL 8.0+、SQL Server等）：

WITH consecutive_groups AS (
    SELECT
        type,
        startDate,
        endDate,
        eatingDate,
        -- 生成连续区间的分组ID：如果当前行和上一行是连续区间，分组ID不变；否则开启新分组
        SUM(CASE 
            WHEN LAG(endDate) OVER (PARTITION BY type ORDER BY startDate) = startDate THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY type ORDER BY startDate) AS group_id
    FROM your_table -- 替换成你的表名
)
SELECT
    type,
    MIN(startDate) AS startDate,
    MAX(endDate) AS endDate,
    -- 取当前连续区间最后一行的eatingDate
    (SELECT eatingDate 
     FROM consecutive_groups c2 
     WHERE c2.type = c1.type AND c2.group_id = c1.group_id 
     ORDER BY startDate DESC LIMIT 1) AS eatingDate
FROM consecutive_groups c1
GROUP BY type, group_id
ORDER BY startDate;

验证你的测试场景 #

场景1：原始第三行eatingDate为NULL

执行后会将type=1的2011-2012、2012-2013、2013-2014合并为2011-2014，eatingDate取最后一行的NULL；后面的type=1的2016-2017、2017-2018合并为2016-2018，eatingDate取最后一行的1985，完全符合你的期望结果。

场景2：原始第三行eatingDate为1981

此时合并后的type=1的2011-2014区间，eatingDate会取最后一行的1981，和你预期的结果一致。

补充说明 #

• 如果你的SQL方言支持LAST_VALUE窗口函数，也可以用更简洁的方式替代子查询：

  SELECT DISTINCT
      type,
      MIN(startDate) OVER (PARTITION BY type, group_id) AS startDate,
      MAX(endDate) OVER (PARTITION BY type, group_id) AS endDate,
      LAST_VALUE(eatingDate) OVER (PARTITION BY type, group_id ORDER BY startDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS eatingDate
  FROM consecutive_groups
  ORDER BY startDate;
  

• 核心逻辑是通过group_id把同type的连续区间绑定在一起，再做聚合和取值，确保结果完全匹配需求。

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