基于分组的连续月份求和：仅含符合条件的月份（无间隙数据）

本问题是此前Stack Overflow提问《Sum Consecutive Months Based on Groups with Criteria》的延伸，原解决方案不适用于仅存在符合条件连续月份（即仅含数据"岛屿"，无数据"间隙"）的场景，现需实现此类场景下按分组对连续月份求和。

示例数据 #

期望输出 #

尝试的SQL方案 #

-- 获取上一条记录的日期
WITH CTE1 as (
select t.*
  , lag(sale_date) over (partition by fruit order by sale_date asc) as prev_date
from mytable t
)
,
-- 判断当前日期与上一条是否连续
CTE2 as (
  SELECT a.*
  , CASE WHEN sale_date!=DATE_ADD(prev_date, INTERVAL 1 MONTH) or prev_date is null then 1 else 0 end  as test
  FROM CTE1
)
,
-- 根据连续判断结果生成分组ID
CTE3 as (
  SELECT b.*, SUM(test) OVER (partition by fruit ORDER BY sale_date) as groups
  FROM CTE2
)
-- 过滤非起始行并统计，需加1才能得到正确月份数
SELECT fruit, groups, min(prev_date), max(sale_date), count(*)+1 as months
FROM CTE3
where test!=1
GROUP BY fruit, groups
order by 1

优化方案（更优雅实现） #

你需要加1的原因是最后一步过滤掉了每组的起始行（test=1的记录），导致count(*)只统计了组内除首行外的记录数。以下是更简洁的实现，无需额外加1：

WITH grouped_data AS (
    SELECT 
        Fruit,
        SaleDate,
        -- 生成连续月份组ID：当前日期与上一条不连续时，组ID加1
        SUM(CASE 
            WHEN DATEADD(month, -1, SaleDate) = LAG(SaleDate) OVER (PARTITION BY Fruit ORDER BY SaleDate) 
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY Fruit ORDER BY SaleDate) AS group_id
    FROM mytable
)
SELECT 
    Fruit,
    MIN(SaleDate) AS Start,
    MAX(SaleDate) AS End,
    COUNT(*) AS Total
FROM grouped_data
GROUP BY Fruit, group_id
ORDER BY Fruit, Start;

优化说明 #

1. 仅用一个CTE完成组ID生成，减少嵌套层级
2. 保留所有行，避免过滤导致的统计偏差，COUNT(*)直接等于连续月份总数
3. 逻辑直观：通过判断当前日期的上一个月是否等于上一条记录的日期，确定是否属于同一连续组

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