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

Откуда:
Сообщений: 61
SELECT t.[Account]
      ,t.[Code_Currency]
      ,t.[Date_Carry]
      ,t.[Rest]      
  FROM [Storage].[dbo].[rest] t, [Storage].[dbo].[acc] a
  where t.account='40701810400150000111' 
  and t.account=a.account
  and t.[Date_Carry] between (select max(Date_Carry) from [Storage].[dbo].[rest]  (Nolock)  where account=t.Account and Date_Carry<'2015-09-01') and '2015-09-30'
  order by t.[Date_Carry]


Существует некая таблица с остатками по счету.
Требуется усреднить данные за 1 месяц, но соблюдая хронологию. А в таблице данные хранятся об остатках только на день совершения операции.

Итого:

Вот результат запроса
Account	Date_Carry	Rest
40701810400150000111	2015-08-31 00:00:00	580599,09
40701810400150000111	2015-09-01 00:00:00	47481,02
40701810400150000111	2015-09-02 00:00:00	42481,02
40701810400150000111	2015-09-10 00:00:00	64284,02
40701810400150000111	2015-09-14 00:00:00	79191,76
40701810400150000111	2015-09-15 00:00:00	1341210,66
40701810400150000111	2015-09-16 00:00:00	405541,66
40701810400150000111	2015-09-17 00:00:00	199351,01
40701810400150000111	2015-09-29 00:00:00	819208,01
40701810400150000111	2015-09-30 00:00:00	818144,37


А необходимы данные за каждое число. Предпологается выводить и усреднять (avg) по всем счетам (около 20 тыс)

Account	Date_Carry	Rest
40701810400150000111	2015-08-31 00:00:00	580599,09
40701810400150000111	2015-09-01 00:00:00	47481,02
40701810400150000111	2015-09-02 00:00:00	42481,02
40701810400150000111	2015-09-03 00:00:00	42481,02
40701810400150000111	2015-09-04 00:00:00	42481,02
40701810400150000111	2015-09-05 00:00:00	42481,02
40701810400150000111	2015-09-06 00:00:00	42481,02
40701810400150000111	2015-09-07 00:00:00	42481,02
40701810400150000111	2015-09-08 00:00:00	42481,02
40701810400150000111	2015-09-08 00:00:00	42481,02
40701810400150000111	2015-09-09 00:00:00	42481,02
40701810400150000111	2015-09-10 00:00:00	64284,02
40701810400150000111	2015-09-11 00:00:00	64284,02
40701810400150000111	2015-09-12 00:00:00	64284,02
40701810400150000111	2015-09-13 00:00:00	64284,02
40701810400150000111	2015-09-14 00:00:00	79191,76
40701810400150000111	2015-09-15 00:00:00	1341210,66
40701810400150000111	2015-09-16 00:00:00	405541,66
40701810400150000111	2015-09-17 00:00:00	199351,01
40701810400150000111	2015-09-18 00:00:00	199351,01
40701810400150000111	2015-09-19 00:00:00	199351,01
40701810400150000111	2015-09-20 00:00:00	199351,01
40701810400150000111	2015-09-21 00:00:00	199351,01
40701810400150000111	2015-09-22 00:00:00	199351,01
40701810400150000111	2015-09-23 00:00:00	199351,01
40701810400150000111	2015-09-24 00:00:00	199351,01
40701810400150000111	2015-09-25 00:00:00	199351,01
40701810400150000111	2015-09-26 00:00:00	199351,01
40701810400150000111	2015-09-27 00:00:00	199351,01
40701810400150000111	2015-09-28 00:00:00	199351,01
40701810400150000111	2015-09-29 00:00:00	819208,01
40701810400150000111	2015-09-30 00:00:00	818144,37
5 окт 15, 14:41    [18236566]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
Glory
Member

Откуда:
Сообщений: 104760
CheaterX
А необходимы данные за каждое число.

Таблица-календарь со всеми датами на 100 лет вперед легко позволит решить эту задачу
5 окт 15, 14:45    [18236577]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Версия сервера какая? На 2012 никакого календаря не надо, если стоит задача посчитать среднее.
5 окт 15, 14:50    [18236605]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
CheaterX
Member

Откуда:
Сообщений: 61
a_voronin, 2008 R2

Допустим залью еще табличку с календарем (ну я так понимаю все даты за месяц). Как примерно будет выглядеть запрос. Спасибо.
5 окт 15, 14:53    [18236622]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
CheaterX
a_voronin, 2008 R2

Допустим залью еще табличку с календарем (ну я так понимаю все даты за месяц). Как примерно будет выглядеть запрос. Спасибо.


