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

Откуда: Spring
Сообщений: 18
Добрый день, господа!
Есть у меня одна задача - учесть непрерывное время которое провел человек с накладными на руках:
У таблицы тривиальная структура:
ID_N - идентификатор накладной,
ID_U - идентификатор сотрудника,
DT_B - дата выдачи накладной,
DT_E - дата сдачи накладной

Интервалы пересекаются - то есть человек может взять три накладные, две сдать, одна на руках (причину любую на ваш вкус), взять еще одну... затем сдать обе.
Итак есть сырые данные:
ID_WT		ID_U		DT_B				DT_E
34531862	0		2018-09-27 05:12:37.000		2018-09-27 05:12:47.000
34531916	0		2018-09-27 05:11:18.000		2018-09-27 05:13:30.000
34531899	0		2018-09-27 05:09:11.000		2018-09-27 05:11:36.000
34531688	0		2018-09-27 05:08:13.000		2018-09-27 05:10:27.000
34531903	0		2018-09-27 05:07:32.000		2018-09-27 05:10:19.000


Наклепал запрос
select top 100 wt.id_u,
CASE WHEN (	select min(wh.dt_b) 
			from wh 
			where wh.id_u = wt.id_u and wh.dt_b < wt.dt_b and wh.dt_e => wt.dt_b 
	) is null then wt.dt_b end as d_begin,
(select max(wh.dt_e)
	from wh, 
	where wh.id_u = wt.id_u and
	wh.dt_b > wt.dt_b 
	and wh.dt_b <= wt.dt_e 
	and wh.dt_e > wt.dt_e
	order by wh.id_u, wh.dt_b
	) as d_end
from wh wt 
order by id_u, d_begin

работает адски долго. видимо вложенные SELECT с агрегатными функциями тяжко даются. Вытащил самый сок, чтобы посмотреть что он выбирает:
select distinct wh.* from wh, wh wt 
where wh.id_u = wt.id_u 
and wh.dt_b > wt.dt_b 
and wh.dt_b <= wt.dt_e 
and wh.dt_e > wt.dt_e
order by wh.id_u, wh.dt_b

а он не выбирает крайние накладные:
ID_WT		ID_U		DT_B				DT_E
34531688	0		2018-09-27 05:08:13.000		2018-09-27 05:10:27.000
34531899	0		2018-09-27 05:09:11.000		2018-09-27 05:11:36.000
34531916	0		2018-09-27 05:11:18.000		2018-09-27 05:13:30.000


собственно хотелось бы получить начало и конец периода где накладные пересекаются, для наглядности:

Картинка с другого сайта.
27 окт 18, 20:38    [21716977]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
PizzaPizza
Member

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

А может ли один сотрудник взять одну и ту же накладную несколько раз?

Если сортировать по началу, то записи, которые заканчиваются раньше, чем конец предыдущей входят в непрерывное время. Может lag/lead тут удобнее будет?
27 окт 18, 21:30    [21716999]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
vikkiv
Member

Откуда: London
Сообщений: 2704
AR159,

концептуально-архитектурно надо сначала решить,
(не говоря о том что T-SQL конечно может много чего разрулить -
но не обязательно будет самым эффективным средством)

так понимаю какой именно накладной - не важно, т.е. ID_WT в данном
контексте не нужно, остаются интервалы и сотрудник

будет множество случаев, оптимальное решение для разных типов будет различаться

тип 1
есть крайние пересекающиеся интервалы min(dt_b) ~ max(dt_e) для разных накладных
и куча смешанного солянки между ними
тогда естественно всё что между ними (крайними интервалами) - пересчитывать не надо
datediff(min(),max())

тип 2
интервал только один
datediff(min(),max())

тип 3
последовательность пересекающихся интервалов
datediff(min(),max())

тип 4
последовательность непересекающихся интервалов
считать для каждого, потом сумма

тип 5
смесь тип 3 и тип 4
тут целое поле возможных сценариев решений
различные группировки по приведению к типу 3 - от него к типу 2,
потом эти непересекающиеся группы по типу 4 и т.д.
(вариантов подходов хватает, мерять производительность надо)

ну для упрощения всякие обёртки дополнительной логики -
типа dt_e not null , т.е. dt_e default = now() для отчёта

(может пропустил какой тип)

так что для начала надо-бы исследовать доминирование какого-то типа
в самих данных из допустим какого-то случайного образца в скажем
1000 работников (фактически частота каждого сценария по типам
не повторяясь, т.е. отнести к максимальному),

затем оценить по каким именно типам наиболее часто пускаются расчёты

и потом уже на основании результата писать алгоритм запроса чтобы был баланс
покрытия большого количества случаев необходимых для отчётности с приемлемым временем.
(если там ещё какой внутренней классификации по приоритетности нет, типа для одного
начальника это всегда срочно, для другого хватает раз в месяц рассылку делать).

если предварительно писать логику классификации случая (на основе решения которой будет
собственно и выполнен подходящий алгоритм по оптимальности) - то в принципе добавит время
на предварительную оценку - но скорее всего ускорит время
на остальные расчёты для данного работника.

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

это к тому что решение об оптимальным балансе приоритетов между разными
операциями/нодами древа решений логики подзапросов придётся подбирать самому.
27 окт 18, 21:33    [21717003]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
with a as
(
 select
  t.*, row_number() over (partition by t.ID_U order by t.DT_B) as rn
 from
  Таблица t
 where
  not exists(select 1 from @t where ID_U = t.ID_U and DT_B < t.DT_B and t.DT_B between DT_B and DT_E)
),
b as
(
 select
  t.*, row_number() over (partition by t.ID_U order by t.DT_E) as rn
 from
  Таблица t
 where
  not exists(select 1 from @t where ID_U = t.ID_U and DT_E > t.DT_E and t.DT_E between DT_B and DT_E)
)
select
 a.ID_U, a.DT_B, b.DT_E
from
 a join
 b on b.ID_U = a.ID_U and b.rn = a.rn;
27 окт 18, 22:44    [21717043]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
AR159
Member

Откуда: Spring
Сообщений: 18
invm, отлично! получил все пересекающиеся интервалы, приправил их накладными которые не пересекаются, получил минуты в день.
with a as
(
 select
  t.*, row_number() over (partition by t.id_u order by t.dt_b) as rn
 from
  wh t
 where
  not exists(select 1 from wh where id_u = t.id_u and dt_b < t.dt_b and t.dt_b between dt_b and dt_e) 
),
b as
(
 select
  t.*, row_number() over (partition by t.id_u order by t.dt_e) as rn
 from
  wh t
 where
  not exists(select 1 from wh where id_u = t.id_u and dt_e > t.dt_e and t.dt_e between dt_b and dt_e) 
)

 select distinct 
  ft.id_u, 
  CONVERT(VARCHAR(15), ft.dt_b, 104) as dt_b,
  sum(DATEDIFF(MINUTE,ft.dt_b,ft.dt_e)) as _minutes
 from wh ft
 where ft.id_wt not in(
		select distinct 
		 a.id_wt
		from
		 a join
		 b on b.id_u = a.id_u and b.rn = a.rn
		 )
 group by ft.id_u, ft.dt_b
 
 union 

 select distinct 
 a.id_u, 
  CONVERT(VARCHAR(15), a.dt_b, 104) as dt_b,
 sum(DATEDIFF(MINUTE,a.dt_b,b.dt_e)) as _minutes
from
 a join
 b on b.id_u = a.id_u and b.rn = a.rn
 group by a.id_u, a.dt_b


только вот теперь думаю как пронумеровать полученные интервалы и составить соответствие накладной интервалу? ведь получается что хоть одна хоть пять накладных могут относиться к одному интервалу но
...not exist (select 1...

пропускает все id_wt что находятся внутри...
29 окт 18, 18:21    [21718285]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
AR159
Member

Откуда: Spring
Сообщений: 18
PizzaPizza,

накладная берется один раз и сдается один раз. сортировать по всей видимости нет смысла, т.к. есть накладные номер которой больше а дата начала сборки раньше чем у предыдущей - по стеку накладные могут перемещаться с разной скоростью у разных работников, отсюда и разные даты начала при последовательных номерах.
29 окт 18, 18:34    [21718295]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
AR159
Member

Откуда: Spring
Сообщений: 18
vikkiv,

Я подумал и понял что терять накладную внутри интервала было бы нехорошо, с точки зрения статистики. К тому же в перспективе захочется провалиться куда нибудь в количество позиций накладной, количество коробок, вес, объем и прочее...

Думаю должна существовать таблица соответствия интервала накладной, а не сотруднику, т.о. и накладная не будет потеряна и по сотруднику можно посчитать интервалы.

Видится мне такая картинка:
Картинка с другого сайта.
29 окт 18, 18:49    [21718309]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
AR159
Member

Откуда: Spring
Сообщений: 18
invm,

Сейчас заметил что предложенный вами алгоритм не работает

DATE 		_secods		a.dt_b				b.dt_e
26.10.2018	-288780		2018-10-26 03:31:57.000  	2018-10-22 19:18:57.000
25.10.2018	-268928		2018-10-25 20:29:28.000  	2018-10-22 17:47:20.000
29 окт 18, 19:12    [21718337]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
vikkiv
Member

Откуда: London
Сообщений: 2704
AR159,

смотря что ты считаешь держанием накладной
(хотя важно-ли какая именно накладная - в задаче не говорится)
1) можно считать время удержания на руках (не важно сколько штук)
2) а можно считать время нахождения каждой накладной на руках
и считать суммарное время (т.е. время на количество)

мой вариант (где накладная не важна) подразумевал первый сценарий.

другое дело за ранее предусматривать в алгоритме остальные возможные комбинации
вариантов наращивая сложность и теряя производительность без необходимости -
довольно сомнительное занятие, ну если только уже не знаешь всё ширину проекта.
29 окт 18, 19:15    [21718339]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
AR159
Member

Откуда: Spring
Сообщений: 18
vikkiv,

вопрос производительности не стоит.
Если будем считать в лоб все накладные которые держал работник на руках (при этом допускается одновременное) - получим суммы среднепотолочные ))

Цель проекта - оптимизация склада.
Моя задача посчитать сколько времени у человека на руках накладные, если их несколько - необходимо посчитать период, указать какие накладные относятся к какому периоду. Терять накладные в недрах периода нельзя, т.к. статистическая/количественная составляющая будет утеряна.

Если учесть все накладные которые были на руках в одном периоде - можно просчитать какие товары поставить рядом чтобы минимизировать перемещение сотрудника. Посчитать сколько КГ или кубометров перенес, можно группировать схожие по расположению накладные в стеке.
29 окт 18, 19:30    [21718350]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
vikkiv
Member

Откуда: London
Сообщений: 2704
AR159,

хорошо, пока всё плавающее и допускающее множественные интерпретации...

так что с учётом того что формат входных данных уже задан - неплохо-бы
определить в такой-же табличной форме (а этим в данном контексте
и оперирует SQL) что нужно на выходе, в процессе заодно получится
самому более чётко определится с требованиями
(даже возможно пару вариантов таблиц на разные отчёты)
29 окт 18, 20:48    [21718402]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
AR159
Сейчас заметил что предложенный вами алгоритм не работает
Значит неверные исходные данные - есть строки, где DT_E < DT_B.
AR159
необходимо посчитать период, указать какие накладные относятся к какому периоду
+
declare @t table (ID_WT int, ID_U int, DT_B datetime, DT_E datetime);
insert into @t
values
(345318621, 0, '2018-09-27T05:12:37.000', '2018-09-27T05:12:47.000'),
(345319161, 0, '2018-09-27T05:11:18.000', '2018-09-27T05:13:30.000'),
(345318991, 0, '2018-09-27T06:09:11.000', '2018-09-27T06:11:36.000'),
(345316881, 0, '2018-09-27T06:08:13.000', '2018-09-27T06:10:27.000'),
(345319031, 0, '2018-09-27T06:07:32.000', '2018-09-27T06:10:19.000'),
(345318622, 1, '2018-09-28T05:12:37.000', '2018-09-28T05:12:47.000'),
(345319162, 1, '2018-09-28T05:11:18.000', '2018-09-28T05:13:30.000'),
(345318992, 1, '2018-09-28T05:09:11.000', '2018-09-28T05:11:36.000'),
(345316882, 1, '2018-09-28T05:08:13.000', '2018-09-28T05:10:27.000'),
(345319032, 1, '2018-09-28T05:07:32.000', '2018-09-28T05:10:19.000'),
(345316883, 0, '2018-09-29T05:08:13.000', '2018-09-29T05:10:27.000'),
(345316884, 0, '2018-09-30T05:08:13.000', '2018-09-30T05:10:27.000');

with a as
(
 select
  t.*, row_number() over (partition by t.ID_U order by t.DT_B) as rn
 from
  @t t
 where
  not exists(select 1 from @t where ID_U = t.ID_U and DT_B < t.DT_B and t.DT_B between DT_B and DT_E)
),
b as
(
 select
  t.*, row_number() over (partition by t.ID_U order by t.DT_E) as rn
 from
  @t t
 where
  not exists(select 1 from @t where ID_U = t.ID_U and DT_E > t.DT_E and t.DT_E between DT_B and DT_E)
)
select
 a.ID_U, a.DT_B, b.DT_E, t.ID_WT, dense_rank() over (partition by a.ID_U order by a.DT_B) as ID_Period, datediff(second, a.DT_B, b.DT_E) as Duration
from
 a join
 b on b.ID_U = a.ID_U and b.rn = a.rn join
 @t t on t.ID_U = a.ID_U and t.DT_B <= b.DT_E and t.DT_E >= a.DT_B;
29 окт 18, 20:49    [21718405]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
AR159
Цель проекта - оптимизация склада.
Моя задача посчитать сколько времени у человека на руках накладные, если их несколько - необходимо посчитать период, указать какие накладные относятся к какому периоду. Терять накладные в недрах периода нельзя, т.к. статистическая/количественная составляющая будет утеряна.


У вас как бы несколько задач. Интервалы занятости по сотрудникам и содержимое интервалов. Это удобнее делать разными запросами.
29 окт 18, 21:31    [21718425]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
AR159
Member

Откуда: Spring
Сообщений: 18
invm,

увы но не получается...
+
Картинка с другого сайта.
29 окт 18, 21:40    [21718428]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
AR159
Member

Откуда: Spring
Сообщений: 18
invm,

+
Картинка с другого сайта.

прошу прощения за кривой хостинг
29 окт 18, 21:44    [21718430]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
AR159
Member

Откуда: Spring
Сообщений: 18
PizzaPizza,

количество запросов совершенно не принципиально
29 окт 18, 21:45    [21718432]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
PizzaPizza
Member

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

Вы можете дать побольше тестовых данных, что бы с разрывами было? Очень лень самому даты придумывать.

для поиска непрерывных интервалов попробуйте вот такую петрушку:

declare @t table (
id integer,
s DATETIME,
e DATETIME
)

Insert into @t values 

(34531862,'2018-09-27 05:12:37','2018-09-27 05:12:47.000'),
(34531916,'2018-09-27 05:11:40','2018-09-27 05:13:30.000'),
(34531899,'2018-09-27 05:09:11','2018-09-27 05:11:36.000'),
(34531899,'2018-09-27 05:09:15','2018-09-27 05:09:36.000'),
(34531688,'2018-09-27 05:08:13','2018-09-27 05:10:27.000'),
(34531903,'2018-09-27 05:07:32','2018-09-27 05:10:19.000'),
(34531903,'2018-09-27 06:07:35','2018-09-27 06:15:19.000'),
(34531903,'2018-09-27 06:09:32','2018-09-27 06:15:19.000'),
(34531903,'2018-09-27 06:20:32','2018-09-27 06:22:19.000');

with a as (
select
    t.*, 
    case when MAX(e) over (Order by s) = e 
        AND ( DATEDIFF(SECOND,e,LEAD(e) over (Order by s) ) <= 0 
            OR DATEDIFF(SECOND,e,LEAD(e) over (Order by s) ) is NULL) 
    then 1 else 0 end as gr_start
 from
  @t as t
),
b as (
select a.id, a.s, a.e,
sum(gr_start) over (Order by s) as gr
from a
),
c as (
select 
    b.s, 
    b.e, 
    gr = case when b.gr = 0 then 1 else gr end
from b
)

select 
min(s), max(e)
from c
GROUP BY gr
30 окт 18, 05:07    [21718522]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
AR159
увы но не получается...
Видимо у вас для некоторых граничных строк есть дубликаты по датам начала/конца.
Попробуйте так:
with a as
(
 select
  x.*, row_number() over (partition by x.ID_U order by x.DT_B) as rn
 from
  (
   select distinct
    t.ID_WT, t.ID_U, t.DT_B
   from
    @t t
   where
    not exists(select 1 from @t where ID_U = t.ID_U and DT_B < t.DT_B and t.DT_B between DT_B and DT_E)
  ) x
),
b as
(
 select
  x.*, row_number() over (partition by x.ID_U order by x.DT_E) as rn
 from
  (
   select distinct
    t.ID_WT, t.ID_U, t.DT_E
   from
    @t t
   where
    not exists(select 1 from @t where ID_U = t.ID_U and DT_E > t.DT_E and t.DT_E between DT_B and DT_E)
  ) x
)
...
30 окт 18, 12:38    [21718855]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
AR159
Member

Откуда: Spring
Сообщений: 18
PizzaPizza,

увы тоже не выходит - строки 9-10 явный разрыв в несколько часов.

+
Картинка с другого сайта.
31 окт 18, 16:14    [21720533]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
Щукина Анна
Member

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

Картинки у вас - красивые. Но они мало чем могут помочь делу. Лучше приведите репрезентативный тестовый набор данных, иллюстрирующий проблемы, возникшие в предложенных решениях.
Чтобы было на чем пытаться понять суть проблемы и предложить более правильные варианты...
1 ноя 18, 06:34    [21721006]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
AR159
... Интервалы пересекаются ...

... собственно хотелось бы получить начало и конец периода где накладные пересекаются ...


Для затравки - склейка" пересекающихся накладных в сплошные интервалы:
with
-- 
-- Исходные данные:
  wh (ID_WT, ID_U, DT_B, DT_E) as
    (
      select * 
        from ( values
               -- Первый непрерывный интервал:
                 (34531862,0,'2018-09-27 05:12:37.000','2018-09-27 05:12:47.000'),
                 (34531916,0,'2018-09-27 05:11:18.000','2018-09-27 05:13:30.000'),
                 (34531899,0,'2018-09-27 05:09:11.000','2018-09-27 05:11:36.000'),
                 (34531688,0,'2018-09-27 05:08:13.000','2018-09-27 05:10:27.000'),
                 (34531903,0,'2018-09-27 05:07:32.000','2018-09-27 05:10:19.000'),
               -- Второй непрерывный интервал:
                 (34531862,0,'2018-09-27 06:12:37.000','2018-09-27 06:12:47.000'),
                 (34531916,0,'2018-09-27 06:11:18.000','2018-09-27 06:13:30.000'),
                 (34531899,0,'2018-09-27 06:09:11.000','2018-09-27 06:11:36.000'),
                 (34531688,0,'2018-09-27 06:08:13.000','2018-09-27 06:10:27.000'),
                 (34531903,0,'2018-09-27 06:07:32.000','2018-09-27 06:10:19.000')
              ) v (w,a,s,p)
    )
--
-- Поиск начал непрерывных интервалов:
, wh_with_flag as
    (
      select * 
             -- Ключевой момент запроса:
           , case 
               when DT_B <= max(DT_E) over(partition by ID_U
                                               order by DT_B, DT_E 
                                                rows between unbounded preceding
                                                         and 1 preceding) 
                 then 0 
               else 1 
             end as sog 
        from wh
    )
--
-- Идентификация строк, образующих непрерывные интервалы:
, wh_with_group as
    (
      select *
           -- Ключевой момент запроса:
           , sum(sog) over(partition by ID_U order by DT_B, DT_E) as grp_id 
        from wh_with_flag
    )
--
-- "Слияние" строк в интервалы, получение конечных точек непрерывных интервалов:
, wh_ as
    (
      select ID_U, grp_id, min(DT_B) as min_DT_B, max(DT_E) as max_DT_E
        from wh_with_group
       group by ID_U, grp_id
)
--
-- Визуализация результата манипуляций:
select ID_U, min_DT_B, max_DT_E
  from wh_
 order by ID_U, min_DT_B


Теперь рассказывайте, что дальше с этими данными вы хотите сделать? Какие накладные и куда прикрутить к полученным интервалам? Просто с ваших сбивчивых объяснений, разбавленных картинками - совсем не понятна суть затеянных манипуляций...
1 ноя 18, 07:41    [21721021]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Щукина Анна,

Оконные функции не всегда благо. Как с точки зрения производительности, так и с точки зрения наглядности.
+ Небольшой эксперимент
declare @t table (ID_WT int, ID_U int, DT_B datetime, DT_E datetime);
insert into @t
values
(345318621, 0, '2018-09-27T05:12:37.000', '2018-09-27T05:12:47.000'),
(345319161, 0, '2018-09-27T05:11:18.000', '2018-09-27T05:13:30.000'),
(345318991, 0, '2018-09-27T06:09:11.000', '2018-09-27T06:11:36.000'),
(345316881, 0, '2018-09-27T06:08:13.000', '2018-09-27T06:10:27.000'),
(345319031, 0, '2018-09-27T06:07:32.000', '2018-09-27T06:10:19.000'),
(345319031, 0, '2018-09-27T06:07:32.000', '2018-09-27T06:10:19.000');

create table dbo.t (ID_WT int, ID_U int, DT_B datetime, DT_E datetime);

insert into dbo.t
select top (1000000)
 t.ID_WT, t.ID_U, dateadd(day, c.rn - 1, t.DT_B), dateadd(day, c.rn - 1, t.DT_E)
from
 (
  select top (200000)
   row_number() over (order by (select 1))
  from
   master.dbo.spt_values a cross join
   master.dbo.spt_values b
 ) c(rn) cross join
 @t t

create clustered index IX_t on dbo.t (ID_U, DT_B, DT_E);
go

declare @ID_U int, @DT_B datetime, @DT_E datetime;

set statistics xml, time on;

with a as
(
 select
  x.ID_WT, x.ID_U, x.DT_B, row_number() over (partition by x.ID_U order by x.DT_B) as rn
 from
  (
   select distinct
    t.ID_WT, t.ID_U, t.DT_B
   from
    dbo.t t
   where
    not exists(select 1 from dbo.t where ID_U = t.ID_U and DT_B < t.DT_B and t.DT_B between DT_B and DT_E)
  ) x
),
b as
(
 select
  x.ID_WT, x.ID_U, x.DT_E, row_number() over (partition by x.ID_U order by x.DT_E) as rn
 from
  (
   select distinct
    t.ID_WT, t.ID_U, t.DT_E
   from
    dbo.t t
   where
    not exists(select 1 from dbo.t where ID_U = t.ID_U and DT_E > t.DT_E and t.DT_E between DT_B and DT_E)
  ) x
)
select
 @ID_U = a.ID_U, @DT_B = a.DT_B, @DT_E = b.DT_E
from
 a join
 b on b.ID_U = a.ID_U and b.rn = a.rn
option
 (maxdop 1);

with
-- Поиск начал непрерывных интервалов:
wh_with_flag as
    (
      select * 
             -- Ключевой момент запроса:
           , case 
               when DT_B <= max(DT_E) over(partition by ID_U
                                               order by DT_B, DT_E 
                                                rows between unbounded preceding
                                                         and 1 preceding) 
                 then 0 
               else 1 
             end as sog 
        from dbo.t
    )
--
-- Идентификация строк, образующих непрерывные интервалы:
, wh_with_group as
    (
      select *
           -- Ключевой момент запроса:
           , sum(sog) over(partition by ID_U order by DT_B, DT_E) as grp_id 
        from wh_with_flag
    )
--
-- "Слияние" строк в интервалы, получение конечных точек непрерывных интервалов:
, wh_ as
    (
      select ID_U, grp_id, min(DT_B) as min_DT_B, max(DT_E) as max_DT_E
        from wh_with_group
       group by ID_U, grp_id
)
--
-- Визуализация результата манипуляций:
select
 @ID_U = ID_U, @DT_B = min_DT_B, @DT_E = max_DT_E
  from wh_
option
 (maxdop 1);

set statistics xml, time off;
go

drop table dbo.t;
go
1 ноя 18, 10:14    [21721140]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
invm
Оконные функции не всегда благо. Как с точки зрения производительности, так и с точки зрения наглядности.
ни наглядность кода, ни его эффективность - не имеют смысла, если код возвращает "неправильные" данные. Для начала хотелось бы понять суть хотелки автора и претензии к результатам предложенных решений. А код был предложен из соображений "для общего развития" и "расширения кругозора"...
В любом случае, без тестирования на данных, приближенных к реальным, тащить в продакт какой-бы-то-ни-было код из интернета (даже трижды наглядный и сто раз эффективный) - достаточно рисковая затея... ;)
1 ноя 18, 10:33    [21721176]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
AR159
Member

Откуда: Spring
Сообщений: 18
Щукина Анна,
картинки вы имеете ввиду которые я нарисовал в 21718309 ? (остальные были таргетированы авторам запросов, убирал под спойлер, чтобы никого не смущали лишний раз...)
Хочу задать соответствие накладн(ой|ых) интервалу.

Я говорил что очень хочется сохранить id_wt.
+
Для чего? В последствии закинуть все это в QlikView, вычислить "соседей" накладной/товара в пределах одного интервала, но это отдельная история.

Познания в SQL не шибко большие в конструировании запросов. Благодаря вашим ответам узнал об интересной штуке over (partition by ...) и rows between unbounded preceding... Пойду учить матчасть.
И вообще литературы толковой по алгоритмам SQL не нашел, есть либо справочники (польза как от телефонного) либо уровень "за 5 минут" что тоже не вселяет оптимизма. Не хочу сказать что должна быть "книга с готовыми решениями именно для тебя" но и между книжками "за 5 минут" и примерами откликнувшихся - пропасть. (Это комплимент на самом деле)

Возвращаясь к топику: ваш пример, Анна, дает интервалы. Попробую еще более детально зонировать, добавлю склады, чтобы набить шишек и не выглядеть пришедшим на всё готовенькое.
1 ноя 18, 11:13    [21721228]     Ответить | Цитировать Сообщить модератору
 Re: И снова Пересекающиеся интервалы  [new]
PizzaPizza
Member

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

Вот даже не знаю что можно было бы сделать архитектурно для упрощения подобных выборок.
Вроде все крайне просто: сущность, начало работы с ней, конец работы с ней, кто работал. Нормализация и все такое. Но диапазоны выбирать неудобно.

Может быть поставить триггер на обновление таблицы и проверять есть ли у сотрудника другие незакрытые накладные и если нет, то помечать обновляемую как конец диапазона или какой то uid или хеш группы задавать пока не все закрыты? Или overthinking?
2 ноя 18, 08:25    [21722100]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить