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

Откуда:
Сообщений: 54
Доброго времени!
Подскажите как вывести общие периоды, где частные события были активные.

Пример
DECLARE @TAB TABLE (
[STime] [datetime],
[ETime] [datetime])
insert into @TAB values
('2013-12-01 06:00', '2013-12-01 10:00'),
('2013-12-01 08:00', '2013-12-01 12:00'),
('2013-12-01 15:00', '2013-12-01 17:00'),
('2013-12-01 16:00', '2013-12-01 20:00')

Нужно получить два периода
1) с 06:00 до 12:00 (первые два события)
2) с 15:00 до 20:00 (вторые два события).

Событий в сутках будет немеряно, просто упростил в примере.
Подскажите как решить задачку.
Спасибо.
1 мар 14, 09:17    [15651264]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Gizmor
Подскажите как вывести общие периоды, где частные события были активные.
Вот решают похожую задачу, только условие поменять с "максимальное" на "больше 0"
https://www.sql.ru/forum/1079316/maksimalnoe-kolichestvo-odnovremennyh-sessiy
1 мар 14, 09:36    [15651279]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
aleks2
Guest
DECLARE @TAB TABLE (
[STime] [datetime],
[ETime] [datetime],
id int identity primary key clustered)
insert into @TAB([STime], [ETime]) values
('2013-12-01 06:00', '2013-12-01 10:00'),
('2013-12-01 08:00', '2013-12-01 12:00'),
('2013-12-01 15:00', '2013-12-01 17:00'),
('2013-12-01 16:00', '2013-12-01 20:00')

declare @b table (t [datetime], n int identity primary key clustered);
declare @e table (t [datetime], n int identity primary key clustered);

insert @b(t)
  select t.[STime] from @TAB t left outer join @TAB tt on t.id > tt.id and t.[STime] between tt.STime and tt.ETime
    where tt.id is null
    
select * from @b

insert @e(t)
  select t.[ETime] from @TAB t left outer join @TAB tt on t.id < tt.id and t.[ETime] between tt.STime and tt.ETime
    where tt.id is null
    
select * from @e

select b.t as t1, e.t as t2 from @b b inner join @e e on b.n=e.n
1 мар 14, 10:08    [15651310]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Gizmor
Member

Откуда:
Сообщений: 54
Идея хорошая, запрос понятный, но меняешь последовательность событий (например первым более позднее событие) и работает некорректно.
3 мар 14, 11:31    [15661216]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
aleks2
Guest
Gizmor
Идея хорошая, запрос понятный, но меняешь последовательность событий (например первым более позднее событие) и работает некорректно.

order by t.[STime] и order by t.[ETime] то припиши, страдалец.
3 мар 14, 11:46    [15661340]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Gizmor
Member

Откуда:
Сообщений: 54
DECLARE @TAB TABLE (
[STime] [datetime],
[ETime] [datetime],
id int identity primary key clustered)
insert into @TAB([STime], [ETime]) values
('2013-12-01 08:00', '2013-12-01 12:00'),
('2013-12-01 06:00', '2013-12-01 10:00'),
('2013-12-01 15:00', '2013-12-01 17:00'),
('2013-12-01 16:00', '2013-12-01 20:00')

declare @b table (t [datetime], n int identity primary key clustered);
declare @e table (t [datetime], n int identity primary key clustered);

insert @b(t)
select t.[STime] from @TAB t left outer join @TAB tt on t.id > tt.id and t.[STime] between tt.STime and tt.ETime
where tt.id is null
order by t.[STime]

select * from @b

insert @e(t)
select t.[ETime] from @TAB t left outer join @TAB tt on t.id < tt.id and t.[ETime] between tt.STime and tt.ETime
where tt.id is null
order by t.[ETime]

select * from @e

select b.t as t1, e.t as t2 from @b b inner join @e e on b.n=e.n

Приписал. Не получается.
3 мар 14, 13:23    [15662373]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
DECLARE @TAB TABLE (
[STime] [datetime],
[ETime] [datetime],
id int identity primary key clustered)
insert into @TAB([STime], [ETime]) values
('2013-12-01 08:00', '2013-12-01 12:00'),
('2013-12-01 06:00', '2013-12-01 10:00'),
('2013-12-01 15:00', '2013-12-01 17:00'),
('2013-12-01 16:00', '2013-12-01 20:00')

declare @b table (t [datetime], n int identity primary key clustered);
declare @e table (t [datetime], n int identity primary key clustered);

insert @b(t)
select t.[STime] from @TAB t
where not exists(select 1 from @TAB where [STime] < t.[STime] and [ETime] between t.[STime] and t.[ETime])
order by t.[STime]

select * from @b

insert @e(t)
select t.[ETime] from @TAB t
where not exists(select 1 from @TAB where [ETime] > t.[ETime] and [STime] between t.[STime] and t.[ETime])
order by t.[ETime]

select * from @e

select b.t as t1, e.t as t2 from @b b inner join @e e on b.n=e.n
3 мар 14, 13:45    [15662600]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Сортировать надо до того, а не после, как я понимаю.
3 мар 14, 13:49    [15662639]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Gizmor
Member

Откуда:
Сообщений: 54
Подставьте следующие исходные данные
DECLARE @TAB TABLE (
[STime] [datetime],
[ETime] [datetime],
id int identity primary key clustered)
insert into @TAB([STime], [ETime]) values
('2013-12-01 08:00', '2013-12-01 14:00'),
('2013-12-01 06:00', '2013-12-01 10:00'),
('2013-12-01 09:00', '2013-12-01 10:00'),
('2013-12-01 15:00', '2013-12-01 17:00'),
('2013-12-01 16:00', '2013-12-01 20:00')

И получите неадекватный результат, хотя события все без ошибок...
3 мар 14, 14:25    [15662925]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
invm
Member

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

Уже давно могли бы изучить принцип построения такого запроса и исправить ошибку самостоятельно, ибо она тривиальна. А не ждать пока вам готовенькое напишут.
3 мар 14, 14:43    [15663095]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Gizmor
Member

Откуда:
Сообщений: 54
Да, изучаю, принцип вообще другой.
Сначала все интервалы переворачивают как проекцию на временную ось. Потом надо их как то обработать и удалить промежуточные точки.
Если получиться - обязательно напишу.
Тот запрос, который предложен - не работает в некоторых ситуациях.
Соответственно то, что я нахожу в нем ошибки - это естественно. Прошу меня не обвинять, что я лентяй :)
3 мар 14, 14:57    [15663200]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Gizmor
Member

Откуда:
Сообщений: 54
Лучше помогите дальше...
DECLARE @TAB TABLE (
[EventID] [int],
[Equipment] [int],
[STime] [datetime],
[ETime] [datetime])
insert into @TAB([EventID], [Equipment], [STime], [ETime]) values
(1, 1, '2013-12-01 06:00', '2013-12-01 10:00'),
(2, 1, '2013-12-01 06:00', '2013-12-01 10:00'),
(3, 1, '2013-12-01 15:00', '2013-12-01 17:00'),
(4, 1, '2013-12-01 11:00', '2013-12-01 12:00')

DECLARE @TEMP_TABLE TABLE
(
Flag SMALLINT,
Equipment BIGINT,
STime DATETIME,
PRIMARY KEY (Equipment, STime)
)

INSERT INTO @TEMP_TABLE (Equipment, STime, Flag)
SELECT Equipment, STime, MAX(Flag) as Flag
FROM (
SELECT 1 AS Flag, Equipment, STime
FROM @TAB
UNION ALL
SELECT -1 AS Flag, Equipment, ETime
FROM @TAB
UNION ALL
SELECT 2 AS Flag, a.Equipment, a.STime
FROM @TAB a
INNER JOIN @TAB b on a.Equipment = b.Equipment
AND a.STime < b.ETime AND a.ETime > b.STime
AND a.EventID <> b.EventID
AND a.STime > b.STime
UNION ALL
SELECT 2 AS Flag, a.Equipment, a.ETime
FROM @TAB a
INNER JOIN @TAB b on a.Equipment = b.Equipment
AND a.STime < b.ETime AND a.ETime > b.STime
AND a.EventID <> b.EventID
AND a.ETime < b.ETime
) a
GROUP BY Equipment, STime
HAVING MAX(Flag) <> 2

SELECT * FROM @TEMP_TABLE

Теперь надо как-то полученные диапазоны вернуть на место :)
3 мар 14, 15:02    [15663247]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Gizmor
Member

Откуда:
Сообщений: 54
Ура!!! Перевернул...

Вот запрос:
DECLARE @TAB TABLE (
[EventID] [int],
[Equipment] [int],
[STime] [datetime],
[ETime] [datetime])
insert into @TAB([EventID], [Equipment], [STime], [ETime]) values
(1, 1, '2013-12-01 05:00', '2013-12-01 10:00'),
(2, 2, '2013-12-01 06:00', '2013-12-01 11:00'),
(3, 1, '2013-12-01 15:00', '2013-12-01 17:00'),
(4, 1, '2013-12-01 11:00', '2013-12-01 12:00')

DECLARE @TEMP_TABLE TABLE
(
RowNumber BIGINT,
Flag SMALLINT,
Equipment BIGINT,
STime DATETIME,
PRIMARY KEY (Equipment, STime)
)

INSERT INTO @TEMP_TABLE (RowNumber, Equipment, STime, Flag)
SELECT
ROW_NUMBER() OVER(ORDER BY Equipment, STime) AS [RowNumber],
Equipment,
STime,
MAX(Flag) as Flag
FROM (
SELECT 1 AS Flag, Equipment, STime
FROM @TAB
UNION ALL
SELECT -1 AS Flag, Equipment, ETime
FROM @TAB
UNION ALL
SELECT 2 AS Flag, a.Equipment, a.STime
FROM @TAB a
INNER JOIN @TAB b on a.Equipment = b.Equipment
AND a.STime < b.ETime AND a.ETime > b.STime
AND a.EventID <> b.EventID
AND a.STime > b.STime
UNION ALL
SELECT 2 AS Flag, a.Equipment, a.ETime
FROM @TAB a
INNER JOIN @TAB b on a.Equipment = b.Equipment
AND a.STime < b.ETime AND a.ETime > b.STime
AND a.EventID <> b.EventID
AND a.ETime < b.ETime
) a
GROUP BY Equipment, STime
HAVING MAX(Flag) <> 2

SELECT a.Equipment, a.STime, b.STime AS ETime
FROM
@TEMP_TABLE a
CROSS APPLY (
SELECT b.Equipment, b.STime AS STime
FROM @TEMP_TABLE b
WHERE b.Equipment = a.Equipment
AND b.RowNumber = a.RowNumber + 1
) b
WHERE (a.RowNumber % 2) <> 0

Если кто-то напишет проще буду благодарен, так как сомневаюсь в быстродействии всей этой конструкции :)
Еще ... мне бы надо это во вьюху, а при создании вьюхи ругается на всякие объявления таблиц и переменных... подскажите как можно обойти эти ограничения.
Заранее спасибо!
3 мар 14, 15:57    [15663825]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Добрый Э - Эх
Guest
Gizmor
Если кто-то напишет проще буду благодарен, так как сомневаюсь в быстродействии всей этой конструкции :)
Еще ... мне бы надо это во вьюху, а при создании вьюхи ругается на всякие объявления таблиц и переменных... подскажите как можно обойти эти ограничения.
Заранее спасибо!


Проще-непроще - понятия относительные. Но как вариант, на версии сервера 2012 запрос мог бы иметь такой вид:
select min([STime]) as [x_STime], max([ETime]) as [x_ETime]
  from (
         select [STime], [ETime], sum(sog) over(order by [STime],[ETime]) as grp_id
           from (
                  select [STime],[ETime], 
                         case 
                           when [STime] <= max([ETime]) over(order by [STime], [ETime] 
                                                            rows between unbounded preceding
                                                             and 1 preceding) 
                             then 0 
                           else 1 
                         end as sog -- sog = start_of_group :)
                    from tab
                )v0
       )v1
 group by grp_id
 order by 1

on-line проверка на sqlfiddle.com

За производительность ничего не скажу, нет возможности тестировать на солидных объемах данных, но во вью должно легко запихнуться...
4 мар 14, 06:09    [15667040]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Gizmor
Member

Откуда:
Сообщений: 54
К сожалению версия сервера 2008 R2, но очень профессиональный запрос, буду иметь ввиду. Спасибо большое.
4 мар 14, 17:14    [15671379]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Добрый Э - Эх
Guest
Gizmor
К сожалению версия сервера 2008 R2

ну тогда берем за основу старый добрый запрос Ицека Бен Гана и делаем его вьювизацию... По идеи, всё должно получиться... :)
5 мар 14, 05:19    [15673966]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Gizmor
Member

Откуда:
Сообщений: 54
Добрый Э - Эх
Gizmor
К сожалению версия сервера 2008 R2

ну тогда берем за основу старый добрый запрос Ицека Бен Гана и делаем его вьювизацию... По идеи, всё должно получиться... :)


Гениально!!!
Насколько проще и понятнее. Спасибо, низкий поклон!
5 мар 14, 09:35    [15674256]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Gizmor
Member

Откуда:
Сообщений: 54
Ребята,
вот по результатам такой запрос. Я его еще чуть доработал идентификатором оборудования, так как эти события надо делить.

DECLARE @TAB TABLE (
[ID] [int],
[Equip] [int],
[STime] [int],
[ETime] [int])
insert into @TAB([ID], [Equip], [STime], [ETime]) values
(1, 1, 5, 7),
(2, 2, 6, 11),
(3, 1, 15, 17),
(4, 1, 6, 10)

SELECT
[v_begin].[Equip],
--[v_begin].[rn],
[v_begin].[STime] AS [Начало],
--[v_begin].[ETime],
--[v_end].[Equip],
--[v_end].[rn],
--[v_end].[STime],
[v_end].[ETime] AS [Окончание]
FROM
(SELECT -- Находим все начала диапазонов:
[Equip],
[STime],
[ETime],
ROW_NUMBER() OVER(ORDER BY [Equip], [STime]) AS rn
FROM
@TAB s1
WHERE
NOT EXISTS
(SELECT
NULL
FROM
@TAB s2
WHERE
s1.[Equip] = s2.[Equip]
AND s1.[ID] <> s2.[ID]
AND s1.[STime] > s2.[STime]
AND s1.[STime] <= s2.[ETime]
)
) v_begin
INNER JOIN
(SELECT -- Находим все окончания диапазонов:
[Equip],
[STime],
[ETime],
ROW_NUMBER() OVER(ORDER BY [Equip], [ETime]) AS rn
FROM
@TAB s1
WHERE
NOT EXISTS
(SELECT
NULL
FROM
@TAB s2
WHERE
s1.[Equip] = s2.[Equip]
AND s1.[ID] <> s2.[ID]
AND s1.[ETime] < s2.[ETime]
AND s1.[ETime] >= s2.[STime])
) v_end
ON v_begin.rn = v_end.rn AND v_begin.Equip = v_end.Equip

Все работает, но если 4 событию ставим время начала 5 - все слетает.
Не могу придумать как доработать условия. Также глюк если есть два события с одинаковым временем начала и окончания. Помогите пожалуйста... Заранее спасибо.
5 мар 14, 17:05    [15678402]     Ответить | Цитировать Сообщить модератору
 Re: Снова про пересечение периодов  [new]
Gizmor
Member

Откуда:
Сообщений: 54
DECLARE @TAB TABLE (
[ID] [int],
[Equip] [int],
[STime] [int],
[ETime] [int])
insert into @TAB([ID], [Equip], [STime], [ETime]) values
(1, 1, 5, 7),
(2, 2, 6, 11),
(3, 1, 15, 17),
(4, 1, 5, 8)

SELECT
[v_begin].[Equip],
--[v_begin].[rn],
[v_begin].[STime] AS [Начало],
--[v_begin].[ETime],
--[v_end].[Equip],
--[v_end].[rn],
--[v_end].[STime],
[v_end].[ETime] AS [Окончание]
FROM
(SELECT -- Находим все начала диапазонов:
[Equip],
[STime],
[ETime],
ROW_NUMBER() OVER(ORDER BY [Equip], [STime]) AS rn
FROM
@TAB s1
WHERE
NOT EXISTS
(SELECT
NULL
FROM
@TAB s2
WHERE
s1.[Equip] = s2.[Equip]
AND s1.[ID] <> s2.[ID]
AND s1.[STime] > s2.[STime]
AND s1.[STime] <= s2.[ETime]
)
) v_begin
INNER JOIN
(SELECT -- Находим все окончания диапазонов:
[Equip],
[STime],
[ETime],
ROW_NUMBER() OVER(ORDER BY [Equip], [ETime]) AS rn
FROM
@TAB s1
WHERE
NOT EXISTS
(SELECT
NULL
FROM
@TAB s2
WHERE
s1.[Equip] = s2.[Equip]
AND s1.[ID] <> s2.[ID]
AND s1.[ETime] < s2.[ETime]
AND s1.[ETime] >= s2.[STime])
) v_end
ON v_begin.rn = v_end.rn AND v_begin.Equip = v_end.Equip

Вот пример запроса с глюком
5 мар 14, 17:08    [15678426]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить