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

Откуда:
Сообщений: 1551
Есть таблица с данными по пластиковым картам:

Карта_ИДДата_открытияДата_закрытия
12013-01-012013-05-01


А также таблица фактических операций по картам:
Дата_операции Сумма Знак_операции
2013-01-01100+
2013-02-1510-
2013-03-0110+


Нужно сделать таблицу остатков по картам НА КАЖДУЮ ДАТУ, начиная с даты, следующей после даты_открытия.
То есть для данной карты на второе число января остаток по ней будет = 100
Третьего февраля = 90 (операция 2013-02-15 на 10 едениц со знаком "-")
Второго марта = опять 100.

Таблица с датами - DimDate (Datekey [date])
Никак у меня не получается...
12 ноя 13, 09:26    [15113095]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
SilverVolg
Member

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

В курсоре гнать, по каждой операции рассчитывать баланс.
12 ноя 13, 09:31    [15113139]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
Santa89
Member

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

пять тыщ карт, фактические операции начиная с 2007го года, миллионов 5 строк получится. сдохнет же...
12 ноя 13, 09:33    [15113148]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
SilverVolg
Member

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

натравливать на пачки записей, а не на весь массив ...
12 ноя 13, 09:41    [15113191]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2428
SilverVolg,

курсор не надо.
куча вариантов, всплыывает каждую неделю подобная тема. ищите поиском, или хоть версию бд озвучьте
12 ноя 13, 09:48    [15113224]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
Santa89
Member

Откуда:
Сообщений: 1551
SQL 2012


select
[Datekey] as [Дата_баланса],
[Карта_Ид],
sum( case when [Знак_операции] = '+' then [Сумма] else [Сумма] * -1 end)

from [Факты_Карты],[DimDate]
where [Дата_операции] <= [Datekey] and [Datekey] between [Дата_открытия] and [Дата_закрытия]
group by [Дата],[Карта_Ид]

Оно ?
12 ноя 13, 09:51    [15113234]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
quest123456
Guest
Santa89,

USE tempdb
GO


DECLARE @date DATE = '20130101'
DECLARE @t TABLE ([date] DATE)
WHILE @date < '20140101'
begin
	INSERT INTO @t(date) VALUES(@date)
	SET @date = DATEADD(DAY, 1, @date)
end;

--SELECT * FROM @t;

WITH crd AS (
	SELECT
		*
	FROM
		(VALUES(1, CAST('20130101' AS DATE), CAST('20130501' AS DATE))) AS crd(id, date_from, date_till)
),
op AS (
	SELECT
		*
	FROM
		(VALUES
			(1, CAST('20130101' AS DATE), 100),
			(1, CAST('20130215' AS DATE), -10),
			(1, CAST('20130301' AS DATE), 10)) AS op(card_id, op_date, mon)
		)
, op2 AS (
	SELECT
		*
	FROM
		op
	
	UNION ALL
		
	SELECT
		crd.id,
		t.date,
		mon = 0
	FROM
		crd 
	CROSS APPLY @t AS t
	WHERE
		NOT EXISTS(
			SELECT * FROM op WHERE t.date = op.op_date AND crd.id = op.card_id)
)
--SELECT * FROM op2

SELECT 
	op1.card_id,
	op1.op_date,
	op1.mon + SUM(ISNULL(op2.mon, 0))
FROM 
	crd
LEFT JOIN
	op2 AS op1
ON
	crd.id = op1.card_id 
AND op1.op_date BETWEEN crd.date_from AND crd.date_till
LEFT JOIN
	op AS op2
ON
	op1.card_id = op2.card_id 
AND op1.op_date > op2.op_date 
AND op2.op_date BETWEEN crd.date_from AND crd.date_till
GROUP BY
	op1.card_id,
	op1.op_date,
	op1.mon 

оно?
12 ноя 13, 10:04    [15113298]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
Jaffar
Member

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

select t.IDCrd, dd.Date, t.RestOnDate
from  ( select --- это нарастающий итог 
        crd.*, 
        b.*,
        sum(case when b.Sign = '-' then -b.Summa else b.Summa end) over(partition by crd.ID order by b.Date asc) RestOnDate, --- нарастает
        IsNULL(lead(b.Date)  over(partition by crd.ID order by b.Date asc) - 1, b.Date) NextDate ---- дата следующей строки, если нет то текущая дата 
        from tcrd_Main crd  with(nolock)
        join tcrd_Balance b with(nolock) on b.IDCard = crd.ID /**/ ) t
--- разварачиваем период в строки
cross apply(select t.Date + fd.NN DayNext 
            from dbo.GenerateNumbers(0, datediff(day, t.Date, t.NextDate), 1) fd /**/ ) dd
order by t.IDCrd, dd.Date

--- dbo.GenerateNumbers - табличная функция(возвращает столбец) генерит числа от P1, до P2, c шагом P3. - напишите ее сами, в качестве Д.З.
--- просто декларите @__TEMP и в цикле в нее вставляете значения.
--- или найдите в инете там я видел что-то покруче через CTE сделано.


ОНО?
12 ноя 13, 11:03    [15113613]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
prog882
Guest
Santa89,
with 
t1 as (
	select id=1,dt1='20130101',dt2='20130501'
),
t2 as (
	select cd='20130101', sm=100, zn='+' union all
	select cd='20130215', sm=10, zn='-' union all
	select cd='20130301', sm=10, zn='+'
),
cte as (
	select dt=cast(dt1 as date), dt2=cast(dt2 as date), sm=0 from t1
	union all
	select dt=dateadd(day, 1, dt), dt2, sm=0 from cte
	where dateadd(day, 1, dt) <= cast(dt2 as date)
)
select cte.dt, 
sm_t=sum(isnull(t2.sm,0)*case when t2.zn='-' then -1 else 1 end+cte.sm)
from cte
left join t2 on cte.dt >= t2.cd	
group by cte.dt
option (maxrecursion 0);
12 ноя 13, 12:49    [15114459]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
Santa89
Member

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

по-моему нужно еще в t2 привязаться к Карта_ИД ??
12 ноя 13, 17:36    [15117122]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
Santa89
Member

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

у меня ведь не одна карта будет...
12 ноя 13, 17:57    [15117346]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
Santa89
по-моему нужно еще в t2 привязаться к Карта_ИД ??
Естественно, привяжитесь к Карта_ИД.
Выше 2 примера, допустим, 15113298
12 ноя 13, 18:08    [15117438]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
Santa89
Member

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

как привязать ИД_Карты в примере от prog882 ?
13 ноя 13, 04:02    [15119040]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
prog882
Guest
Santa89, id - Карта_Ид

with 
t1 as (
	select id=1,dt1='20130101',dt2='20130501'
),
t2 as (
	select cd='20130101', sm=100, zn='+' union all
	select cd='20130215', sm=10, zn='-' union all
	select cd='20130301', sm=10, zn='+'
),
cte as (
	select id, dt=cast(dt1 as date), dt2=cast(dt2 as date), sm=0 from t1
	union all
	select id, dt=dateadd(day, 1, dt), dt2, sm=0 from cte
	where dateadd(day, 1, dt) <= cast(dt2 as date)
)
select cte.id, cte.dt, 
sm_t=sum(isnull(t2.sm,0)*case when t2.zn='-' then -1 else 1 end+cte.sm)
from cte
left join t2 on cte.dt >= t2.cd	
group by cte.id, cte.dt
option (maxrecursion 0);
13 ноя 13, 08:24    [15119168]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
Santa89
Member

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

помогите с таким моментом - теперь мне не нужно опираться на знак, все операции я уже получаю с + или -.
оставил такую строчку: sm_t=sum(isnull(t2.sm,0)*+cte.sm)
но почему-то теперь все значения (суммы) нулевые...я наверное как-то неправильно обрезал?
13 ноя 13, 09:39    [15119366]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
prog882
Guest
Santa89,
sm_t=sum(isnull(t2.sm,0)*+cte.sm)
13 ноя 13, 09:44    [15119397]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
Santa89
Member

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

спасибо.
на 5 тысячах карта и 200 тыс.операций по ним, начиная с 2007 года запрос висит очень долго.

вобще эта таблица остатков будет использоваться для table-valued функции, которая будет возвращать остатки только в определенном промежутке (предположительно по годам), т.е. у нее будет два параметра - @start_date и @end_date или просто@year

скажите, ваш скрипт как-то можно подогнать к тому, чтобы он вычислял остатки для карт, операции у которых попадают в промежуток между @start_date и @end_date?
Напомню, что остатков по картам на первые числа месяца или года - нет. есть только фактические операции.
13 ноя 13, 09:50    [15119425]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
prog882
Guest
Santa89
на 5 тысячах карта и 200 тыс.операций по ним, начиная с 2007 года запрос висит очень долго.
...

Это конечно изврат, но кто будет смотреть/анализировать сто тысяч записей?

Santa89
...
скажите, ваш скрипт как-то можно подогнать к тому, чтобы он вычислял остатки для карт, операции у которых попадают в промежуток между @start_date и @end_date?
Напомню, что остатков по картам на первые числа месяца или года - нет. есть только фактические операции.


т.е. для у каждой карты нулевой остаток с начала заданного периода? Если я правильно понял, что для каждой карты своя сумма операции, то:
with 
t1 as (
	select id=1, dt1='20130101', dt2='20130110' union all
	select id=2, dt1='20130104', dt2='20130106'
),
t2 as (
	select cd='20130102', sm=100, zn='+' union all
	select cd='20130103', sm=-20, zn='-' union all
	select cd='20130106', sm=10,  zn='+' union all
	select cd='20130108', sm=90,  zn='+' union all
	select cd='20130109', sm=-10, zn='-'
),
t3 as (
	select t1.id, t2.cd, t2.sm, t2.zn, t1.dt1, t1.dt2
	from t1
	inner join t2 on t2.cd between t1.dt1 and t1.dt2
),
cte as (
	select id, dt=cast(dt1 as date), dt2=cast(dt2 as date), sm=0 from t1
	union all
	select id, dt=dateadd(day, 1, dt), dt2, sm=0 from cte
	where dateadd(day, 1, dt) <= cast(dt2 as date)
)
select cte.id, cte.dt, 
sm_t=sum(isnull(t3.sm,0)+cte.sm)
from cte 
left join t3 on cte.id = t3.id and cte.dt >= t3.cd
group by cte.id, cte.dt
order by cte.id, cte.dt
option (maxrecursion 0);
13 ноя 13, 10:01    [15119471]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
Santa89
Member

Откуда:
Сообщений: 1551
Сто тысяч записей, это еще мало, такие требования у бизнеса.

Нет, искуственно собирать остатки зная остаток на первую дату выбранного месяца и обороты в этом месяце - легко.
Но у меня есть только измерение(таблица) уникальных карт (сейчас выяснилось что неважно какая у неё дата закрытия - операции по ней всеравно могут идти и после даты закрытия) и все операции по этим картам. Вот сижу и ломаю голову..
Сделал скрипт, но он все остатки с 2007года собирает полтора часа. Это слишком долго, учитывая что карт не так уж и много, всего 5000 тысяч..
13 ноя 13, 10:16    [15119546]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
Santa89
SQL 2012
declare @t table (dt datetime, v int);

insert into @t
values
 ('20130101', 10),
 ('20130101 10:00', 10),
 ('20130101 20:00', 10),
 ('20130103', -10),
 ('20130104', -10),
 ('20130201', 5);

with x as
(
 select
  dt,
  row_number() over (partition by cast(dt as date) order by dt desc) as rn,
  sum(v) over (order by dt) - sum(v) over (partition by cast(dt as date) order by dt) as s
 from
  @t
)
select
 cast(dt as date),
 s
from
 x
where
 rn = 1
order by dt;
13 ноя 13, 10:47    [15119746]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Santa89
таблица фактических операций по картам:
Дата_операции Сумма Знак_операции
2013-01-01100+
2013-02-1510-
2013-03-0110+

Афигительный дизайн таблицы. Знак так и хранится — в виде символа? Есть какая-то причина, по которой не сделан один столбец "Сумма" с положительными и отрицательными значениями?
13 ноя 13, 11:09    [15119902]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
Santa89
Member

Откуда:
Сообщений: 1551
Гость333,

в транзакционной системе есть признак - влияние на баланс, он вобще текстовый, по нему узнается с каким знаком берется сумма операции для того чтобы построить баланс. а для упрощения понимания моей задачки я сделал + и -,
13 ноя 13, 12:15    [15120498]     Ответить | Цитировать Сообщить модератору
 Re: Плиз хэлп с нарастающим итогом  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
Santa89
по нему узнается с каким знаком берется сумма операции для того чтобы построить баланс
А по знаку числа этого понять нельзя?
13 ноя 13, 12:33    [15120683]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить