使用Group By With Rollup和Count(Distinct)

Question

我在使用Group By With Rollup计算Distinct的数量时遇到了一个小问题。

问题在于Rollup汇总只是所有分组中Distinct值的总数，而不是所有分组的总结。

以下是一个测试场景，以说明我的意思：

Create Table #Test
(
    GroupId Int Not Null,
    Value   Int Not Null
)

Insert  #Test (GroupId, Value)
Values  (1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(1, 5),(1, 1),
        (2, 1),(2, 6),(2, 7),(2, 5),(2, 7),(2, 5),
        (3, 9),(3, 10),(3, 11),(3, 4),(3, 5),(3, 7),(3, 8),(3, 5),(3, 7),(3, 8)

对于这个特定的表，如果我运行以下查询：

Select  Case When Grouping(GroupId) = 1 Then 'Total:' Else Str(GroupId) End As GroupId, 
        Count(Distinct Value) Count
From    #Test
Group By GroupId With Rollup
Order By Grouping(GroupId), GroupId

我得到了以下的结果：

GroupId Count
-------------
1       5
2       4
3       7
Total:  11  

我期望总行的结果是16，但实际上我只得到了11——这是所有组中不同值的总数。

如果从查询中移除Distinct，则可以显示该Rollup的预期结果:

Select  Case When Grouping(GroupId) = 1 Then 'Total:' Else Str(GroupId) End As GroupId, 
        Count(Value) Count
From    #Test
Group By GroupId With Rollup
Order By Grouping(GroupId), GroupId

这将产生以下结果:

GroupId Count
-------------
1       7
2       6
3       10
Total:  23

这符合预期的分组总结。

我的问题是：对于 Count Distinct ， Rollup 是否正常？是否有其他像 Rollup 一样的选项可用于 Grouping 以显示16而不是上面例子中的11个？

- Siyual

---

你想在SELECT语句中获得DISTINCT结果，但是最后一行的所有值都需要显示吗？ - Mihai

我想要每个组的不同计数，并且汇总所有组的摘要。虽然组的计数显示正确，但我希望汇总行显示所有组中的总数（例如，显示16而不是11）。 - Siyual

Answer 1

您可以通过嵌套查询并使用技巧来获得您想要的结果：

select (Case When Grouping(GroupId) = 1 Then 'Total:' Else Str(GroupId) End) as GroupId, 
       Sum(Count) as Count
from (Select GroupId, 
             Count(Distinct Value) as Count
      From  #Test
      Group By GroupId
     ) t
Group By GroupId With Rollup
Order By Grouping(GroupId), GroupId;

第二个 group by 在逻辑上不进行聚合，因为每组只有一行。 它只是为了在 rollup 中获取您想要的值。

- Gordon Linoff

---

啊 - 我希望能够在不嵌套的情况下实现这一点。你有什么想法为什么 Rollup 会表现出这种行为？ - Siyual

@Siyual . . . Rollup 是在 count(distinct) 上进行的。许多人认为这种行为是一件好事。不同计数的总和与事物的并集的不同计数不同。 - Gordon Linoff

啊，这很有道理。所以，Rollup本质上将相同的聚合应用于所有组作为一个整体，而不是将相同的聚合应用于每个组的结果。这就解释了为什么Count(Distinct)会表现出这种行为。感谢您的帮助！ - Siyual

@Siyual . . . жғідёҖжғі min()гҖҒmax() жҲ– avg() еә”иҜҘеҸ‘з”ҹд»Җд№ҲпјҢиҝҷж ·е°ұдјҡеҫҲжңүж„Ҹд№үдәҶгҖӮ - Gordon Linoff

Answer 2

创建测试数据：

DECLARE @Test TABLE
(
    GroupId Int Not Null,
    Value   Int Not Null
)

Insert  @Test 
(GroupId, Value)
Values  (1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(1, 5),(1, 1),
        (2, 1),(2, 6),(2, 7),(2, 5),(2, 7),(2, 5),
        (3, 9),(3, 10),(3, 11),(3, 4),(3, 5),(3, 7),(3, 8),(3, 5),(3, 7),(3, 8)

我将第三列更改为按组ID和值分组的独特组

Select  Case When Grouping(GroupId) = 1 Then 'Total:' Else Str(GroupId) End As GroupId, 
       Count(DISTINCT Value) As Count,
        Count(Value) AS Count2,
        Count(DISTINCT (GroupId * 10) + Value) AS Count3
From    @Test
Group By GroupId With Rollup
Order By Grouping(GroupId), GroupId

以下是输出结果：

GroupId Count Count2 Count3
1       5     7      5
2       4     6      4
3       7     10     7
Total:  11    23     16

- Kevin Cook