在 SQL Server 中，有没有办法在聚合时使用 GROUPING SETS 删除重复项，而不会进行巨大的基数扩展？

问：

一个简化的（有点傻的）例子会让我要问的问题更清楚一些。Bob/Tim 应为此处的唯一标识符。

DROP TABLE IF EXISTS #People

CREATE TABLE #People 
(
    Person varchar(10), 
    City varchar(20), 
    Department varchar(1), 
    PersonAge int
);

INSERT INTO #People VALUES ('Bob', 'New York', 'A', 40),
                           ('Tim', 'New York', 'A', 30),
                           ('Tim', 'New York', 'B', 30)

;WITH InitialGrouping AS 
(
    SELECT 
        Person,
        City,
        Department,
        MAX(PersonAge) PersonAge,
        COUNT(*) NumRows
    FROM
        #People
    GROUP BY 
        Person, City, Department
) --SELECT * FROM InitialGrouping
SELECT 
    City,
    Department,
    AVG(PersonAge) AveragePersonAge,
    COUNT(DISTINCT Person) PersonCount
FROM
    InitialGrouping
GROUP BY 
    GROUPING SETS (City, Department, (City, Department))
ORDER BY 
    City, Department

;WITH InitialGrouping AS 
(
    SELECT 
        GROUPING_ID(Person,City,Department) GROUPID,
        Person,
        City,
        Department,
        MAX(PersonAge) PersonAge,
        COUNT(*) NumRows
    FROM
        #People
    GROUP BY  
        Person, GROUPING SETS (City, Department, (City, Department))
) --SELECT * FROM InitialGrouping
SELECT 
    City,
    Department,
    AVG(PersonAge) AveragePersonAge,
    COUNT(DISTINCT Person) PersonCount
FROM 
    InitialGrouping
GROUP BY 
    GROUPID, City, Department
ORDER BY 
    City, Department

这两个示例在功能上并不等效。如果你看一下纽约的汇总行，你会发现第一个（33）的平均年龄是错误的，第二个（35）是正确的。第二种方法在功能上是正确的，但是，对于具有大量分组集的更复杂的示例，它绝对会在查询计划的串联步骤中爆发基数，从而导致性能不可接受。在这个简单示例中，您可以看到第一个查询将内容保持在 5 行，而第二个查询扩展到 8 行。在具有大量分组集的更复杂情况下，我看到基数在数百万行中扩展了很多倍。

有没有办法解决这个问题，可以防止事情扩展到超过最终输出的目标粒度？

下面的评论中提出了一个解决方案，该解决方案确实有效。这可能是最好的选择。它看起来像这样（我添加了一个额外的组）：

DROP TABLE IF EXISTS #People

CREATE TABLE #People 
(
    Person varchar(10), 
    City varchar(20), 
    Department varchar(1), 
    Badge int,
    PersonAge int
);

INSERT INTO #People VALUES ('Bob', 'New York', 'A', 1, 40),
                           ('Tim', 'New York', 'A', 1, 30),
                           ('Tim', 'New York', 'B', 2, 30),
                           ('Tom', 'New York', 'B', 1, 30),
                           ('Jim', 'Paris', 'A', 2, 50)


;WITH InitialGrouping AS 
(
    SELECT 
        Person,
        City,
        Department,
        Badge,
        ROW_NUMBER() OVER (PARTITION BY Person ORDER BY Person) OverallRowNum,
        ROW_NUMBER() OVER (PARTITION BY Person, City, Department, Badge ORDER BY Person) CityDepartmentBadgeRowNum,
        ROW_NUMBER() OVER (PARTITION BY Person, City, Department ORDER BY Person) CityDepartmentRowNum,
        ROW_NUMBER() OVER (PARTITION BY Person, City, Badge ORDER BY Person) CityBadgeRowNum,
        ROW_NUMBER() OVER (PARTITION BY Person, Badge, Department ORDER BY Person) DepartmentBadgeRowNum,
        ROW_NUMBER() OVER (PARTITION BY Person, City ORDER BY Person) CityRowNum,
        ROW_NUMBER() OVER (PARTITION BY Person, Department ORDER BY Person) DepartmentRowNum,
        ROW_NUMBER() OVER (PARTITION BY Person, Badge ORDER BY Person) BadgeRowNum,
        MAX(PersonAge) PersonAge
    FROM
        #People
    GROUP BY 
        Person, City, Department, Badge
) --SELECT * FROM InitialGrouping


SELECT 
    GROUPING_ID(City,Department,Badge),
    City,
    Department,
    Badge,
    CASE WHEN GROUPING_ID(City,Department,Badge) = 7 THEN AVG(CASE WHEN OverallRowNum = 1 THEN PersonAge ELSE NULL END)
         WHEN GROUPING_ID(City,Department,Badge) = 6 THEN AVG(CASE WHEN BadgeRowNum = 1 THEN PersonAge ELSE NULL END)
         WHEN GROUPING_ID(City,Department,Badge) = 5 THEN AVG(CASE WHEN DepartmentRowNum = 1 THEN PersonAge ELSE NULL END)
         WHEN GROUPING_ID(City,Department,Badge) = 4 THEN AVG(CASE WHEN DepartmentBadgeRowNum = 1 THEN PersonAge ELSE NULL END)
         WHEN GROUPING_ID(City,Department,Badge) = 3 THEN AVG(CASE WHEN CityRowNum = 1 THEN PersonAge ELSE NULL END)
         WHEN GROUPING_ID(City,Department,Badge) = 2 THEN AVG(CASE WHEN CityBadgeRowNum = 1 THEN PersonAge ELSE NULL END)
         WHEN GROUPING_ID(City,Department,Badge) = 1 THEN AVG(CASE WHEN CityDepartmentRowNum = 1 THEN PersonAge ELSE NULL END)
         WHEN GROUPING_ID(City,Department,Badge) = 0 THEN AVG(CASE WHEN CityDepartmentBadgeRowNum = 1 THEN PersonAge ELSE NULL END)
         END AS AveragePersonAge,
    COUNT(DISTINCT Person) PersonCount
FROM
    InitialGrouping
GROUP BY 
    GROUPING SETS (City, Department, Badge, (City, Department), (City,Badge), (Department,Badge), (City,Department,Badge),())
ORDER BY 
    City, Department