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

Откуда:
Сообщений: 7
Залип с задачей эффективного разделения интервалов на подинтервалы.

Есть таблица интервалов:

CREATE TABLE #Intervals
	(
	Name varchar(50) NOT NULL, --колонка не имеет ограничения на уникальность, т.к. интервал будем делить, сохраняя имя
	DateBeg datetime NOT NULL,
	DateEnd datetime NOT NULL
	)


Первая колонка - идентификатор некоего абстрактного интервала, вторая и третья - начало и конец интервала.

Есть таблица произвольного набора дат:

CREATE TABLE #Dates
	(
	Date datetime NOT NULL
	)


Нужно с помощью стандартных [select,update,insert,delete] разбить записи интервалов таблицы Intervals на более мелкие интервалы по датам из таблицы Dates.

Т.е. если у нас есть интервалы и даты:

insert into
	#Intervals
values
	('Интервал 1','20150101','20150131')

insert into
	#Dates
values
	'20150110'

insert into
	#Dates
values
	'20150120'


то вместо исходного интервала в таблице Intervals должно получиться три записи:

('Интервал 1','20150101','20150110')
('Интервал 1','20150110','20150120')
('Интервал 1','20150120','20150131')

Если дата попадает на границу интервала - деления не происходит.

Главное условие: не работать с интервалами по одному (с помощью курсоров или как-то еще), а применять операторы DML на таблицу в целом. Т.е. получить результирующий сет за всегда одно и то же количество запросов вне зависимости от исходных данных (т.к. интервалов в таблице - сотни тысяч).

Вторичное условие: использование функционала не выше MS SQL 2005.

В идеале, конечно, хотелось бы получить результат одним выражением.
14 май 15, 14:36    [17639840]     Ответить | Цитировать Сообщить модератору
 Re: Разделение интервалов на подинтервалы  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21253
Преврати таблицу дат в таблицу диапазонов дат, добавив даты 01-01-0001 и 31-12-9999. Умножь на таблицу интервалов. Сделай отбор по наличию пересечения. Выведи пересечение.
14 май 15, 14:41    [17639902]     Ответить | Цитировать Сообщить модератору
 Re: Разделение интервалов на подинтервалы  [new]
invm
Member

Откуда: Москва
Сообщений: 9844
Aclz
Главное условие: не работать с интервалами по одному (с помощью курсоров или как-то еще), а применять операторы DML на таблицу в целом. Т.е. получить результирующий сет за всегда одно и то же количество запросов вне зависимости от исходных данных (т.к. интервалов в таблице - сотни тысяч).

Вторичное условие: использование функционала не выше MS SQL 2005.

В идеале, конечно, хотелось бы получить результат одним выражением.
declare @Intervals table
(
    id int identity primary key,
	Name varchar(50) NOT NULL, --колонка не имеет ограничения на уникальность, т.к. интервал будем делить, сохраняя имя
	DateBeg datetime NOT NULL,
	DateEnd datetime NOT NULL
);

declare @Dates table
(
	d datetime NOT NULL
);

insert into	@Intervals
values
	('Интервал 1','20150101','20150131')

insert into	@Dates
values
	('20150110');

insert into	@Dates
values
	('20150120');

with s as
(
 select
  i.Name, i.id, a.d, row_number() over (partition by i.id order by a.d) as rn
 from
  @Intervals i cross apply
  (select i.DateBeg union all select d from @Dates where d > i.DateBeg and d < i.DateEnd union all select i.DateEnd) a(d)
)
select
 s1.Name, s2.d, s1.d
from
 s s1 join
 s s2 on s2.id = s1.id and s2.rn = s1.rn - 1;
14 май 15, 15:00    [17640045]     Ответить | Цитировать Сообщить модератору
 Re: Разделение интервалов на подинтервалы  [new]
Aclz
Member

Откуда:
Сообщений: 7
invm, это CTE, данный код с большой долей вероятности захлебнется на большом сете входных данных, нагенерив кучу селектов, т.к. не удовлетворяет условию:
автор
Главное условие: не работать с интервалами по одному (с помощью курсоров или как-то еще), а применять операторы DML на таблицу в целом. Т.е. получить результирующий сет за всегда одно и то же количество запросов вне зависимости от исходных данных (т.к. интервалов в таблице - сотни тысяч).


По варианту Akina (интересно, можно ли минимизировать):

---------------------------------ОБЪЯВЛЕНИЕ ИСХОДНЫХ ДАННЫХ--------------------------------------
CREATE TABLE #Intervals
    (
    Name varchar(50) NOT NULL,
    DateBeg datetime NOT NULL,
    DateEnd datetime NOT NULL
    )

CREATE TABLE #Dates
    (
    Date datetime NOT NULL
    )

insert into
    #Intervals
values
    ('Интервал 1','20150101','20150131')

truncate table #Dates

insert into
    #Dates
values
    ('20150110')

insert into
    #Dates
values
    ('20150120')

insert into
    #Dates
values
    ('20150131')

insert into
    #Dates
values
    ('20150201')

---------------------------------ЗАПРОС--------------------------------------

select
    intl.name,
    case
        when intl.datebeg > dates_intl.datebeg then
            intl.datebeg
        else
            dates_intl.datebeg
    end as intersect_beg,
    case
        when intl.dateend > dates_intl.dateend then
            dates_intl.dateend
        else
            intl.dateend
    end as intersect_end
from
    #Intervals intl
    join
    (
    select
        rn1.Date as DateBeg,
        rn2.Date as DateEnd
    from
        (
        select
            Date,
            row_number() over (order by Date) rn
        from
            (
            select
                Date
            from
                #Dates

            union

            select
                '19700101'

            union

            select
                '99991231'
            ) sub
        ) rn1
        join
        (
        select
            Date,
            row_number() over (order by Date) rn
        from
            (
            select
                Date
            from
                #Dates

            union

            select
                '19700101'

            union

            select
                '99991231'
            ) sub
        ) rn2
            on rn2.rn = rn1.rn + 1
    ) dates_intl
        on (dates_intl.DateEnd > intl.DateBeg and dates_intl.DateEnd < intl.DateEnd)
        or (dates_intl.DateBeg > intl.DateBeg and dates_intl.DateBeg < intl.DateEnd)
        or (dates_intl.DateBeg <= intl.DateBeg and dates_intl.DateEnd >= intl.DateBeg)
14 май 15, 15:43    [17640370]     Ответить | Цитировать Сообщить модератору
 Re: Разделение интервалов на подинтервалы  [new]
invm
Member

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

Вы не понимаете, что такое CTE.
14 май 15, 15:57    [17640479]     Ответить | Цитировать Сообщить модератору
 Re: Разделение интервалов на подинтервалы  [new]
o-o
Guest
вот это прикол, да.

Aclz, а как вам такой запрос?
никаких CTE,
еще и побыстрее вашего, вам нравится?
declare @Intervals table
(
    id int identity primary key,
	Name varchar(50) NOT NULL, --колонка не имеет ограничения на уникальность, т.к. интервал будем делить, сохраняя имя
	DateBeg datetime NOT NULL,
	DateEnd datetime NOT NULL
);

declare @Dates table
(
	d datetime NOT NULL
);

insert into	@Intervals
values
	('Интервал 1','20150101','20150131')

insert into	@Dates
values
	('20150110');

insert into	@Dates
values
	('20150120');

select
 s1.Name, s2.d, s1.d
from
 (
 select
  i.Name, i.id, a.d, row_number() over (partition by i.id order by a.d) as rn
 from
  @Intervals i cross apply
  (select i.DateBeg union all select d from @Dates where d > i.DateBeg and d < i.DateEnd union all select i.DateEnd) a(d)
)
 s1 join
(
 select
  i.Name, i.id, a.d, row_number() over (partition by i.id order by a.d) as rn
 from
  @Intervals i cross apply
  (select i.DateBeg union all select d from @Dates where d > i.DateBeg and d < i.DateEnd union all select i.DateEnd) a(d)
)
 s2 on s2.id = s1.id and s2.rn = s1.rn - 1;
14 май 15, 16:03    [17640533]     Ответить | Цитировать Сообщить модератору
 Re: Разделение интервалов на подинтервалы  [new]
Aclz
Member

Откуда:
Сообщений: 7
Всем спасибо за ответы, прошу пардона, если где-то соврал. Прогоню на большом объеме данных и отпишусь, если профайлер найдет меж всех вариантов разницу в затрачиваемых ресурсах.
14 май 15, 16:29    [17640731]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить