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

Откуда:
Сообщений: 167
Привет всем!

Есть таблица интервалов c приоритетами
CREATE TABLE @tbl
(
  IntervalPriority INT,
  DateFrom DATETIME,
  DateTill DATETIME,
  Payload DECIMAL
)

Некоторые интервалы могут пересекаться, причем не только попарно. В экстремальном случае каждый пересекается со всеми остальными. Требуется преобразовать таблицу так, чтобы пересечений не было, и с учетом приоритета. Итеративный алгоритм прост: сперва берется интервал с наименьшим приоритетом и из него вычитается (в теоретико-множественном смысле) интервал со следующим по порядку приоритетом. Разница может представлять 0, 1 или 2 интервала. В случае 0 первый интервал удаляется, в случае 1 один из краев исправляется, в случае 2 первый интервал копируется и края изменяются соответственно. Потом берется следующий по приоритету интервал и вышеописанная процедура по очереди применяется ко всем интервалам с меньшим приоритетом. Даты округляются до дней.

Теперь нужно придумать как это выразить в SQL без циклов.
Производительность очень важна, может быть под миллион записей

Должно работать под SQL Server 2005, 2008, 2008R2

Может кто помочь советом?

Спасибо.
13 июн 12, 10:55    [12706155]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Добрый Э - Эх
Guest
SergASh
Может кто помочь советом?

Приведи набор тестовых данных, описывающий все возможные ситуации...
13 июн 12, 11:09    [12706276]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
SergASh
Member

Откуда:
Сообщений: 167
Именно все возможные ситуации покрыть конечными тестовыми данными не получится, потому что
автор
Некоторые интервалы могут пересекаться, причем не только попарно. В экстремальном случае каждый пересекается со всеми остальными.

Вот несколько примеров. Для простоты я дату заменю натуральными числами
INSERT INTO @tbl ( IntervalPriority, DateFrom, DateTill, Payload )

SELECT 1, 5, 7, 12.3
UNION 
SELECT 2, 4, 8, 45.6

UNION 
SELECT 3, 10, 20, 12.3
UNION 
SELECT 4, 15, 25, 45.6

UNION 
SELECT 5, 110, 120, 12.3
UNION 
SELECT 6, 113, 117, 45.6

UNION 
SELECT 7, 210, 230, 12.3
UNION 
SELECT 8, 220, 240, 45.6
UNION 
SELECT 9, 225, 250, 78.9

в результате должно быть вот что

[4, 8]     45.6

[10, 14] 12.3
[15, 25] 45.6

[110, 112] 12.3
[113, 117] 45.6
[118, 120] 12.3

[210, 219] 12.3
[220, 224] 45.6
[225, 250] 78.9

Возможно, пониманию поможет происхождение задачи. Есть таблица прайсов (не в базе данных, просто таблица где-то), действующих в течение определенного промежутка времени. Сегодня Вася записал в нее, что весь октябь безделушка А стоит сто рублей. Завтра Петя написал, что с десятого по двадцатое она стоит девяносто. А потом пришел Федя и сказал, что с середины октября и до конца года она будет стоить сто двадцать. Приоритет, про который написано в исходном посте, это номер записи в этой таблице. Смысл алгоритма в том, чтобы с одной стороны, всегда хранить наиболее свежее обновление цены, а с другой стороны, иметь как можно более полную историю цены. Под наиболее свежим я понимаю запись, внесенную последней. К значениям дат, когда цена действует, это отношения не имеет. Итеративный алгоритм, которые я привел выше, решает задачу в точности. Просьба не предлагать что-то "упростить" на основе этого описания задачи, оно очень поверхностное.
13 июн 12, 12:01    [12706661]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
qweкен
Guest
имхо, это задача не для сервера
13 июн 12, 12:55    [12707039]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Minamoto
Member

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

из чистого интереса написал свое решение. НЕмного не соответствует требуемому (дает не интервалы, а значение для каждой записи), и я практически уверен в его неэффективности (с точки зрения ресурсоемкости), тем не менее:

;with   minmax (min_date, max_date) as
(select MIN(DateFrom) as min_date, 
        MAX(DateTill) as max_date
   from @tbl),
        cte ([date]) as
(select min_date as [date]
   from minmax
  union all
 select [date] + 1 as [date]
   from minmax
        inner join cte 
                on [date] <= max_date),
        ttbl as 
(select cte.date, 
        tbl.Payload, 
        tbl.IntervalPriority, 
        ROW_NUMBER() over (partition by cte.[date] order by tbl.IntervalPriority desc) as rn
   from cte
        inner join @tbl tbl
                on cte.date between tbl.DateFrom and tbl.DateTill)
 select * 
   from ttbl
  where rn = 1
 OPTION (MAXRECURSION 32767)
13 июн 12, 13:41    [12707436]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
qwerty112
Guest
не думаю, что такое можно "пользовать", просто "как вариант" :)
+
declare @tbl TABLE 
(
  IntervalPriority INT,
  DateFrom int,
  DateTill int,
  Payload DECIMAL(18,2)
)
--------
INSERT INTO @tbl ( IntervalPriority, DateFrom, DateTill, Payload )

SELECT 1, 5, 7, 12.3
UNION 
SELECT 2, 4, 8, 45.6

UNION 
SELECT 3, 10, 20, 12.3
UNION 
SELECT 4, 15, 25, 45.6

UNION 
SELECT 5, 110, 120, 12.3
UNION 
SELECT 6, 113, 117, 45.6

UNION 
SELECT 7, 210, 230, 12.3
UNION 
SELECT 8, 220, 240, 45.6
UNION 
SELECT 9, 225, 250, 78.9

-- 

declare @res table
(
  IntervalPriority INT,
  DateFrom int,
  DateTill int,
  Payload DECIMAL(18,2),
  xz int
)
--
select * from @tbl order by DateFrom
--
insert into @res

select IntervalPriority, DateFrom, DateTill, Payload , 0
from @tbl t1
where not exists 
	(select 1 from @tbl t2
	where t1.DateFrom between t2.DateFrom and t2.DateTill
	and t1.IntervalPriority<t2.IntervalPriority)
  and not exists 
	(select 1 from @tbl t2
	where t1.DateTill between t2.DateFrom and t2.DateTill
	and t1.IntervalPriority<t2.IntervalPriority)
  and not exists 
	(select 1 from @tbl t2
	where t1.DateFrom < t2.DateFrom 
	and t1.DateTill > t2.DateTill
	and t1.IntervalPriority<t2.IntervalPriority)

---

while @@rowcount>0
begin
	--select * from @res order by DateFrom
	--
	insert into @res

	select a.IntervalPriority, a.DateFrom, min(r.DateFrom)-1, a.Payload , 1
	from @res r
	cross apply (select top 1 IntervalPriority, DateFrom, Payload from @tbl t
				where t.DateFrom<r.DateFrom and t.DateTill>=r.DateFrom
				and IntervalPriority not in (select IntervalPriority from @res)
				order by IntervalPriority desc) a
	where r.xz<>2
	group by a.IntervalPriority, a.DateFrom, a.Payload

	union all

	select a.IntervalPriority, max(r.DateTill)+1, a.DateTill, a.Payload , 2
	from @res r
	cross apply (select top 1 IntervalPriority, DateTill, Payload from @tbl t
				where t.DateTill>r.DateTill and t.DateFrom<=r.DateTill
				and IntervalPriority not in (select IntervalPriority from @res)
				order by IntervalPriority desc) a
	where r.xz<>1
	group by a.IntervalPriority, a.DateTill, a.Payload

end

select * from @res order by DateFrom

(9 row(s) affected)
IntervalPriority DateFrom    DateTill    Payload
---------------- ----------- ----------- ---------------------------------------
2                4           8           45.60
1                5           7           12.30
3                10          20          12.30
4                15          25          45.60
5                110         120         12.30
6                113         117         45.60
7                210         230         12.30
8                220         240         45.60
9                225         250         78.90

(9 row(s) affected)

(4 row(s) affected)

(4 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

IntervalPriority DateFrom    DateTill    Payload                                 xz
---------------- ----------- ----------- --------------------------------------- -----------
2                4           8           45.60                                   0
3                10          14          12.30                                   1
4                15          25          45.60                                   0
5                110         112         12.30                                   1
6                113         117         45.60                                   0
5                118         120         12.30                                   2
7                210         219         12.30                                   1
8                220         224         45.60                                   1
9                225         250         78.90                                   0

(9 row(s) affected)
13 июн 12, 13:44    [12707466]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить