Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 запрос: поиск более элегантного решения  [new]
Idol_111
Member

Откуда:
Сообщений: 604
Задача упрощенна, дано:
class value_n value_m
C 10 NULL
C 10 1
F 10 1
F 10 NULL
N 10 2
N 10 10
N 10 NULL

Нужно получить:
class value_n value_m
C 20 14
F 20 NULL
N 30 NULL

Т.е. все значения по колонке value_m должны проходить по классу "С".

Конечно, это можно сделать использую unpivot/pivot комбинацию.
Но хотелось бы более элегантное решения. К примеру, возможно ли добавить доболнительную стороку, если предыдущая содержит не нулевое значение в колонке value_m.
Как то так:
class value_n value_m
C 10 NULL
C 10 1
F 10 NULL
C NULL 1
F 10 NULL
N 10 NULL
C NULL 2
N 10 NULL
C NULL 10
N 10 NULL

Скрипт прилагается:
drop table #tmp

create table #tmp (
class varchar(5)
,value_n int
,value_m int
)

insert into #tmp 
values('C',10, null)
,('C',10, 1)
,('F',10, 1)
,('F',10, null)
,('N',10, 2)
,('N',10, 10)
,('N',10, null)

select * from #tmp

--select class
--	,sum(value_n) as Svalue_n
--	,sum(value_m) as Svalue_m
--from #tmp
--group by class

select class
	,value_n
	,value_m
from(
	select case 
			when type = 'value_m' then 'C'
			else class
			end as class
		, type
		, value_S
	from
	(
	select * from #tmp
	) as p
	unpivot
	(value_S for type in(value_n, value_m)
	) as unp
) as pp
PIVOT (
sum(value_S)
for type in ([value_n], [value_m])
) as pvt


Примечание:
В реальной задаче в колонке value_m крайне мало не нулевых значений.
Изначальный запрос очень тяжелый, поэтому и хочу избежать комбинации с pivot.

Буду рад любым идеям.
Заранее спасибо.
15 ноя 19, 06:59    [22016712]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Idol_111
хочу избежать комбинации с pivot.
А почему не проще?
select class
	,sum(value_n) as Svalue_n
	,case when class = 'C' then (select sum(value_m) from #tmp ) end as Svalue_m
from #tmp
group by class
15 ноя 19, 07:44    [22016713]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2979
Idol_111,
или так
Select
	x.class
	, value_n = Sum(x.value_n)
	, value_m = Max(x.value_m)
From (
	Select
		t.class
		, value_n = t.value_n
		, value_m = Case When t.class = N'C' Then Sum(t.value_m) Over () End
	From
		#tmp t) x
Group By
	x.class



хотя alexeyvg предложил, скорее всего, более оптимальный вариант
15 ноя 19, 07:49    [22016715]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Idol_111
В реальной задаче в колонке value_m крайне мало не нулевых значений.
А тут хорошо бы добавить фильтрованный индекс, если "крайне мало".
15 ноя 19, 08:53    [22016744]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
PizzaPizza
Member

Откуда:
Сообщений: 368
Пивот? Тут?

Если учесть
Idol_111
все значения по колонке value_m должны проходить по классу "С".

то задача сводится к группировке с суммой по [class] с джойном потом суммы [value_m] к заданному [class]
15 ноя 19, 09:01    [22016747]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
select
 class, sum(value_n),
 case when class = 'C' then sum(sum(value_m)) over () end
from
 #tmp
group by
 class;
15 ноя 19, 10:34    [22016860]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
Idol_111
Member

Откуда:
Сообщений: 604
Извиняюсь, тут похоже моя ошибка в постановке задачи. Надо было больше акцентировать внимание, что временная талица - это на самом деле ну очень тяжелый запрос. И получается, что просчитывать его дважды как это предложил alexeyvg не рентабельно и с индексами играть не получится, т.к. значения сложно просчитываются (это комбинация нескольких запросов внутри вьюхи).

А вот другие два варианта хоть и сканируют временную таблицу один раз, но создают несколько Table Spool, как это отразится на скорости запроса сложно сказать. Как проверю на реальном запросе, отпишусь.
17 ноя 19, 23:38    [22018349]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
Idol_111
Member

Откуда:
Сообщений: 604
Кстати, в конечном итоге нужно получить не просто это:
class value_n value_m
C 20 14
F 20 NULL
N 30 NULL

а вот это:
class value_n + value_m
C 34
F 20
N 30
17 ноя 19, 23:42    [22018351]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Idol_111
временная талица - это на самом деле ну очень тяжелый запрос. И получается, что просчитывать его дважды как это предложил alexeyvg не рентабельно
Не факт, что будет "дважды".
Нужно сложный запрос оформить как CTE, и потом по предложенному варианту.
А сервер уже либо сможет, либо не сможет :-)

Или, как вариант, сделать предварительную группировку в ещё одном CTE (это может помочь, если агрегированный результат небольшой).

Т.е. эти 2 варианта:

;with tmp as (
	... -- тут очень сложный запрос
)
select class
	,sum(value_n) as Svalue_n
	,case when class = 'C' then (select sum(value_m) from tmp ) end as Svalue_m
from tmp
group by class


;with tmp as (
	... -- тут очень сложный запрос
)
, tmp2 as ( -- агрегируем
	select class
		,sum(value_n) as Svalue_n
		,sum(value_m) as Svalue_m
	from tmp
)
select class
	,Svalue_n
	,case when class = 'C' then (select sum(Svalue_m) from tmp2 ) end as Svalue_m
from tmp2
18 ноя 19, 00:50    [22018379]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Idol_111,

Чем Вас не устраивает вариант предложенный invm?
18 ноя 19, 00:53    [22018380]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Может быть стоит еще посмотреть в сторону вашего "очень тяжелого запроса", чтобы он выводил данные в более удобном для последующей аггрегации формате. Или что мешает результат очень тяжелого запроса положить во временную таблицу?
18 ноя 19, 01:00    [22018382]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
Idol_111
Member

Откуда:
Сообщений: 604
Remind
Может быть стоит еще посмотреть в сторону вашего "очень тяжелого запроса", чтобы он выводил данные в более удобном для последующей аггрегации формате. Или что мешает результат очень тяжелого запроса положить во временную таблицу?

Это вьюха, которая заточена не только под этот запрос. Играем с тем, что имеем.
18 ноя 19, 02:20    [22018386]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
Idol_111
Member

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

на удивление Ваш варинт все же читает большинство таблиц в два раз больше, чем вариант invm.

В варианте invm Table Spool не оказывают сколь-нибудь заметного влияния на время исполнения. По крайней мере пока, при выводе не большого количества строк.
18 ноя 19, 02:31    [22018387]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
PizzaPizza
Member

Откуда:
Сообщений: 368
Idol_111
Ваш варинт все же читает большинство таблиц в два раз больше, чем вариант invm.

Table Spool не оказывают сколь-нибудь заметного влияния на время исполнения.


Интересный способ сравнения: объем чтения vs. время исполнения.

Set statistics on вам должен показать данные для сравнения.
18 ноя 19, 04:23    [22018389]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
Idol_111
Member

Откуда:
Сообщений: 604
PizzaPizza
Idol_111
Ваш варинт все же читает большинство таблиц в два раз больше, чем вариант invm.

Table Spool не оказывают сколь-нибудь заметного влияния на время исполнения.


Интересный способ сравнения: объем чтения vs. время исполнения.

Set statistics on вам должен показать данные для сравнения.

В данном случае, у запроса сильная корреляция: time и IO.

Так я про это и говорю - set statistics.
18 ноя 19, 05:23    [22018393]     Ответить | Цитировать Сообщить модератору
 Re: запрос: поиск более элегантного решения  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Idol_111
на удивление Ваш варинт все же читает большинство таблиц в два раз больше, чем вариант invm.
И чему тут удивляться? CTE - синтаксический сахар для derived table. Будет выполняться столько раз, сколько упомянуто в запросе.
Хотя бывают исключения и результат CTE спулится. Например, такое возможно в варианте 2 из 22018379.
Но быстрее, чем sum() over () не станет.
Idol_111
В варианте invm Table Spool не оказывают сколь-нибудь заметного влияния на время исполнения.
А с чего ему оказывать? Спуляться уже агрегированные данные. Для вашего примера это будет три строки.
18 ноя 19, 11:04    [22018504]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить