ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Опять Нарастающий итог
10 сообщений из 10, страница 1 из 1
Опять Нарастающий итог
    #37053848
Фотография orunbek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
orunbek
Участник
Приветствую всех.
Проблема с нарастающим итогом, смотрел FAQ, но не смог переделать под свой случай.
Необходимо реализовать без курсоров и CTE.
Есть таблица оплат клиентов, скажем
2010-11-01 10:57:56 1 21000
2010-11-01 15:55:43 1 19000
2010-11-03 08:51:17 1 30000
2010-11-03 14:09:16 1 15000
2010-11-04 09:01:15 1 25000
2010-11-05 09:33:28 1 35000


Необходимо получить нарастающий итог с добавлением входящего определенного значения - 1000, т.е.
2010-11-01 10:57:56 1 1000 21000 22000
2010-11-01 15:55:43 1 22000 19000 41000
2010-11-03 08:51:17 1 41000 30000 71000
2010-11-03 14:09:16 1 71000 15000 86000
2010-11-04 09:01:15 1 86000 25000 111000
2010-11-05 09:33:28 1 111000 35000 146000


делаю так:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
declare
	@inc money,
	@dt1 datetime, @dt2 datetime
	
set @inc= 1000 
set @dt1='2010-11-01'
set @dt2='2010-11-05'

select
	cp1.DT,
	@inc as Inc,
	cp1.PaySum,
	(@inc+(select isnull(SUM(PaySum), 0 ) from TblClientPayments cp2 where cp2.DT<=cp1.DT and cp2.Client_ID=cp1.Client_ID)) AS PT
from
	TblClientPayments cp1
where
	cp1.Client_ID= 1  and cp1.DT>=@dt1 and cp1.DT<DATEADD(DAY, 1 ,@dt1)
order by
	cp1.DT

Но не могу передать в качестве входящего остатка накопленное значение.
Подскажите камрады.

Скрипты для создания таблиц и заполнения тестовыми данными:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
CREATE TABLE [dbo].[TblClientPayments](
	[ID] [bigint] NOT NULL,
	[DT] [datetime] NOT NULL,
	[Client_ID] [bigint] NOT NULL,
	[PaySum] [money] NOT NULL,
 CONSTRAINT [PK_TblClientPayments] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO TblClientPayments (DT, Client_ID, PaySum)
SELECT '2010-11-01 10:57:56',  1 ,  21000 

INSERT INTO TblClientPayments (DT, Client_ID, PaySum)
SELECT '2010-11-01 15:55:43',  1 ,  19000 

INSERT INTO TblClientPayments (DT, Client_ID, PaySum)
SELECT '2010-11-03 08:51:17',  1 ,  30000 

INSERT INTO TblClientPayments (DT, Client_ID, PaySum)
SELECT '2010-11-03 14:09:16',  1 ,  15000 

INSERT INTO TblClientPayments (DT, Client_ID, PaySum)
SELECT '2010-11-04 09:01:15',  1 ,  25000 

INSERT INTO TblClientPayments (DT, Client_ID, PaySum)
SELECT '2010-11-05 09:33:28',  1 ,  35000 , 00
...
Рейтинг: 0 / 0
12.01.2011, 09:48
| Ответить | Цитировать | Написать  
Опять Нарастающий итог
    #37053896
Geep
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Geep
Участник
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
declare
	@inc money,
	@dt1 datetime, @dt2 datetime
	
set @inc= 1000 

select
	cp1.DT,
	cp1.PaySum,
	@inc + cp1.PaySum 
	+ isnull((select sum(x.PaySum) from TblClientPayments x
	where x.Client_ID = cp1.Client_ID and x.DT < cp1.DT
	), 0 )
from
	TblClientPayments cp1
order by
	cp1.DT
...
Рейтинг: 0 / 0
12.01.2011, 10:12
| Ответить | Цитировать | Написать  
Опять Нарастающий итог
    #37053915
Фотография orunbek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
orunbek
Участник
Geep,

не выдает нужный результат, мне нужно чтобы был и входящий остаток и чтобы он переходил от предыдущей записи
но! ваш код натолкнул меня на такой вариант:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
declare
	@inc money,
	@dt1 datetime, @dt2 datetime
	
set @inc= 1000 
set @dt1='2010-11-01'
set @dt2='2010-11-05'

select
	cp1.DT,
	(@inc+(select isnull(SUM(PaySum), 0 ) from TblClientPayments cp2 where cp2.dt>=@dt1 and cp2.DT<cp1.DT and cp2.Client_ID=cp1.Client_ID)) as Inc,
	cp1.PaySum,
	(@inc+(select isnull(SUM(PaySum), 0 ) from TblClientPayments cp2 where cp2.DT<=cp1.DT and cp2.Client_ID=cp1.Client_ID)) AS PT
from
	TblClientPayments cp1
where
	cp1.Client_ID= 1  and cp1.DT>=@dt1 and cp1.DT<DATEADD(DAY, 1 ,@dt2)
order by
	cp1.DT

Что выдает нужный результат, но может быть есть решение более оптимальнее, потому как данных будет "куча", на тестовых записях все срабатывает быстро, но на больших не будет ли проблем ;-(
...
Рейтинг: 0 / 0
12.01.2011, 10:20
| Ответить | Цитировать | Написать  
Опять Нарастающий итог
    #37054097
Фотография orunbek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
orunbek
Участник
можно ли последнйи вариант преобразовать под вариант в FAQ ???
...
Рейтинг: 0 / 0
12.01.2011, 11:36
| Ответить | Цитировать | Написать  
Опять Нарастающий итог
    #37054297
Фотография orunbek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
orunbek
Участник
Нашел одну здесь , где рассматриваются различные методы решения задачи с нарастающим итогом.
Автор показывает метод с применением локального переменного. При таком методе время выполнения меньше всех методов (подзапрос, self join, курсор):

 

Recently I was looking at an existing view on a client's SQL server 2005 database. This view calculated the running total for a transaction amount from a table, but was performing very poorly.
I had always believed there were three different methods for calculating a running total using TSQL:
1. Use a nested sub-query
2. Use a self join
3. Use Cursors
My own personal preference was to use the cursors option. If the cursor guidelines are followed, I've always found this to be the quickest, because the other two methods involve multiple scans of the table. The key for the cursor method is to ensure the data you are "cursoring" through is in the correct order, as the query optimzier does not understand cursors. This usually means cursoring through the data by clustered index, or copying the data into a temp table / table var first, in the relevant order.
A blog posted by Garth Wells back in 2001 gives these three techniques ( http://www.sqlteam.com/article/calculating-running-totals)
I came across a fourth technique for the running total calculation, which is related to the cursor method. Like the cursor method, it involves a single scan of the source table, then inserting the calculated running total for each row into a temp table or table variable. However, instead of using a cursor, it makes use of the following UPDATE command syntax:
UPDATE table
SET variable = column = expression
The TSQL to calculate the running total is:

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @RunningTotal money

SET @RunningTotal = 0

INSERT INTO @SalesTbl
SELECT DayCount, Sales, null
FROM Sales
ORDER BY DayCount

UPDATE @SalesTbl
SET @RunningTotal = RunningTotal = @RunningTotal + Sales
FROM @SalesTbl

SELECT * FROM @SalesTbl

I tested this query along with the other three methods on a simple set of test data (actually the same test data from Garth Wells’ blog mentioned above).
The results of my test runs are:

Nested sub-query=9300 ms
Self join=6100 ms
Cursor=400 ms
Update to local variable=140 ms

I was surprised just how much faster using the “Update to a local variable” method was. I expected it to be similar to the cursor method, as both involve a single scan of the source table, and both calculate the running total once only for each row in the table. The Nested Sub-query and Self join methods are so much slower because they involve the repeated recalculation of all of the previous running totals.


вот код:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)
 
DECLARE @RunningTotal money
 
SET @RunningTotal =  0 
 
INSERT INTO @SalesTbl 
SELECT DayCount, Sales, null
FROM Sales
ORDER BY DayCount
 
UPDATE @SalesTbl 
SET @RunningTotal = RunningTotal = @RunningTotal + Sales
FROM @SalesTbl 
 
SELECT * FROM @SalesTbl

Скрипт создания таблицы и заполнения тестовыми данными:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE TABLE Sales (DayCount smallint, Sales money)
CREATE CLUSTERED INDEX ndx_DayCount ON Sales(DayCount)
go
INSERT Sales VALUES ( 1 , 120 )
INSERT Sales VALUES ( 2 , 60 )
INSERT Sales VALUES ( 3 , 125 )
INSERT Sales VALUES ( 4 , 40 )
 
DECLARE @DayCount smallint, @Sales money
SET @DayCount =  5 
SET @Sales =  10 
 
WHILE @DayCount <  5000 
BEGIN
INSERT Sales VALUES (@DayCount,@Sales)
SET @DayCount = @DayCount +  1 
SET @Sales = @Sales +  15 
END

Не могу переделать данный вариант под свой случай, подсобите други
...
Рейтинг: 0 / 0
12.01.2011, 13:01
| Ответить | Цитировать | Написать  
Опять Нарастающий итог
    #37054343
Фотография orunbek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
orunbek
Участник
2 раза выполняется UPDATE:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
declare
	@inc money,
	@pays_total money
declare
	@sales table (
		dt datetime,
		day_inc money,
		pays money,
		day_out money
	)

set @inc= 1000 

insert into @sales
select cp.DT,  0 , cp.PaySum,  0 
from TblClientPayments cp with (nolock)
where cp.Client_ID= 1  and cp.DT>='2010-11-01' and cp.DT<'2010-11-06'
order by cp.DT

set @pays_total= 0 
update
	@sales
set
	@pays_total=day_out=@inc+@pays_total+pays

update
	@sales
set
	day_inc=day_out-pays
...
Рейтинг: 0 / 0
12.01.2011, 13:18
| Ответить | Цитировать | Написать  
Опять Нарастающий итог
    #37054366
aleks2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks2
Участник
orunbek
Не могу переделать данный вариант под свой случай, подсобите други

Ну и фигли тут делать?

Код: plaintext
SET @RunningTotal = @inc

и ФСЕ.
...
Рейтинг: 0 / 0
12.01.2011, 13:26
| Ответить | Цитировать | Написать  
Опять Нарастающий итог
    #37054395
Фотография orunbek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
orunbek
Участник
aleks2,

Мне нужен не только нарастающий итог, но и входящий остаток
а входящий остаток это нарастающий итог от предыдущей записи, т.е.
при этом входящий остаток берется от переменной (@inc = 1000)

IncomingPaysRunningTotal@inc 1000 20002000 500 25002500 3000 5500
...
Рейтинг: 0 / 0
12.01.2011, 13:37
| Ответить | Цитировать | Написать  
Опять Нарастающий итог
    #37059374
Фотография orunbek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
orunbek
Участник
решил покамест сделать через двойной UPDATE, если у кого-нибудь будет более реальный вариант
дайте знать, спасибо
...
Рейтинг: 0 / 0
14.01.2011, 16:14
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
Опять Нарастающий итог
    #40119594
shalomb
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
shalomb
Гость
UPDATE SalesTbl SET @RunningTotal=@RunningTotal=RunningTotal + Sales
FROM @SalesTbl
Есть один не достаток в этой команде если в один день было больше одного платежа ,то начинается танец в бубном.
...
Рейтинг: 0 / 0
13.12.2021, 12:00
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Опять Нарастающий итог
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=37059374&tid=1684019]:
 0ms
get settings:
 11ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 40ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 48ms
get tp. blocked users:
 1ms
others: 14ms
total:  149ms
созд. / загр.: 149ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]