GROUP BY ROLLUP / CUBE

Давайте посмотрим на мою вымышленную прогрессию заработной платы по сравнению с вымышленной подругой, которая выбрала другой путь карьеры (обратите внимание на повышение зарплаты в 2011 году):

Теперь мы привыкли собирать статистику с помощью простой группировки и простых агрегатных функций. Например, давайте посчитаем, сколько Лукас и Том заработали в среднем за последние несколько лет:

Это покажет, что Лукас заработал больше:

  + -------- + ----------- +
 | СОТРУДНИК | AVG (ЗАРПЛАТА) |
 + -------- + ----------- +
 | Лукас |  132500 |
 Том |  91500 |
 + -------- + ----------- +

Так что, наверное, интересно узнать, что они в среднем заработали в какой компании:

И сразу становится ясно, где большие деньги, и что Том принял плохое решение.

  + ---------- + -------- + ----------- +
 | КОМПАНИЯ | СОТРУДНИКИ | AVG (SALARY) |
 + ---------- + -------- + ----------- +
 | JOOQ | Лукас |  225000 |
 | СмартСофт | Лукас |  92500 |
 | SmartSoft | Том |  93000 |
 | SoftSkills | Лукас |  80000 |
 | SoftSkills | Том |  90000 |
 + ---------- + -------- + ----------- +

СВЕРНУТЬ

Добавляя поля группировки, мы «теряем» некоторую информацию об агрегации. В приведенных выше примерах общая средняя заработная плата на одного работника больше не доступна непосредственно из результата. Это очевидно, учитывая алгоритм группировки. Но в красивых отчетах мы часто хотим отображать и заголовки групп. Здесь вступают в игру ROLLUP, CUBE (и GROUPING SETS). Рассмотрим следующий запрос:

Приведенная выше функция свертки теперь добавит дополнительные строки в набор результатов группировки, содержащие полезные агрегированные значения. В этом случае, когда мы «свернем зарплату компании», мы получим среднее значение оставшихся полей группировки, то есть среднее значение на одного сотрудника:

  + ---------- + -------- + ----------- +
 | КОМПАНИЯ | СОТРУДНИКИ | AVG (SALARY) |
 + ---------- + -------- + ----------- +
 | SmartSoft | Том |  93000 |
 | SoftSkills | Том |  90000 |
 | {ноль} | Том |  91500 |
 | JOOQ | Лукас |  225000 |
 | СмартСофт | Лукас |  92500 |
 | SoftSkills | Лукас |  80000 |
 | {null} | Лукас |  132500 |
 + ---------- + -------- + ----------- +

Обратите внимание, что эти строки содержат ту же информацию, что и в первом запросе, где мы группировали только по сотруднику … Это становится еще интереснее, когда мы добавляем больше группирующих полей в функцию свертки:

Как видите, порядок группировки полей важен в функции свертки. Результат этого запроса теперь также добавляет общую среднюю зарплату, выплачиваемую всем сотрудникам во всех компаниях.

  + ---------- + -------- + ----------- +
 | КОМПАНИЯ | СОТРУДНИКИ | AVG (SALARY) |
 + ---------- + -------- + ----------- +
 | SmartSoft | Том |  93000 |
 | SoftSkills | Том |  90000 |
 | {ноль} | Том |  91500 |
 | JOOQ | Лукас |  225000 |
 | СмартСофт | Лукас |  92500 |
 | SoftSkills | Лукас |  80000 |
 | {null} | Лукас |  132500 |
 | {ноль} | {нуль} |  112000 |
 + ---------- + -------- + ----------- +

Чтобы идентифицировать строки итогов для отчетов, вы можете использовать функцию GROUPING () в DB2, Oracle, SQL Server и Sybase SQL Anywhere. В Oracle и SQL Server есть еще более полезная функция GROUPING_ID ():

Он документирует, на каком «уровне группировки» функции свертки была создана текущая строка:

  + ---- + ---------- + -------- + ----------- +
 |  ID | КОМПАНИЯ | СОТРУДНИКИ | AVG (SALARY) |
 + ---- + ---------- + -------- + ----------- +
 |  0 | СмартСофт | Том |  93000 |
 |  0 | SoftSkills | Том |  90000 |
 |  1 | {null} | Том |  91500 |
 |  0 | jOOQ | Лукас |  225000 |
 |  0 | СмартСофт | Лукас |  92500 |
 |  0 | SoftSkills | Лукас |  80000 |
 |  1 | {null} | Лукас |  132500 |
 |  3 | {null} | {null} |  112000 |
 + ---- + ---------- + -------- + ----------- +

  CUBE 
  Функция куба работает аналогично, за исключением того, что порядок полей группировки куба становится неактуальным, так как все комбинации группировки объединяются.  Это немного сложно выразить словами, поэтому давайте запустим это: 
1
2
3
4
5
6
7
with data as (...)
 
select grouping_id(employee, company) id, company, employee, avg(salary)
 
from data
 
group by cube(employee, company)
  В следующем результате вы получите: 
  GROUPING_ID () = 0: в среднем по компании и сотруднику.  Это нормальный результат группировки 
  GROUPING_ID () = 1: в среднем на сотрудника 
  GROUPING_ID () = 2: в среднем по компании 
  GROUPING_ID () = 3: общее среднее 
  + ---- + ---------- + -------- + ----------- +
 |  ID | КОМПАНИЯ | СОТРУДНИКИ | AVG (SALARY) |
 + ---- + ---------- + -------- + ----------- +
 |  3 | {null} | {null} |  112000 |
 |  2 | jOOQ | {null} |  225000 |
 |  2 | SmartSoft | {null} |  92800 |
 |  2 | SoftSkills | {null} |  86000 |
 |  1 | {null} | Том |  91500 |
 |  0 | СмартСофт | Том |  93000 |
 |  0 | SoftSkills | Том |  90000 |
 |  1 | {null} | Лукас |  132500 |
 |  0 | jOOQ | Лукас |  225000 |
 |  0 | СмартСофт | Лукас |  92500 |
 |  0 | SoftSkills | Лукас |  80000 |
 + ---- + ---------- + -------- + ----------- +

  Другими словами, используя функцию CUBE (), вы получите результаты группировки для каждой возможной комбинации полей группировки, переданных в функцию CUBE (), что приведет к 2 ^ n GROUPING_ID () для n «кубированных» полей группировки 
  Поддержка в JOOQ 
  В jOOQ 2.0 появилась поддержка этих функций.  Если вы хотите перевести последний выбор в jOOQ, вы получите примерно такой код Java: 






01
02
03
04
05
06
07
08
09
10
11



// assuming that DATA is an actual table...
 
create.select(
 
         groupingId(DATA.EMPLOYEE, DATA.COMPANY).as("id"),
 
         DATA.COMPANY, DATA.EMPLOYEE, avg(SALARY))
 
      .from(DATA)
 
      .groupBy(cube(DATA.EMPLOYEE, DATA.COMPANY));







  С этим мощным инструментом вы готовы ко всем этим модным отчетам и обзорам данных.  Для получения более подробной информации читайте о функциях ROLLUP (), CUBE () и GROUPING SETS () на странице документации по SQL Server, которая довольно хорошо объясняет это: 
  http://msdn.microsoft.com/en-us/library/bb522495.aspx 
  Ссылка: GROUP BY ROLLUP / CUBE от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и JOOQ . 
  Статьи по Теме : 

  Навигация по схеме базы данных в Java 
  Проблемы с ORM 
  SQL или NOSQL: вот в чем вопрос? 
  Что такое NoSQL?