Вам надо сделать связь между предыдущий и последующим (например сделать ROW_NUMBER () OVER (ORDER BY [rest]), а потом самоджойн. На SQL 2012 для этого есть lag lead. Когда у вас будет пара предыдущий последующий можете просто уможить datadiff на значение и от суммы взять среднее.

Если хотите развернуть записи, то OUTER APPLY на последовательность цифр.
5 окт 15, 15:04    [18236683]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
CheaterX
Member

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

Если можно поподробнее про самоджойн и сделать связь между предыдущий и последующим.
Вот этот момент не совсем понятен. А также с аутер эплай для развертки. Не совсем понимаю принцип действия данного оператора. Спасибо!
5 окт 15, 15:19    [18236779]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
Glory
Member

Откуда:
Сообщений: 104760
CheaterX
Как примерно будет выглядеть запрос.

Для каждой даты календаря найти подходящее значение Остатка
5 окт 15, 15:21    [18236787]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
CheaterX
Member

Откуда:
Сообщений: 61
Всё равно немного не понимаю. Скрещу я даты в таблице моей rest с датами таблицы календарь через outer join я так понимаю. А как сделать то чтоб они были не NULL, а заполненные предыдущими. Я понимаю что возможно запрос очень простой, но если не трудно напишите примерно хотя бы. Спасибо!
5 окт 15, 15:45    [18236941]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
Glory
Member

Откуда:
Сообщений: 104760
CheaterX
Скрещу я даты в таблице моей rest с датами таблицы календарь через outer join я так понимаю.
А как сделать то чтоб они были не NULL, а заполненные предыдущими.

Не outer join, а OUTER APPLY
5 окт 15, 15:51    [18236983]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
a_voronin
Member

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

Дальше сами уж допилите

WITH B AS 
(
	SELECT 1  A UNION ALL 
	SELECT 5  A UNION ALL 
	SELECT 8  A UNION ALL 
	SELECT 11 A UNION ALL 
	SELECT 15 A UNION ALL 
	SELECT 25 A UNION ALL 
	SELECT 31 
), 
C AS
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY A), A FROM B
),
D AS 
(
	SELECT C1.A, C2.A AS A_NEXT FROM C C1, C C2 WHERE C1.RN = C2.RN - 1
), 
N AS 
(
	-- сюда до 30
	SELECT 0 AS N UNION ALL 
	SELECT 1 AS N UNION ALL 
	SELECT 2 AS N UNION ALL 
	SELECT 3 AS N UNION ALL 
	SELECT 4 AS N UNION ALL 
	SELECT 5 AS N UNION ALL 
	SELECT 6 AS N 
) 
SELECT D.A, A + NN.N, D.A_NEXT FROM D
OUTER APPLY 
(
	SELECT N.N FROM N 
	WHERE N.N BETWEEN 0 AND D.A_NEXT - D.A - 1
) NN
5 окт 15, 16:36    [18237316]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
declare @t table (dt date, v money);

insert into @t
values
('20150831 00:00:00',	580599.09),
('20150901 00:00:00',	47481.02),
('20150902 00:00:00',	42481.02),
('20150910 00:00:00',	64284.02),
('20150914 00:00:00',	79191.76),
('20150915 00:00:00',	1341210.66),
('20150916 00:00:00',	405541.66),
('20150917 00:00:00',	199351.01),
('20150929 00:00:00',	819208.01),
('20150930 00:00:00',	818144.37);

with a as
(
 select dt, v, row_number() over (order by dt) as rn from @t
)
select
 year(t1.dt) as [year], month(t1.dt) as [month], sum(t1.v * t3.c) / sum(t3.c) as [avg]
from
 a t1 left join
 a t2 on t2.rn = t1.rn + 1 cross apply
 (select isnull(datediff(day, t1.dt, t2.dt), 1)) t3(c)
group by
 year(t1.dt), month(t1.dt);
5 окт 15, 17:24    [18237520]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
o-o
Guest
declare @t table (dt date, val decimal(10,2));
insert into @t values
(	'2015-08-31 00:00:00',	580599.09),
(	'2015-09-01 00:00:00',	47481.02),
(	'2015-09-02 00:00:00',	42481.02),
(	'2015-09-10 00:00:00',	64284.02),
(	'2015-09-14 00:00:00',	79191.76);

with cte as
(
select *, ROW_NUMBER() over (order by dt) as rn
from @t
),

cte1 as
(
select t1.dt, t1.val, isnull(t2.dt, t1.dt) as dt_nxt
from cte t1 left join cte t2 on t2.rn = t1.rn + 1
)

select dateadd(day, isnull(a.n, 1) - 1, c.dt), c.val
from cte1 c outer apply (select n from dbo.nums
                         where n between 1 and  datediff(day, c.dt, c.dt_nxt))a
-----------
(No column name)	val
2015-08-31	580599.09
2015-09-01	47481.02
2015-09-02	42481.02
2015-09-03	42481.02
2015-09-04	42481.02
2015-09-05	42481.02
2015-09-06	42481.02
2015-09-07	42481.02
2015-09-08	42481.02
2015-09-09	42481.02
2015-09-10	64284.02
2015-09-11	64284.02
2015-09-12	64284.02
2015-09-13	64284.02
2015-09-14	79191.76

в dbo.nums у меня числа с 1, поэтому isnull(a.n, 1) - 1
5 окт 15, 17:29    [18237554]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
CheaterX
Member

Откуда:
Сообщений: 61
Спасибо всем! Но немного задача усложнилась.
Средняя хронологическая- средняя, рассчитанная из значений, изменяющихся во времени. Используется для расчета среднего уровня моментного ряда. В том случае, если имеющиеся данные относятся к фиксированным моментам времени c равными интервалами, то используется следующая формула (во вложении). Может кто подскажет как это решить?

К сообщению приложен файл. Размер - 3Kb
7 окт 15, 13:55    [18246692]     Ответить | Цитировать Сообщить модератору
 Re: Среднехронологический остаток (добавление искусственно предыдущих значений)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
CheaterX
Спасибо всем! Но немного задача усложнилась.
Средняя хронологическая- средняя, рассчитанная из значений, изменяющихся во времени. Используется для расчета среднего уровня моментного ряда. В том случае, если имеющиеся данные относятся к фиксированным моментам времени c равными интервалами, то используется следующая формула (во вложении). Может кто подскажет как это решить?


Да ничего в постановке не изменилось. Надо половинку первого и последнего значения вычесть. Уж с этим попробуйте сами справиться.
8 окт 15, 11:41    [18251694]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить