如何解决SQL中重复类别分段日期区间统计错误的问题

你的问题核心在于：现有查询仅通过productid, producttitle, productcategory分组，会把所有相同类别的记录合并，哪怕它们被其他类别隔开（比如XYZ在GHI前后各出现一次），导致无法区分连续的时间段分段。这是典型的**间隙与孤岛（Gaps and Islands）**场景，我们可以用窗口函数来识别连续的相同类别组，再进行聚合统计。

修改后的SQL语句 #

WITH CategorizedGroups AS (
    SELECT 
        ProductId,
        ProductTitle,
        ProductCategory,
        LoadDate,
        -- 标记每个连续相同类别组的ID：如果当前类别和上一行不同，加1，否则继承
        SUM(CASE WHEN PreviousCategory = ProductCategory THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ProductId, ProductTitle ORDER BY LoadDate) AS GroupId
    FROM (
        SELECT 
            *,
            -- 获取上一行的产品类别，用于判断是否连续
            LAG(ProductCategory) OVER (PARTITION BY ProductId, ProductTitle ORDER BY LoadDate) AS PreviousCategory
        FROM dbo.Product
    ) AS SubQuery
)
SELECT 
    ProductId,
    ProductTitle,
    ProductCategory,
    MIN(LoadDate) AS BeginDate,
    -- 判断当前组的最大日期是否是整个产品的最后日期，是则设为9999-12-31
    CASE 
        WHEN MAX(LoadDate) = (SELECT MAX(LoadDate) FROM dbo.Product WHERE ProductId = cg.ProductId) 
        THEN '9999-12-31' 
        ELSE CONVERT(VARCHAR, MAX(LoadDate), 120) 
    END AS EndDate
FROM CategorizedGroups cg
GROUP BY ProductId, ProductTitle, ProductCategory, GroupId
ORDER BY BeginDate;

逻辑说明 #

1. 子查询用LAG()函数：为每一行获取同一ProductId/ProductTitle下前一行的ProductCategory，判断当前行是否和前一行属于同一个连续类别组。
2. 生成GroupId：用SUM() OVER()累加差异值——如果当前类别和前一行不同，就加1，这样连续相同的类别会被分到同一个GroupId下，被其他类别隔开的相同类别会得到不同的GroupId。
3. 按GroupId聚合：现在我们可以按ProductId, ProductTitle, ProductCategory, GroupId分组，每个分组对应一段连续的相同类别时间段，计算这段的开始和结束日期。
4. 处理最后一组的EndDate：判断当前组的最大日期是否是该产品的最后加载日期，如果是则设为9999-12-31，否则用实际最大日期。

执行结果 #

这个查询会输出你期望的结果：

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