包含多个GROUP BY的总体摘要

Question

假设我有一个名为census的表，其中包含以下信息：

COUNTRY     PROVINCE    CITY        POPULATION
==============================================
USA         California  Sacramento  1234
USA         California  SanFran     4321
USA         Texas       Houston     1111
USA         Texas       Dallas      2222
Canada      Ontario     Ottawa      3333
Canada      Manitoba    Winnipeg    4444

我正在建立一个国家/省级别的报告，它给了我以下内容：

SELECT country, province, SUM(population)
FROM census
GROUP BY country, province;
COUNTRY     PROVINCE    SUM(POPULATION)
=======================================
USA         California  5555
USA         Texas       3333
Canada      Ontario     3333
Canada      Manitoba    4444

我希望在报告中包含一个"总体摘要"行，以便最终结果看起来像：

COUNTRY     PROVINCE    SUM(POPULATION)
=======================================
USA         California   5555
USA         Texas        3333
Canada      Ontario      3333
Canada      Manitoba     4444
TOTAL                   16665

我熟悉ROLLUP，但我似乎找不到能满足我需求的组合。使用GROUP BY ROLLUP(country, province)包括我想要的总值，但它也包括大量我不关心的额外值。GROUP BY ROLLUP(country), province 也是如此

我该怎么做"总"记录呢
我目前正在用UNION ALL计算它，并用不同的GROUP BY重复第一个查询的90%，但因为第一个查询不是平凡的，所以结果是缓慢而丑陋的代码。

这里有一个SQL Fiddle，适合那些想玩它的人：http://sqlfiddle.com/#！4/12ad9/5

Answer 1

这正是GROUPING SETS表达式的设计目的：

SELECT country, province, SUM(population)
FROM census
GROUP BY GROUPING SETS
   ( (country, province),        -- first group by country and province
     ()                          -- then by (nothing), i.e. a total grouping
   );

请参阅SQL Fiddle

Answer 2

好吧，我终于想出了两种灵活的方法，不会让我觉得自己是一个糟糕的程序员。

---

第一种解决方案涉及CCD_ 8
我主要想做的是将表达式分为两个不同的级别：一个在总体级别，另一个在(country, province)级别。

如果我将查询分为两部分并使用UNION ALL，那么一半将具有GROUP BY country, province，另一半将缺少分组子句。如果我们喜欢的话，未分组的部分也可以表示为GROUP BY ()。这很快就会派上用场。

这给了我们类似的东西：

SELECT country, province, SUM(population)
FROM census
GROUP BY country, province
UNION ALL
SELECT NULL AS country, NULL AS province, SUM(population)
FROM census
GROUP BY ();

该查询有效，但扩展性不好。你需要做的计算越多，你花在重复自己上的时间就越多。

通过使用GROUPING SETS，我可以指定我希望数据以两种不同的方式分组：

SELECT country, province, SUM(population)
FROM census
GROUP BY GROUPING SETS( (country, province), () );

现在我们有进展了！但我们的结果排呢？我们如何检测它并相应地标记它？这就是GROUPING函数的作用所在。如果列由于GROUP BY语句而为NULL，它将返回1。

SELECT
    CASE
        WHEN GROUPING(country) = 1 THEN 'TOTAL'
        ELSE country
    END AS country,
    province,
    SUM(population),
    GROUPING(country) AS grouping_flg
FROM census
GROUP BY GROUPING SETS ( (country, province), () );

---

如果我们不喜欢GROUPING SETS方法，我们仍然可以使用传统的ROLLUP，但要做一些小的更改。

我们不是将每一列单独传递给ROLLUP，而是将列集合作为一个集合传递，方法是将它们封装在括号中。这使得列集被视为单个组，而不是多个组合。以下查询将为您提供与上一次相同的结果：

SELECT
    CASE
        WHEN GROUPING(country) = 1 THEN 'TOTAL'
        ELSE country
    END AS country,
    province,
    SUM(population),
    GROUPING(country) AS grouping_flg
FROM census
GROUP BY ROLLUP( (country, province) );

---

请随意尝试这两种方法
http://sqlfiddle.com/#！4/12ad9/102

Answer 3

在Oracle中，您可以使用having子句来完成此操作：

SELECT coalesce(c.country, 'Total') as province, c.country, SUM(c.population)
FROM census c
GROUP BY ROLLUP(c.country, c.province)
HAVING c.province is not null or
       c.province is null and c.country is null;

这是SQL Fiddle。

Answer 4

首先想到的是在应用rollup后过滤掉小计：

SELECT *
FROM   (SELECT   country, province, SUM (population)
        FROM     census
        GROUP BY ROLLUP (country, province))
WHERE  province IS NOT NULL OR country IS NULL;

---

通过在HAVING子句中使用GROUPING_ID，您可以更紧凑地完成相同的事情：

SELECT   country,
         province,
         SUM (population)
FROM     census
GROUP BY ROLLUP (country, province)
HAVING   GROUPING_ID (country, province) <> 1

---

而且，正如@Anssssss所指出的，您也可以在HAVING子句的第一个答案中使用WHERE子句中的标准：

SELECT   country, province, SUM (population)
FROM     census
GROUP BY ROLLUP (country, province)
HAVING   province IS NOT NULL OR country IS NULL

Answer 5

您可以使用Union:

SELECT country, province, SUM(population)
FROM census
GROUP BY country, province
UNION
SELECT
   'Total', '', SUM(population)
FROM census

Answer 6

我想出了一个使用Union将Total添加到结果末尾的sql。你可以在这里看到查询

SELECT country, province, SUM(population) as population, 0 as OrderBy
FROM census
GROUP BY country, province
UNION
SELECT country, province, population, 1 as OrderBy FROM (
  SELECT 'Total' as country, '' as province, SUM(population) as population
  FROM census
)
ORDER BY OrderBy;