Вычислить разницу лагов для разных дней

Oleg Melnikov спросил: 14 ноября 2017 в 05:44 в: sql-server

Мне нужна помощь в вычислении разницы дат в разных строках с переменной задержкой (в частности, строки, которые не в один и тот же день) без подзапросов, объединений и т. д. Я думаю, что это должно быть возможно с некоторыми встроенными агрегатами t-SQL, которые используют OVER(PARTITION BY), например, LAG, DENSE_RANK и т. д., но я не могу понять, как это сделать. Это для редакции SQL Server 2017 Developer.

Уточняющий пример:

Рассмотрим набор данных с датами начала и окончания работы (для разных проектов). Некоторые задания начинаются и заканчиваются в один и тот же день (например, задания 2 и 3, 4 и 5). Мне нужно вычислить время простоя между последовательными заданиями, которые начинались в разные дни (для проекта). Это дни между временем окончания последней работы и временем начала текущей работы. Если предыдущая работа началась в тот же день, то загляните в историю того же проекта. То есть задания, начатые в один и тот же день, можно рассматривать как части одного и того же задания.

ОБНОВЛЕНИЕ: я упростил код / ​​вывод, отбросив значения времени (в истории вопроса есть исходный набор данных).

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t; 
CREATE TABLE #t(Prj TINYINT, Beg DATE, Eñd DATE);
INSERT INTO #t SELECT 1, '1/1/17', '1/2/17';
INSERT INTO #t SELECT 1, '1/5/17', '1/7/17';
INSERT INTO #t SELECT 1, '1/5/17', '1/7/17';
INSERT INTO #t SELECT 1, '1/15/17', '1/15/17';
INSERT INTO #t SELECT 1, '1/15/17', '1/18/17';
INSERT INTO #t SELECT 1, '1/20/17', '1/24/17';
INSERT INTO #t SELECT 2, '2/2/17', '2/5/17';
INSERT INTO #t SELECT 2, '2/7/17', '2/9/17';
ALTER TABLE #t ADD Job INT NOT NULL IDENTITY (1,1) PRIMARY KEY;

Функция LAG(.,1) точно использует время окончания предыдущего задания, а это не то, чего я хочу. Это дает неправильную продолжительность простоя для заданий 2 & 3, 4 & 5. Вакансии 2 & 3 должны оба использовать время окончания задания 1. Задания 4 и 5 должны оба использовать время окончания задания 3. В соединенном запросе правильно вычисляется длительность простоя, но здесь желателен встроенный расчет (без объединений, подзапросов).

SELECT c.Job, c.Prj, c.Beg, c.Eñd, 
-- in-line computation with OVER clause
PrvEñd_lg=LAG(c.Eñd,1) OVER(PARTITION BY c.Prj ORDER BY c.Beg),
Idle_lg=DATEDIFF(DAY, LAG(c.Eñd,1) OVER(PARTITION BY c.Prj ORDER BY c.Beg), c.Beg),
-- calculation over current and (joined) previous records
PrvEñd_j=MAX(p.Eñd), 
IdleDur_j=DATEDIFF(DAY, MAX(p.Eñd), c.Beg)
FROM #t c LEFT JOIN #t p ON c.Prj=p.Prj AND c.Beg > p.Eñd
GROUP BY c.Job, c.Prj, c.Beg, c.Eñd
ORDER BY c.Prj, c.BegJob Prj Beg         Eñd         PrvEñd_lg   Idle_lg PrvEñd_j    IdleDur_j
1   1   2017-01-01  2017-01-02  NULL        NULL    NULL        NULL
2   1   2017-01-05  2017-01-07  2017-01-02  3       2017-01-02  3
3   1   2017-01-05  2017-01-07  2017-01-07  -2      2017-01-02  3
4   1   2017-01-15  2017-01-15  2017-01-07  8       2017-01-07  8
5   1   2017-01-15  2017-01-18  2017-01-15  0       2017-01-07  8
6   1   2017-01-20  2017-01-24  2017-01-18  2       2017-01-18  2
7   2   2017-02-02  2017-02-05  NULL        NULL    NULL        NULL
8   2   2017-02-07  2017-02-09  2017-02-05  2       2017-02-05  2

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

Большое спасибо!

0 ответов