计算不同天的滞后时间差 [英] Compute lag difference for different days
问题描述
我需要帮助来计算具有可变滞后时间的不同行(特别是不在同一天的行)之间的日期差，而无需子查询，联接等.我认为这可以通过使用某些内联t-SQL聚合来实现OVER(PARTITION BY)子句，例如LAG，DENSE_RANK等，但是我不太愿意在上面讲.这是针对SQL Server 2017开发人员版的.
I need help computing a date difference across different rows with variable lag (specifically, rows that are not on the same day) without subqueries, joins, etc. I think this should be possible with some inline t-SQL aggregates that use OVER(PARTITION BY) clause, such as LAG, DENSE_RANK, etc., but I can't quite put a finger on it. This is for a SQL Server 2017 Developer's edition.
一个澄清的例子:
考虑一个带有Job开始和结束日期的数据集(适用于各个项目).一些作业在同一天开始和结束(例如作业2和3、4和5).我需要计算在不同日期(每个项目)开始的后续工作之间的空闲时间.那是最后一个作业的结束时间与当前作业的开始时间之间的天数.如果以前的工作是在同一天开始的，那么请回头看一下同一项目的历史. IE.在同一天开始的工作可以视为同一工作的一部分.
Consider a dataset with Job beginning and end dates (across various projects). Some jobs start and end on the same day (such as jobs 2 & 3, 4 & 5). I need to compute the idle time between consequent jobs that started on different days (per project). That is the days between last job's ending time and current job's beginning time. If the previous job started on the same day, then look further back in history of the same project. I.e. the jobs that started on the same day can be considered as parts of the same job.
更新:我通过删除时间值简化了代码/输出(问题的历史记录具有原始数据集).
UPDATE: I simplified the code/output by dropping time values (question's history has original dataset).
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和作业2产生不正确的闲置持续时间. 3、4和5.职位2& 3个都应使用作业1的结束时间. 5都应使用作业3的结束时间.联接的查询可以正确计算空闲持续时间，但是此处需要进行内联计算(不包括联接，子查询).
A LAG(.,1) function uses precisely the previous job's ending time, which is not what I want. It yields incorrect idle duration for jobs 2 & 3, 4 & 5. Jobs 2 & 3 should both use the ending time of job 1. Jobs 4 & 5 should both use the ending time of job 3. The joined query computes idle duration correctly, but an inline calculation is desirable here (without joins, subqueries).
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.Beg
Job 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
请让我知道，如果我能进一步澄清任何具体细节.
Please let me know, if I can further clarify any specific details.
非常感谢！
推荐答案
You can use a self-join.
select a.Job
, a.Prj
, a.Beg
, a.Eñd
, max(b.Eñd) as PrevEñd
, min(datediff(mi, b.Eñd, a.Beg) / (60*24.0)) as IdleDur
from #t as a
left join #t as b on a.Prj = b.Prj
and cast(a.Beg as date) > cast(b.Eñd as date)
group by a.Job
, a.Prj
, a.Beg
, a.Eñd
这将产生以下输出:
+-----+-----+---------------------+---------------------+---------------------+-----------+
| Job | Prj | Beg | Eñd | PrevEñd | IdleDur |
+-----+-----+---------------------+---------------------+---------------------+-----------+
| 1 | 1 | 2017-01-01 01:00:00 | 2017-01-02 02:00:00 | NULL | NULL |
| 2 | 1 | 2017-01-05 02:00:00 | 2017-01-07 03:00:00 | 2017-01-02 02:00:00 | 3.0000000 |
| 3 | 1 | 2017-01-05 03:00:00 | 2017-01-07 02:00:00 | 2017-01-02 02:00:00 | 3.0416666 |
| 4 | 1 | 2017-01-15 04:00:00 | 2017-01-15 03:00:00 | 2017-01-07 03:00:00 | 8.0416666 |
| 5 | 1 | 2017-01-15 15:00:00 | 2017-01-18 03:00:00 | 2017-01-07 03:00:00 | 8.5000000 |
| 6 | 1 | 2017-01-20 05:00:00 | 2017-01-24 02:00:00 | 2017-01-18 03:00:00 | 2.0833333 |
| 7 | 2 | 2017-02-02 06:00:00 | 2017-02-05 03:00:00 | NULL | NULL |
| 8 | 2 | 2017-02-07 07:00:00 | 2017-02-09 02:00:00 | 2017-02-05 03:00:00 | 2.1666666 |
+-----+-----+---------------------+---------------------+---------------------+-----------+
这篇关于计算不同天的滞后时间差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
