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

Откуда:
Сообщений: 4
Доброе утро, дамы и господа!

Сегодня попрошу вашей помощи в решении одной задачи. Излагаю суть проблемы. Есть таблица с данными:

urf_code_actual business_dt saphr_id curr_counter unavail_start_dttm unavail_end_dttm
016_7003_0727 07.07.2017 1 403 237 10 07.07.2017 12:50:36.000000 07.07.2017 13:50:04.000000
016_7003_0727 07.07.2017 997 147 13 07.07.2017 11:02:09.000000 07.07.2017 11:17:35.000000
016_7003_0727 07.07.2017 1 342 161 11 07.07.2017 13:44:38.000000 07.07.2017 15:43:57.000000
016_7003_0727 07.07.2017 675 071 9 07.07.2017 15:56:45.000000 07.07.2017 16:13:35.000000
016_7003_0727 07.07.2017 997 147 13 07.07.2017 11:29:29.000000 07.07.2017 12:04:42.000000
016_7003_0727 07.07.2017 675 071 9 07.07.2017 13:38:21.000000 07.07.2017 13:43:26.000000
016_7003_0727 07.07.2017 997 147 13 07.07.2017 14:00:20.000000 07.07.2017 15:05:19.000000
016_7003_0727 07.07.2017 675 071 9 07.07.2017 16:39:03.000000 07.07.2017 16:44:52.000000
016_7003_0727 07.07.2017 997 147 13 07.07.2017 12:33:26.000000 07.07.2017 12:38:58.000000
016_7003_0727 07.07.2017 675 071 9 07.07.2017 12:51:14.000000 07.07.2017 12:55:25.000000
016_7003_0727 07.07.2017 675 071 9 07.07.2017 14:37:48.000000 07.07.2017 14:40:50.000000
016_7003_0727 07.07.2017 1 342 161 11 07.07.2017 12:05:12.000000 07.07.2017 12:32:46.000000
016_7003_0727 07.07.2017 1 342 161 11 07.07.2017 13:16:52.000000 07.07.2017 13:29:33.000000
016_7003_0727 07.07.2017 675 071 9 07.07.2017 15:28:57.000000 07.07.2017 15:53:21.000000
016_7003_0727 07.07.2017 1 342 161 11 07.07.2017 10:39:15.000000 07.07.2017 11:06:33.000000
016_7003_0727 07.07.2017 1 342 161 11 07.07.2017 11:25:16.000000 07.07.2017 11:59:06.000000



Задача такова, что нужно посчитать количество недоступных curr_counter в течении всего дня(unavail_start_dttm - начало периода недоступности, unavail_end_dttm -конец периода недоступности). Но есть небольшая проблема, интервалы недоступности пересекаются, т.е:
urf_code_actual business_dt saphr_id curr_counter unavail_start_dttm unavail_end_dttm
016_7003_0727 07.07.2017 997 147 13 07.07.2017 11:29:29.000000 07.07.2017 12:04:42.000000
016_7003_0727 07.07.2017 1 342 161 11 07.07.2017 11:25:16.000000 07.07.2017 11:59:06.000000


Результатом запроса надо вывести такую таблицу(где в последней строке указываются номера curr_counter, которые были недоступны в данный интервал времени):
urf_code_actual business_dt unavail_start_dttm unavail_end_dttm unavail_curr_counter
016_7003_0727 07.07.2017 07.07.2017 11:25:16.000000 07.07.2017 11:29:29.000000 11
016_7003_0727 07.07.2017 07.07.2017 11:29:29.000000 07.07.2017 11:59:06.000000 11; 13
016_7003_0727 07.07.2017 07.07.2017 11:59:06.000000 07.07.2017 12:04:42.000000 13


Сразу скажу, что пересекающихся интервалов может быть больше чем 2, поэтому варианты с max и min не рассматриваю.
Придумал свой алгоритм, но не знаю как его реализовать и он не указывает номер curr_counter, которые были недоступны.
Мой алгоритм заключается в записывание всех времен начала и конца недоступности в один столбец, построчно, а в другой столбец обозначение этого времени как старт или конец, чтобы получилось, что-то типа(фильтруем от старой дате к новой в time):
time describe
07.07.2017 11:25:16.000000 start
07.07.2017 11:29:29.000000 start
07.07.2017 11:59:06.000000 end
07.07.2017 12:04:42.000000 end


Потом разворачиваем time в табличку, где первое значение первого стоблца и первой строки это 1 значение из time, 2 значение из time это 2 столбец 1 строки и значение 2 строки 1 столбца, 3 значение из time это 2 столбец 2 строки и 1 столбец 3 строки, получается такая таблица:
unavail_start unavail_end
07.07.2017 11:25:16.000000 07.07.2017 11:29:29.000000
07.07.2017 11:29:29.000000 07.07.2017 11:59:06.000000
07.07.2017 11:59:06.000000 07.07.2017 12:04:42.000000


После этого я считаю количество недоступных касс путем цикла с условием, что если мы встречаем в describe start -> i = i+1, end -> i = i - 1 и записываю i в мою таблицу с образованными интервалами, т.е. получается:
unavail_start unavail_end count(curr_counter)
07.07.2017 11:25:16.000000 07.07.2017 11:29:29.000000 1
07.07.2017 11:29:29.000000 07.07.2017 11:59:06.000000 2
07.07.2017 11:59:06.000000 07.07.2017 12:04:42.000000 1

Надеюсь на вашу помощь, знатоки. Буду рад любому решению тех вариантов, в которых я попросил помощи!
7 авг 17, 11:02    [20704641]     Ответить | Цитировать Сообщить модератору
 Re: Задача на пересечение интервалов и подсчет количества в них  [new]
Remind
Member

Откуда: UK
Сообщений: 523
mazzletoff,

Так какова задача то все-таки?

автор
Задача такова, что нужно посчитать количество недоступных curr_counter в течении всего дня

или
автор
Результатом запроса надо вывести такую таблицу(где в последней строке указываются номера curr_counter, которые были недоступны в данный интервал времени)
7 авг 17, 12:30    [20704950]     Ответить | Цитировать Сообщить модератору
 Re: Задача на пересечение интервалов и подсчет количества в них  [new]
aleks222
Guest
1. Господи, какой бред.
2. Изменения "занятости" имеет место только при начале/конце "какого-нибудь" существующего интервала.
3. Поэтому тупо считаем "занятые" для всех начал и концов.
4. Не менее тупо сортируем их по возрастанию.
5. И... бинго!
7 авг 17, 12:43    [20705029]     Ответить | Цитировать Сообщить модератору
 Re: Задача на пересечение интервалов и подсчет количества в них  [new]
mazzletoff
Member

Откуда:
Сообщений: 4
Remind,
Вывести таблицу недоступности curr_counter в течение всего дня по интервалам. Как и показано в таблице.
7 авг 17, 13:14    [20705168]     Ответить | Цитировать Сообщить модератору
 Re: Задача на пересечение интервалов и подсчет количества в них  [new]
mazzletoff
Member

Откуда:
Сообщений: 4
aleks222,
Прошу прощения, но все же, как вы вычислите эти какие-нибудь интервалы??
7 авг 17, 13:15    [20705173]     Ответить | Цитировать Сообщить модератору
 Re: Задача на пересечение интервалов и подсчет количества в них  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
mazzletoff, "При написании сообщений с просьбой о составлении запроса - хорошим тоном считается предоставление скрипта на создание таблицы и заполнение ее тестовыми данными..."
set nocount on;
use tempdb;
go
declare @t table(
  urf_code_actual char(13) not null,
  business_dt date not null,
  saphr_id varchar(9) not null,
  curr_counter int not null check(curr_counter>0),
  unavail_start_dttm datetime not null,
  unavail_end_dttm datetime not null,
  check(unavail_start_dttm<unavail_end_dttm)
);
insert @t(urf_code_actual,business_dt,saphr_id,curr_counter,unavail_start_dttm,unavail_end_dttm)
values('016_7003_0727','07.07.2017','1 403 237',10,'07.07.2017 12:50:36','07.07.2017 13:50:04'),
      ('016_7003_0727','07.07.2017','997 147',13,'07.07.2017 11:02:09','07.07.2017 11:17:35'),
      ('016_7003_0727','07.07.2017','1 342 161',11,'07.07.2017 13:44:38','07.07.2017 15:43:57'),
      ('016_7003_0727','07.07.2017','675 071',9,'07.07.2017 15:56:45','07.07.2017 16:13:35'),
      ('016_7003_0727','07.07.2017','997 147',13,'07.07.2017 11:29:29','07.07.2017 12:04:42'),
      ('016_7003_0727','07.07.2017','675 071',9,'07.07.2017 13:38:21','07.07.2017 13:43:26'),
      ('016_7003_0727','07.07.2017','997 147',13,'07.07.2017 14:00:20','07.07.2017 15:05:19'),
      ('016_7003_0727','07.07.2017','675 071',9,'07.07.2017 16:39:03','07.07.2017 16:44:52'),
      ('016_7003_0727','07.07.2017','997 147',13,'07.07.2017 12:33:26','07.07.2017 12:38:58'),
      ('016_7003_0727','07.07.2017','675 071',9,'07.07.2017 12:51:14','07.07.2017 12:55:25'),
      ('016_7003_0727','07.07.2017','675 071',9,'07.07.2017 14:37:48','07.07.2017 14:40:50'),
      ('016_7003_0727','07.07.2017','1 342 161',11,'07.07.2017 12:05:12','07.07.2017 12:32:46'),
      ('016_7003_0727','07.07.2017','1 342 161',11,'07.07.2017 13:16:52','07.07.2017 13:29:33'),
      ('016_7003_0727','07.07.2017','675 071',9,'07.07.2017 15:28:57','07.07.2017 15:53:21'),
      ('016_7003_0727','07.07.2017','1 342 161',11,'07.07.2017 10:39:15','07.07.2017 11:06:33'),
      ('016_7003_0727','07.07.2017','1 342 161',11,'07.07.2017 11:25:16','07.07.2017 11:59:06');
with
  a as(select urf_code_actual,business_dt,unavail_start_dttm unavail_dttm,1 f from @t
       union all
       select urf_code_actual,business_dt,unavail_end_dttm,-1 from @t),
  b as(select urf_code_actual,business_dt,unavail_dttm unavail_start_dttm,
         lead(unavail_dttm)over(
           partition by urf_code_actual,business_dt order by unavail_dttm,f) unavail_end_dttm,
         sum(f)over(partition by urf_code_actual,business_dt order by unavail_dttm,f) cc
       from a),
  c as(select b.urf_code_actual,b.business_dt,b.unavail_start_dttm,b.unavail_end_dttm,t.curr_counter
       from b
       join @t t
         on (b.unavail_start_dttm>=t.unavail_start_dttm and b.unavail_start_dttm<t.unavail_end_dttm) or
            (t.unavail_start_dttm>=b.unavail_start_dttm and t.unavail_start_dttm<b.unavail_end_dttm))
select distinct c.urf_code_actual,c.business_dt,c.unavail_start_dttm,c.unavail_end_dttm
  ,(select cast(i.curr_counter as varchar)+';' as [text()]
    from c i
    where i.urf_code_actual=c.urf_code_actual and
          i.business_dt=c.business_dt and
          i.unavail_start_dttm=c.unavail_start_dttm
    order by i.curr_counter
    for xml path('')) unavail_curr_counter
from c
order by unavail_start_dttm;
Попробовать.
8 авг 17, 14:37    [20708549]     Ответить | Цитировать Сообщить модератору
 Re: Задача на пересечение интервалов и подсчет количества в них  [new]
mazzletoff
Member

Откуда:
Сообщений: 4
andrey odegov,
В следующий раз буду предоставлять скрипт на создание таблицы.
Спасибо за решение. Пришел с работы, хотел написать, что уже решил и как, но вы меня опередили. Решения оказались идентичны.
8 авг 17, 19:54    [20709373]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить