CUBE
Мы обсудили полезность операции ROLLUP для группирования данных на разных уровнях детальности в одном измерении. (В примерах этим измерением была география.) При потребности анализировать данные путем их группировки в более, чем одном измерении, нужно использовать операцию CUBE.
Предположим, что нас интересует влияние пола и даты рождения на сумму дохода. Поскольку пол и дата рождения являются независимыми переменными, имеются четыре возможных способа группировки данных переписи:
- Группировка по sex и birthdate (типичный пример: женщины, родившиеся в 1955 г.)
- Группировка только по sex (типичный пример: женщины всех возрастов)
- Группировка только по birthdate (типичный пример: люди обоих полов, родившиеся в 1955 г.)
- Обработка таблицы как одной группы, содержащей оба пола и все даты рождения
Операция CUBE заставляет систему производить группировку по списку выражений всеми возможными способами. Например, если указывается GROUP BY CUBE (sex, year(birthdate)), то система будет формировать группы всеми четырьмя перечисленными выше способами. В запросе с CUBE, как и в запросе с ROLLUP, содержимое сгруппированного столбца появляется как неопределенное значение.
При взгляде на строку результата запроса с CUBE может оказаться трудно сказать, какой вид группы представляет строка. Например, строка с неопределенным значением birth_year может представлять группу с неопределенными годами рождения или группу, содержащую все возможные годы рождения. Чтобы различить группы, можно использовать функцию grouping.
В запросе с CUBE можно применять функцию grouping к любым столбцам или выражениям, используемым внутри операции CUBE. Как и в запросах с ROLLUP, если неопределенное значение выражения группировки имеет специальный смысл "все значения", функция grouping возвращает "1". Например, если grouping(sex) = 1, неопределенное значение столбца sex означает "оба пола". (Такая ситуация могла бы встретиться в строке, для которой данные группировались по году рождения, а не по полу.) Можно использовать функцию grouping внутри выражений CASE, чтобы выдавать на экран некоторое слово или символ для представления специального смысла "все значения".
В следующем примере для этой цели используется "(-all-)". Нужно помнить, что в выражении CASE все возможные значения выражения должны иметь совместимые типы. В примере было необходимо использовать функцию char внутри CASE для преобразования year(birthdate) из целого типа в тип символьных строк, чтобы его тип был совместим со строкой "(-all-)".
SELECT CASE grouping(sex) WHEN 1 THEN '(-all-)' ELSE sex END AS sex, CASE grouping(year(birthdate)) WHEN 1 THEN '(-all)' ELSE char(year(birthdate)) END AS birth_year, max(income) AS max_income FROM census GROUP BY CUBE(sex, year(birthdate));
В таблице 5 показаны результаты этого запроса. Операция CUBE, примененная к n измерениям, будет генерировать 2n различных видов групп. Так, GROUP BY CUBE (sex, year(birthdate)) произвела бы трехмерный результат, содержащий восемь видов групп. К каждой группе можно обычным образом применить раздел HAVING.
| F | 1955 | 46700 |
| F | 1956 | 36300 |
| F | 1957 | 26500 |
| F | (null) | 44700 |
| M | 1955 | 32100 |
| M | 1956 | 42500 |
| M | 1957 | 40200 |
| F | (-all-) | 46700 |
| M | (-all-) | 42500 |
| (-all-) | 1955 | 46700 |
| (-all-) | 1956 | 42500 |
| (-all-) | 1957 | 40200 |
| (-all-) | (null) | 44700 |
| (-all-) | (-all-) | 46700 |