sql - 以两种不同的方式汇总数据

Question

假设我有一个存储客户名称、设备编号和一些标志的表。一些样本数据看起来像这样:

[CustomerName], [Device], [A], [B], [C]
'Customer A',   '1234',   1,   0,   0
'Customer A',   '1235',   1,   1,   1
'Customer A',   '1234',   1,   1,   1
'Customer B',   '1236',   1,   1,   0
'Customer B',   '1236',   1,   0,   0
'Customer C',   '1235',   1,   1,   1

我需要一份同时呈现 3 种类型数据的报告:

• 每个标志的总行数
• 每个客户每个标志的行数
• 出现在带有每个标志的任何行中的唯一设备的数量

使用 ROLLUP 创建前两项相对容易，但最后一项比较棘手。我能弄清楚如何做到这一点的唯一方法是使用 UNION:

SELECT CASE WHEN (GROUPING([CustomerName]) = 1) THEN 'ALL CUSTOMERS' 
       ELSE [CustomerName] END [CustomerName], 
       COUNT(*) [Rows], 
       SUM(CAST([A] AS [int])) [A], 
       SUM(CAST([B] AS [int])) [B], 
       SUM(CAST([C] AS [int])) [C]
  FROM [MyTable]
 GROUP BY [CustomerName] WITH ROLLUP
 UNION
SELECT 'UNIQUE DEVICES', 
       COUNT(*),
       SUM(CASE WHEN [A] > 0 THEN 1 ELSE 0 END),
       SUM(CASE WHEN [B] > 0 THEN 1 ELSE 0 END),
       SUM(CASE WHEN [C] > 0 THEN 1 ELSE 0 END)
  FROM (
    SELECT [Device],
           COUNT(*) [Rows], 
           SUM(CAST([A] AS [int])) [A], 
           SUM(CAST([B] AS [int])) [B], 
           SUM(CAST([C] AS [int])) [C]
      FROM [MyTable]
     GROUP BY [Device] ) q

结果:

[CustomerName],   [Rows], [A], [B], [C]
'ALL CUSTOMERS',  6,      6,   4,   3
'Customer A',     3,      3,   2,   2
'Customer B',     2,      2,   1,   0
'Customer C',     1,      1,   1,   1
'UNIQUE DEVICES', 3,      3,   3,   2

这对我现在的目的来说已经足够好了，但是之前没有太多使用 ROLLUP 或 CUBE 的经验，我想我会尝试看看我是否可以摆脱 UNION 并拥有一个 SELECT。来自examples on MSDN , ROLLUP 支持多个分组，但我不知道如何让它产生我想要的结果。任何人都可以演示如何使用单个 SELECT 来获得这些结果吗？

Answer 1

I need a report that presents 3 types of data simultaneously:

这并不一定意味着您需要一个查询。您想要的结果在逻辑上是不同的。还有其他方法可以将它们组合在一起，但最简单的查询是将这三者结合起来:

select   CustomerName
       , count(*) as Rows
       , sum(cast(A as int)) as A
       , sum(cast(B as int)) as B
       , sum(cast(C as int)) as C
from T
group by CustomerName
UNION
select   'ALL CUSTOMERS'
       , count(*) as Rows
       , sum(cast(A as int)) as A
       , sum(cast(B as int)) as B
       , sum(cast(C as int)) as C
from T
UNION
select   'UNIQUE DEVICES'
       , count(*) as Rows
       , sum(A) as A
       , sum(B) as B
       , sum(C) as C
from (
       select   Device
              , max(cast(A as int)) as A
              , max(cast(B as int)) as B
              , max(cast(C as int)) as C
       from T
       group by Device
) as A

整个 rollup/cube 总是一场奇特的灾难。如果你从它的细节中受益，很好，使用它，否则我建议给它一个广泛的铺位。没有您的解释，我无法轻易理解您的查询；相比之下，我敢说我的前两部分无需解释就很容易理解。

您可以创建三个 UNION 组件中每一个的 View ，并以其他有趣的方式排列它们(除了 One Big Query 之外)。

Can anyone demonstrate how to use a single SELECT to get these results?

你的查询和我的都是都是单个SELECT语句； UNION 是 SELECT 的一部分。我想你的意思是，如果没有 UNION 是否可以完成，答案是否定的。或者，也许“是的，但你不会喜欢它”。正如您自己表述的问题，您希望以两种不同的方式 总结数据。这两种方式在逻辑上是不同的:按客户和按标志。 UNION 是连接两个关系的关系代数运算符，这正是您所要求的! :-)

SQL 服务器还支持 OVER clause ;它允许更复杂的分组类型，并且您可以在没有 UNION 的情况下让它产生所需的结果。但我怀疑 SQL 是否会像您目前所拥有的那样易于理解和更改，或者只有几行。