Нахождение мин. и макс. дат вхождения в диапазон / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нахождение мин. и макс. дат вхождения в диапазон

24 сообщений из 24, страница 1 из 1

Нахождение мин. и макс. дат вхождения в диапазон

#39905268

Kast2K

Участник

Откуда: Санкт-Петербург

Сообщения: 522

Рейтинг: 0 / 0

Добрый день!

В таблице есть вхождения дат и необходимо найти эти диапазоны.

Как пытался:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

 declare @t table (Element nvarchar(5), DTS datetime, DTE datetime)

 insert into @t
 (Element,DTS,DTE)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:12:00', '2019-12-12 05:15:00')

    select
		top 1 with ties
		he.Element,
		he.DTS,
		fj.DTE
	from @t he
	full outer join (    select
		top 1 with ties
		he.Element,
		he.DTE
	from @t he
	order by ROW_NUMBER() over (partition by he.Element order by he.DTE desc)) fj on
	fj.Element=he.Element 
	order by ROW_NUMBER() over (partition by he.Element order by he.DTS)

В результате ожидаемо получаю
ElementDTSDTEA2019-12-12 05:00:00.0002019-12-12 05:15:00.000

Ожидаемый результат:
ElementDTSDTEA2019-12-12 05:00:00.0002019-12-12 05:09:00.000 A2019-12-12 05:12:00.0002019-12-12 05:15:00.000

С одной стороны можно было бы создать курсор и пробежаться по отсортированной по Element и DTS таблице и определить наличие вхождений текущих записей в предыдущие, но это какой-то кривой способ получается и при наличии 20+К записей будет достаточно долгим.

Можно ли получить желаемое без использования курсора?

Подскажите, пожалуйста.

Спасибо.

...

Рейтинг:

0 / 0

19.12.2019, 10:24

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39905288

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

https://www.sql.ru/forum/afsearch.aspx?s=?????????? ?????????&submit=?????&bid=1

...

Рейтинг:

0 / 0

19.12.2019, 10:47

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39905316

Minamoto

Участник

Откуда: Москва

Сообщения: 1 115

Рейтинг: 0 / 0

Kast2K,

https://www.sql.ru/forum/1218798/zapros-na-ukrupnenie-periodov

...

Рейтинг:

0 / 0

19.12.2019, 11:17

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39905728

Kast2K

Участник

Откуда: Санкт-Петербург

Сообщения: 522

Рейтинг: 0 / 0

Коллеги,

Требуется ваша помощь. :(

В таблице проранжировал все временные отрезки, но если добавляю строчку С, то на выходе получается что первая строка и последняя встают по рангу друг за другом и, следовательно, весь расчет сбивается.
Может быть вы глянете своим свежим не замыленным взглядом, что я упускаю?

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.

 declare @t table (id int identity ,Element nvarchar(5), DateBegin datetime, DateEnd datetime)

 insert into @t
 (Element,DateBegin,DateEnd)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00')
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')

		select 
			Element,
			DateBegin,
			DateEnd,
			max(DateEnd) over (partition by Element ) maxx,
			row_number() over (order by Datebegin),
			row_number() over (partition by Element order by DateBegin),
			row_number() over (order by DateBegin)-row_number() over (partition by Element order by DateBegin) rn
		from @t

select 
	Element, 
	rn,
	min(DateBegin), 
	max(dateend)
from (
		select 
			Element,
			DateBegin,
			DateEnd,
			max(DateEnd) over () maxx,
			row_number() over (order by Datebegin)-row_number() over (partition by Element order by DateBegin) rn
		from @t
	  ) ttt
group by Element,rn,maxx

В итоге должно получиться 5 строчек:
A 0 2019-12-12 05:00:00.000 2019-12-12 05:09:00.000A 2 2019-12-12 05:11:00.000 2019-12-12 05:15:00.000B 4 2019-12-12 05:10:00.000 2019-12-12 05:18:00.000B 5 2019-12-12 05:17:00.000 2019-12-12 05:29:00.000C 1 2019-12-12 05:00:00.000 2019-12-12 05:05:00.000

но сейчас выходит 6
A 0 2019-12-12 05:00:00.000 2019-12-12 05:05:00.000A 1 2019-12-12 05:02:00.000 2019-12-12 05:09:00.000A 2 2019-12-12 05:11:00.000 2019-12-12 05:15:00.000B 4 2019-12-12 05:10:00.000 2019-12-12 05:18:00.000B 5 2019-12-12 05:17:00.000 2019-12-12 05:29:00.000C 1 2019-12-12 05:00:00.000 2019-12-12 05:05:00.000

...

Рейтинг:

0 / 0

19.12.2019, 17:08

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39905746

Владислав Колосов

Участник

Сообщения: 9 447

Рейтинг: 0 / 0

Kast2K,

порядок строк определяет выражение сортировки и больше ничто.

...

Рейтинг:

0 / 0

19.12.2019, 17:38

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39905773

Remind

Участник

Откуда: UK

Сообщения: 412

Рейтинг: 0 / 0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

 declare @t table (id int identity ,Element nvarchar(5), DateBegin datetime, DateEnd datetime)

 insert into @t
 (Element,DateBegin,DateEnd)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00')
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')

;WITH CTE AS
(
  SELECT
    *,
    LAG(DateBegin)  OVER (PARTITION BY Element ORDER BY DateBegin) AS PrevBegin,
    LAG(DateEnd)    OVER (PARTITION BY Element ORDER BY DateBegin) AS PrevEnd   
  FROM @t

),
Groups AS
(
  SELECT *, SUM(CASE WHEN (PrevBegin <= DateEnd AND PrevEnd >= DateBegin) THEN 1 ELSE 0 END) OVER (PARTITION BY Element ORDER BY DateBegin) AS GRP
  FROM CTE
),
FinalGroups AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Element ORDER BY DateBegin) - GRP AS FinalGroup
  FROM Groups
)
SELECT Element, MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd
FROM FinalGroups
GROUP BY Element, FinalGroup
ORDER BY 1, 2

...

Рейтинг:

0 / 0

19.12.2019, 18:47

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39905875

nullin

Гость

Remind, нет гарантии, что в очередном вхождении не будет существовать вложенного интервала дат в один из существующих d(s,e)=(1,9),(4,5).
Если докинуть логически непротиворечивых данных(s<=e) на подобие следующих:

Код: sql

1.
2.
3.
4.
5.
6.

 ,('C', '2019-12-12 05:01:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:02:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:03:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:08:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:07:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:06:00')

То в таком случае последний запрос возвращает некорректные данные. (В наличии пересечение интервала + вложенный).

Вот что у меня получилось:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.

with DatesСollapsed as
(select a.Element, min(a.DateBegin) as DateStart, a.CoveringDateEnd as DateEnd
        /*without group by*/
        /*min(a.DateBegin)
         over(partition by a.Element, a.CoveringDateEnd) as DateStart*/
   from (select t.Element, t.DateBegin, t.DateEnd,
                max(t.DateEnd)
                 over(partition by t.Element order by t.DateBegin) as CoveringDateEnd
           from @t as t) as a
  group by a.Element, a.CoveringDateEnd)
  
, DatesGroupMarked as
(select b.Element, b.DateStart, b.DateEnd, 
        sum(b.CrossingDateStatus)
         over(partition by b.Element order by b.DateStart) as CrossingGroup
   from (select c.Element, c.DateStart, c.DateEnd,
                iif(lag(c.DateEnd, 1, c.DateStart)
                     over(partition by c.Element order by c.DateStart) >= c.DateStart, 0, 1) as CrossingDateStatus 
           from DatesСollapsed as c) as b)
           
, DatesFinal as
(select g.Element,
        min(g.DateStart) as DateStart,
        max(g.DateEnd) as DateEnd
   from DatesGroupMarked as g
  group by g.Element, g.CrossingGroup)
  
select f.Element, f.DateStart, f.DateEnd
  from DatesFinal as f
  
 order by f.Element, f.DateStart

Нейминг стилем автора)))

...

Рейтинг:

0 / 0

20.12.2019, 01:50

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39905893

3unknown

Гость

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.

declare @t table (Element nvarchar(5), DTS datetime, DTE datetime)

 insert into @t
 (Element,DTS,DTE)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00')
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')
 
 
 
 ;with a as(
 select*
 ,ROW_NUMBER() over(partition by Element order by DTS) n
 from @t
 )
 select Element,MIN(DTS) DTS,MAX(DTE) DTE
 from(
   select Element,DTS,DTE,num
 from(
 select a.Element,a.DTS,a.DTE,
 ROW_NUMBER() over(partition by a.Element order by a.DTS) m,
  case when  a.DTE < a1.DTS then 1 else 0 end num
 from a
  join a a1 on a1.n - a.n = 1
  ) c
  where m=1
 union 
 select Element,DTS,DTE,max(num) num
 from(
 select a1.Element,a1.DTS,a1.DTE,
 ROW_NUMBER() over(partition by a.Element order by a.DTS) m,
  case when  a.DTE < a1.DTS then 1 else 0 end num
 from a
  join a a1 on a1.n - a.n = 1
  ) c
  where m>1
  group by Element,DTS,DTE
  
 ) b
 group by Element,num
 order by Element

...

Рейтинг:

0 / 0

20.12.2019, 04:26

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906105

Remind

Участник

Откуда: UK

Сообщения: 412

Рейтинг: 0 / 0

nullin,

Достаточно добавить DateEnd в ORDER BY:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.

 declare @t table (id int identity ,Element nvarchar(5), DateBegin datetime, DateEnd datetime)

 insert into @t
 (Element,DateBegin,DateEnd)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00')
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:01:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:02:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:03:00', '2019-12-12 05:05:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:08:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:07:00')
 ,('C', '2019-12-12 05:04:00', '2019-12-12 05:06:00')

;WITH CTE AS
(
  SELECT
    *,
    LAG(DateBegin)  OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS PrevBegin,
    LAG(DateEnd)    OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS PrevEnd   
  FROM @t

),
Groups AS
(
  SELECT *, SUM(CASE WHEN (PrevBegin <= DateEnd AND PrevEnd >= DateBegin) THEN 1 ELSE 0 END) OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS GRP
  FROM CTE
),
FinalGroups AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) - GRP AS FinalGroup
  FROM Groups
)
SELECT Element, MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd
FROM FinalGroups
GROUP BY Element, FinalGroup
ORDER BY 1, 2

...

Рейтинг:

0 / 0

20.12.2019, 13:20

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906176

entrypoint

Гость

Remind,

Решение не работает

insert into @t
(Element,DateBegin,DateEnd)
values
('A', '1753-12-12 05:00:00', '2050-12-12 05:05:00'), --- ***
('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00'),
('B', '1753-12-12 05:00:00', '2050-12-12 05:05:00') --- ***
,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
,('C', '1753-12-12 05:00:00', '2050-12-12 05:05:00') --- ***
,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')
,('C', '2019-12-12 05:01:00', '2019-12-12 05:05:00')
,('C', '2019-12-12 05:02:00', '2019-12-12 05:05:00')
,('C', '2019-12-12 05:03:00', '2019-12-12 05:05:00')
,('C', '2019-12-12 05:04:00', '2019-12-12 05:08:00')
,('C', '2019-12-12 05:04:00', '2019-12-12 05:07:00')
,('C', '2019-12-12 05:04:00', '2019-12-12 05:06:00')

...

Рейтинг:

0 / 0

20.12.2019, 15:04

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906178

entrypoint

Гость

3unknown,
???

insert into @t
(Element,DTS,DTE)
values
('A', '1753-12-12 05:00:00', '2030-12-12 05:05:00'), -- ***
('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00')
,('B', '1753-12-12 05:10:00', '2030-12-12 05:18:00') -- ***
,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00')
,('C', '1753-12-12 05:00:00', '2030-12-12 05:05:00') -- ***

...

Рейтинг:

0 / 0

20.12.2019, 15:06

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906210

invm

Участник

Откуда: Москва

Сообщения: 9 956

Рейтинг: 0 / 0

Экзотика

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.

 declare @t table (id int identity ,Element nvarchar(5), DateBegin datetime, DateEnd datetime)

 insert into @t
 (Element,DateBegin,DateEnd)
 values
 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00'),
 ('A', '2019-12-12 05:14:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00');
 
with a as
(
 select
  t.id, t.Element, dl.dt, dl.f,
  lag(dl.f, 1, 1) over (partition by t.Element order by dl.dt) as pf,
  lead(dl.f, 1, 0) over (partition by t.Element order by dl.dt) as nf
 from
  @t t cross apply
  (values (t.DateBegin, 0), (t.DateEnd, 1)) dl(dt, f)
),
b as
(
 select
  *,
  (row_number() over (partition by Element order by dt, f) - 1) / 2 as g
 from
  a
 where
  (f = 0 and pf = 1) or (f = 1 and nf = 0)
)
select
 Element, min(dt), max(dt)
from
 b
group by
 Element, g
order by
 Element, min(dt);

...

Рейтинг:

0 / 0

20.12.2019, 15:54

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906212

nullin

Гость

Kast2K, вы поясните, случай, который я охарактеризовал у вас возможен?
Если - да, то запрос 3unknown, тоже нерабочий, потому что здесь уже шаблонное решение требует учета дополнительных условий.
Почему у unknown не работает - читая по диагонали не понятно, надо разбираться.
Кинул все предложенные варианты(последние) на dbfiddle

...

Рейтинг:

0 / 0

20.12.2019, 15:55

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906215

entrypoint

Гость

invm,

insert into @t
(Element,DateBegin,DateEnd)
values
('A', '2019-12-12 04:59:59', '2019-12-12 05:19:01'), -- **
('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00'),
('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00'),
('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00'),
('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00'),
('A', '2019-12-12 05:14:00', '2019-12-12 05:19:00')
,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00')
,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00')
,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00')
,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00');

...

Рейтинг:

0 / 0

20.12.2019, 15:57

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906218

nullin

Гость

invm, а у вас тоже что-то пошло не так))) хотя автор еще не пояснил

...

Рейтинг:

0 / 0

20.12.2019, 15:59

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906221

Remind

Участник

Откуда: UK

Сообщения: 412

Рейтинг: 0 / 0

entrypoint,

Пятница :(

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

;WITH CTE AS
(
  SELECT
    *,
    LAG(DateBegin)  OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS PrevBegin,
    LAG(DateEnd)    OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS PrevEnd   
  FROM @t t
  WHERE NOT EXISTS
  (
    SELECT 1 FROM @t WHERE Element = t.Element AND DateBegin < t.DateBegin AND DateEnd > t.DateEnd
  )
),
Groups AS
(
  SELECT *, SUM(CASE WHEN (PrevBegin <= DateEnd AND PrevEnd >= DateBegin) THEN 1 ELSE 0 END) OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) AS GRP
  FROM CTE
),
FinalGroups AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Element ORDER BY DateBegin, DateEnd) - GRP AS FinalGroup
  FROM Groups
)
SELECT Element, MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd
FROM FinalGroups
GROUP BY Element, FinalGroup
ORDER BY 1, 2

...

Рейтинг:

0 / 0

20.12.2019, 16:02

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906246

Kast2K

Участник

Откуда: Санкт-Петербург

Сообщения: 522

Рейтинг: 0 / 0

Добрый день!

хочу сразу оговориться, что сервер 2008 (стыжусь, что не указал сразу) и не все предложенные варианты на нем стартуют.
Обнаруженные проблемы при таком представлении данных (настоящие):
A541 2019-12-18 10:55:24.627 2019-12-18 11:14:44.710 1A541 2019-12-18 10:56:44.623 2019-12-18 11:15:25.733 2A541 2019-12-18 10:59:25.163 2019-12-18 11:18:04.990 3A541 2019-12-18 11:02:05.350 2019-12-18 11:20:45.003 4A541 2019-12-18 11:04:44.793 2019-12-18 11:23:24.670 5A541 2019-12-18 11:07:24.960 2019-12-18 11:26:05.327 6A541 2019-12-18 11:10:04.913 2019-12-18 11:28:45.030 7A541 2019-12-18 11:12:45.313 2019-12-18 11:31:24.917 8A541 2019-12-18 15:33:24.340 2019-12-18 15:52:04.523 9A541 2019-12-18 15:36:44.180 2019-12-18 15:55:24.620 10A541 2019-12-18 15:40:04.193 2019-12-18 15:59:24.637 11A541 2019-12-18 15:42:44.327 2019-12-18 16:02:05.063 12A541 2019-12-18 15:54:04.840 2019-12-18 16:12:05.450 13A541 2019-12-18 15:56:44.480 2019-12-18 16:14:44.850 14A541 2019-12-18 15:58:04.407 2019-12-18 16:16:05.407 15

При данной выборке из БД все примеры спотыкаются (в частности от 3unknown) и в результате получается
A541 2019-12-18 10:55:24.627 2019-12-18 15:52:04.523A541 2019-12-18 15:33:24.340 2019-12-18 16:16:05.407

nullin

Возможен только при услови, что Element разные.
В один момент времени в систему попадает несколько Element с одинаковой датой\временем (
A541, 2019-12-18 15:58:04.407,
B741, 2019-12-18 15:58:04.407,
C121, 2019-12-18 15:58:04.407,
)
Выходят (DTE )они также в один и тот же момент

...

Рейтинг:

0 / 0

20.12.2019, 16:48

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906252

Remind

Участник

Откуда: UK

Сообщения: 412

Рейтинг: 0 / 0

Kast2K,

Интересно какой результат вы ожидаете на таких входных данных.

