Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Здравствуйте Господа,
Настало время немного размять могзи.

Есть такая исходная задача -- посчитать против каждого заказа пользователя количество его заказов ЗА ПОСЛЕДНИЕ ПОЛГОДА от даты РАССМАТРИВАЕМОГО ЗАКАЗА. Не от текущей даты, а от даты РАССМАТРИВАЕМОГО ЗАКАЗА.

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


use tempdb
go 

CREATE TABLE T(
	Order_ID INT NOT NULL IDENTITY (0,1), 
	User_Id INT NULL, 
	DT DATE NULL
)
GO
SET NOCOUNT ON 
DECLARE @C INT = 12 * 5;

WHILE @C > 0
BEGIN 
	INSERT INTO T(User_Id) VALUES(DEFAULT)
	SET @C = @C - 1
END 
;
UPDATE T 
SET User_Id = Order_ID % 5, DT = DATEADD(month, Order_ID % 12, '2012-01-01') 
GO
SELECT 
	T.*, 
	Window_Count = SUM(1) OVER (PARTITION BY User_Id ORDER BY DT RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
	-- SUM(1) ИЛИ COUNT(*), COUNT(order_id)
	-- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- чисто для прикола -- сработает и без него 
FROM T 
ORDER BY Order_ID
GO 

DROP TABLE T
GO


(Гранулярность до дня. Порядок заказов в течение одного несущественный.)

Вопрос. Как посчитать с ограничением за полгода.

Для справки, в ORACLE есть оконная конструкция, которая просто позволяет решить эту задачу без геморроя.

RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND CURRENT ROW
http://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#DWHSG8659
SELECT time_id, daily_sum, SUM(daily_sum) OVER (ORDER BY time_id
RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND CURRENT ROW)
AS current_group_sum
FROM (SELECT time_id, channel_id, SUM(s.quantity_sold)
AS daily_sum
FROM customers c, sales s, countries
WHERE c.cust_id=s.cust_id
  AND c.country_id = countries.country_id
  AND s.cust_id IN (638, 634, 753, 440 ) AND s.time_id BETWEEN '01-MAY-00'
  AND '13-MAY-00' GROUP BY time_id, channel_id);


В SQL SERVER такой нет. Вопрос, у кого есть какие предложения как ЭФФЕКТИВНО решить эту задачу.

Сразу скажу, что предложения вида

T T1 JOIN T T2 ON T1.UserId = T2.UserId AND T1.DT < T2.DT 


и им аналогичные сразу не интересны. Так как 100+ лямов строк.

Как это решить на оконных функциях? Или менее ресурсоемко.

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

У кого есть идеи на эту тему?
22 июл 15, 16:26    [17922189]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
komrad
Member

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

а не смотрели OFFSET & FETCH NEXT у ORDER BY (работает только с 2012 и выше)?
22 июл 15, 16:34    [17922225]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
komrad
Member

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

а не смотрели OFFSET & FETCH NEXT у ORDER BY (работает только с 2012 и выше)?

ссылка
22 июл 15, 16:34    [17922232]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Проблема-то в том, как я понимаю, что RANGE работает только со значением поля,
а здесь - дата, от которой так просто 6 месяцев не отнимешь.
Правильно?
22 июл 15, 16:39    [17922262]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
komrad
a_voronin,

а не смотрели OFFSET & FETCH NEXT у ORDER BY (работает только с 2012 и выше)?


У оконного ORDER BY ? Не получится. Или вы предлагаете подзапрос с OFFSET ?
22 июл 15, 16:39    [17922264]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
iap
Проблема-то в том, как я понимаю, что RANGE работает только со значением поля,
а здесь - дата, от которой так просто 6 месяцев не отнимешь.
Правильно?


Да. Можно было бы LAG LEAD взять, но неизвестно сколько этих строк за последние 6 месяцев -- это динамическая величина.
22 июл 15, 16:41    [17922276]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
получится же лесенка:
число заказовномер заказа
51
42
33
23
15


поэтому просто пронумеровать в обратном порядке.
22 июл 15, 17:37    [17922619]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Владислав Колосов
получится же лесенка:
число заказовномер заказа
51
42
33
23
15


поэтому просто пронумеровать в обратном порядке.


Тут не лесенка должна получиться. А значение, которое у регулярного покупателя должно колебаться вокруг величины его среднего числа покупок.
22 июл 15, 17:43    [17922652]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
Владислав Колосов
Member

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

задача стоит так:
посчитать против каждого заказа пользователя количество его заказов ЗА ПОСЛЕДНИЕ ПОЛГОДА от даты РАССМАТРИВАЕМОГО ЗАКАЗА

т.е. количество заказов от текущего до окончания всех заказов до сегодняшнего момента. Именно это я и показал. Где в задаче речь о среднем или о направлении движения? Причем здесь гранулярность? В задаче нет ни слова об этом.
22 июл 15, 17:52    [17922689]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
a_voronin
Member

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

задача стоит так:
посчитать против каждого заказа пользователя количество его заказов ЗА ПОСЛЕДНИЕ ПОЛГОДА от даты РАССМАТРИВАЕМОГО ЗАКАЗА

т.е. количество заказов от текущего до окончания всех заказов до сегодняшнего момента. Именно это я и показал. Где в задаче речь о среднем или о направлении движения? Причем здесь гранулярность? В задаче нет ни слова об этом.



Результат на тестовом примере выше, полученный медленным способом через селф-джойн, выглядит вот так

use tempdb
go 

CREATE TABLE T(
	Order_ID INT NOT NULL IDENTITY (0,1), 
	User_Id INT NULL, 
	DT DATE NULL
)
GO
SET NOCOUNT ON 
DECLARE @C INT = 12 * 5;

WHILE @C > 0
BEGIN 
	INSERT INTO T(User_Id) VALUES(DEFAULT)
	SET @C = @C - 1
END 
;
UPDATE T 
SET User_Id = Order_ID % 5, DT = DATEADD(month, Order_ID % 12, '2012-01-01') 
GO
--SELECT 
--	T.*, 
--	Window_Count = SUM(1) OVER (PARTITION BY User_Id ORDER BY DT RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
--	-- SUM(1) ИЛИ COUNT(*), COUNT(order_id)
--	-- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- чисто для прикола -- сработает и без него 
--FROM T 
--ORDER BY User_Id, DT
--GO 


SELECT 
	T1.Order_ID, t1.User_Id, t1.DT, Window_Count = COUNT(*) - 1
FROM T T1 JOIN T T2 ON T1.User_Id = T2.User_Id AND T2.DT BETWEEN DATEADD(MONTH, -6, T1.DT) AND T1.DT 
GROUP BY T1.Order_ID, t1.User_Id, t1.DT
ORDER BY User_Id, DT
GO 

DROP TABLE T
GO


В исходном скрипте неточность. Надо RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
22 июл 15, 18:00    [17922724]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
window spool_
Guest
a_voronin,

оракловый range быстро работает (с интервалом) ?

потому, что sql server'ный range очень угрюмо работает даже в том виде, что есть

14379134
22 июл 15, 19:00    [17922898]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
churupaha`
Guest
вот тут еще колупался, как медленно это поделье работает (range) (даже в том виде что есть)
тынц
22 июл 15, 19:08    [17922912]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
o-o
Guest
а churupaha под ударением это churupaha под градусом?
22 июл 15, 19:23    [17922939]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
churupaha`
Guest
o-o
а churupaha под ударением это churupaha под градусом?


поставил cookie monster в firefox и пока не настроил.
22 июл 15, 19:27    [17922944]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
o-o,

Вот есть у нас такие значение (индекс последовательности, значения)

SELECT 
	*
FROM (VALUES (1, 10), (2, 30), (3, 70), (4, 100), (5, 600), (6, 400), (7, 30), (8, 50) ) T(I,V)


Как найти все пары I:(J, K), где SUM(V) от J до К <= 180, А где SUM(V) от J-1 до К > 180 ?
22 июл 15, 19:38    [17922956]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
o-o
Guest
a_voronin,

Лучше без пар и оконных ф-ций, индексированное вью, завтра попробую
22 июл 15, 20:02    [17923026]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
a_voronin,

в порядке бреда, если ваше полгода назад рассчитывается с точностью до дня, можно попытаться свести к прыжкам по lead/lag,

1) надо сгруппировать select count(1) as daily_cnt ... from продукт, дата (индекс по продукт, дата)
2) рассчитать нарастающий итог sum() over (partition by продукт order by дата) as running_total_cnt (сортировки поидее не будет в плане)
3) дополнить дырки в датах в (2) парами дата,0 недостающими датами (сохранить бы сортировку предыдущего шага)
4) с помощью lag(...) over (partition by продукт, дата) прыгать назад на 365 . 2 строк и отнимая running_total_current - running_total_prev получать количество за полгода для данной пары продукт, дата с точностью до дня
5) так как вам нужна величина для каждой исходной строки - заджойнить по паре продукт,дата с исходной таблицей.

если при переходе от 2 к 3 удастся в плане сохранить сортировку по продукт, дата может и получится все одним запросом, а так где-то можно indexed vew, где-то temporary table + индексы.
22 июл 15, 20:21    [17923081]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
churupaha,

автор
1) надо сгруппировать select count(1) as daily_cnt ... from продукт, дата (индекс по продукт, дата)


fix

автор
1) надо сгруппировать select count(1) as daily_cnt ... from src_tbl group by продукт, дата (индекс по продукт, дата)
22 июл 15, 20:23    [17923084]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
опечаток тьма, надеюсь смысл передал. на sql писать че-то лень. это надо таблицы создавать, тестовые данные.

в порядке бреда, если ваше полгода назад рассчитывается с точностью до дня, можно попытаться свести к прыжкам по lead/lag,

1) надо сгруппировать select count(1) as daily_cnt ... from src_tbl group by продукт, дата (индекс по продукт, дата)
2) рассчитать нарастающий итог sum() over (partition by продукт order by дата) as running_total_cnt (сортировки поидее не будет в плане)
3) дополнить дырки в датах в (2) парами дата,0 недостающими датами (сохранить бы сортировку предыдущего шага)
4) с помощью lag(..., 365 / 2) over (partition by продукт order by дата) as running total_prev прыгать назад на 365 / 2 строк и отнимая running_total_current - running_total_prev, получать количество за полгода для данной пары (продукт, дата) с точностью до дня
5) так как вам нужна величина для каждой исходной строки - заджойнить (5) по паре (продукт,дата) с исходной таблицей.

если при переходе от 2 к 3 удастся в плане сохранить сортировку по продукт, дата может и получится все одним запросом, а так где-то можно indexed vew, где-то temporary table + индексы.
22 июл 15, 20:28    [17923092]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
Jaffar
Member

Откуда:
Сообщений: 633
declare @__TEMP table(ID int identity(1, 1), IDCLient int, Data datetime)
insert @__TEMP(IDClient, Data)
values (1, '2013-01-01'), (1, '2015-02-01'), (1, '2015-03-01'), (1, '2015-04-01'), (1, '2015-05-01'), (1, '2015-06-01'),  (1, '2015-06-02'),  (1, '2015-06-05'),
       (1, '2015-07-01'), (1, '2015-08-01'), (1, '2015-09-01'), (1, '2015-10-01'), (1, '2015-11-01'), (1, '2015-12-01'),
       (2, '2015-01-01'), (2, '2015-02-01'), (2, '2015-03-01'), (2, '2015-04-01'), (2, '2015-05-01'), (2, '2015-06-01'),  (2, '2015-06-02'),  (2, '2015-06-05'),
       (2, '2015-07-01'), (2, '2015-08-01'), (2, '2015-09-01'), (2, '2015-10-01'), (2, '2015-11-01')
select
z.*,
tp1.Data_prev,
tp2.Count_Prev
from @__TEMP z
cross apply(select dateadd(day, 180, z.Data) Data_prev /**/) tp1
cross apply(select count(1) Count_Prev
			from @__TEMP z2
			where
					z2.IDClient = z.IDClient
			and		z2.Data between z.Data and tp1.Data_prev
			and		z2.ID != z.ID /**/ ) tp2
23 июл 15, 11:05    [17924714]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
o-o
Guest
индексированное вью пролетает, т.к. self-join.
попробую вот такой вариант с apply:
select *
from a.voronin v1 cross apply (select COUNT(*) as cnt
                               from a.voronin v2 
                               where v2.CustomerID = v1.CustomerID and 
                                     v2.OrderDate >= dateadd(dd, -180, v1.OrderDate) and v2.OrderDate < v1.OrderDate)a;

пока что у меня еще только данные готовятся в в кол-ве 100 млн строк
23 июл 15, 11:40    [17924964]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
o-o
Guest
почти 22 минуты считало.
если на всей таблице.
но неужели кому-то надо вывести все 104,5 млн строк?
с разумным фильтром будет разумное же время.

исходная таблица Sales.SalesOrderHeader взята из AdventureWorks,
2000 ее копий с измененной датой слито в таблицу a.voronin
+

create table a.log(id int);
go

declare @i int = 1;
while @i <= 2000
begin
   print @i;
   insert into a.voronin(CustomerID, OrderDate)
   select CustomerID, dateadd(dd, @i, OrderDate)
   from Sales.SalesOrderHeader;
   set @i = @i + 1;
end;
go

alter table a.voronin add constraint PK_voronin_SalesOrderID primary key clustered (SalesOrderID);
go

create index ix_voronin_CustomerID_OrderDate on a.voronin(CustomerID, OrderDate);
go

set statistics io on;
set statistics time on;

declare @SalesOrderID int,
        @OrderDate datetime,
        @DueDate datetime,
        @cnt int;
select @SalesOrderID = v1.SalesOrderID,
       @OrderDate = v1.OrderDate,
       @DueDate = v1.DueDate,
       @cnt = a.cnt
from a.voronin v1 cross apply (select COUNT(*) as cnt
                               from a.voronin v2 
                               where v2.CustomerID = v1.CustomerID and 
                                     v2.OrderDate >= dateadd(dd, -180, v1.OrderDate) and v2.OrderDate < v1.OrderDate)a;

Table 'voronin'. Scan count 43123962, logical reads 307229550, physical reads 186893, read-ahead reads 2869221, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 208899074, logical reads 885850213, physical reads 2153, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 5363610 ms, elapsed time = 1300966 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.


К сообщению приложен файл. Размер - 55Kb
23 июл 15, 12:24    [17925171]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
o-o
почти 22 минуты считало.
если на всей таблице.
но неужели кому-то надо вывести все 104,5 млн строк?


Маркетологи решили насчитать скользящую RFM сегментацию. Это в куб пойдёт. Спасибо.
23 июл 15, 12:27    [17925181]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
a_voronin
в куб пойдёт. Спасибо.

а всякие scope/ paralelperiod не взлетели?
23 июл 15, 12:35    [17925200]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать в SQL SERVER оконный RANGE BETWEEN INTERVAL ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
StarikNavy
a_voronin
в куб пойдёт. Спасибо.

а всякие scope/ paralelperiod не взлетели?


Не знаю о чём вы, честно говоря задачей занимаюсь даже не я.
23 июл 15, 12:53    [17925268]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить