理解CUBE和ROLLUP之间的区别

Question

我的作业要求我找出“每个日期写了多少张发票？”

我有点困惑，于是向我的教授求助。她给我发了一个查询，可以回答这个问题：“每种类型和版本的炉子已经建造了多少台？如果你想挑战自己，可以算出总共建造了多少台炉子，但不会得到额外的分数。”

这是她发送给我的查询：

SELECT STOVE.Type + STOVE.Version AS 'Type+Version'
, COUNT(*) AS 'The Count'
FROM STOVE
GROUP BY STOVE.Type + STOVE.Version WITH ROLLUP;

所以我调整了那个查询语句直到符合我的需求。这就是我想出来的：

SELECT InvoiceDt
, COUNT(InvoiceNbr) AS 'Number of Invoices' 
FROM INVOICE 
GROUP BY InvoiceDt WITH ROLLUP 
ORDER BY InvoiceDt ASC;

我得到了我想要的结果。

不管怎样，我决定研究ROLLUP子句，并从Microsoft的一篇文章开始。它说ROLLUP子句类似于CUBE子句，但以下两种方式使其区别于CUBE子句：

1. CUBE生成一个结果集，显示所选列值组合的所有聚合值。
2. ROLLUP生成一个结果集，显示所选列中值层次结构的聚合值。

因此，我决定用CUBE替换查询中的ROLLUP，看看会发生什么。它们产生了相同的结果。我猜这就是我感到困惑的地方。

好像如果你正在使用我这里的查询类型，那么两个子句之间没有任何实际区别。对吗？还是说我理解有误？当我读完Microsoft的文章时，我原本以为使用CUBE子句会得到不同的结果。

- krebshack

Answer 1

由于您只是在汇总单个列，因此您看不到任何区别。考虑以下示例：

ROLLUP (YEAR, MONTH, DAY)

使用ROLLUP，将产生以下输出：

YEAR, MONTH, DAY
YEAR, MONTH
YEAR
()

使用CUBE，它将包括以下内容：

YEAR, MONTH, DAY
YEAR, MONTH
YEAR, DAY
YEAR
MONTH, DAY
MONTH
DAY
()

CUBE 实质上包含每个节点的所有可能的汇总情况，而 ROLLUP 将保持层次结构不变（因此它不会跳过 MONTH 并显示 YEAR/DAY，而 CUBE 会）

这就是为什么您没有看到差异，因为您只有一个要汇总的列。

- Derek Kromm

---

有趣。我想知道为什么设计师们没有选择一个并添加一个额外的属性参数来控制它的行为。对于某个基本上做同样事情但略有不同的东西，拥有两个不同的名称是相当愚蠢的。 - mBrice1024

我现在完全清楚了！ - PKS

Answer 2

我们可以通过一个简单的例子来了解ROLLUP和CUBE之间的区别。考虑一张表，其中包含学生季度测试结果。在某些情况下，我们需要查看相应季度以及学生的总计。以下是示例表格：

SELECT * INTO #TEMP
FROM
(
    SELECT 'Quarter 1' PERIOD,'Amar' NAME ,97 MARKS
    UNION ALL
    SELECT 'Quarter 1','Ram',88 
    UNION ALL
    SELECT 'Quarter 1','Simi',76 
    UNION ALL
    SELECT 'Quarter 2','Amar',94 
    UNION ALL
    SELECT 'Quarter 2','Ram',82 
    UNION ALL
    SELECT 'Quarter 2','Simi',71 
    UNION ALL
    SELECT 'Quarter 3' ,'Amar',95 
    UNION ALL
    SELECT 'Quarter 3','Ram',83 
    UNION ALL
    SELECT 'Quarter 3','Simi',77
    UNION ALL
    SELECT 'Quarter 4' ,'Amar',91 
    UNION ALL
    SELECT 'Quarter 4','Ram',84 
    UNION ALL
    SELECT 'Quarter 4','Simi',79
)TAB

1. ROLLUP（可以找到与一列相对应的总数）

(a) 获取每个学生在所有季度中的总分。

SELECT * FROM #TEMP
UNION ALL
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY NAME,PERIOD 
WITH ROLLUP
HAVING PERIOD IS NULL AND NAME IS NOT NULL 
// Having is used inorder to emit a row that is the total of all totals of each student

以下是（a）的结果

（b）如果您需要获得每个季度的总分数

SELECT * FROM #TEMP
UNION ALL
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY PERIOD,NAME 
WITH ROLLUP
HAVING PERIOD IS NOT NULL AND NAME IS NULL

以下是(b)的结果

2. CUBE(在一次查询中查找季度和学生的总数)

SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY NAME,PERIOD 
WITH CUBE
HAVING PERIOD IS NOT NULL OR NAME IS NOT NULL

以下是CUBE的结果

现在您可能会想知道ROLLUP和CUBE的实时用途。有时，我们需要一份报告，在其中一次性查看每个学季的总计和每个学生的总计。这里有一个例子。

我稍微修改了上面的CUBE查询，因为我们需要两个总计的总数。

SELECT CASE WHEN PERIOD IS NULL THEN 'TOTAL' ELSE PERIOD END PERIOD,
CASE WHEN NAME IS NULL THEN 'TOTAL' ELSE NAME END NAME,
SUM(MARKS) MARKS
INTO #TEMP2
FROM #TEMP
GROUP BY NAME,PERIOD 
WITH CUBE

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + PERIOD + ']', 
               '[' + PERIOD + ']')
               FROM    (SELECT DISTINCT PERIOD FROM #TEMP2) PV  
               ORDER BY PERIOD    


DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
                 SELECT * FROM #TEMP2
             ) x
             PIVOT 
             (
                 SUM(MARKS)
                 FOR [PERIOD] IN (' + @cols + ')
            ) p;' 

EXEC SP_EXECUTESQL @query

现在您将获得以下结果

- Sarath Subramanian

---

你好。根据你的例子，可以说如果你需要找到两列（例如季度和学生）的聚合，则最好使用CUBE，而ROLLUP更适用于单个列吗？ - datanewbie96

Answer 3

这是因为你只有一个分组的列。 增加Group by InvoiceDt, InvoiceCountry（或其他能提供更多数据的字段）。 使用Cube将为每个InvoiceDt提供总和，并为每个InvoiceCountry提供总和。

- JeffO

Answer 4

您可以了解有关GROUPING SET、CUBE、ROLL UP的更多细节。简单来说，它们只是以某种方式扩展了GROUP BY + UNION ALL以进行聚合 :)

https://technet.microsoft.com/en-us/library/bb510427(v=sql.105).aspx

- o0omycomputero0o

Answer 5

所有投票的答案都很好。

---

一般来说，一个重要的区别是：

1. ROLLUP 规范中的 N 个元素对应于 N+1 个GROUPING SETS。
2. CUBE 规范中的 N 个元素对应于 2^N 个 GROUPING SETS。

---

更多阅读请参考我的关于Spark SQL的文章

例如：

store_id，product_type

rollup等同于

GROUP BY store_id,product_type
 GROUPING SETS (
(store_id,product_type)
,(product_type)
, ())

对于2（n）个分组列的分组集，有（n+1）= 3种列的组合方式。

立方体等同于

GROUP BY store_id,product_type
 GROUPING SETS (
(store_id,product_type)
,(store_id)
,(product_type)
, ())

对于按列分组的2（n）组，使用grouping set将有（2^n）= 4种列组合。

- Ram Ghadiyaram