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

Откуда:
Сообщений: 74
Задача разновидность нарастающего итога.
Есть таблица приведенного ниже вида. Требуется посчитать нарастающий итог по столбцу Col.
Но есть условия, если Doc_Type=0 значит итог должен сбрасываться и считаться от количества в данном документе, но если в одном документе типа 0 присутствуют одинаковых строк то сбрасываться должно на первой а последующие суммироваться.
И подсчет должен производится в рамках нескольких столбцов С_ID, Price (на самом деле их около 20, так как внизу упращенная таблица)
create table #table(DocDate datetime, Doc_Type int, Doc_ID int, C_ID int,col float, Price float, total float);

insert #table(Doc_ID, DocDate, Doc_Type, C_ID, col, Price) values(1, '20150101', 1, 1, 10, 120);
insert #table(Doc_ID, DocDate, Doc_Type, C_ID, col, Price) values(1, '20150201', 1, 1, -5, 120);
insert #table(Doc_ID, DocDate, Doc_Type, C_ID, col, Price) values(1, '20150201', 2, 5, -1, 130);
insert #table(Doc_ID, DocDate, Doc_Type, C_ID, col, Price) values(1, '20150201', 0, 1, 2, 120);
insert #table(Doc_ID, DocDate, Doc_Type, C_ID, col, Price) values(1, '20150301', 1, 1, -10, 120);
insert #table(Doc_ID, DocDate, Doc_Type, C_ID, col, Price) values(1, '20150301', 6, 1, 4, 120);
insert #table(Doc_ID, DocDate, Doc_Type, C_ID, col, Price) values(1, '20150401', 0, 1, 2, 120);
insert #table(Doc_ID, DocDate, Doc_Type, C_ID, col, Price) values(1, '20150401', 0, 5, 5, 130);
insert #table(Doc_ID, DocDate, Doc_Type, C_ID, col, Price) values(1, '20150501', 1, 1, 2, 120);
insert #table(Doc_ID, DocDate, Doc_Type, C_ID, col, Price) values(1, '20150601', 1, 1, 4, 120);
insert #table(Doc_ID, DocDate, Doc_Type, C_ID, col, Price) values(1, '20150601', 1, 5, -5, 130);

drop table #table



Должно получится что то вроде этого
Doc_ID DocDate Doc_Type C_ID col Pricetotal
12015-01-01 00:00:00.000111012010
22015-02-01 00:00:00.00011-51205
32015-02-01 00:00:00.00025-1130-1
42015-02-01 00:00:00.0000121202
52015-03-01 00:00:00.00011-10120-8
62015-03-01 00:00:00.000614120-4
72015-04-01 00:00:00.0000121202
72015-04-01 00:00:00.0000551305
72015-04-01 00:00:00.00005513010
82015-05-01 00:00:00.0001121204
92015-06-01 00:00:00.0001141208
102015-06-01 00:00:00.00015-51305


Задачу со сбросом там где на типе 0 я решил (для этого в представлении появился. Но как сделать подсчет сумм в рамках уникальных параметров товара кроме курсора пока ничего не приходит в голову.

declare @tot_sum  float;
declare @dateinv datetime;

set @tot_sum = 0

;with t AS 
(select #table.*, (select MAX(DocDate) from #table as table1 where DocDate<=#table.DocDate and Doc_Type=0 ) as D_inv
from #table)

select * into #table2
from t

update #table2 set @tot_sum = total = case when @dateinv=D_inv then @tot_sum + ISNULL(Col, 0) ELSE COL END, @dateinv=D_inv

select * from #table2 
 
drop table #table2


может есть какие-то другие способы? Сервер 2008 R2.
24 сен 15, 15:12    [18190263]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Владислав Колосов
Member

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

Вопрос в чём? Нет идей как пронумеровать группы для заданного порядка сортировки записей?
24 сен 15, 17:11    [18191202]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Smoke999
Member

Откуда:
Сообщений: 74
нет, вопрос в том как подсчитать итог в рамках уникальных значений не используя курсор, если возможно конечно. Потому что в моем примере не учитываются эти поля. Если можно сделать update например в рамках групп каких-то то было бы хорошо)
24 сен 15, 17:21    [18191270]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Shakill
Member

Откуда: мск
Сообщений: 1882
Smoke999
нет, вопрос в том как подсчитать итог в рамках уникальных значений не используя курсор, если возможно конечно. Потому что в моем примере не учитываются эти поля. Если можно сделать update например в рамках групп каких-то то было бы хорошо)

шаг 1. пишете подсчет нарастающего итога при помощи рекурсивного cte для одной конкретной группы (сочетания полей)
шаг 2. расширяете ваш запрос - в корневую часть cte помещаете первые записи от каждой группы, допиливаете рекурсивную часть, чтоб была привязка в рамках группы
24 сен 15, 17:29    [18191327]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Владислав Колосов
Member

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

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

Из вашего сообщения непонятны параметры и условия сортировки данных, например. По дате и еще по какому полю?
24 сен 15, 17:32    [18191342]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Макбет
Member

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

попробуй так
p.s. сори если есть мелкие ошибки, негде проверить =)
главное идея
p.p.s. на будущее, если указать версию сервера ответ будет более точным =)
select Doc_ID, DocDate, Doc_Type, C_ID, col, Price, 
sum(col) over(partition by GroupNum order by DocDate, Doc_Id ) Total
from 
(
	select Doc_ID, DocDate, Doc_Type, C_ID, col, Price, 
	sum(case when Doc_Type=0 then 1 else 0 end) over(order by DocDate, Doc_Id) GroupNum 
	from #table
) Q
24 сен 15, 17:32    [18191344]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
_djХомяГ
Guest
sum(col) over(partition by GroupNum order by DocDate, Doc_Id ) Total

Если версия >=2012
24 сен 15, 17:33    [18191351]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Smoke999
Member

Откуда:
Сообщений: 74
Макбет,
версию сервера я указал 2008 R2
24 сен 15, 17:37    [18191373]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Макбет
Member

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

извини, косоглазие в конце дня

with Q as (	select Doc_ID, DocDate, Doc_Type, C_ID, col, Price, 
	(select count(1) from #table tv 
		where tv.DocDate<=tq.DocDate and tv.Doc_ID<=tq.Doc_ID and tv.Doc_Type=0) GroupNum 
	from #table tq)
	
select Doc_ID, DocDate, Doc_Type, C_ID, col, Price, GroupNum,
(select SUM(col) from Q q2
		where q2.DocDate<=q1.DocDate and q2.Doc_ID<=q1.Doc_ID and q2.GroupNum=q1.GroupNum) Total
from 
Q q1
24 сен 15, 17:50    [18191459]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Владислав Колосов
Member

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

Doc_ID равен 1 для всех строк.
24 сен 15, 17:53    [18191477]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Smoke999
Member

Откуда:
Сообщений: 74
Владислав Колосов
Smoke999,

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

Из вашего сообщения непонятны параметры и условия сортировки данных, например. По дате и еще по какому полю?


сброс как я уже написал я сделал (там выделена дата инвентаризации в рамках которой существует данный набор данных), но там подсчет сквозной (в рамках товара), но надо чтобы если у товара разная поставка (С_ID) ,цена то и количество следовательно считаться должно свое (но разными могут быть не только цена, там еще раз повторюсь порядка 20 столбцов).
Там все решалось если в цикле повторять мой пример то получится что Where Price=..... and ..=.. and....... и так много раз. Хочется этого не делать.

Результат запроса таблица движения товаров чтобы показать пользователю почему у него такие остатки на складе и когда минусы как говорится у него образовались.
24 сен 15, 17:56    [18191492]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Smoke999
Member

Откуда:
Сообщений: 74
Макбет
Smoke999,

извини, косоглазие в конце дня

with Q as (	select Doc_ID, DocDate, Doc_Type, C_ID, col, Price, 
	(select count(1) from #table tv 
		where tv.DocDate<=tq.DocDate and tv.Doc_ID<=tq.Doc_ID and tv.Doc_Type=0) GroupNum 
	from #table tq)
	
select Doc_ID, DocDate, Doc_Type, C_ID, col, Price, GroupNum,
(select SUM(col) from Q q2
		where q2.DocDate<=q1.DocDate and q2.Doc_ID<=q1.Doc_ID and q2.GroupNum=q1.GroupNum) Total
from 
Q q1


.. результат такой же как и у меня, наверно придется таким же способом сделать но добавить в условие нужные поля еще
24 сен 15, 18:01    [18191517]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Макбет
Member

Откуда:
Сообщений: 72
Владислав Колосов,

ориентируюсь на желаемый результат
но главное - показать идею, думаю ТС сможет выбрать признак для сортировки, тк в примере данные не стыкуются (на мой взгляд)
Smoke999
Doc_ID DocDate Doc_Type C_ID col Pricetotal
12015-01-01 00:00:00.000111012010
22015-02-01 00:00:00.00011-51205
32015-02-01 00:00:00.00025-1130-1
42015-02-01 00:00:00.0000121202
52015-03-01 00:00:00.00011-10120-8
62015-03-01 00:00:00.000614120-4
72015-04-01 00:00:00.0000121202
72015-04-01 00:00:00.0000551305
72015-04-01 00:00:00.00005513010
82015-05-01 00:00:00.0001121204
92015-06-01 00:00:00.0001141208
102015-06-01 00:00:00.00015-51305
24 сен 15, 18:02    [18191529]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Smoke999
Member

Откуда:
Сообщений: 74
Макбет
Владислав Колосов,

ориентируюсь на желаемый результат
но главное - показать идею, думаю ТС сможет выбрать признак для сортировки, тк в примере данные не стыкуются (на мой взгляд)


.. данные стыкуются. Просто там где другой Price, C_ID свой итог
24 сен 15, 18:04    [18191542]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в подсчете нарастающего итога  [new]
Smoke999
Member

Откуда:
Сообщений: 74
Все, вопрос решен, всем спасибо!)
25 сен 15, 12:37    [18194368]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить