ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / запрос: поиск более элегантного решения
16 сообщений из 16, страница 1 из 1
запрос: поиск более элегантного решения
    #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, 06:59
| Ответить | Цитировать | Написать  
запрос: поиск более элегантного решения
    #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:44
| Ответить | Цитировать | Написать  
запрос: поиск более элегантного решения
    #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, 07:49
| Ответить | Цитировать | Написать  
запрос: поиск более элегантного решения
    #39889358
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Idol_111
В реальной задаче в колонке value_m крайне мало не нулевых значений.
А тут хорошо бы добавить фильтрованный индекс, если "крайне мало".
...
Рейтинг: 0 / 0
15.11.2019, 08:53
| Ответить | Цитировать | Написать  
запрос: поиск более элегантного решения
    #39889360
PizzaPizza
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PizzaPizza
Участник
Пивот? Тут?

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

то задача сводится к группировке с суммой по [class] с джойном потом суммы [value_m] к заданному [class]
...
Рейтинг: 0 / 0
15.11.2019, 09:01
| Ответить | Цитировать | Написать  
запрос: поиск более элегантного решения
    #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
15.11.2019, 10:34
| Ответить | Цитировать | Написать  
запрос: поиск более элегантного решения
    #39890243
Idol_111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Idol_111
Участник
Извиняюсь, тут похоже моя ошибка в постановке задачи. Надо было больше акцентировать внимание, что временная талица - это на самом деле ну очень тяжелый запрос. И получается, что просчитывать его дважды как это предложил alexeyvg не рентабельно и с индексами играть не получится, т.к. значения сложно просчитываются (это комбинация нескольких запросов внутри вьюхи).

А вот другие два варианта хоть и сканируют временную таблицу один раз, но создают несколько Table Spool, как это отразится на скорости запроса сложно сказать. Как проверю на реальном запросе, отпишусь.
...
Рейтинг: 0 / 0
17.11.2019, 23:38
| Ответить | Цитировать | Написать  
запрос: поиск более элегантного решения
    #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
17.11.2019, 23:42
| Ответить | Цитировать | Написать  
запрос: поиск более элегантного решения
    #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:50
| Ответить | Цитировать | Написать  
запрос: поиск более элегантного решения
    #39890259
Remind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Remind
Участник
Idol_111,

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

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

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

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

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


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

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


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