Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / запрос: поиск более элегантного решения / 16 сообщений из 16, страница 1 из 1
15.11.2019, 06:59
    #39889345
Idol_111
Idol_111
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Задача упрощенна, дано:
class value_n value_mC 10 NULLC 10 1F 10 1F 10 NULLN 10 2N 10 10N 10 NULL
Нужно получить:
class value_n value_mC 20 14F 20 NULLN 30 NULL
Т.е. все значения по колонке value_m должны проходить по классу "С".

Конечно, это можно сделать использую unpivot/pivot комбинацию.
Но хотелось бы более элегантное решения. К примеру, возможно ли добавить доболнительную стороку, если предыдущая содержит не нулевое значение в колонке value_m.
Как то так:
class value_n value_mC 10 NULLC 10 1F 10 NULLC NULL 1F 10 NULLN 10 NULLC NULL 2N 10 NULLC NULL 10N 10 NULL
Скрипт прилагается:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
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.

Буду рад любым идеям.
Заранее спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2019, 07:44
    #39889346
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Idol_111
хочу избежать комбинации с pivot.
А почему не проще?
Код: sql
1.
2.
3.
4.
5.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2019, 07:49
    #39889348
HandKot
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Idol_111,
или так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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 предложил, скорее всего, более оптимальный вариант
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2019, 08:53
    #39889358
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Idol_111
В реальной задаче в колонке value_m крайне мало не нулевых значений.
А тут хорошо бы добавить фильтрованный индекс, если "крайне мало".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2019, 09:01
    #39889360
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Пивот? Тут?

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

то задача сводится к группировке с суммой по [class] с джойном потом суммы [value_m] к заданному [class]
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.11.2019, 10:34
    #39889418
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Код: sql
1.
2.
3.
4.
5.
6.
7.
select
 class, sum(value_n),
 case when class = 'C' then sum(sum(value_m)) over () end
from
 #tmp
group by
 class;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.11.2019, 23:38
    #39890243
Idol_111
Idol_111
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Извиняюсь, тут похоже моя ошибка в постановке задачи. Надо было больше акцентировать внимание, что временная талица - это на самом деле ну очень тяжелый запрос. И получается, что просчитывать его дважды как это предложил alexeyvg не рентабельно и с индексами играть не получится, т.к. значения сложно просчитываются (это комбинация нескольких запросов внутри вьюхи).

А вот другие два варианта хоть и сканируют временную таблицу один раз, но создают несколько Table Spool, как это отразится на скорости запроса сложно сказать. Как проверю на реальном запросе, отпишусь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.11.2019, 23:42
    #39890245
Idol_111
Idol_111
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Кстати, в конечном итоге нужно получить не просто это:
class value_n value_mC 20 14F 20 NULLN 30 NULL
а вот это:
class value_n + value_mC 34 F 20 N 30
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2019, 00:50
    #39890258
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Idol_111
временная талица - это на самом деле ну очень тяжелый запрос. И получается, что просчитывать его дважды как это предложил alexeyvg не рентабельно
Не факт, что будет "дважды".
Нужно сложный запрос оформить как CTE, и потом по предложенному варианту.
А сервер уже либо сможет, либо не сможет :-)

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

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
;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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2019, 00:53
    #39890259
Remind
Remind
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Idol_111,

Чем Вас не устраивает вариант предложенный invm?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2019, 01:00
    #39890260
Remind
Remind
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Может быть стоит еще посмотреть в сторону вашего "очень тяжелого запроса", чтобы он выводил данные в более удобном для последующей аггрегации формате. Или что мешает результат очень тяжелого запроса положить во временную таблицу?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2019, 02:20
    #39890262
Idol_111
Idol_111
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Remind
Может быть стоит еще посмотреть в сторону вашего "очень тяжелого запроса", чтобы он выводил данные в более удобном для последующей аггрегации формате. Или что мешает результат очень тяжелого запроса положить во временную таблицу?

Это вьюха, которая заточена не только под этот запрос. Играем с тем, что имеем.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2019, 02:31
    #39890263
Idol_111
Idol_111
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
alexeyvg,

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

В варианте invm Table Spool не оказывают сколь-нибудь заметного влияния на время исполнения. По крайней мере пока, при выводе не большого количества строк.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2019, 04:23
    #39890264
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Idol_111
Ваш варинт все же читает большинство таблиц в два раз больше, чем вариант invm.

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


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

Set statistics on вам должен показать данные для сравнения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2019, 05:23
    #39890267
Idol_111
Idol_111
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
PizzaPizza
Idol_111
Ваш варинт все же читает большинство таблиц в два раз больше, чем вариант invm.

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


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

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

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

Так я про это и говорю - set statistics.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2019, 11:04
    #39890339
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
запрос: поиск более элегантного решения
Idol_111
на удивление Ваш варинт все же читает большинство таблиц в два раз больше, чем вариант invm.
И чему тут удивляться? CTE - синтаксический сахар для derived table. Будет выполняться столько раз, сколько упомянуто в запросе.
Хотя бывают исключения и результат CTE спулится. Например, такое возможно в варианте 2 из 22018379 .
Но быстрее, чем sum() over () не станет.
Idol_111
В варианте invm Table Spool не оказывают сколь-нибудь заметного влияния на время исполнения.
А с чего ему оказывать? Спуляться уже агрегированные данные. Для вашего примера это будет три строки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / запрос: поиск более элегантного решения / 16 сообщений из 16, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]