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

Откуда:
Сообщений: 74
Не очень усердно искал. Если вопрос уже обсуждался пошлите по ссылке или дайте комбинацию слов для поиска.

create table #tmpTbl(DATE_ date, value float)
insert #tmpTbl
select '2012-01-01', 100
union all
select '2012-01-02', 200
union all
select '2012-01-03', 300
union all
select '2012-01-04', 400
union all
select '2012-01-05', 500
union all
select '2012-01-06', 600
union all
select '2012-01-07', 700
union all
select '2012-01-08', 800
union all
select '2012-01-09', 900
union all
select '2012-01-10', null
/* Value(сегодня)+[value(три дня назад)+value(четыре дня назад)-value(1 день назад)]  . */
select 
isnull(t1.value,0)+(isnull(t2.value,0)+isnull(t3.value,0)-isnull(t4.value,0)) 
from #tmpTbl t1
	full join #tmpTbl t2
		on isnull(t2.date_,'1900-01-01') = isnull(dateadd(day,-3,t1.date_),'1900-01-01')
	full join #tmpTbl t3
		on isnull(t3.date_,'1900-01-01') = isnull(dateadd(day,-4,t1.date_),'1900-01-01')
	full join #tmpTbl t4
		on isnull(t4.date_,'1900-01-01') = isnull(dateadd(day,-1,t1.date_),'1900-01-01')
		
drop table #tmpTbl

Есть таблица с данными на дату.
Необходимо для каждой даты в таблице посчитать значение, для расчета которого используется поле value за даты отличные от текущей.

Вот например. Расчет производится по такой формуле: Value(сегодня)+[value(три дня назад)+value(четыре дня назад)-value(1 день назад)]
Что мне приходит в голову, дк это join'ить таблицу саму к себе. Но с большими объемами работает довольно медленно.

Есть другой способ реализовать эту задачу?
18 июн 12, 15:08    [12732035]     Ответить | Цитировать Сообщить модератору
 Re: Связать таблицу саму с собой со смещением по дате  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Надо или считать, или хранить уже посчитанное. Вот и весь выбор.
18 июн 12, 15:42    [12732401]     Ответить | Цитировать Сообщить модератору
 Re: Связать таблицу саму с собой со смещением по дате  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
А зачем full join? O_o
18 июн 12, 15:42    [12732411]     Ответить | Цитировать Сообщить модератору
 Re: Связать таблицу саму с собой со смещением по дате  [new]
Hexogon
Member

Откуда:
Сообщений: 74
расчитанные данные сохраняются.
Full join необходим для расчета значения, если одной из компонент нет. Т.е. значение на текущую дату имет такой же приоритет как и на прошлую
18 июн 12, 16:02    [12732633]     Ответить | Цитировать Сообщить модератору
 Re: Связать таблицу саму с собой со смещением по дате  [new]
RubinDm
Member

Откуда:
Сообщений: 461
Hexogon, мне не очень понятно, откуда берутся большие объемы данных? Если, например, за 100 лет, по одному значению в день... набралось бы немногим более 36500 записей - курам на смех. Или Вы ведете статистику со времен палеолита?

Кроме того, full join - это реально дорого. Дешевле "разориться" местом на диске под одну запись в день, чтобы записать туда хотя бы ноль (если нет реального значения за день). Ноль, кстати, не влияет на результат суммы. Если делать по записи на каждый день, то все full join'ы можно смело менять на inner join'ы. Кроме того, можно будет смело поубивать выражения типа isnull(date, '1900-1-1'). Убийство isnull'ов даст серверу возможность работать с полями, а не с производными от них выражениями isnull. Работа с полями - это круто, потому что по ним еще и индексы можно делать, что наверняка разгонит выборку в разы.
18 июн 12, 19:32    [12734275]     Ответить | Цитировать Сообщить модератору
 Re: Связать таблицу саму с собой со смещением по дате  [new]
qwrqwr
Member

Откуда: Msk
Сообщений: 1684
Hexogon
Вот например. Расчет производится по такой формуле: Value(сегодня)+[value(три дня назад)+value(четыре дня назад)-value(1 день назад)]
Что мне приходит в голову, дк это join'ить таблицу саму к себе. Но с большими объемами работает довольно медленно.
Есть другой способ реализовать эту задачу?

create table #tmpTbl(date_ datetime, value float)
insert #tmpTbl
select '20120101', 100
union all
select '20120102', 200
union all
select '20120103', 300
union all
select '20120104', 400
union all
select '20120105', 500
union all
select '20120106', 600
union all
select '20120107', 700
union all
select '20120108', 800
union all
select '20120109', 900
union all
select '20120110', null;
/* Value(сегодня)+[value(три дня назад)+value(четыре дня назад)-value(1 день назад)]  */
select t1.date_,
isnull(t1.value,0)+(isnull(t2.value,0)+isnull(t3.value,0)-isnull(t4.value,0)) 
from #tmpTbl t1
	full join #tmpTbl t2
		on isnull(t2.date_,'19000101') = isnull(dateadd(day,-3,t1.date_),'19000101')
	full join #tmpTbl t3
		on isnull(t3.date_,'19000101') = isnull(dateadd(day,-4,t1.date_),'19000101')
	full join #tmpTbl t4
		on isnull(t4.date_,'19000101') = isnull(dateadd(day,-1,t1.date_),'19000101');

/* Value(сегодня)+[value(три дня назад)+value(четыре дня назад)-value(1 день назад)]  */
select dt_, isnull(d0,0)+(isnull(d3,0)+isnull(d4,0)-isnull(d1,0)) 
from
(
select max(case n when 0 then date_ end) dt_
     , max(case n when 0 then value end) d0
     , max(case n when 1 then value end) d1
     , max(case n when 3 then value end) d3
     , max(case n when 4 then value end) d4
  from #tmpTbl t 
 cross join (select 0 union all select 1 union all select 3 union all select 4)N(n)
 group by dateadd(day,n,date_) 
having max(case n when 0 then date_ end) is not null
) Q;

drop table #tmpTbl;
?
18 июн 12, 20:48    [12734498]     Ответить | Цитировать Сообщить модератору
 Re: Связать таблицу саму с собой со смещением по дате  [new]
Hexogon
Member

Откуда:
Сообщений: 74
Вот последний пост интересный! Результат, конечно, тот же. Осталось протестировать на большом объеме.

По поводу больших объемов. На дату может быть неограниченное кол-во записей. Например, база данных, где логируются транзакции на бирже.
Не всегда приходится работать со своей БД. Зачастую, приходится иметь дело с источниками, на структуру которых повлиять никак нельзя
19 июн 12, 11:09    [12736516]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить