sql-server - MSSQL 组直到类型改变

Question

我目前有这张表:

╔════╦══════════╦════════════╗
║ ID ║ PartType ║ PartStatus ║
╠════╬══════════╬════════════╣
║  1 ║ A        ║ OK         ║
║  2 ║ A        ║ BAD        ║
║  3 ║ A        ║ OK         ║
║  4 ║ A        ║ OK         ║
║  5 ║ B        ║ OK         ║
║  6 ║ B        ║ BAD        ║
║  7 ║ A        ║ OK         ║
╚════╩══════════╩════════════╝

我希望能够按 PartType 对它们进行分组，直到它发生变化。所以它应该像这样输出:

╔══════════╦══════════╗
║ PartType ║ Quantity ║
╠══════════╬══════════╣
║ A        ║        4 ║
║ B        ║        2 ║
║ A        ║        1 ║
╚══════════╩══════════╝

Answer 1

如果您使用的是 SQL Server 2012 或更高版本，那么另一种值得一提的方法是利用 2012 中可用的窗口函数。

您可以使用 LAG 函数检测数据集中何时发生状态更改，并且可以使用 SUM OVER 子句为数据生成分组 ID。以下示例演示了如何做到这一点。

    DECLARE @parts TABLE
    (
        ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
        PartType nvarchar(1) NOT NULL,
        PartStatus nvarchar(50) NOT NULL
    )

    INSERT INTO @parts (PartType,PartStatus)
    VALUES 
    (N'A',N'OK'),
    (N'A',N'BAD'),
    (N'A',N'OK'),
    (N'A',N'OK'),
    (N'B',N'OK'),
    (N'B',N'BAD'),
    (N'A',N'OK');


    WITH CTE_PartTypeWithStateChange
    AS
    (
        SELECT   ID
                ,PartType
                ,PartStatus
                ,(
                    CASE
                        WHEN (LAG(PartType, 1, '') OVER (ORDER BY ID) <> PartType) THEN  1
                        ELSE 0
                    END
                    ) HasStateChanged
        FROM  @parts 
    )
    ,
    CTE_PartTypeWithGroupID
    AS
    (
        SELECT   ID
                ,PartType
                ,PartStatus
                ,SUM(HasStateChanged) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) AS GroupID
        FROM    CTE_PartTypeWithStateChange
    )
    SELECT   MAX(PartType) AS PartType
            ,COUNT(PartType) AS Quantity
    FROM     CTE_PartTypeWithGroupID
    GROUP BY GroupID

虽然代码有点多，但由于您没有执行任何自联接，因此这种方法确实可以减少对源表的读取次数。这种方法还减少了查询必须执行的排序次数，这应该会提高更大数据集的性能。