SQL查找多个重叠间隔中经过的时间 [英] SQL to find time elapsed from multiple overlapping intervals
问题描述
不使用MSSQL或DB2或Oracle. 没有CTE. 没有OVERLAP谓词. 没有INTERVAL数据类型. 情况:要维修的车辆无法启动，直到 已收到订购工作的所有零件. 在开始维修之前，可能需要多次订购零件. 我们需要提取车辆处于零件保持"状态的时间
Not using MSSQL or DB2 or Oracle. No CTE. No OVERLAP predicate. No INTERVAL data type. The situation: on a vehicle to be repaired work can not start until all parts ordered for the job have been received. Parts may be ordered multiple times prior to the start of repair. We need to extract the time for which the vehicle was on "parts hold"
因此对于标识为id = 1的车辆 在4种不同的情况下订购了零件(d1)，并收到了零件(d2)
So for a vehicle identified as id = 1 parts were ordered (d1) and received (d2) on 4 different occasions
ID d1 d2
1 8/1 8/8
1 8/2 8/6
1 8/12 8/14
1 8/3 8/10
8/1 8/8
d1 d2
|-------------------------------|
8/2 8/6 8/12 8/14
d1 d2 d1 d2
|---------------| |----------|
8/3 8/10
d1 d2
|---------------------|
8/1 8/14
|---------------------------------------------------------| = 13 days
8/10 8/12
|--------------------------------------| + |----------| = parts hold = 11 days
从上方看，开始工作的等待时间(假设8/1为 该车辆可用于工作的日期是13天. 等待零件的实际时间是11天，这是数字 我们需要从数据中得出. 实际的日期时间数据将是我们从中提取小时数的时间戳， 为了简化演示，我们在此示例数据中使用了日期. 我们正在努力生成一个基于集合(不是psm，不是udf，不是游标)的解决方案. TIA
As seen from above, the wait time to start work (assuming 8/1 as the date from which the vehicle was available for work) was 13 days. The actual time spent waiting for parts was 11 days, which is the number we need to derive from the data. The actual datetime data will be timestamps from which we will extract hours, we used dates in this sample data for simplicity of presentation. We are struggling to generate a set (not psm, not udf, not cursor) based solution. TIA
推荐答案
此SQL语句似乎可以得到所需的内容(t是样本表的表名):
This SQL statement seems to get what you want (t is the table name of the sampe table):
SELECT
d.id,
d.duration,
d.duration -
IFNULL(
( SELECT Sum( timestampdiff( SQL_TSI_DAY,
no_hold.d2,
( SELECT min(d1) FROM t t4
WHERE t4.id = no_hold.id and t4.d1 > no_hold.d2 )))
FROM ( SELECT DISTINCT id, d2 FROM t t1
WHERE ( SELECT sum( IIF( t1.d2 between t2.d1 and t2.d2, 1, 0 ) )
FROM t t2 WHERE t2.id = t1.id and t2.d2 <> t1.d2 ) = 0
And d2 <> ( select max( d2 ) from t t3 where t3.id = t1.id )) no_hold
WHERE no_hold.id = d.id ),
0 ) "parts hold"
FROM
( SELECT id, timestampdiff( SQL_TSI_DAY, min( d1 ), max( d2 ) ) duration
FROM t GROUP BY id ) d
外部查询获取修复工作的持续时间.复杂子查询计算不等待零件的总天数.这可以通过以下步骤确定:不等待零件的开始日期，然后计算直到开始再次等待零件的天数:
The outer query gets the duration of the repair work. The complex subquery calculates the total number of days not waiting for parts. This is done by locating the start dates where the vehicle is not waiting for parts, and then count the number of days until it begins to wait for parts again:
// 1) The query for finding the starting dates when the vehicle is not waiting for parts,
// i.e. finding all d2 that is not within any date range where the vehicle is waiting for part.
// The DISTINCT is needed to removed duplicate starting "no hold" period.
SELECT DISTINCT id, d2
FROM t t1
WHERE ( SELECT sum( IIF( t1.d2 between t2.d1 and t2.d2, 1, 0 ) ) from t t2
WHERE t2.id = t1.id and t2.d2 <> t1.d2 ) = 0 AND
d2 <> ( SELECT max( d2 ) FROM t t3 WHERE t3.id = t1.id ) )
//2)车辆不等待零件的日期是上述查询直到车辆再次等待零件的日期
// 2) The days where it vehicle is not waiting for part is the date from the above query till the vehicle is // waiting for part again
timestampdiff( SQL_TSI_DAY, no_hold.d2, ( SELECT min(d1) FROM t t4 WHERE t4.id = no_hold.id and t4.d1 > no_hold.d2 ) )
将以上两个时间段相结合并汇总所有这些时间段，就可以得出车辆不等待零件的天数.最终查询会添加一个额外条件，以计算外部查询中每个ID的结果.
Combining the two above and aggregating all such periods gives the number of days that the vehicle is not waiting for parts. The final query adds an extra condition to calculate result for each id from the outer query.
在具有许多id的超大表上，这可能效率不高.如果id限制为一个或几个，应该没问题.
This probably is not terribly efficient on very large table with many ids. It should fine if the id is limited to one or just a few.
这篇关于SQL查找多个重叠间隔中经过的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