...

Рейтинг:

0 / 0

20.12.2019, 17:01

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906254

Minamoto

Участник

Откуда: Москва

Сообщения: 1 115

Рейтинг: 0 / 0

Kast2K, а один из ответов по ссылке из второго ответа в этой теме отдает верный ответ )

Осталось там только группировку по Element добавить.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.

declare @t table(
	 ID int PRIMARY KEY CLUSTERED,
     Element varchar(100)
	,DateBegin datetime
	,DateEnd datetime
);

insert into @t (Element, DateBegin, DateEnd, ID) VALUES 
('A541', '2019-12-18 10:55:24.627', '2019-12-18 11:14:44.710',	1 ),
('A541', '2019-12-18 10:56:44.623', '2019-12-18 11:15:25.733',	2 ),
('A541', '2019-12-18 10:59:25.163', '2019-12-18 11:18:04.990',	3 ),
('A541', '2019-12-18 11:02:05.350', '2019-12-18 11:20:45.003',	4 ),
('A541', '2019-12-18 11:04:44.793', '2019-12-18 11:23:24.670',	5 ),
('A541', '2019-12-18 11:07:24.960', '2019-12-18 11:26:05.327',	6 ),
('A541', '2019-12-18 11:10:04.913', '2019-12-18 11:28:45.030',	7 ),
('A541', '2019-12-18 11:12:45.313', '2019-12-18 11:31:24.917',	8 ),
('A541', '2019-12-18 15:33:24.340', '2019-12-18 15:52:04.523',	9 ),
('A541', '2019-12-18 15:36:44.180', '2019-12-18 15:55:24.620',	10),
('A541', '2019-12-18 15:40:04.193', '2019-12-18 15:59:24.637',	11),
('A541', '2019-12-18 15:42:44.327', '2019-12-18 16:02:05.063',	12),
('A541', '2019-12-18 15:54:04.840', '2019-12-18 16:12:05.450',	13),
('A541', '2019-12-18 15:56:44.480', '2019-12-18 16:14:44.850',	14),
('A541', '2019-12-18 15:58:04.407', '2019-12-18 16:16:05.407',	15);


with a as
(
 select
  t.DateBegin, row_number() over (order by t.DateBegin) as rn
 from
  @t t
 where
  not exists(select 1 from @t where DateBegin < t.DateBegin and DateBegin <= t.DateEnd and DateEnd >= t.DateBegin)
),
b as
(
 select
  t.DateEnd, row_number() over (order by t.DateEnd) as rn
 from
  @t t
 where
  not exists(select 1 from @t where DateEnd > t.DateEnd and DateBegin <= t.DateEnd and DateEnd >= t.DateBegin)
)
select
 a.DateBegin, b.DateEnd
from
 a join
 b on b.rn = a.rn;

...

Рейтинг:

0 / 0

20.12.2019, 17:03

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906255

Kast2K

Участник

Откуда: Санкт-Петербург

Сообщения: 522

Рейтинг: 0 / 0

Remind

Kast2K,

Интересно какой результат вы ожидаете на таких входных данных.

A541 2019-12-18 10:55:24.627 2019-12-18 11:31:24.91 A541 2019-12-18 15:33:24.340 2019-12-18 16:16:05.407

...

Рейтинг:

0 / 0

20.12.2019, 17:04

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906260

Minamoto

Участник

Откуда: Москва

Сообщения: 1 115

Рейтинг: 0 / 0

собственно, вот с группировкой:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.

declare @t table(
	 ID int PRIMARY KEY CLUSTERED,
     Element varchar(100)
	,DateBegin datetime
	,DateEnd datetime
);

insert into @t (Element, DateBegin, DateEnd, ID) VALUES 
('A541', '2019-12-18 10:55:24.627', '2019-12-18 11:14:44.710',	1 ),
('A541', '2019-12-18 10:56:44.623', '2019-12-18 11:15:25.733',	2 ),
('A541', '2019-12-18 10:59:25.163', '2019-12-18 11:18:04.990',	3 ),
('A541', '2019-12-18 11:02:05.350', '2019-12-18 11:20:45.003',	4 ),
('A541', '2019-12-18 11:04:44.793', '2019-12-18 11:23:24.670',	5 ),
('A541', '2019-12-18 11:07:24.960', '2019-12-18 11:26:05.327',	6 ),
('A541', '2019-12-18 11:10:04.913', '2019-12-18 11:28:45.030',	7 ),
('A541', '2019-12-18 11:12:45.313', '2019-12-18 11:31:24.917',	8 ),

('A541', '2019-12-18 15:33:24.340', '2019-12-18 15:52:04.523',	9 ),
('A541', '2019-12-18 15:36:44.180', '2019-12-18 15:55:24.620',	10),
('A541', '2019-12-18 15:40:04.193', '2019-12-18 15:59:24.637',	11),
('A541', '2019-12-18 15:42:44.327', '2019-12-18 16:02:05.063',	12),
('A541', '2019-12-18 15:54:04.840', '2019-12-18 16:12:05.450',	13),
('A541', '2019-12-18 15:56:44.480', '2019-12-18 16:14:44.850',	14),
('A541', '2019-12-18 15:58:04.407', '2019-12-18 16:16:05.407',	15),


 ('A', '2019-12-12 05:00:00', '2019-12-12 05:05:00', 21),
 ('A', '2019-12-12 05:02:00', '2019-12-12 05:07:00', 22),
 ('A', '2019-12-12 05:03:00', '2019-12-12 05:09:00', 23),
 ('A', '2019-12-12 05:11:00', '2019-12-12 05:15:00', 24)
 ,('B', '2019-12-12 05:10:00', '2019-12-12 05:18:00', 31)
 ,('B', '2019-12-12 05:17:00', '2019-12-12 05:19:00', 32)
 ,('B', '2019-12-12 05:27:00', '2019-12-12 05:29:00', 33)
 ,('C', '2019-12-12 05:00:00', '2019-12-12 05:05:00', 34)
;


with a as
(
 SELECT t.Element,
  t.DateBegin, row_number() over (PARTITION BY t.Element ORDER by t.DateBegin) as rn
 from
  @t t
 where
  not exists(select 1 from @t where Element = t.Element AND DateBegin < t.DateBegin and DateBegin <= t.DateEnd and DateEnd >= t.DateBegin)
),
b as
(
 SELECT t.Element,
  t.DateEnd, row_number() over (PARTITION BY t.Element ORDER by t.DateEnd) as rn
 from
  @t t
 where
  not exists(select 1 from @t where Element = t.Element AND DateEnd > t.DateEnd and DateBegin <= t.DateEnd and DateEnd >= t.DateBegin)
)
SELECT a.Element,
 a.DateBegin, b.DateEnd
from
 a join
 b on b.rn = a.rn AND b.Element = a.Element;

...

Рейтинг:

0 / 0

20.12.2019, 17:09

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906261

Remind

Участник

Откуда: UK

Сообщения: 412

Рейтинг: 0 / 0

Kast2K,

Как минимум решение nullin'a дает правильный ответ (мое тоже, но у вас версия сервера не подходящая)

upd. А, у него тоже lag используется, не заметил сразу.

...

Рейтинг:

0 / 0

20.12.2019, 17:09

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906272

nullin

Гость

Remind, да посмотрел - у меня все ок в последнем примере, но у меня там еще order by в окне, в 2008 такого не было - автор конечно вовремя вспомнил)
Но все эти lag/lead и прочие нарастающие итоги, имея код на руках, легко отображаются в соответствующие cross/outer apply c top(1) или с агрегацией.

...

Рейтинг:

0 / 0

20.12.2019, 17:19

| Ответить | Цитировать | Написать

Нахождение мин. и макс. дат вхождения в диапазон

#39906290

Kast2K

Участник

Откуда: Санкт-Петербург

Сообщения: 522

Рейтинг: 0 / 0

Коллеги,

Я хочу всем участвующим огромное спасибо за помощь и примеры реализации.

Я понял в чём была моя изначальная ошибка: Скрипт предложенный Minamoto я тестировал на кривых тестовых данных и он не отрабатывал нормально + логика понимания была некорректной (образно, надо 7 раз нарисовать и 1 раз написать :) )

Также, благодаря вам я понял как правильно обрабатывать такие массивы данных и как поменять свою логику работы.

Ещё раз всем спасибо!

...

Рейтинг:

0 / 0

20.12.2019, 17:35

| Ответить | Цитировать | Написать

24 сообщений из 24, страница 1 из 1

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нахождение мин. и макс. дат вхождения в диапазон

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&msg=39906218&tid=1686743]:	0ms
get settings:	11ms
get forum list:	15ms
check forum access:	4ms
check topic access:	4ms
track hit:	32ms
get topic data:	10ms
get forum data:	3ms
get page messages:	60ms
get tp. blocked users:	1ms
others:	17ms

total:	157ms