Возможно ли сделать без подзапросов такое суммирование / Microsoft SQL Server

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Возможно ли сделать без подзапросов такое суммирование

14 сообщений из 14, страница 1 из 1

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

#39924987

Nafigator

Гость

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

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

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

;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

...

Рейтинг:

0 / 0

11.02.2020, 00:52

| Ответить | Цитировать | Написать

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

#39924995

felix_ff

Участник

Откуда: Moscow

Сообщения: 1 982

Рейтинг: 0 / 0

Nafigator,

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

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

;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 не хватает стобцов

...

Рейтинг:

0 / 0

11.02.2020, 02:30

| Ответить | Цитировать | Написать

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

#39924997

Nafigator

Гость

felix_ff,
благодарю, примерно так и представлял - хотел убедиться, что такой способ - оптимальный...
Да, в CTE пропустил в конце столбцы transid, но идею Вы верно поняли.
Маленький вопрос: order by 1/0 - что сие означает? Никогда не встречал :)

...

Рейтинг:

0 / 0

11.02.2020, 02:59

| Ответить | Цитировать | Написать

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

#39925028

felix_ff

Участник

Откуда: Moscow

Сообщения: 1 982

Рейтинг: 0 / 0

Nafigator,

Выражение заглушка чтобы не писать столбец сортировки. Ну кстати я тут ступил его лучше указать вам же надо в порядке последнего trans_id, т.е должно быть так:

Код: sql

Select row_number() over (order by trans_id) as [rn], ...

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

Еще вариант через коррелирующий подзапрос с outer apply

...

Рейтинг:

0 / 0

11.02.2020, 09:05

| Ответить | Цитировать | Написать

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

#39925045

Ennor Tiegael

Участник

Сообщения: 3 069

Рейтинг: 0 / 0

felix_ff,

Имхо, судя по изначальной формулировке, там надо во все оконные функции добавить партиционирование:

Код: sql

1.
2.
3.
4.
5.
6.

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
)

Иначе условие

Код: sql

when [rn] = [cnt]

будет выполняться только для одной строки из всей выборки.

...

Рейтинг:

0 / 0

11.02.2020, 10:13

| Ответить | Цитировать | Написать

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

#39925049

entrypoint

Гость

Nafigator,

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

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]);

...

Рейтинг:

0 / 0

11.02.2020, 10:17

| Ответить | Цитировать | Написать

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

#39925120

nullin

Гость

Ennor Tiegael, можно еще так

Код: sql

row_number() over (partition by lt.logtransid order by lt.transid desc) as rn

Соответственно, тогда count(1) не понадобится и сравнение в результирующем запросе

Код: sql

sum(case when [rn] = 1 then [amount2] end)

...

Рейтинг:

0 / 0

11.02.2020, 12:08

| Ответить | Цитировать | Написать

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

#39925122

felix_ff

Участник

Откуда: Moscow

Сообщения: 1 982

Рейтинг: 0 / 0

nullin,

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

хотя ТС не прелоставил что в итоге хочет получить, поэтому здесь можно только догадываться :)

...

Рейтинг:

0 / 0

11.02.2020, 12:12

| Ответить | Цитировать | Написать

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

#39925170

Ennor Tiegael

Участник

Сообщения: 3 069

Рейтинг: 0 / 0

nullin,

Согласен, так лучше. Торопился с работы, не додумал :)

...

Рейтинг:

0 / 0

11.02.2020, 13:31

| Ответить | Цитировать | Написать

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

#39925330

Nafigator

Гость

Господа,

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

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

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

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

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() - единственный вариант, так?

...

Рейтинг:

0 / 0

11.02.2020, 17:26

| Ответить | Цитировать | Написать

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

#39925356

nullin

Гость

Nafigator, тогда все проще:

Код: sql

1.
2.

select sum(amount1) as totalamount1, max(amount2) as constamount2 from logtrans
group by logtranid

...

Рейтинг:

0 / 0

11.02.2020, 18:06

| Ответить | Цитировать | Написать

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

#39925367

Nafigator

Гость

nullin,

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

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

...

Рейтинг:

0 / 0

11.02.2020, 18:36

| Ответить | Цитировать | Написать

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

#39925401

nullin

Гость

Nafigator,

Код: sql

1.
2.
3.
4.

select sum(amount1) over(partition by logtranid) as totalamount1
     , max(amount2) over(partition by logtranid) as constamount2
     , /*все остальное*/
  from logtrans

...

Рейтинг:

0 / 0

11.02.2020, 20:44

| Ответить | Цитировать | Написать

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

#39925415

Nafigator

Гость

nullin,

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

Всем спасибо!

...

Рейтинг:

0 / 0

11.02.2020, 21:47

| Ответить | Цитировать | Написать

14 сообщений из 14, страница 1 из 1

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&fpage=69&tid=1686508]:	0ms
get settings:	9ms
get forum list:	13ms
check forum access:	4ms
check topic access:	4ms
track hit:	32ms
get topic data:	13ms
get forum data:	3ms
get page messages:	54ms
get tp. blocked users:	2ms
others:	340ms

total:	474ms