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

Откуда: Planet Earth
Сообщений: 107
CREATE TABLE #test(
	[RID] [BIGINT] NULL,
	[EffectiveDate] [DATETIME] NULL,
	[TransactionNumber] [INT] NULL,
	[ActivityCode] [INT] NOT NULL,
	[Quantity] [DECIMAL](18, 3) NULL,
	[Price] [MONEY] NULL
) ON [PRIMARY]

INSERT INTO #test VALUES
(1, '2015-04-01 00:00:00.000', 3812445, 3001, 1.000, 0.2032),
(2, '2015-04-01 00:00:00.000', 3812446, 3000, -0.130, 0.2032),
(3, '2015-04-01 00:00:00.000', 3812449, 3000, -0.130, 0.2032), 
(4, '2015-04-01 00:00:00.000', 3812453, 3000, -0.390, 0.2032),
(5, '2015-04-01 00:00:00.000', 3812609, 3000, -0.050, 0.2032),
(6, '2015-04-01 00:00:00.000', 3812618, 3000, -0.025, 0.2032),
(7, '2015-04-01 00:00:00.000', 3813443, 3001, 2.000, 0.2032),
(8, '2015-04-01 00:00:00.000', 3813444, 3000, -2.000, 0.2032),
(9, '2015-04-01 00:00:00.000', 3813446, 3001, 8.000, 0.2032),
(10, '2015-04-01 00:00:00.000', 3813447, 3000, -8.000, 0.2032),
(11, '2015-04-01 00:00:00.000', 3813449, 3001, 8.000, 0.2032),
(12, '2015-04-01 00:00:00.000', 3813450, 3000, -8.000, 0.2032),
(13, '2015-04-01 00:00:00.000', 3813452, 3001, 1.000, 0.2032),
(14, '2015-04-01 00:00:00.000', 3813455, 3000, -1.000, 0.2032),
(15, '2015-04-01 00:00:00.000', 3813457, 3001, 2.000, 0.2032),
(16, '2015-04-01 00:00:00.000', 3813460, 3000, -2.000, 0.2032),
(198, '2015-04-02 00:00:00.000', 3817130, 3000, -2.000, 0.2032),
(199, '2015-04-02 00:00:00.000', 3817131, 3000, -2.000, 0.2032),
(200, '2015-04-02 00:00:00.000', 3817134, 3001, 7.000, 0.2032),
(201, '2015-04-02 00:00:00.000', 3817139, 3001, 2.000, 0.2032),
(202, '2015-04-02 00:00:00.000', 3817141, 3000, -7.000, 0.2032),
(203, '2015-04-02 00:00:00.000', 3817142, 3000, -7.000, 0.2032),
(204, '2015-04-02 00:00:00.000', 3817145, 3001, 3.000, 0.2032),
(205, '2015-04-02 00:00:00.000', 3817149, 3001, 3.000, 0.2032),
(206, '2015-04-02 00:00:00.000', 3817151, 3000, -3.000, 0.2032),
(207, '2015-04-02 00:00:00.000', 3817152, 3000, -3.000, 0.2032),
(208, '2015-04-02 00:00:00.000', 3817154, 3001, 2.000, 0.2032),


SELECT RID,EffectiveDate,TransactionNumber,ActivityCode,Quantity,Price 
,(SELECT SUM(Quantity) FROM #test WHERE RID< T.RiD AND RID >(SELECT MAX(RID) FROM #test WHERE RID<T.RID AND Quantity>0))  TOTAL_SUM
FROM #test T

 WHERE Quantity>0
 
DROP TABLE #test


MSSQL 2008+

Есть таблица транзакций #test. Она сформирована из нескольких таблиц через UNION и выстроена по необходимым правилам с новым идентификатором RID (Row_Number...).
Нужно для каждого положительного Quantity в #test найти сумму Quantity предыдущих отрицательных транзакций.
Если два подряд положительных Quantity то у второго должно быть соответственно 0 или NULL.

Может подобный "накопительный итог" можно считать еще на стадии формирования временной таблицы, то есть "налету"?
У меня накапливать сумму получается, но обнулять её при нахождении следующего положительного значения не получается.

Есть у кого-нибудь идеи? :)
10 апр 15, 14:43    [17498941]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли оптимальней запрос на сумму в интервалах?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8488
Newber, подсчет нарастающего итога ищите в FAQ.
10 апр 15, 14:52    [17499024]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли оптимальней запрос на сумму в интервалах?  [new]
Newber
Member

Откуда: Planet Earth
Сообщений: 107
Владислав Колосов, в том-то и дело что я его находил. :)
Но в моём случае не смог его применить, так как накапливать надо для условий.
10 апр 15, 14:56    [17499056]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли оптимальней запрос на сумму в интервалах?  [new]
stdvb
Member

Откуда:
Сообщений: 39
select	t1.RID, SUM(t3.Quantity) - t1.Quantity
from	#test t1
	left join #test t2 on t2.RID = (
		select MAX(#test.RID)
		from #test
		where RID < t1.RID and Quantity > 0)
	join #test t3 on
		t3.RID > isnull(t2.RID, 0) and
		t3.RID <= t1.RID
where t1.Quantity > 0
group by t1.RID, t1.Quantity
10 апр 15, 15:41    [17499355]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли оптимальней запрос на сумму в интервалах?  [new]
stdvb
Member

Откуда:
Сообщений: 39
Извините, он не оптимальнее оказался. То же самое.
10 апр 15, 15:49    [17499406]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли оптимальней запрос на сумму в интервалах?  [new]
Добрый Э - Эх
Guest
Newber,

про оптимальность, х.з., но как вариант:
select v.*, 
       case 
         when Quantity > 0 
           then sum(case when Quantity >= 0 then 0 else Quantity end) over(partition by grp_id) 
         else 0 
       end as x_sum
  from (
         select t.*, sum(case when Quantity >= 0 then 1 else 0 end) over(order by rid desc) as grp_id
           from #test t
       ) v   
order by rid

Правда, наличие MS SQL SErver 2012 и выше - обязательно.
10 апр 15, 17:08    [17499807]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли оптимальней запрос на сумму в интервалах?  [new]
Newber
Member

Откуда: Planet Earth
Сообщений: 107
stdvb
Извините, он не оптимальнее оказался. То же самое.

Спасибо всё равно.

Добрый Э - Эх
Newber,

про оптимальность, х.з., но как вариант:
select v.*, 
       case 
         when Quantity > 0 
           then sum(case when Quantity >= 0 then 0 else Quantity end) over(partition by grp_id) 
         else 0 
       end as x_sum
  from (
         select t.*, sum(case when Quantity >= 0 then 1 else 0 end) over(order by rid desc) as grp_id
           from #test t
       ) v   
order by rid

Правда, наличие MS SQL SErver 2012 и выше - обязательно.


Спасибо, по Execution плану быстрее (81% на 19%) первого. Буду еще проверять и проверять совместимость.
А я вот как раз и думал как схитрить чтобы сгруппировать "плюс и все его минусы" в один RANK, теперь понял как :)
10 апр 15, 19:00    [17500309]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить