模拟没有该关键字的汇总

Question

在Postgres中，我可以通过执行以下查询来模拟二维枢轴表：

SELECT ... FROM ...
GROUP BY
    ROLLUP(x,y,z), -- ROWS
    ROLLUP(a,b,c)  -- COLS

作为具体示例在dbfiddle 中：

​我知道可以使用 union all 进行一维 crodup ，例如：

SELECT a, b, SUM(c) FROM Input GROUP BY ROLLUP(a, b);
-->
SELECT NULL, NULL, SUM(c) FROM Input               UNION ALL
SELECT a,    NULL, SUM(c) FROM Input GROUP BY a    UNION ALL
SELECT a,    b,    SUM(c) FROM Input GROUP BY a, b;

但是，如果不访问 lollup 关键词？我们在此处使用Postgres（或MySQL）作为数据库，但是在您的答案中，请避免使用 Rollup 关键字。

Answer 1

您也可以使用 union all s模拟这一点，因为我们有：

GROUP BY ROLLUP(a,b)
--> GROUP BY (), (a), (a,b)

因此，使用 croup（a，b），lollup（x，y）我们将产品乘以乘以：

GROUP BY ROLLUP(a,b), (x,y)
--> GROUP BY (), a, (a,b) *cross product* (), x, (x,y)
--> (),() + (),x + (),x,y + a,() + a,x + a,x,y + a,b,(), a,b,x, a,b,x,y

因此，将其应用于原始问题：

WITH sales (Year, Half, Category, Product, Revenue) AS (
SELECT 2020,    'H1',   'Electronics',  'Phone',    200 UNION ALL
SELECT 2020,    'H1',   'Electronics',  'Computer', 300 UNION ALL
SELECT 2020,    'H2',   'Electronics',  'Phone',    100 UNION ALL
SELECT 2020,    'H2',   'Electronics',  'Computer', 175 UNION ALL
SELECT 2021,    'H1',   'Electronics',  'Phone',    109 UNION ALL
SELECT 2021,    'H1',   'Electronics',  'Computer', 32  UNION ALL
SELECT 2021,    'H2',   'Electronics',  'Phone',    93  UNION ALL
SELECT 2021,    'H2',   'Electronics',  'Computer', 111
)


SELECT SUM(Revenue) AS "sum", NULL AS category, NULL AS product, NULL AS year, NULL AS half FROM Sales GROUP BY (),() UNION ALL
SELECT SUM(Revenue), NULL, NULL, Year, NULL FROM Sales GROUP BY (),Year UNION ALL
SELECT SUM(Revenue), NULL, NULL, Year, Half FROM Sales GROUP BY (),Year,Half UNION ALL
SELECT SUM(Revenue), Category, NULL, NULL, NULL FROM Sales GROUP BY Category,() UNION ALL
SELECT SUM(Revenue), Category, NULL, Year, NULL FROM Sales GROUP BY Category,Year UNION ALL
SELECT SUM(Revenue), Category, NULL, Year, Half FROM Sales GROUP BY Category,Year,half UNION ALL
SELECT SUM(Revenue), Category, Product, NULL, NULL FROM Sales GROUP BY Category,Product,() UNION ALL
SELECT SUM(Revenue), Category, Product, Year, NULL FROM Sales GROUP BY Category,Product,Year UNION ALL
SELECT SUM(Revenue), Category, Product, Year, half FROM Sales GROUP BY Category,Product,Year,Half