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

Откуда:
Сообщений: 163
Добрый день.
Что-то торможу с совсем простым, по идее, запросом.
Если упростить, то есть такая таблица (приходы/возвраты):

DECLARE @a table (id int, quan int)

INSERT @a VALUES (1, 200), (2, 100), (3,-150), (4,200), (5,-70)

Заранее известно, что отрицательных значений не может быть больше положительных (то есть возвратов не может быть больше приходов).
То есть в первой партии пришло 200, во второй 100, затем было возвращено 150, затем пришло 200 и опять возврат 70.
Возврат осуществляется в обратном порядке, то есть начиная с последней партии.
Необходимо получить остатки в таком виде:
1 150
4 130

То есть от первой партии осталось 150, так как от второй партии не осталось ничего (100-150) и от четвертой партии осталось 130, так как пришло 200 и возврат 70.

Каким запросом это вычислить наиболее "красиво"?

Спасибо.
18 июн 19, 18:51    [21910948]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с нарастающим итогом  [new]
_human
Member

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

DECLARE @a table (id int, quan int)

INSERT @a VALUES (1, 200), (2, 100), (3,-150), (4,200), (5,-70)

;with grp_cte as 
(select id, quan
	,row_number() over(order by id)-row_number() over(partition by sign(quan) order by id) as grp
from @a)

, in_cte as (
select *, DENSE_RANK() over(order by grp) as g
from grp_cte
where quan>0 )

,out_cte as
(
select *, DENSE_RANK() over(order by grp) as g
from grp_cte
where quan<0 )

, union_cte as (
select * from in_cte
union all
select * from out_cte )

select min(id), sum(quan)
from union_cte
group by g


DECLARE @a table (id int, quan int)

INSERT @a VALUES (1, 200), (2, 100), (3,-150), (4,200), (5,-70)

;with anal_cte as (
select id, quan
	, sum(
	  case sign(quan)
		when 1 then 0
		when -1 then 1
	  end) over(order by id desc) as g
from @a)

select min(id), sum(quan)
from anal_cte
group by g
18 июн 19, 21:04    [21911018]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с нарастающим итогом  [new]
Alexey30
Member

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

Спасибо большое, второй вариант отличный!
19 июн 19, 01:44    [21911127]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с нарастающим итогом  [new]
Alexey30
Member

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

Ан-нет...
Неверно работает при двух отрицательных подряд...

DECLARE @a table (id int, quan int)

INSERT @a VALUES (1, 200), (2, 100), (3,-150), (4,200), (5,-70), (6,-30)

;with anal_cte as (
select id, quan
	, sum(
	  case sign(quan)
		when 1 then 0
		when -1 then 1
	  end) over(order by id desc) as g
from @a)

select min(id), sum(quan)
from anal_cte
group by g
19 июн 19, 02:04    [21911129]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с нарастающим итогом  [new]
aleks222
Member

Откуда:
Сообщений: 985
Пишите явно номер партии возврата и минуют вас геморрой и печали.

Или объединяйте идущие подряд возвраты в один возврат.
19 июн 19, 06:10    [21911140]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с нарастающим итогом  [new]
Cristiano_Rivaldo
Member

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

;WITH cte (id,quant_src,quant_over)
AS 
(
	SELECT	TOP 1
			a.id,
			a.quan		AS quant_src,
			a.quan		AS quant_over
	FROM @a AS a
	ORDER BY a.id DESC 
	
	UNION ALL
	
	SELECT	a.id,
			a.quan,
			CASE WHEN C.quant_src > 0 AND A.quan < 0  -- был "переход"
					THEN A.quan
				ELSE c.quant_over + a.quan
			END 
	FROM cte AS c
		-- из-за органичений в cte на top  и агрегацию
		CROSS APPLY
		(
			SELECT 
					A.id,
					A.quan,
					ROW_NUMBER() OVER (ORDER BY a.id DESC ) n  
			FROM @a AS a
			WHERE a.id < c.id
		) A
	WHERE A.n = 1
)
SELECT *
FROM cte AS c
WHERE c.quant_over > 0
ORDER BY c.id
19 июн 19, 09:17    [21911216]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с нарастающим итогом  [new]
Alexey30
Member

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

Спасибо большое!
20 июн 19, 12:24    [21912129]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить