Использование ROLL UP/CUBE в сочетании с PIVOT [Oracle]

У меня есть таблица в Oracle, которая выглядит так:

year    month   customer
------------------------
2011    Jan     Smith
2011    Jan     Smith
2012    Feb     Howard
2013    Feb     Howard
...

Теперь хочу сделать так:

year    Jan     Feb     ...     Dec     ytotal
-----------------------------------------------
2011    3       1       ...     5       27
2012    1       4       ...     11      45
...                                     ...
2018    9       1       ...     1       21
mtotal  35      19              51      275

Где числа в каждой ячейке соответствуют РАЗЛИЧНОМУ количеству имен клиентов.

Когда я пытаюсь выполнить этот запрос:

SELECT DECODE(GROUPING(year), 1, 'mtotal:', year) year,
       DECODE(GROUPING(month), 1, 'ytotal:', month) month,
       COUNT(DISTINCT customer) AS cust_count
FROM mytable
GROUP BY ROLLUP(year, month)

Я получаю этот промежуточный результат:

year    month   cust_count
--------------------------
2011    Jan     3
2011    Feb     1
...
2011    Dec     5
2011    ytotal  27
2012    Jan     1
2012    Feb     4
...
2012    Dec     11
2012    ytotal  45
...
2018    Jan     9
2018    Feb     1
...
2018    Dec     1
2018    ytotal  21
mtotal  ytotal  275

Когда я использую это как подзапрос, выполните поворот:

SELECT * FROM (
    SELECT DECODE(GROUPING(year), 1, 'mtotal:', year) year,
           DECODE(GROUPING(month), 1, 'ytotal:', month) month,
           COUNT(DISTINCT customer) AS cust_count
    FROM mytable
    GROUP BY ROLLUP(year, month)
) 
PIVOT (
    COUNT(month) FOR month IN ('Jan', 'Feb', ..., 'Dec', 'ytotal')
)

Я не получаю ожидаемого результата. Пожалуйста, включите в ответ использование ROLL UP/CUBE и PIVOT.


sql rollup oracle group-by
person oikonomiyaki    schedule 18.04.2019    source источник

Ответы (1)


arrow_upward
1
arrow_downward

При группировке по rollup(year, month) у вас не было сумм за один и тот же месяц за разные годы. Поэтому я использовал cube и немного изменил ваш запрос, проверьте его:

select * 
  from (select case when grouping(year)  = 1 then 'ysum' else to_char(year) end year,
               case when grouping(month) = 1 then 'msum' else to_char(month) end month,
               count(distinct customer) as cnt
          from mytable
          group by cube(year, month) )
  pivot (sum(cnt) for month in ('Jan', 'Feb', 'Dec', 'msum'))
  order by year

демонстрация

Изменить:

Если в столбцах суммирования вам нужны суммы различных счетчиков, то сначала сделайте базовую группировку, а затем используйте куб. И поворот в конце. Агрегирующая функция в своде не важна, потому что вы уже подсчитали значения, по одному на каждую строку/столбец.

select * 
  from (
    select nvl(to_char(year), 'ys') year, nvl(to_char(month), 'ms') month, sum(cnt) cnt
      from (
        select year, month, count(distinct customer) cnt 
          from mytable 
          group by year, month)
      group by cube(year, month))
  pivot (sum(cnt) for month in ('Jan', 'Feb', 'Dec', 'ms')) 
  order by year
person Ponder Stibbons    schedule 18.04.2019
comment
Неправильно суммируется в конце строки и конце столбца. - person oikonomiyaki; 18.04.2019
comment
Он показывает количество различных клиентов. Если только один, то же самое было в каждом месяце, то в столбце суммы мы видим 1, а не 12. Если вы хотите, чтобы там были суммы, это можно сделать с помощью союзов. - person Ponder Stibbons; 18.04.2019