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

Откуда:
Сообщений: 216
Всем привет. Если туплю - извините. Есть некая таблица транзакций (logtrans), столбцов - много, для простоты изобразил CTE с четырьмя.
В гриде каждой логической транзакции отвечает несколько "обычных" транзакций.
Выводиться должно всё так, как изображено в CTE.

НО с суммами - иначе: внизу грида выводится сумма следующим образом - totalamount1- обычная сумма по всем amount1, а для totalamount2 должны суммироваться лишь последние amount2 (т.е. с максимальным transid). Можно ли это как-то сделать с минимальным вмешательством в существующий запрос? Столбец amount2 (и соответствующая сумма) были добавлены недавно, основной запрос - страшный, уродливый и длинный. MS SQL 2012.

;with logtrans (logtransid, transid, amount1, amount2) as
(
select 1, 1, 1.1, 2.2 union all
select 1, 2, 1.2, -2.3 union all

select 2, 1, 22.13, -0.2 union all

select 3, 1, 1.4, 100 union all
select 3, 2, 1.5, 300 union all
select 3, 3, 4.5, -40.44 union all

select 4, 1.6, 11.111 union all

select 5, 1.7, cast(null as money)
)
select sum(amount1) as totalamount1, sum(???) as totalamount2 from logtrans 
11 фев 20, 00:52    [22077296]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1150
Nafigator,


я несколько раз попытался прочесть что вам надо но так до конца и не понял. придется гадать:

;with logtrans (logtransid, transid, amount1, amount2) as
(
select 1, 1, 1.1, 2.2 union all
select 1, 2, 1.2, -2.3 union all

select 2, 1, 22.13, -0.2 union all

select 3, 1, 1.4, 100 union all
select 3, 2, 1.5, 300 union all
select 3, 3, 4.5, -40.44 union all

select 4, 1.6, 11.111, null union all

select 5, 1.7, cast(null as money), 100
),
result (
  select row_number() over (order by 1/0) as rn, count(1) over () as [cnt], * from logtrans
)
select sum(amount1) as totalamount1, sum(case when [rn] = [cnt] then [amount2] end) as totalamount2 from result


и у вас кстати CTE корявая, в некоторых стейтментах union select не хватает стобцов
11 фев 20, 02:30    [22077308]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
Nafigator
Member

Откуда:
Сообщений: 216
felix_ff,
благодарю, примерно так и представлял - хотел убедиться, что такой способ - оптимальный...
Да, в CTE пропустил в конце столбцы transid, но идею Вы верно поняли.
Маленький вопрос: order by 1/0 - что сие означает? Никогда не встречал :)
11 фев 20, 02:59    [22077310]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1150
Nafigator,

Выражение заглушка чтобы не писать столбец сортировки. Ну кстати я тут ступил его лучше указать вам же надо в порядке последнего trans_id, т.е должно быть так:
Select row_number() over (order by trans_id) as [rn], ... 


Если бы версия сиквела позволяла можно бы было использовать оконку first_value но у вас 2012.

Еще вариант через коррелирующий подзапрос с outer apply
11 фев 20, 09:05    [22077349]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
Ennor Tiegael
Member

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

Имхо, судя по изначальной формулировке, там надо во все оконные функции добавить партиционирование:
result as (
	select row_number() over (partition by lt.logtransid order by lt.transid) as rn,
		count(1) over (partition by lt.logtransid) as [cnt],
		lt.*
	from logtrans lt
)
Иначе условие
when [rn] = [cnt]
будет выполняться только для одной строки из всей выборки.
11 фев 20, 10:13    [22077374]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
entrypoint
Member

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

WITH logtrans(logtransid, transid, amount1, amount2)
AS (
     SELECT 1, 1, 1.1, 2.2		 UNION ALL
     SELECT 1, 2, 1.2, -2.3		 UNION ALL
     SELECT 2, 1, 22.13, -0.2		 UNION ALL
     SELECT 3, 1, 1.4, 100		 UNION ALL
     SELECT 3, 2, 1.5, 300		 UNION ALL
     SELECT 3, 3, 4.5, -40.44		 UNION ALL
     SELECT 4, 1.6 , 11.111, NULL	 UNION ALL
     SELECT 5, 1.7, CAST(NULL AS MONEY), 100
)
-- totalamount1- обычная сумма по всем amount1, 
-- а для totalamount2 должны суммироваться лишь последние amount2 (т.е. с максимальным transid)
SELECT 
       SUM(l.amount1) AS totalamount1
     , SUM(CASE z.[максимальный transid] WHEN l.transid THEN l.amount2 END) AS totalamount2
FROM 
     logtrans AS l
	   CROSS APPLY (SELECT MAX(transid) FROM logtrans) AS z([максимальный transid]);
     
11 фев 20, 10:17    [22077379]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
nullin
Member

Откуда: pullin
Сообщений: 113
Ennor Tiegael, можно еще так
row_number() over (partition by lt.logtransid order by lt.transid desc) as rn

Соответственно, тогда count(1) не понадобится и сравнение в результирующем запросе
sum(case when [rn] = 1 then [amount2] end)
11 фев 20, 12:08    [22077486]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1150
nullin,

Да это наверное самый профитный вариант.

хотя ТС не прелоставил что в итоге хочет получить, поэтому здесь можно только догадываться :)
11 фев 20, 12:12    [22077489]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
Ennor Tiegael
Member

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

Согласен, так лучше. Торопился с работы, не додумал :)
11 фев 20, 13:31    [22077564]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
Nafigator
Member

Откуда:
Сообщений: 216
Господа,

большое спасибо за внимание к вопросу!

Как часто бывает, всплыли некоторые подробности.
Во-первых, в конечном селекте не фигурирует transid. Во-вторых, amount2 в пределах каждого "окошка" (транзакции) одинаковы - можно брать любое.

То есть данные выглядят следующим образом:


logtranid | amount1 | amount2
1 100.0 -55.0
1 200.0 -55.0
2 100.0 1500.0
3 300.0 -2100.0
3 150.0 -2100.0
3 600.0 -2100.0
4 300.0 null

Сумма по amount2 - это сумма однократных вхождений amount2 в каждую транзакцию, т.е. -55.0 + 1500.0 - 2100.0

При таких "раскладах" row_number() - единственный вариант, так?

Сообщение было отредактировано: 11 фев 20, 17:27
11 фев 20, 17:26    [22077788]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
nullin
Member

Откуда: pullin
Сообщений: 113
Nafigator, тогда все проще:
select sum(amount1) as totalamount1, max(amount2) as constamount2 from logtrans
group by logtranid


Сообщение было отредактировано: 11 фев 20, 18:08
11 фев 20, 18:06    [22077821]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
Nafigator
Member

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

ну, да, самый простой вариант. Но правильно ли я понимаю, что на 2012-м SQL в любом случае придётся "протащить" эту промежуточную агрегацию (max с группировкой) - затем уже сумму по этой агрегации, т.е. в одну строчку не сделать?

Почему этим заморочился вообще - потому что в существующем запросе несколько десятков столбцов, явно выбираемых, многие из которых суммируются, плюс часть запроса "клеится" в коде (куча условий, фильтров)... Надеялся, что удастся что-то с партиционированием намутить для вычисления этой суммы - в одну строку, не добавляя уровень...

Сообщение было отредактировано: 11 фев 20, 18:37
11 фев 20, 18:36    [22077834]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
nullin
Member

Откуда: pullin
Сообщений: 113
Nafigator,
select sum(amount1) over(partition by logtranid) as totalamount1
     , max(amount2) over(partition by logtranid) as constamount2
     , /*все остальное*/
  from logtrans
11 фев 20, 20:44    [22077878]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли сделать без подзапросов такое суммирование  [new]
Nafigator
Member

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

да, я понял идею - но это тот самый промежуточный select. А затем, в следующем запросе, мне надо просуммировать все MAX-ы из "нашего-вашего", чтобы получить итоговый sumamount2... В общем, всё-таки обошёлся row_number - втулил его в один из запросов и впоследствии прокейсил внутри суммы.

Всем спасибо!
11 фев 20, 21:47    [22077897]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить