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

Откуда: от верблюда
Сообщений: 428
есть 2 таблички, т1 и т2. в результирующую таблицу должны попасть все строки из т1, которым соответствуют строки из т2, при этом нужно вычислить сумму для т2 в разрезе ссылок на т1. но самое главное - в результирующую таблицу нужно вывести все строки т2 вошедшие в сумму, но при этом напротив каждой строки должна отображаться общая сумма для вышеуказанного разреза, т.е. сумма будет повторяться ровно столько раз, сколько строк вошло в эту сумму

есть такой вариант решения
DECLARE @Table1 table (ID int, value1 char(10))
INSERT @Table1 values(1,'val1')
INSERT @Table1 values(2,'val2')

DECLARE @Table2 table (ID int, T1ID int, summ1 int, name char(20))
INSERT @Table2 values(1,1,10,'name1')
INSERT @Table2 values(2,1,20,'name2')
INSERT @Table2 values(3,1,15,'name3')
INSERT @Table2 values(4,2,1,'name4')
INSERT @Table2 values(5,2,2,'name5')
INSERT @Table2 values(6,3,5,'name6')

select
	t1.id, value1, name, s1
from
	@table1 t1
join
	@table2 t2
on t2.t1id = t1.id
join (
		select
			t1id,sum(summ1) s1
		from
			@table2
		group by t1ID
	) t2s
on t2s.t1id = t1.id


этот запрос возвращает правильный результат, возможно я чего-то в нем не учел, но вроде правильный

может решение "плоское", но ничего другого в голову не пришло

вопрос в правильности этого решения
возможно такие задачи решаются как-то иначе?(SQL2000)
21 июн 13, 11:11    [14464211]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Ennor Tiegael
Member

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

Да, такие задачи решаются гораздо проще - оконными функциями, которые впервые появились в MSSQL 2005.

Для вашей версии - нормальное решение.
21 июн 13, 11:30    [14464365]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
или подзапросом в резалт
select
	t1.id, value1, name
	,(select sum(summ1) s1 from @table2 where t1id = t1.id group by t1ID) s1
from
	@table1 t1
join
	@table2 t2
on t2.t1id = t1.id
21 июн 13, 11:51    [14464577]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Ennor Tiegael,

значит я не ошибся, спасибо! :)

но теперь есть еще один подводный камень - в реальности @table2 является достаточно большим вложенным запросом с джойнами, юнионами и прочими наворотами

если его изобразить схематично и представить что @table2 является результатом вложенного запроса, то основной запрос выглядит примерно так:
select
  ...,s1
from
  (
   select
   ...,s1
   from
     (select
        ...,sum(summ1) s1
      from
        @table2
      group by t1ID)
   join1 ...
   joinN ...
  )
join1...
joinN...


на текущий момент, вложенный запрос возвращает
select
...,sum(summ1) s1
from
@table2
group by t1ID

и эти результаты протягиваются до самого верха с присоединением доп столбцов и наложением доп фильтров

но понадобилось развернуть результат так, как я написал в первом посте

для наглядности немного перепишу запрос из первого поста чтобы стало понятнее

select
	t1.id, value1, name, s1
from
	@table1 t1
join
	(select * from @table2) t2 --
on t2.t1id = t1.id
join (
		select
			t1id,sum(summ1) s1
		from
			(select * from @table2) tt2
		group by t1ID
	) t2s
on t2s.t1id = t1.id


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

как поступить? сделать временную таблицу или можно как-то еще выкрутиться?

напомню, что у меня SQL2000
21 июн 13, 11:59    [14464658]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Ennor Tiegael
Member

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

Ну да, закэшировать в табличной переменной либо временной таблице, что больше подходит. Потом можно использовать хоть 10 раз.
21 июн 13, 12:02    [14464700]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
LexusR,

да, согласен, так тоже можно, спасибо за вариант! :)

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

первый пост был в образовательных целях, уж не обижайтесь, если что :)
где-то в глубине души надеялся что может мне скажут что я все неправильно сделал и предложат вариант который мне подойдет и я больше не буду никому забивать голову, но надежда умирает быстро :D

Ennor Tiegael

спасибо!

(пока что радует только одно - вложенный запрос не зависит от джойнов на верхнем уровне)
21 июн 13, 12:07    [14464739]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Glory
Member

Откуда:
Сообщений: 104751
TJ001
где-то в глубине души надеялся что может мне скажут что я все неправильно сделал и предложат вариант который мне подойдет и я больше не буду никому забивать голову, но надежда умирает быстро :D

Можно написать триггер, который будет поддерживать в Table1 сумму дочерних записей из Table2
21 июн 13, 12:11    [14464764]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
aleks2
Guest
Glory
TJ001
где-то в глубине души надеялся что может мне скажут что я все неправильно сделал и предложат вариант который мне подойдет и я больше не буду никому забивать голову, но надежда умирает быстро :D

