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

declare @t table(i int, d1 datetime, d2 datetime)

insert into @t
select 4, '20120101 00:00','20120101 14:00'
union
select 1, '20120101 08:00','20120101 10:00'
union
select 2, '20120101 09:00','20120101 11:00'
union
select 3, '20120101 15:00','20120101 16:00'
union
select 5, '20120101 17:00','20120101 17:30'
union
select 6, '20120101 16:00','20120101 18:30'


Необходимо объединить в группы пересекающиеся периоды времени и пронумеровать их.

В данном случае в результате должны получить следующее:

datestartdatefinishgroupnumber
20120101 00:0020120101 14:001
20120101 08:0020120101 10:001
20120101 09:0020120101 11:001
20120101 15:0020120101 16:002
20120101 17:0020120101 17:303
20120101 16:0020120101 18:303
17 янв 13, 17:38    [13786898]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Strangers
Member [заблокирован]

Откуда: Україна
Сообщений: 2613
если столбец [i] уникален, то можно номер группы оставить для нумерации

declare @t table(i int, d1 datetime, d2 datetime)

insert into @t
select 4, '20120101 00:00','20120101 14:00'
union
select 1, '20120101 08:00','20120101 10:00'
union
select 2, '20120101 09:00','20120101 11:00'
union
select 3, '20120101 15:00','20120101 16:00'
union
select 5, '20120101 17:00','20120101 17:30'
union
select 6, '20120101 16:00','20120101 18:30'

;with asd as
	(SELECT 
			MIN(t1.i) as [i],
			t2.d1,
			t2.d2
	FROM @t t1
		JOIN @t t2 ON t1.d1 <t2.d2 and t1.d2>t2.d1
	GROUP BY t2.d1,
			t2.d2)
SELECT	i,
		d1,
		d2
FROM asd	
ORDER BY i,	d1,	d2	
17 янв 13, 17:56    [13786981]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Гость333
Member

Откуда:
Сообщений: 3683
периодпересекаются,

Для таких данных:
00:00 — 10:00
01:00 — 03:00
06:00 — 08:00
какой должен быть результат? Промежуток 00:00 — 10:00 будет входить в две группы?
17 янв 13, 17:56    [13786983]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Гость333
Member

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

Ваш скрипт не работает для данных, по которым я задал вопрос:
declare @t table(i int, d1 datetime, d2 datetime)

insert into @t
select 1, '20120101 00:00','20120101 10:00'
union
select 2, '20120101 01:00','20120101 03:00'
union
select 3, '20120101 06:00','20120101 08:00'

;with asd as
	(SELECT 
			MIN(t1.i) as [i],
			t2.d1,
			t2.d2
	FROM @t t1
		JOIN @t t2 ON t1.d1 <t2.d2 and t1.d2>t2.d1
	GROUP BY t2.d1,
			t2.d2)
SELECT	i,
		d1,
		d2
FROM asd	
ORDER BY i,	d1,	d2

— всё попадает в одну группу, хотя периоды 2 и 3 друг с другом не пересекаются.
17 янв 13, 18:04    [13787031]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Strangers
Member [заблокирован]

Откуда: Україна
Сообщений: 2613
Гость333
Strangers,
— всё попадает в одну группу, хотя периоды 2 и 3 друг с другом не пересекаются.

Согласно техзадания ТС период 0-14 тоже не пересекается, но входит в 1-ю группу
17 янв 13, 18:08    [13787044]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Strangers
Согласно техзадания ТС период 0-14 тоже не пересекается, но входит в 1-ю группу

Не понял, что вы имеете в виду. Периоды 0-14, 8-10 и 9-11 пересекаются между собой, интервал пересечения — 9-10.
17 янв 13, 18:14    [13787064]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
aleks2
Guest
Мне лень искать ответ на этот часто задаваемый вопрос.
Но я намекну: начало периода НЕПРЕРЫВНОСТИ = начальная точка периода, которая не попадает ни в один другой интервал;
конец периода НЕПРЕРЫВНОСТИ = конечная точка периода, которая не попадает ни в один другой интервал;

Таких начал и концов всегда ОДИНАКОВОЕ количество.
17 янв 13, 18:21    [13787084]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Strangers
Member [заблокирован]

Откуда: Україна
Сообщений: 2613
Гость333
Strangers
Согласно техзадания ТС период 0-14 тоже не пересекается, но входит в 1-ю группу

Не понял, что вы имеете в виду. Периоды 0-14, 8-10 и 9-11 пересекаются между собой, интервал пересечения — 9-10.

Увы, признаю, неправ
17 янв 13, 18:35    [13787129]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
qwerty112
Guest
aleks2
Мне лень искать ответ на этот часто задаваемый вопрос.
Но я намекну: начало периода НЕПРЕРЫВНОСТИ = начальная точка периода, которая не попадает ни в один другой интервал;
конец периода НЕПРЕРЫВНОСТИ = конечная точка периода, которая не попадает ни в один другой интервал;

Таких начал и концов всегда ОДИНАКОВОЕ количество.

12567851
17 янв 13, 20:01    [13787468]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Гость333
Member

Откуда:
Сообщений: 3683
qwerty112
aleks2
Мне лень искать ответ на этот часто задаваемый вопрос.
Но я намекну: начало периода НЕПРЕРЫВНОСТИ = начальная точка периода, которая не попадает ни в один другой интервал;
конец периода НЕПРЕРЫВНОСТИ = конечная точка периода, которая не попадает ни в один другой интервал;

Таких начал и концов всегда ОДИНАКОВОЕ количество.

12567851

ТСу нужны не интервалы непрерывности, а пересечения интервалов. Запрос по ссылке выдаёт два интервала: 00:00 — 14:00 и 15:00 — 18:30, что, конечно, прекрасно, но не соответствует условию задачи.
+ Вот запрос, вытащенный по предложенной ссылке
declare @t table(par1 int, b_num datetime, e_num datetime)

insert into @t
select 4, '20120101 00:00','20120101 14:00'
union
select 1, '20120101 08:00','20120101 10:00'
union
select 2, '20120101 09:00','20120101 11:00'
union
select 3, '20120101 15:00','20120101 16:00'
union
select 5, '20120101 17:00','20120101 17:30'
union
select 6, '20120101 16:00','20120101 18:30'

-- Непосредственно сам запрос:
select v_begin.b_num, min(v_end.e_num) as e_num
  from 
       ( -- Находим все начала диапазонов:
          select b_num, par1
            from @t s1
           where not exists (
                               select null
                                 from @t s2
                                where s2.b_num < s1.b_num
                                  and s2.e_num >= s1.b_num
                            )
       ) v_begin
  join 
       ( -- Находим все кончала диапазонов:
          select e_num, par1
            from @t s1
           where not exists (
                               select null
                                 from @t s2
                                where s2.e_num > s1.e_num
                                  and s2.b_num <= s1.e_num
                            )
       ) v_end
--
-- Сливаем начала с кончалами:
    on v_begin.b_num <= v_end.e_num
 group by v_begin.b_num
 order by v_begin.b_num, min(v_end.e_num)
18 янв 13, 09:46    [13789123]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Описанные в стартовом посте пересекающиеся периоды - это одно из возможных представлений древовидной структуры.
Так что, мне кажется, стоит поискать на форуме по слову "дерево".
Попробывать рекурсивное CTE и т.д. и т.п.
18 янв 13, 10:38    [13789470]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
qwerty112
Guest
Гость333
qwerty112
пропущено...

12567851

ТСу нужны не интервалы непрерывности, а пересечения интервалов. Запрос по ссылке выдаёт два интервала: 00:00 — 14:00 и 15:00 — 18:30, что, конечно, прекрасно, но не соответствует условию задачи.

нуу, начнём с того, что топикстартёр - "наивный парень",
и почему-то считает, что интервалы, имеющие одну общую точку - не пересекаются ...

вообщем, - его право,
и, даже, если "стать" на его эту "математику", то "идея" этого запроса - вполне подходит
+
declare @t table(par1 int, b_num datetime, e_num datetime)

insert into @t
select 4, '20120101 00:00','20120101 14:00'
union
select 1, '20120101 08:00','20120101 10:00'
union
select 2, '20120101 09:00','20120101 11:00'
union
select 3, '20120101 15:00','20120101 16:00'
union
select 5, '20120101 17:00','20120101 17:30'
union
select 6, '20120101 16:00','20120101 18:30'

select a.groupnumber, t.*
from

-- Непосредственно сам запрос:
(select row_number() over(order by v_begin.b_num) as groupnumber, v_begin.b_num, min(v_end.e_num) as e_num
  from 
       ( -- Находим все начала диапазонов:
          select b_num, par1
            from @t s1
           where not exists (
                               select null
                                 from @t s2
                                where s2.b_num < s1.b_num
                                  and s2.e_num > s1.b_num
                            )
       ) v_begin
  join 
       ( -- Находим все кончала диапазонов:
          select e_num, par1
            from @t s1
           where not exists (
                               select null
                                 from @t s2
                                where s2.e_num > s1.e_num
                                  and s2.b_num < s1.e_num
                            )
       ) v_end
--
-- Сливаем начала с кончалами:
    on v_begin.b_num < v_end.e_num
 group by v_begin.b_num
 /*order by v_begin.b_num, min(v_end.e_num)*/) a

inner join @t t
	on t.b_num>=a.b_num and t.e_num<=a.e_num

order by 1

(6 row(s) affected)
groupnumber          par1        b_num                   e_num
-------------------- ----------- ----------------------- -----------------------
1                    1           2012-01-01 08:00:00.000 2012-01-01 10:00:00.000
1                    2           2012-01-01 09:00:00.000 2012-01-01 11:00:00.000
1                    4           2012-01-01 00:00:00.000 2012-01-01 14:00:00.000
2                    3           2012-01-01 15:00:00.000 2012-01-01 16:00:00.000
3                    5           2012-01-01 17:00:00.000 2012-01-01 17:30:00.000
3                    6           2012-01-01 16:00:00.000 2012-01-01 18:30:00.000

(6 row(s) affected)
18 янв 13, 10:46    [13789525]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Гость333
Member

Откуда:
Сообщений: 3683
qwerty112
и почему-то считает, что интервалы, имеющие одну общую точку - не пересекаются ...

Почему бы и нет, это называется "полуоткрытые интервалы" :-)

qwerty112
"идея" этого запроса - вполне подходит

Ну опять же, подставляем предложенные мною данные:
00:00 — 10:00
01:00 — 03:00
06:00 — 08:00
И ваш запрос определяет их в одну группу. Непорядок однако.
18 янв 13, 11:06    [13789682]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Гость333
qwerty112
и почему-то считает, что интервалы, имеющие одну общую точку - не пересекаются ...

Почему бы и нет, это называется "полуоткрытые интервалы" :-)

qwerty112
"идея" этого запроса - вполне подходит

Ну опять же, подставляем предложенные мною данные:
00:00 — 10:00
01:00 — 03:00
06:00 — 08:00
И ваш запрос определяет их в одну группу. Непорядок однако.
Почему это непорядок?
С первым пересекаются и второй, и третий.
Первый - это как бы корневой узел, второй и третий - узлы первого уровня.
Типичное дерево. Хотя нет - возможно, это граф, если второй и третий периоды тоже пересекались бы.
18 янв 13, 11:15    [13789747]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
qwerty112
Guest
Гость333
Ну опять же, подставляем предложенные мною данные:
00:00 — 10:00
01:00 — 03:00
06:00 — 08:00
И ваш запрос определяет их в одну группу. Непорядок однако.

хорошо :)
какой результат должен быть на этих данных ? сколько тут ТС-вских groupnumber, и какие интервалы - в какие groupnumber входят ?
18 янв 13, 11:22    [13789802]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Гость333
Member

Откуда:
Сообщений: 3683
qwerty112
какой результат должен быть на этих данных ?

Вот мне тоже любопытно, как только ТС выйдет из анабиоза — узнаем :-)
18 янв 13, 11:38    [13789951]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Добрый Э - Эх
Guest
qwerty112
12567851

Да, много воды утекло с тех пор.... ;) :)
Сегодня, в версии MS SQL Server 2012, я, для слияния пересекающихся интервалов(имеющих одну и более общих точек), написал бы такой запрос :) ) :
+ < Слияние пересекающихся интервалов в один интервал...
--  Дано: 
--|///////|-------------------------------------------------
--------|/////////////|-------------------------------------
--------------|///////|-------------------------------------
--------------|/////////////|-------------------------------
-------------------------------|\\\\|-----------------------
-------------------------------------------|\\\\|-----------
----------------------------------|\\\\\\\\\\\\\\\\|--------
----------------------------------------------|\\\\\\\\\\|--
--  Требуется получить: 
--|/////////////////////////|-------------------------------
-------------------------------|\\\\\\\\\\\\\\\\\\\\\\\\\|--
--
-- Тестовый набор данных:
with 
  intervals (b, e) as 
    (
      select *
        from (
               values ( 1,  4), --\
                      ( 3,  8), -- \ Первый сплошной диапазон
                      ( 5,  8), -- /
                      ( 5, 10), --/
                      (11, 13), --\
                      (15, 17), -- \ Второй сплошной диапазон
                      (12, 18), -- /
                      (16, 20)  --/
             ) i(b,e)
    )
--
-- Основной запрос (наличие MS SQL Server 2012 - обязательно!!!):
select min(b) as x_b, max(e) as x_e
  from (
         select b, e, sum(sog) over(order by b,e) as grp_id
           from (
                  select b, e, 
                         case 
                           when b <= max(e) over(order by b, e 
                                                  rows between unbounded preceding
                                                           and 1 preceding) 
                             then 0 
                           else 1 
                         end as sog -- sog = Start Of Group :)
                    from intervals as i
                ) as v0
       ) as v1
 group by grp_id
 order by 1
on-line проверка на sqlfiddle.com
18 янв 13, 13:23    [13790857]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Добрый Э - Эх
Guest
Ну или на тестовых данных автора с учетом его же пожеланий относительно результата выборки:
+ < Фигня запрос, главное - чтобы сервер был 2012 :)
--
-- Тестовый данные автора:
declare @t table(i int, d1 datetime, d2 datetime)
insert into @t
select 4, '20120101 00:00','20120101 14:00'
union
select 1, '20120101 08:00','20120101 10:00'
union
select 2, '20120101 09:00','20120101 11:00'
union
select 3, '20120101 15:00','20120101 16:00'
union
select 5, '20120101 17:00','20120101 17:30'
union
select 6, '20120101 16:00','20120101 18:30'
--
-- Основонй запрос (MS SQL Server 2012 обязателен):
select d1, d2, sum(sog) over(order by d1,d2) as grp_id
  from (
         select d1, d2, 
                case 
                  when d1 < max(d2) over(order by d1, d2 
                                                  rows between unbounded preceding
                                                           and 1 preceding) 
                    then 0 
                  else 1 
                end as sog -- sog = Start Of Group :)
           from @t
       ) as v0
on-line проверка на sqlfiddle.com
18 янв 13, 15:46    [13792228]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Добрый Э - Эх
Guest
qwerty112
... "идея" этого запроса - вполне подходит
+
+ < Тут исправленный мною запрос, написанный тобою на основе моего запроса по слиянию интервалов :)
declare @t table(par1 int, b_num datetime, e_num datetime)

insert into @t
select 4, '20120101 00:00','20120101 14:00'
union
select 1, '20120101 08:00','20120101 10:00'
union
select 2, '20120101 09:00','20120101 11:00'
union
select 3, '20120101 15:00','20120101 16:00'
union
select 5, '20120101 17:00','20120101 17:30'
union
select 6, '20120101 16:00','20120101 18:30'

-- Непосредственно сам запрос:
select a.b_rn, t.*
from (
select v_begin.b_rn, v_begin.b_num, v_end.e_num
  from 
       ( -- Находим все начала диапазонов:
          select b_num, par1, row_number() over(order by b_num) as b_rn
            from @t s1
           where not exists (
                               select null
                                 from @t s2
                                where s2.b_num < s1.b_num
                                  and s2.e_num > s1.b_num
                            )
       ) v_begin
  join 
       ( -- Находим все кончала диапазонов:
          select e_num, par1, row_number() over(order by e_num) as e_rn
            from @t s1
           where not exists (
                               select null
                                 from @t s2
                                where s2.e_num > s1.e_num
                                  and s2.b_num < s1.e_num
                            )
       ) v_end
--
-- Сливаем начала с кончалами:
    on v_begin.b_rn = v_end.e_rn
) a
inner join @t t
	on t.b_num>=a.b_num and t.e_num<=a.e_num
order by 1

как всегда on-line проверка на sqlfiddle.com
раз уж речь зашла за использование row_number-а, то тогда можно им же перенумеровать "начала" и "кончала" и соединять наборы начал / кончал по этому полю-нумератору, используя этьот же нумератор для итоговой перенумерации групп;)
18 янв 13, 18:15    [13793342]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
qwerty112
Guest
Добрый Э - Эх
раз уж речь зашла за использование row_number-а, то тогда можно им же перенумеровать "начала" и "кончала" и соединять наборы начал / кончал по этому полю-нумератору, используя этьот же нумератор для итоговой перенумерации групп;)

нуу, даа - так конечно и проще, и наглядней, и "всё такое" :)
но, пропадает один из основных "цымусов" Вашего исходного запроса - то, что он "единый" во всех СУБД ! :)

... а так - даа, он, наверняка, будет и по-быстрее того что с группировкой/агрегированием ...
18 янв 13, 20:09    [13793835]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Добрый Э - Эх
Guest
qwerty112
... пропадает один из основных "цымусов" ...
Если бороться за сохранение "цымуса", то следовало бы написать так:
+ < Сохранён весь "цымус", запрос практически кроссСУБДшный :)
declare @t table(par1 int, b_num datetime, e_num datetime)

insert into @t
select 4, '20120101 00:00','20120101 14:00'
union
select 1, '20120101 08:00','20120101 10:00'
union
select 2, '20120101 09:00','20120101 11:00'
union
select 3, '20120101 15:00','20120101 16:00'
union
select 5, '20120101 17:00','20120101 17:30'
union
select 6, '20120101 16:00','20120101 18:30'

select a.groupnumber, t.*
from

-- Непосредственно сам запрос:
(select count(distinct v_begin.b_num) as groupnumber, max(v_begin.b_num) as b_num, v_end.e_num
  from 
       ( -- Находим все начала диапазонов:
          select b_num, par1
            from @t s1
           where not exists (
                               select null
                                 from @t s2
                                where s2.b_num < s1.b_num
                                  and s2.e_num > s1.b_num
                            )
       ) v_begin
  join 
       ( -- Находим все кончала диапазонов:
          select e_num, par1
            from @t s1
           where not exists (
                               select null
                                 from @t s2
                                where s2.e_num > s1.e_num
                                  and s2.b_num < s1.e_num
                            )
       ) v_end
--
-- Сливаем начала с кончалами:
    on v_begin.b_num < v_end.e_num
 group by v_end.e_num
) a

inner join @t t
	on t.b_num>=a.b_num and t.e_num<=a.e_num

order by 1
on-line проверка на sqlfiddle.com
19 янв 13, 08:29    [13795557]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
bdv007
Member

Откуда:
Сообщений: 17
Добрый Э - Эх,

Добрый Э - Эх,

Что-то не работает последний запрос с такими данными.
Не понятно, в чем дело. По идее должна быть одна группа

+

insert into @t
select 1, '20120203','20120229'
union
select 2, '20120301','20120331'
union
select 3, '20120401','20120430'
union
select 4, '20120501','20120531'
union
select 5, '20120601','20120621'
union
select 6, '20120203','20120621'
12 сен 13, 18:08    [14832945]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Гость333
Member

Откуда:
Сообщений: 3683
bdv007
Что-то не работает последний запрос с такими данными.
Не понятно, в чем дело. По идее должна быть одна группа

Что значит "не работает"? Сколько групп у вас получилось? Можете показать результат выполнения?
12 сен 13, 18:20    [14832981]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Добрый Э - Эх
Guest
bdv007,

у тебя в тестовых данных периоды не пересекаются (в строгом смысле этого слова), а "стыкуются" - общие точки у дат отсутствуют.
+1 день делать нужно к конечной дате периода в условиях сравнения ...
13 сен 13, 07:29    [14834332]     Ответить | Цитировать Сообщить модератору
 Re: Пересекающиеся периоды  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Добрый Э - Эх,

У него же есть период "select 6, '20120203','20120621'", который покрывает собой все остальные периоды. Скорее всего, bdv007 просто не догадался достать из скобок "Непосредственно сам запрос" и не обратил внимания, что у всех строк GROUPNUMBER = 1.
13 сен 13, 08:44    [14834437]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить