Общее резюме с несколькими GROUP BY

Допустим, у меня есть стол под названием 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 и повторяя 90% первого запроса с другим GROUP BY, но поскольку первый запрос нетривиален, результатом будет медленный и некрасивый код.

Вот SQL Fiddle для тех, кто хочет поиграть с этим: http://sqlfiddle.com/

Источник

6 ответов

Решение

Хорошо, я наконец-то придумал два гибких подхода, которые не заставляют меня чувствовать себя ужасным программистом.

Первое решение включает в себя GROUPING SETS,
По сути, я пытаюсь сгруппировать выражение на двух разных уровнях: один на общем уровне и один на (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 приходит функция. Возвращает 1, если столбец равен NULL из-за оператора GROUP BY.

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/

Источник

Это именно то, что 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-скрипку

Источник

В 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.

Источник

Первое, что приходит на ум, это отфильтровать промежуточные итоги после rollup применены:

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

Вы можете сделать то же самое немного более компактно, используя GROUPING_ID в HAVING пункт:

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

И, как указал @Anssssss, вы также можете использовать критерии из WHERE пункт в первом ответе в HAVING пункт:

SELECT   country, province, SUM (population)
FROM     census
GROUP BY ROLLUP (country, province)
HAVING   province IS NOT NULL OR country IS NULL
Источник

Вы можете использовать Союз:

SELECT country, province, SUM(population)
FROM census
GROUP BY country, province
UNION
SELECT
   'Total', '', SUM(population)
FROM census
Источник

Я придумал sql, используя Union, чтобы добавить Total в конец ваших результатов. Вы можете увидеть запрос здесь

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;
Источник
Другие вопросы по тегам