Агрегатные функции

ROLLUP

При проведении аналитики часто требуется посмотреть на статистические показатели в разных разрезах. Например, для анализа средней зарплаты интересно посмотреть на среднюю зарплату по каждому подразделению и по всей организации в целом. Для решения этой задачи SQL поддерживает наборы группирования, реализованные через ROLLUP, CUBE, GROUPING SETS.

Данные, выбранные предложениями FROM и WHERE, группируются отдельно для каждого заданного набора группирования, затем для каждой группы вычисляются агрегатные функции как для простых предложений GROUP BY, и в конце результаты объединяются.

Конструкция ROLLUP в цикле выполняет группировку по подспискам переданного списка выражений, на каждой итерации убирая по одному выражению с конца списка, включая пустой список (вычисление агрегатных функций по всем строкам).

Например, для GROUP BY ROLLUP (выражение1, выражение2, выражение3) результатом запроса будет объединение результатов GROUP BY по

  • выражение1, выражение2, выражение3;
  • выражение1, выражение2;
  • выражение1;
  • все строки выборки.

Синтаксис ROLLUP:

GROUP BY ROLLUP (выражение1, выражение2, ... , выражениеN)

Рассмотрим пример. Получим минимальную и максимальную цены на товары в городах с идентификаторами 2 и 3 в следующих разрезах:

  • по каждой категории товаров в городе;
  • по всем магазинам города;
  • по всем категориям товаров всех городов.

Для начала выведем исходные данные:

SELECT sa.city_id, 
       p.category_id,
       pp.price
  FROM product_price pp
  JOIN product p
    ON p.product_id = pp.product_id
  JOIN store_address sa
    ON sa.store_id = pp.store_id
 WHERE sa.city_id IN (2, 3)
 ORDER BY sa.city_id,
          p.category_id,
          pp.price
city_idcategory_idprice
12621500.00
22622900.00
32623500.00
42717800.00
53310500.00

Воспользуемся ROLLUP:

SELECT sa.city_id, 
       p.category_id,
       min(pp.price) AS price_min,
       max(pp.price) AS price_max
  FROM product_price pp
  JOIN product p
    ON p.product_id = pp.product_id
  JOIN store_address sa
    ON sa.store_id = pp.store_id
 WHERE sa.city_id IN (2, 3)
 GROUP BY ROLLUP (sa.city_id, p.category_id)
 ORDER BY sa.city_id NULLS LAST, 
          p.category_id NULLS LAST
city_idcategory_idprice_minprice_max
12621500.0023500.00
22717800.0017800.00
32NULL17800.0023500.00
43310500.0010500.00
53NULL10500.0010500.00
6NULLNULL10500.0023500.00

Обрати внимание, что значения полей из GROUP BY, по которым не производилась группировка, равны NULL. Таким образом, результаты группировки по sa.city_id и p.category_id представлены в строках 1, 2 и 4. Результаты группировки по sa.city_id представлены в строках 3 и 5. Строка 6 является вычислением агрегатных функций по всему набору данных.

    ПредыдущаяСледующая