ROLLUP
ROLLUP можно использовать каждый раз, когда требуется анализ коллекции данных в одном измерении, но более, чем на одном уровне детальности. Можно включить ROLLUP в раздел GROUP BY с указанием списка выражений группировки. DB2 UDB сначала группирует данные по всем выражениям группировки, потом по всем выражениям, кроме последнего, потом по всем, кроме двух последних, и т.д. После группировки по только первому выражению система выполняет последнюю группировку, в которой образуется одна группа, включающая всю таблицу целиком. (Это звучит похоже на повторяющийся процесс, но реально все группировки выполняются одновременно за один проход по таблице.)
В следующем примере иллюстрируется мощность ROLLUP. Систему просят найти число людей и среднюю сумму дохода в каждом городе, графстве и штате, а также аналогичные показатели для всей таблицы переписи:
SELECT state, county, city, count (*) AS population, avg (income) AS avg_income FROM census GROUP BY ROLLUP (state, county, city);
Поскольку этот запрос не включает раздел ORDER BY, в результирующем наборе не гарантируется какая-либо упорядоченность строк. Однако, чтобы проиллюстрировать, каким образом вычислялся результат, в таблице 2 строки приведены в соответствующем порядке. Во-первых, имеются девять строк для групп, образованных группировкой по state, county и city; затем идут четыре строки для группировки по state и county с неопределенными значениями для city; затем - две строки для группировки по state с неопределенными значениями для county и city; и, наконец, одна завершающая строка для всей таблицы Census с неопределенными значениями для state, county и city.
Напомним, что порядок выражений в списке ROLLUP является существенным. Если одна разновидность группы логически содержится внутри другой (как county содержится внутри state), следует убедиться, что первой указывается самая включающая группа (state до county).
| FL | Dada | Hialeah | 2 | 38700 |
| FL | Dade | Miami | 2 | 36150 |
| *FL | Dade | (null) | 2 | 32950 |
| FL | Orange | Orlando | 2 | 42350 |
| FL | Orange | Taft | 2 | 29550 |
| TX | Harris | Baytown | 2 | 30650 |
| TX | Harris | Houston | 3 | 37800 |
| TX | Travis | Austin | 2 | 40450 |
| TX | Travis | (null) | 1 | 34800 |
| *FL | Dade | (null) | 6 | 35933 |
| FL | Orange | (null) | 4 | 35950 |
| TX | Harris | (null) | 5 | 34225 |
| TX | Travis | (null) | 3 | 38566 |
| FL | (null) | (null) | 10 | 35940 |
| TX | (null) | (null) | 8 | 36085 |
| (null) | (null) | (null) | 18 | 36000 |
Таблица 2. Результаты запроса с ROLLUP
Как показывает таблица 2, один запрос вычислил четыре разных уровня группировки, для чего потребовалось бы четыре запроса без ROLLUP. Поэтому возможность ROLLUP обеспечивает большие преимущества в отношение и удобства, и эффективности. Однако, если внимательно посмотреть на результаты запроса, можно заметить наличие некоторого беспорядка. Результат содержит две строки (помеченные звездочками) для графства Dade, штат Florida с неопределенным значением city. В одной из этих строк значение population равно двум, в другой - шести.
Первая из этих строк представляет группу на уровне state, county, city, включающую людей, проживающих в графстве Dade, Florida в сельской местности вне какого-либо города. (Из таблицы Census видно, что таких людей двое - Jim и Joan.) С другой стороны, вторая строка представляет группу уровня state, county, включающую всех людей, проживающих в графстве Dade. (В таблице Census представлено шесть таких человек.) Поэтому можно сказать, что неопределенное значение в первой строке означает "нет города", а неопределенное значение во второй строке означает "все города". Понятно, что требуется какой-то способ различать эти случаи путем указания уровня группировки, относящегося к каждой строке. DB2 UDB обеспечивает функцию, называемую grouping и служащую в точности для этих целей.
Функция grouping предназначена для использования в запросах, производящих более одного типа группировки. Аргументом функции является один из столбцов группировки, и функция возвращает значение "1", если указанный столбец слит с группой более высокого уровня. Таким образом, для тех специальных строк, в которых неопределенное значение city означает "все города", значение grouping(city) есть "1"; для обычных строк значение grouping(city) есть "0".
Функцию grouping можно использовать несколькими способами. Когда запрос с ROLLUP выполняется из прикладной программы, следует применять функцию grouping к каждому столбцу списка ROLLUP и считывать результаты в переменные основной программы для использования при интерпретации строк результата запроса.
Когда функция grouping возвращает "1", ее столбец- аргумент содержит неопределенное значение, которое следует интерпретировать как "все значения".
Если запрос выбирает значения для их непосредственного отображения на экране, можно использовать функцию grouping в выражении CASE, в котором указана специальная строка для представления "всех значений". Для этой цели можно использовать любую строку, но, конечно, стоит выбрать строку, которую легко отличить от допустимого значения данных. В следующем запросе выражения CASE используются для отображения строки "(-all-)" вместо неопределенного значения, когда функция grouping показывает, что неопределенное значение представляет "все значения":
SELECT CASE grouping(state) WHEN 1 THEN '(-all)' ELSE state END AS state, CASE grouping (county) WHEN 1 THEN '(-all-)' ELSE county END AS county, CASE grouping (city) WHEN 1 THEN '(-all-)' ELSE city END AS city, count(*) AS pop, avg(income) AS avg_income FROM census GROUP BY ROLLUP(state, county, city);
В таблице 3 показаны результата запроса. Как видно, легко отличить строку, которая представляет людей в графстве Dade с неопределенными городами от строки, представляющей группу всех людей в графстве Dade независимо от города.
| FL | Dade | Hialeah | 2 | 38700 |
| FL | Dade | Miami | 2 | 36150 |
| FL | Dade | (null) | 2 | 32950 |
| FL | Orange | Orlando | 2 | 42350 |
| FL | Orange | Taft | 2 | 29550 |
| TX | Harris | Baytown | 2 | 30650 |
| TX | Harris | Houston | 3 | 37800 |
| TX | Travis | Austin | 2 | 40450 |
| TX | Travis | (null) | 1 | 34800 |
| FL | Dade | (-all-) | 6 | 35933 |
| FL | Orange | (-all-) | 4 | 35950 |
| TX | Harris | (-all-) | 5 | 34225 |
| TX | Travis | (-all-) | 3 | 38566 |
| FL | (-all-) | (-all-) | 10 | 35940 |
| TX | (-all-) | (-all-) | 8 | 36085 |
| (-all-) | (-all-) | (-all-) | 18 | 36000 |
В запросе с ROLLUP можно также использовать разделы WHERE и HAVING. Например, следующий запрос позволяет найти число женщин и среднюю величину их дохода для каждого города, графства и штата, для которых в переписи зарегистрировано не меньше двух женщин:
SELECT CASE grouping(state) WHEN 1 THEN '(-all)' ELSE state END AS state, CASE grouping (county) WHEN 1 THEN '(-all-)' ELSE county END AS county, CASE grouping (city) WHEN 1 THEN '(-all-)' ELSE city END AS city, count(*) AS f_pop, avg(income) AS avg_f_income FROM census WHERE sex = 'F' GROUP BY ROLLUP(state, county, city) HAVING count(*) >= 2;
Результаты, приведенные в таблице 4, показывают, что данные переписи содержат двух или более женщин в одном городе (Houston), в трех графствах (Dade, Orange и Harris); в двух штатах (Florida и Texas) и во всей таблице целиком.
| TX | Harris | Houston | 2 | 44700 |
| FL | Dade | (-all-) | 2 | 40100 |
| FL | Orange | (-all-) | 2 | 36600 |
| TX | Harris | (-all-) | 2 | 44700 |
| FL | (-all-) | (-all-) | 4 | 38350 |
| TX | (-all-) | (-all-) | 3 | 39750 |
| (-all-) | (-all-) | (-all-) | 7 | 38816 |
