воскресенье, 27 марта 2011 г.

Разбираем магический CUBE с помощью GROUPING_ID

    Задача 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.


2 комментария:

  1. 3! = 6. И тут скорее всего зависимость как 2^n. Статья очень понравилась, все написано прозрачно.

    ОтветитьУдалить
    Ответы
    1. 2^n, где n - это кол-во полей учавствующих в "group by cube(region, city, market)"

      Удалить