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

Откуда:
Сообщений: 6
Всем добрый день.

Имеется задачка по поиску промежутков дат. Пытаюсь её реализовать на TSQL.

Например, если кто-то поставил цену 1000, потом снизил до 900, а потом вообще убрал свое предложение, то это выглядит так:
agent price since
1 1000 1 апреля
1 900 2 апреля
1 NULL 3 апреля

Агентов может быть много, и они могут по многу раз менять свои предложения.

Нужно выяснить периоды, в течение которых были предложения хоть от кого-то. Т. е. из этого:

agent price since
1 1000 1 мая
1 900 5 мая
1 NULL 10 мая
1 1100 20 мая
1 NULL 25 мая
2 1200 3 мая
2 1300 12 мая
2 NULL 15 мая

необходим следующий результат:

begin end какая_нибудь_функция_например_MIN(price)
1 мая 15 мая 900
20 мая 25 мая 1100

потому что между 15 и 20 мая предложений не было, а потом они снова появились.

Добрался до следующего момента:

create table Stest(
agent int,
price int,
since date);

create table Stest1(
id int,
agent int,
price int,
bd date,
ed date);

insert into Stest values (1, 1000, '2013-05-01'),
(1, 900, '2013-05-05'),
(1, null, '2013-05-10'),
(1, 1100, '2013-05-20'),
(1, null, '2013-05-25'),
(2, 1200, '2013-05-03'),
(2, 1300, '2013-05-12'),
(2, null, '2013-05-15');

with Stest2 as
(
select *, ROW_NUMBER() over(order by agent) as n from Stest
)

insert into Stest1
select ROW_NUMBER() over(order by (select 1)) as id, t1.agent, t1.price, t1.since as bd, t2.since as ed
from Stest2 t1 join Stest2 t2 on t1.n = t2.n - 1
where t1.price is not null
order by t1.agent;

Получил таблицу малых промежутков дат:

id agent price bd ed
1 1 1000 2013-05-01 2013-05-05
2 1 900 2013-05-05 2013-05-10
3 1 1100 2013-05-20 2013-05-25
4 2 1200 2013-05-03 2013-05-12
5 2 1300 2013-05-12 2013-05-15

Проблема у меня с нахождением внутреннего диапазона от min(bd) до max(ed), как этот алгоритм можно реализовать?
10 июл 13, 17:29    [14548526]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
ПОИСК ,тема тока сегодня 2 раза всплывала
10 июл 13, 17:30    [14548535]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
Shark_8
Member

Откуда:
Сообщений: 6
Версия MS SQL 2008.
К сожалению не в части нахождения минимального и максимального значения в пересечении любого возожного набора интервалов дат.
10 июл 13, 18:05    [14548768]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4539
по мотивам Собрать интервал из отрезков
10 июл 13, 19:44    [14549141]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
qfqwefqwef
Guest
with t1
as
(
	select *
	from
		(values
			(1, 1000, cast('20130501' as datetime)),
			(1, 900, cast('20130505' as datetime)),
			(1, NULL, cast('20130510' as datetime)),
			(1, 1100, cast('20130520' as datetime)),
			(1, NULL, cast('20130525' as datetime)),
			(2, 1200, cast('20130503' as datetime)),
			(2, 1300, cast('20130512' as datetime)),
			(2, NULL, cast('20130515' as datetime))
		) as d(agent, price, since)
),
t2 as
(
	select *, lag(price) over(partition by agent order by since) as lg
	from t1
),
t4 as
(
	select agent, price, since, sum(coalesce(sign(price), -1)) over(order by since rows between unbounded preceding and current row) as a
	from t2
	where price is null or lg is null
),
t5 as
(
	select
		*,
		lag(a, 1, 0) over(order by since) as prv_a
	from t4
),
t6 as
(
	select *, sum(price) over (order by since rows between unbounded preceding and current row) as g
	from t5
	where prv_a = 0 or a = 0
)
select min(since) as s, max(since) as e, min(price) as price
from t6
group by g
10 июл 13, 22:54    [14549540]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
weefereffew
Guest
пофиксил, теперь если агент 1 ставит NULL и в эту же дату агент 2 ставит НЕ NULL, то промежуток считается продолженным... прокомментировал как мог.

+

with t1
as
(
	select *
	from
		(values
			(1, 1000, cast('20130501' as datetime)),
			(1, 900, cast('20130505' as datetime)),
			(1, NULL, cast('20130510' as datetime)),
			(1, 1100, cast('20130520' as datetime)),
			(1, NULL, cast('20130525' as datetime)),
			(2, 1200, cast('20130503' as datetime)),
			(2, 1300, cast('20130512' as datetime)),
			(2, NULL, cast('20130515' as datetime))
		) as d(agent, price, since)
),
t2 as
(
	select *, lag(price) over(partition by agent order by since) as lg
	from t1
),
t3 as -- выкидываем мясо из промежутков каждого агента, оставляем только начало и конец и признак +1 (начало), -1 (конец)
(
	select *, coalesce(sign(price), -1) as s_or_e
	from t2
	where price is null or lg is null
),
t4 as -- количество активных для каждой строки пересекающихся интервалов
(
	select *, sum(s_or_e) over(order by since, s_or_e desc rows between unbounded preceding and current row) as a
	from t3
	where price is null or lg is null
),
t5 as
(
	select *, lag(a, 1, 0) over(order by since) as prv_a
	from t4
),
t6 as -- выкидываем кишки из пересеченных интервалов, оставляем только два конца и маркируем концы номером интервала
(
	select *, (row_number() over (order by since) - 1) / 2 as g
	from t5
	where prv_a = 0 or a = 0
)
select min(since) as s, max(since) as e, min(price) as price
from t6
group by g

10 июл 13, 23:30    [14549673]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
Добрый Э - Эх
Guest
weefereffew,
ога... lead / lag, sum() over( order by ) и всё это в рамках:
Shark_8
Версия MS SQL 2008.
???
11 июл 13, 02:26    [14549967]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
Добрый Э - Эх
Guest
qfqwefqwef,

опять же, если придираться по полной, то конструкцию окна "rows between unbounded preceding and current row" можно и не указывать, ибо умолчательное окно (при наличии в кляузе OVER сортировки) как раз и равно диапазону строк rows between unbounded preceding and current row. Стало быть, получается "масло масленое". ;)
11 июл 13, 02:43    [14549984]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
Ennor Tiegael
Member

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

declare @t table (
	AgentId int not null,
	Price money null,
	StartDate date not null
);

insert into @t (AgentId, Price, StartDate)
values
(1, 900, '2013-05-05'),
(1, null, '2013-05-10'),
(1, 1100, '2013-05-20'),
(1, null, '2013-05-25'),
(2, 1200, '2013-05-03'),
(2, 1300, '2013-05-12'),
(2, null, '2013-05-15');

select * from @t order by StartDate, AgentId;

with ist as (
	select t.StartDate, row_number() over(order by t.StartDate) as [RN]
	from @t t
	where t.Price is not null
		and not exists (
			select 0
			from (
				select top (1) with ties p.Price
				from @t p
				where p.StartDate < t.StartDate
				order by row_number() over(partition by p.AgentId order by p.StartDate desc)
				) pp
			where pp.Price is not null
		)
)
select pri.StartDate as [Start], ie.StartDate as [End]
from ist pri
	left join ist i on i.RN = pri.RN + 1
	outer apply (
		select top (1) e.StartDate
		from @t e
		where e.StartDate < isnull(i.StartDate, dateadd(d, 1, e.StartDate)) and e.StartDate > isnull(pri.StartDate, dateadd(d, -1, e.StartDate))
			and e.Price is null
		order by e.StartDate desc
	) ie
order by pri.StartDate;
SQL - язык декларативный, в нем несколько иной подход нужен, нежели обычно.

Вы пошли путем, очевидным для клиентских языков - декомпозиция до элементарных кирпичиков и последующая сборка целого. В SQL это плохо работает. Я предпочел найти все начала интервалов (у меня это делается в CTE) - их можно определить как записи с наличествующей ценой, для которых все ближайшие предыдущие записи не содержат цен (NULL, т.е.).

От этого дальше уже нашел и их концы. Ну а нужную вам цену, уверен, сможете прикрутить сами. Да, и подзапрос в CTE лучше материализовать в какую-нибудь таблицу, на больших объемах задохнется.
11 июл 13, 08:19    [14550133]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
ffhjknvddgjj
Guest
Добрый Э - Эх,

"... rows
between unbounded preceding and
current row..."

по умолчанию

"... range
between unbounded preceding and
current row..."

с выгрузкой window spool на диск.

проглядел версию сервера. с rownumber можно тоже.
11 июл 13, 10:13    [14550607]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Добрый Э - Эх
qfqwefqwef,

опять же, если придираться по полной, то конструкцию окна "rows between unbounded preceding and current row" можно и не указывать, ибо умолчательное окно (при наличии в кляузе OVER сортировки) как раз и равно диапазону строк rows between unbounded preceding and current row. Стало быть, получается "масло масленое". ;)
Это не так. По-умолчанию
RANGE UNBOUNDED PRECEDING AND CURRENT ROW
http://msdn.microsoft.com/ru-ru/library/ms189461.aspx

Вот, кстати, недавно интересная тема была:

Physical/Logical operator: Window Spool / Stream Aggregate
11 июл 13, 10:28    [14550719]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
Добрый Э - Эх
Guest
iap, ffhjknvddgjj,

За range / rows в умолчательном окне - каюсь, был неправ. Память подвела.
Оконные функции-то изучал аж в далеком 2000-м году. ;)
11 июл 13, 10:48    [14550841]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
wergwergwergwer
Guest
Добрый Э - Эх,

2008 от left join избавиться не получилось

with t1 as
(
	select *
	from
		(values
			(1, 1000, cast('20130501' as datetime)),
			(1, 900, cast('20130505' as datetime)),
			(1, NULL, cast('20130510' as datetime)),
			(1, 1100, cast('20130520' as datetime)),
			(1, NULL, cast('20130525' as datetime)),
			(2, 1200, cast('20130503' as datetime)),
			(2, 1300, cast('20130512' as datetime)),
			(2, NULL, cast('20130515' as datetime))
		) as d(agent, price, since)
),
t2 as
(
	select *, row_number() over (order by agent, since) rn1
	from t1
),
t3 as
(
	select tt1.*, coalesce(sign(tt1.price), -1) as s_or_e, row_number() over (order by tt1.since) as se_cnt
	from t2 tt1 left join t2 tt2 on tt1.rn1 - 1 = tt2.rn1
	where tt2.price is null or tt1.price is null
),
t4 as
(
	select *, row_number() over(order by since) as s_cnt, null as e_cnt
	from t3
	where s_or_e = 1

		union all

	select *, null as s_cnt, row_number() over(order by since) as e_cnt
	from t3
	where s_or_e = -1
),
t5 as
(
	select since, price, (row_number() over (order by since) - 1) / 2 as g
	from t4
	where coalesce(s_cnt - (se_cnt - s_cnt) - 1, (se_cnt - e_cnt) - e_cnt) = 0
)
select min(since), max(since), min(price)
from t5
group by g
11 июл 13, 11:23    [14551106]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
Добрый Э - Эх
Guest
wergwergwergwer,

теоретически, для избавления LEFT JOIN-а и получения результатов вычислений, аналогичных твоим T2 и T3, можно воспользоваться вот таким "финтом ушами":
with t1 as
(
	select *
	from
		(values
			(1, 1000, cast('20130501' as datetime)),
			(1, 900, cast('20130505' as datetime)),
			(1, NULL, cast('20130510' as datetime)),
			(1, 1100, cast('20130520' as datetime)),
			(1, NULL, cast('20130525' as datetime)),
			(2, 1200, cast('20130503' as datetime)),
			(2, 1300, cast('20130512' as datetime)),
			(2, NULL, cast('20130515' as datetime))
		) as d(agent, price, since)
),
t_2_3 as
(
      select agent, max(price) price, min(since) as since, x_flag as s_or_e,
             row_number() over(order by min(since)) as se_cnt
        from (
               select t0.*,
                      case when price is null then -1 else 1 end as x_flag,
                      row_number() over(partition by agent order by since) - 
                      row_number() 
                            over(partition by agent, case when price is null then -1 else 1 end
                                     order by since) as grp_id
                 from t1 t0
             ) v
       group by agent, x_flag, grp_id
    ),
t4 as
(
	select *, row_number() over(order by since) as s_cnt, null as e_cnt
	from t_2_3
	where s_or_e = 1

		union all

	select *, null as s_cnt, row_number() over(order by since) as e_cnt
	from t_2_3
	where s_or_e = -1
),
t5 as
(
	select since, price, (row_number() over (order by since) - 1) / 2 as g
	from t4
	where coalesce(s_cnt - (se_cnt - s_cnt) - 1, (se_cnt - e_cnt) - e_cnt) = 0
)

select min(since), max(since), min(price)
from t5
group by g
11 июл 13, 12:27    [14551573]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
hertherherth
Guest
Добрый Э - Эх,

отличная мысль, клево!
11 июл 13, 13:41    [14552215]     Ответить | Цитировать Сообщить модератору
 Re: Поиск полных интервалов при пересечении малых интервалов дат.  [new]
Shark_8
Member

Откуда:
Сообщений: 6
Всем принявшим активное участие огромное СПАСИБО.

-- to buser, ага разбирал этот пост.

-- to Добрый Э - Эх, полезно и для MSSQL 2012, т.к. прогресс не стоит на месте, а здесь на живом примере видно, как он движется :)

-- to Ennor Tiegael, работаю в этом направлении, а для этого необходим хороший бэк-граунд.
11 июл 13, 18:09    [14554370]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить