group by, grouping sets, and total count few times.

Всем доброго времени суток, попытаюсь объяснить "в чем проблема".

Все мы знаем метод, когда в одном запросе надо подсчитать несколько необходимых "количеств" как

select count(1) as totalCount,
         count(case when ... then 1 end) as countType1,
         count(case when ... then 1 end) as countType2,
         ...
         count(case when ... then 1 end) as countTypeN

  from cteSomeResult

Всем мы знаем что предложение group by grouping sets сформирует множество груп, но "итого" можно найти функцией grouping_id().
При помощи дополнительного case-оператора можно заставить считать итоговые суммы только для одной строки.

Но дело в том, что колонки всё равно будут возвращены и для подовляеющего числа строк в результате они будут "нуловые".

Мне бы хотелось что бы "итоговая группа" повторялась как будто есть какой-то "гибрит с pivot/unpivot операторами".

что-то в духе

select count(1) as totalCount, 0 as type
union all
select count(case when ... then 1 end) as countType, 1 as type
union all
select count(case when ... then 1 end) as countType, 2 as type

И как такое возможно сделать (без реального юниона и многократного пересчёта?

Заранее спасибо.

Приветствую NIIK, а можно пояснить задачу на примере тестовых данных и желаемого результата?

Заранее скажу что варинат
1) подсчитать значения для всех груп одним запросом
2) подсчитать "итого" и все колонки по всем условиям авторым запросом
3) сделать unpivot второму запросу
4) объеденить результаты union-ом

я проверил. Вариант работает медленее из-за двойного ображения к одному CTE.
Вариант с лишними столбцами работает быстрее. Крайний пример позволяет просто обойтись одним union-оператором и "двумя подзапросами".

Паганель,

попробую накатать понятный пример

Вот такой вариант работает быстрее хоть и много лишних колонок

with cteSearchResult
as
(
select 1 as a1, 1 as a2, 1 as a3, 1 as b 
union all
select 1 as a1, 2 as a2, 1 as a3, 1 as b 
union all
select 1 as a1, 2 as a2, 1 as a3, 2 as b
union all
select 2 as a1, 1 as a2, 2 as a3, 3 as b 
union all
select 2 as a1, 1 as a2, 1 as a3, 1 as b 

)

select a1,
       a2,
	   a3,
       count(1) as cnt,
	   
	   grouping_id(a1, a2, a3) as gr,

	   case grouping_id(a1, a2, a3) when 7 then count(case b when 1 then 1 end) end as cnt1,	   
	   case grouping_id(a1, a2, a3) when 7 then count(case b when 2 then 1 end) end as cnt2,	   
	   case grouping_id(a1, a2, a3) when 7 then count(case b when 3 then 1 end) end as cnt3
	    
  from cteSearchResult r
 group 
    by grouping sets
	   (
	    (),
		a1,
		(a2, a3)
	   )
 order 
    by gr

результат нужен в такой форме, но что бы не искользовал два раза CTE с результатом поиска

with cteSearchResult
as
(
select 1 as a1, 1 as a2, 1 as a3, 1 as b 
union all
select 1 as a1, 2 as a2, 1 as a3, 1 as b 
union all
select 1 as a1, 2 as a2, 1 as a3, 2 as b
union all
select 2 as a1, 1 as a2, 2 as a3, 3 as b 
union all
select 2 as a1, 1 as a2, 1 as a3, 1 as b 

),
cteTotalCount as
(
select count(1) as cnt,
       count(case b when 1 then 1 end) as cnt1,	   
	   count(case b when 2 then 1 end) as cnt2,	   
	   count(case b when 3 then 1 end) as cnt3
  from cteSearchResult

)

select a1,
       a2,
	   a3,
	   'grouping' as cntType,
       count(1) as cnt
	    
  from cteSearchResult r
 group 
    by grouping sets
	   (
	    --(),
		a1,
		(a2, a3)
	   )

union all 

select null, null, null,
       cntType,
	   cntP
  from cteTotalCount
  unpivot (cntP for cntType in (cnt, cnt1, cnt2, cnt3)) as unpvt

NIIIK,

так что ли:

--
-- Тестовые данные:
with
  cteSearchResult as
    (
      select 1 as a1, 1 as a2, 1 as a3, 1 as b 
      union all
      select 1 as a1, 2 as a2, 1 as a3, 1 as b 
      union all
      select 1 as a1, 2 as a2, 1 as a3, 2 as b
      union all
      select 2 as a1, 1 as a2, 2 as a3, 3 as b 
      union all
      select 2 as a1, 1 as a2, 1 as a3, 1 as b 
    )
--
-- Основной запрос:
select a1, a2, a3, b,
       case grouping_id(a1, a2, a3, b) 
         when 15 then 'cnt'
         when 14 then
           case b
             when 1 then 'cnt1'
             when 2 then 'cnt2'
             when 3 then 'cnt3'
           end 
         else 'grouping'
       end as cntType,
       count(1) as cnt	    
  from cteSearchResult r
 group by grouping sets ((), a1, (a2, a3), b);

on-line проверка на sqlfiddle.com
???

Добрый Э - Эх,

Не, несовсем. В "Б" более сложные условия из разных полей с проверками на значение "или нул". При этом там условия могут быть перекрывающие. Например, "мужчины" и "возраст 18-27 лет".

Третья итерация всё той же темы (Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE). Примеры становятся всё более развёрнутыми, но всё равно оказывается, что в реальности "более сложные условия", которые, видимо, надо додумывать самим. Глядишь, к десятой итерации наконец выяснится, что же именно нужно сделать.

NIIIK,

не, ну с таким подходом к постановке вопросов - тебе прямая дорога на "битву экстрасенсов"...
либо, рассказывай уже всё как есть, без сокращений, упрощений и упущений...