Можно написать триггер, который будет поддерживать в Table1 сумму дочерних записей из Table2

Проще, аднако, Indexed View забабахать
select t1id, sum(summ1) s1 from @table2 group by t1ID
.
21 июн 13, 12:15    [14464797]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Всем спасибо за отзывы, буду пробовать варианты :)
21 июн 13, 13:08    [14465272]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Ennor Tiegael
TJ001,

Да, такие задачи решаются гораздо проще - оконными функциями, которые впервые появились в MSSQL 2005.

а не просветите как? Аналогичная задача, только сиквел 2008 и сумм две, по разным критериям (не по ключевому полю, а по сочетаниям двух-трех полей).
(в соседнем топике описал моё «решение», которое назвали шизофренией, с чем я в принципе согласен, просто делалось всё в условиях весьма ограниченного времени и, увы, моих знаний)
21 июн 13, 16:36    [14467036]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3376
Изерлонер,

2005+:

select *, sum(t2.summ1) over(partition by t1.Id)
from @Table1 t1
  inner join @Table2 t2 on t2.t1id = t1.id;
21 июн 13, 16:48    [14467139]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Ennor Tiegael
Изерлонер,

2005+:

select *, sum(t2.summ1) over(partition by t1.Id)
from @Table1 t1
  inner join @Table2 t2 on t2.t1id = t1.id;


Вроде автор не указывал возможность использования более поздних версий:

TJ001
возможно такие задачи решаются как-то иначе?(SQL2000)


Но в 2005+ это было бы, наверное, оптимально)
21 июн 13, 16:51    [14467153]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
nezhadnye_my
Guest
http://msdn.microsoft.com/en-us/library/ms189461(v=sql.90).aspx

там в примере B смотрите колонку Total:
сумма посчитана "как если бы мы сгруппировали по SalesOrderID",
но в запросе GROUP BY отсутствует, сумма считается "в окне" defined by SalesOrderID.
(задается с помощью OVER(PARTITION BY SalesOrderID))
21 июн 13, 16:54    [14467174]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3376
Сергей Викт.
Вроде автор не указывал возможность использования более поздних версий
Я ответил на конкретный вопрос, и не ТС-у.
21 июн 13, 16:54    [14467182]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Сергей Викт.,

это был ответ на мой вопрос. Спасибо!
21 июн 13, 16:55    [14467185]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Ennor Tiegael
Сергей Викт.
Вроде автор не указывал возможность использования более поздних версий
Я ответил на конкретный вопрос, и не ТС-у.

Да, прошу прощения, прохлопал... Неправ. признаю) Для 2005+ это хороший вариант!))
21 июн 13, 16:56    [14467191]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
nezhadnye_my
Guest
ой!
мой ответ тоже не ТС-у, а Изерлонер-у,
надо было на другую кнопку жать
21 июн 13, 16:57    [14467199]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Где вы раньше были? Где я раньше был?! Я Over partition by только для нумерации строк использовал.
21 июн 13, 17:03    [14467248]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
aleks2
Guest
Ennor Tiegael
TJ001,
Да, такие задачи решаются гораздо проще - оконными функциями, которые впервые появились в MSSQL 2005.

Проще чего? Проще только запись. Да и то, это дело привычки. Т.е. иллюзия.

А быстродействие, всяко, не лучше, чем у явной группировки.
21 июн 13, 18:32    [14467689]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
По крайней мере в моем случае быстродействие просто бешенное. На не очень мощном компе выдал 80 тыс строк за 7 сек. При том что во вью довольно много таблиц объединено, и суммы у меня чуть сложнее расчитываются.
21 июн 13, 19:50    [14467882]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3376
aleks2
Ennor Tiegael
TJ001,
Да, такие задачи решаются гораздо проще - оконными функциями, которые впервые появились в MSSQL 2005.

Проще чего? Проще только запись. Да и то, это дело привычки. Т.е. иллюзия.

А быстродействие, всяко, не лучше, чем у явной группировки.
Ну например тем, что в случае использования окна получается на одно обращение к таблице меньше.
22 июн 13, 03:49    [14468892]     Ответить | Цитировать Сообщить модератору
 Re: Сумма напротив каждой строки вошедшей в эту сумму  [new]
aleks2
Guest
Ennor Tiegael
aleks2
пропущено...

Проще чего? Проще только запись. Да и то, это дело привычки. Т.е. иллюзия.

А быстродействие, всяко, не лучше, чем у явной группировки.
Ну например тем, что в случае использования окна получается на одно обращение к таблице меньше.

Мечтать не вредно.
Все одно
1. Сначала надо просуммировать.
2. Потом приписать КАЖДОЙ строке, полученную сумму.
3. А типерь розжувайте: як это можно сделать за ОДИН скан таблицы?

ЗЫ. То, что оптимизатор вас утешает, рисуя в плане одно обращение к таблице - ничо не меняет.
22 июн 13, 06:13    [14468919]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить