Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Всем доброго времени суток, попытаюсь объяснить "в чем проблема".

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

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


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

Заранее спасибо.
18 фев 13, 23:38    [13945579]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Приветствую NIIK, а можно пояснить задачу на примере тестовых данных и желаемого результата?
19 фев 13, 00:17    [13945687]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Заранее скажу что варинат
1) подсчитать значения для всех груп одним запросом
2) подсчитать "итого" и все колонки по всем условиям авторым запросом
3) сделать unpivot второму запросу
4) объеденить результаты union-ом

я проверил. Вариант работает медленее из-за двойного ображения к одному CTE.
Вариант с лишними столбцами работает быстрее. Крайний пример позволяет просто обойтись одним union-оператором и "двумя подзапросами".
19 фев 13, 00:20    [13945698]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Паганель,

попробую накатать понятный пример
19 фев 13, 00:20    [13945700]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Вот такой вариант работает быстрее хоть и много лишних колонок

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
19 фев 13, 00:30    [13945723]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
результат нужен в такой форме, но что бы не искользовал два раза 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
19 фев 13, 00:36    [13945754]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
Добрый Э - Эх
Guest
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
???
19 фев 13, 06:35    [13946151]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Добрый Э - Эх,

Не, несовсем. В "Б" более сложные условия из разных полей с проверками на значение "или нул". При этом там условия могут быть перекрывающие. Например, "мужчины" и "возраст 18-27 лет".
19 фев 13, 10:57    [13947120]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Третья итерация всё той же темы (Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE). Примеры становятся всё более развёрнутыми, но всё равно оказывается, что в реальности "более сложные условия", которые, видимо, надо додумывать самим. Глядишь, к десятой итерации наконец выяснится, что же именно нужно сделать.
19 фев 13, 11:25    [13947279]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
Добрый Э - Эх
Guest
NIIIK,

не, ну с таким подходом к постановке вопросов - тебе прямая дорога на "битву экстрасенсов"...
либо, рассказывай уже всё как есть, без сокращений, упрощений и упущений...
19 фев 13, 12:04    [13947588]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Гость333
Глядишь, к десятой итерации наконец выяснится, что же именно нужно сделать.

Эмулировать OLAP на TSQL, например ? )
19 фев 13, 12:06    [13947611]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Гость333,
Флуд!

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

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

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

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

Glory
Эмулировать OLAP на TSQL, например ? )

вы сами себя хоть раз банили?
Вы что думаете я первый раз об ОЛАП слышу?
- технически не подходит (много условий, которые не просто "преагреация", я бы его даже "сэмулировать бы смог" по нужным измерениям сохранив данные в таблицы предподсчитанные, если бы это было возможно, но когда там ещё включается ФТС, который я совсем не знаю как ввести в измерения куба, сразу понятно что ОЛАП отпадает)
- ОЛАП всё равно там делать никто не даст
- Я и так добился "изменений" первоначальной функциональности, потому что-то что-то не смог решить "без временной таблицы и т. п. предыдущих тем"
- вы можите посмотреть ответы таких польхователей как Mnior и давать ответы в этом духе, что бы не провоцировать топик-стартера? В вашем ответе смысла ноль.

мой вопрос кому-то непонятен, тест-кейс где-то неудачен?
19 фев 13, 12:45    [13947858]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
Glory
Member

Откуда:
Сообщений: 104760
NIIIK
- вы можите посмотреть ответы таких польхователей как Mnior и давать ответы в этом духе, что бы не провоцировать топик-стартера? В вашем ответе смысла ноль.

Вообще то это был ответ для Гость333.
NIIIK
Вы что думаете я первый раз об ОЛАП слышу?

Откуда я знаю ? Может во второй.

NIIIK
- технически не подходит (много условий, которые не просто "преагреация", я бы его даже "сэмулировать бы смог" по нужным измерениям сохранив данные в таблицы предподсчитанные, если бы это было возможно, но когда там ещё включается ФТС, который я совсем не знаю как ввести в измерения куба, сразу понятно что ОЛАП отпадает)

Т.е. вы
- провели анализ желаний клиента
- составили список запросов
- создали схему под эти запросы
?
И "сразу понятно что ОЛАП отпадает" ?

NIIIK
мой вопрос кому-то непонятен, тест-кейс где-то неудачен?

Ваш запрос опять из разряда - какие буквы и слова мне использовать в тексте запроса, чтобы план выполнения всегда был оптимальным.
19 фев 13, 12:56    [13947925]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
Добрый Э - Эх
Guest
                        на правах легкого троллинга и небольшого оффтопа

NIIIK
- мой частный пример вполне себе полноценный ... интересует конкретный вопрос на этот вопрос ... тест-кейс где-то неудачен
и мой частный ответ тоже вполне себе полноценный и полностью удовлетворяет твоему частному полноценному примеру. считаю, что ответ на этот ответ тобою получен и мой ответ удачен в полном соответствии с твоим удачным тест-кейсом.
19 фев 13, 13:15    [13948058]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Glory
И "сразу понятно что ОЛАП отпадает" ?

Однозначно.
Именно в такой ситуации.
Если хотите можете дополнительном мно ответить как бы вы делали ОЛАП для
- условия поиска с FTS
- условия которые "ложатся в case-оператор" я ещё попытался бы вывести "в измерения" сделав это как "атрибут", но это получилось бы не для всех случаев.
- существующие сущности таблицы и т. п. менять я НЕ могу, но в целом вычисляемые столбцы (персистед или обычные подсчитанные триггерами), новые таблицы "можно попробовать".

Эти вопросы в принципе "оффтоп"

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

Я понимаю что запрос по сути "отчётный", а не "пользовательский". Но работать его надо заставить "максимально быстро".
Вся работа с объяснениями "по архитектуре и т. п." ведётся. Можете "незабивать себе голову этим вопросом". Не надо быть увереным что "знаете многочисленные детали".

Я, если честно (для общего случая, а не для этого), сомневаюсь что ОЛАП поможет если нельзя сделать "большое преагрегирование". Т. е. по сути хранить предподсчитанные значения по тем полям которые могли бы быть в group by и дополнительных условиях. Я не спорю что SSAS я знаю "слишком поверхностно", но кубы с иерархиями и т. п. я делал (просто делал их самостоятельно и нет такого опыта как на Т-СКЛ). В целом, как я уже говорил, любой куб можно сводить к предподсчитанной таблице. В данном случае я бы этого не смог бы сделать. Текущие косяки структуры таблиц я вижу, который приводят и к "union all" операторам и "or or or" и т. п. мути и ответы "о том что можно делать" получены.

По основному вопросу есть какие-нить идеи?
19 фев 13, 13:16    [13948062]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
NIIIK
тест-кейс где-то неудачен?

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

Поэтому я делаю вывод, что да, тест-кейс неудачен. А где именно он неудачен — вам решать.
Например, добавьте в "Б" эти "более сложные условия" (я вот даже не понял, что такое "Б" — это столбец b из cteSearchResult? Тогда непонятно само словосочетание "условия в столбце" — это бессмыслица. Или "Б" — это какой-то подзапрос? Но в вашем примере нет ни одного where). Думаю, тест-кейс сразу станет удачнее.
19 фев 13, 13:24    [13948115]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Добрый Э - Эх
                        на правах легкого троллинга и небольшого оффтопа

NIIIK
- мой частный пример вполне себе полноценный ... интересует конкретный вопрос на этот вопрос ... тест-кейс где-то неудачен
и мой частный ответ тоже вполне себе полноценный и полностью удовлетворяет твоему частному полноценному примеру. считаю, что ответ на этот ответ тобою получен и мой ответ удачен в полном соответствии с твоим удачным тест-кейсом.


with cteSearchResult
as
(
select 1 as a1, 1 as a2, 1 as a3, 1 as b1, 0 b2 
union all
select 1 as a1, 2 as a2, 1 as a3, 1 as b,  null b2    
union all
select 1 as a1, 2 as a2, 1 as a3, 2 as b,  1 b2  
union all
select 2 as a1, 1 as a2, 2 as a3, 3 as b,  1 b2  
union all
select 2 as a1, 1 as a2, 1 as a3, 1 as b,  2 b2   

),
cteTotalCount as
(
select count(1) as cnt,
       count(case when (b2 is null or b2 = 0) and b1 = 1 then 1 end) as cnt1,	   
	   count(case when b2 = 1  or b1 = 1 then 1 end) as cnt2,	   
	   count(case when (b2 > 0 and b1 = 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


Если описания не хватает.

Гость333,
изачально написано что "там какие-то сложные условия" и уростил тот момент именно в тест кейсе.

Сейчас ещё одну "идейку" с файековы left joun (select 1 as itIs) t on (улосвие) для каждой колонки проверю, но там с нуловыми подсчитает ...
19 фев 13, 13:39    [13948241]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
NIIIK,

Я правильно понимаю, что у вас есть результат некого ресурсоемкого запроса, который надо вернуть клиенту. Плюс по этому результату нужно посчитать некую статистику и так же вернуть клиенту отдельным набором?
19 фев 13, 14:00    [13948415]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
invm,

Да, в целом правильно.
Есть большой поиск, возвращается первая страница и надо вернуть "статистику" (а не просто количество строк в результате поиска).

В целом,
по основной теме помогло "не потерять произвоитетельность" если сделать для каждой колонки, на уровне CTE поиска

 left
 join (select 1 isColumn1) tColumn1
   on (condition1)


Я в этот раз даже не проверял сделать это на уровне полей внутри СТЕ
select case (condition1) then 1 end as osColumn1


(был до этого прицедент в этом же запросе + лень)

Далее, кажое поле добавляется в группировку. Конечно выводится и то что "не надо" (нуловые количества для кажой группы), но быстродействие не снизили (время выполнение, количество чтений и т. п. ещё не смотрел).

Потом идёт "большой case grouping_id()...."

но блин это "количество", колтоаря на самом деле "статистика" возвращает 3000+ тысячи строк.
При том что "первая старинца Top 10 - Top 20
В целом то что я вижу 0,7-0,75 секунды уже "подуспокаивает". Но структура ту "до этого сделаная и ничего убрать не могу" действительно "какашка".
19 фев 13, 16:12    [13949674]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Glory,
или ещё кто-нить.

Есть ли метод создать "какой-то куб с FTS" ?!

Вопрос уже "не по теме", но хотелось бы знать как работали и индексировали всякие Google и т. п. где надо сделать быстрый поиск и "выдать статистику по различным категориям" результатов этого поиска.
21 фев 13, 15:33    [13961707]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
NIIIK,

"всякие Google и т. п." не используют реляционные СУБД в своих поисковых движках...
21 фев 13, 16:01    [13961958]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Конкретно в поисковом движке Google используется Google File System.
21 фев 13, 16:03    [13961975]     Ответить | Цитировать Сообщить модератору
 Re: group by, grouping sets, and total count few times.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Гость333
Конкретно в поисковом движке Google используется Google File System.

Знаю, понимаю
поэтому и вопрос поставлен в духе "как бы он писался если бы выбрали МсСКЛ + статистика результатов поиска" с максимально возможным быстродействием.
21 фев 13, 16:23    [13962107]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить