Задача CUBE взять множество группируемых колонок и создать под итоги для всех их возможных комбинаций
CUBE. Часто все комбиинации не нужны, а только некоторые из них.
Не всегда GROUPING SETS сможет получить только те итоги которые вы хотите. Или просто я не смог разобраться в нём.
Но мне удалось понять функцию GROUPING_ID, для того чтобы выбрать только нужные итоги я строю матрицу, значениями в которой будет 0 - ноль или единица.
Возьмем следующий пример: запрос в котором будет группировка по региону, городу, супермаркету. Итогом будет количество супермаркетов.
Пример надуманный, и в нем можно использовать и GROUPING SETS, но задача показать как пользоваться функцией GROUPING_ID, для того чтобы из всего множества итогов, генерируемых CUBE выбирать только нужные.
with
test_cube as (
select 'Восточный регион' region, 'Харьков' city, 'Супермаркет 1' market, 2 cnt from dual
union all
select 'Восточный регион' region, 'Харьков' city, 'Супермаркет 2' market, 3 cnt from dual
union all
select 'Восточный регион' region, 'Луганск' city, 'Супермаркет 1' market, 1 cnt from dual
union all
select 'Восточный регион' region, 'Луганск' city, 'Супермаркет 2' market, 1 cnt from dual
union all
select 'Южный регион' region, 'Ялта' city, 'Супермаркет 1' market, 2 cnt from dual
union all
select 'Южный регион' region, 'Симферополь' city, 'Супермаркет 2' market, 1 cnt from dual
union all
select 'Западный регион' region, 'Львов' city, 'Супермаркет 1' market, 4 cnt from dual
union all
select 'Западный регион' region, 'Львов' city, 'Супермаркет 2' market, 2 cnt from dual
union all
select 'Западный регион' region, 'Мукачево' city, 'Супермаркет 2' market, 1 cnt from dual
union all
select 'Северный регион' region, 'Чернигов' city, 'Супермаркет 1' market, 2 cnt from dual
union all
select 'Центральный регион' region, 'Киев' city, 'Супермаркет 1' market, 5 cnt from dual
union all
select 'Центральный регион' region, 'Киев' city, 'Супермаркет 2' market, 3 cnt from dual
)
select grp_id,
decode(grp_id,
0, 'Запись как есть в test_cube',
1, 'Кол-во в разрезе региона & города',
2, 'Кол-во в разрезе региона & магазина',
3, 'Кол-во в каждом регионе',
4, 'Кол-во в разрезе города & магазина',
5, 'Кол-во в каждом городе',
6, 'Кол-во каждого магазина',
7, 'Кол-во супермаркетов в стране'
) grp_descr,
region, city, market, sum_cnt
from (
select grouping_id(region, city, market) grp_id,
region, city, market, sum(cnt) sum_cnt
from test_cube
group by cube(region, city, market)
)
order by 1
Количество возможных итогов получится (n-1)! - факториал, где n - это количество полей указанных в CUBE, в примере использовано 3 поля (region, city, market), 3! = 8 групп, нумерация групп начинается с нуля. Группа 0 - это записи как есть(не итоговые), а с единицы это вид итога. Если бы в CUBE использовалось 6 полей то вариантов групп было бы 6! = 120.
Далее выведем только нужные нам итоги, например необходимо отобразить только:
- 0 - Запись как есть в test_cube
- 2 - Кол-во в разрезе региона & магазина
- 5 - Кол-во в каждом городе
- 6 - Кол-во каждого магазина
В секции HAVING укажем только нужные нам группы:
having grouping_id(region, city, market) in (0, 2, 5, 6)
Но самое главное это понять как вычисляются значения групп функцией grouping_id. Почему для группировки количества магазинов "только по городу" имеет значение 5, а группировка "в разрезе региона & магазина" имеет значение 2?
Следующая матрица наглядно демонстрирует как формируются значения для групп. Поля которые участвуют в итоге устанавливаются в ноль, остальные поля в единицу.
/* -- -- -- -- */
region -- 0 1 1 1 --
city -- 1 0 1 1 --
market -- 0 1 0 1 --
/* -- -- -- -- */
-- поля по которым нужен итог
-- устанавливаем в ноль,
-- остальные поля в единицу
-- ------------------------------------
-- 3 поля, сверху вниз пишем значения,
-- получаем десятичный код:
-- 2 5 6 7
-- ------------------------------------
-- 2 == 010 - Кол-во в разрезе региона & магазина
-- 5 == 101 - Итог по (Городу)
-- 6 == 110 - Итог по (Магазину)
Чтобы вывести итог 2 ("в разрезе региона & магазина") - поле city устанавливаем в единицу, поля region и market в 0.
Для итога 5 ("только по городу") - поле city устанавливаем в 0, поля region и market в 1.
В итоге 6 ("только по магазину") - поле market устанавливаем в 0, а остальные поля в 1 (region и city).
Использование матрицы в запросе:
with
test_cube as (
select 'Восточный регион' region, 'Харьков' city, 'Супермаркет 1' market, 2 cnt from dual
union all
select 'Восточный регион' region, 'Харьков' city, 'Супермаркет 2' market, 3 cnt from dual
union all
select 'Восточный регион' region, 'Луганск' city, 'Супермаркет 1' market, 1 cnt from dual
union all
select 'Восточный регион' region, 'Луганск' city, 'Супермаркет 2' market, 1 cnt from dual
union all
select 'Южный регион' region, 'Ялта' city, 'Супермаркет 1' market, 2 cnt from dual
union all
select 'Южный регион' region, 'Симферополь' city, 'Супермаркет 2' market, 1 cnt from dual
union all
select 'Западный регион' region, 'Львов' city, 'Супермаркет 1' market, 4 cnt from dual
union all
select 'Западный регион' region, 'Львов' city, 'Супермаркет 2' market, 2 cnt from dual
union all
select 'Западный регион' region, 'Мукачево' city, 'Супермаркет 2' market, 1 cnt from dual
union all
select 'Северный регион' region, 'Чернигов' city, 'Супермаркет 1' market, 2 cnt from dual
union all
select 'Центральный регион' region, 'Киев' city, 'Супермаркет 1' market, 5 cnt from dual
union all
select 'Центральный регион' region, 'Киев' city, 'Супермаркет 2' market, 3 cnt from dual
)
select grp_id,
decode(grp_id,
0, 'Запись как есть в test_cube',
1, 'Кол-во в разрезе региона & города',
2, 'Кол-во в разрезе региона & магазина',
3, 'Кол-во в каждом регионе',
4, 'Кол-во в разрезе города & магазина',
5, 'Кол-во в каждом городе',
6, 'Кол-во каждого магазина',
7, 'Кол-во супермаркетов в стране'
) grp_descr,
region, city, market, sum_cnt
from (
select grouping_id( /* -- -- -- -- */
region, -- 0 1 1 1 --
city, -- 1 0 1 1 --
market -- 0 1 0 1 --
/* -------------- */
-- поля по которым нужен итог
-- устанавливаем в ноль,
-- остальные поля в единицу
-- ------------------------------------
-- 3 поля, сверху вниз пишем значения,
-- получаем десятичный код:
-- 2 5 6 7
-- ------------------------------------
-- 2 == 010 - Кол-во в разрезе региона & магазина
-- 5 == 101 - Итог по (Городу)
-- 6 == 110 - Итог по (Магазину)
) grp_id,
region, city, market, sum(cnt) sum_cnt
from test_cube
having grouping_id(region, city, market) in (0, 2, 5, 6)
group by cube(region, city, market)
)
order by 1;
Результат запроса:
GRP_ID GRP_DESCR REGION CITY MARKET SUM_CNT
---------- ----------------------------------- ------------------ ----------- ------------- ----------
0 Запись как есть в test_cube Северный регион Чернигов Супермаркет 1 2
0 Запись как есть в test_cube Западный регион Львов Супермаркет 1 4
0 Запись как есть в test_cube Южный регион Ялта Супермаркет 1 2
0 Запись как есть в test_cube Восточный регион Луганск Супермаркет 1 1
0 Запись как есть в test_cube Восточный регион Харьков Супермаркет 1 2
0 Запись как есть в test_cube Центральный регион Киев Супермаркет 2 3
0 Запись как есть в test_cube Южный регион Симферополь Супермаркет 2 1
0 Запись как есть в test_cube Западный регион Львов Супермаркет 2 2
0 Запись как есть в test_cube Западный регион Мукачево Супермаркет 2 1
0 Запись как есть в test_cube Восточный регион Луганск Супермаркет 2 1
0 Запись как есть в test_cube Восточный регион Харьков Супермаркет 2 3
0 Запись как есть в test_cube Центральный регион Киев Супермаркет 1 5
2 Кол-во в разрезе региона & магазина Северный регион Супермаркет 1 2
2 Кол-во в разрезе региона & магазина Западный регион Супермаркет 1 4
2 Кол-во в разрезе региона & магазина Южный регион Супермаркет 1 2
2 Кол-во в разрезе региона & магазина Восточный регион Супермаркет 1 3
2 Кол-во в разрезе региона & магазина Центральный регион Супермаркет 2 3
2 Кол-во в разрезе региона & магазина Восточный регион Супермаркет 2 4
2 Кол-во в разрезе региона & магазина Западный регион Супермаркет 2 3
2 Кол-во в разрезе региона & магазина Южный регион Супермаркет 2 1
2 Кол-во в разрезе региона & магазина Центральный регион Супермаркет 1 5
GRP_ID GRP_DESCR REGION CITY MARKET SUM_CNT
---------- ----------------------------------- ------------------ ----------- ------------- ----------
5 Кол-во в каждом городе Мукачево 1
5 Кол-во в каждом городе Чернигов 2
5 Кол-во в каждом городе Харьков 5
5 Кол-во в каждом городе Ялта 2
5 Кол-во в каждом городе Львов 6
5 Кол-во в каждом городе Луганск 2
5 Кол-во в каждом городе Киев 8
5 Кол-во в каждом городе Симферополь 1
6 Кол-во каждого магазина Супермаркет 1 16
6 Кол-во каждого магазина Супермаркет 2 11
31 rows selected.
