MySQL：按照ID分组日期时间求和，过滤重叠时间
xmh
•
2021年03月05日 pm20:23
•
数据库技术,
MySQL,
•
阅读 1730
原始数据
SELECT d.waybill_no,
@group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id,
@parent_code1:=picked_by AS picked_by1,
MIN(spd.picked_date) start_time,
MAX(spd.picked_date) end_time,
TIMESTAMPDIFF(HOUR,MIN(spd.picked_date),MAX(spd.picked_date)) times,
spd.picked_by
FROM `shipping_order` d
LEFT JOIN shipping_pick_detail spd
ON d.`SHIPPING_ORDER_ID`=spd.SHIPPING_ORDER_ID
GROUP BY spd.picked_by,d.waybill_no
如图：
原始需要求和数据
添加分组行号：
SELECT
start_time,
end_time,
picked_by,
@group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id,
@parent_code:=picked_by AS parent_code
FROM (
SELECT d.waybill_no,
@group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id,
@parent_code1:=picked_by AS picked_by1,
MIN(spd.picked_date) start_time,
MAX(spd.picked_date) end_time,
TIMESTAMPDIFF(HOUR,MIN(spd.picked_date),MAX(spd.picked_date)) times,
spd.picked_by
FROM `shipping_order` d
LEFT JOIN shipping_pick_detail spd
ON d.`SHIPPING_ORDER_ID`=spd.SHIPPING_ORDER_ID
GROUP BY spd.picked_by,d.waybill_no) iop
LEFT JOIN ( SELECT @group_row:=1, @parent_code:='',@parent_code1:='' ) b
ON 1=1
ORDER BY picked_by
添加分组行号结果
分组汇总效果
SELECT
picked_by,
SUM(t.duration) - SUM(t.overlap) AS filtered_duration
FROM
(
SELECT
t1.picked_by,
t1.start_time,
t1.end_time,
TIMESTAMPDIFF(HOUR,t1.start_time,t1.end_time) AS duration,
SUM(
IF(t2.start_time < t1.start_time AND t2.end_time > t1.end_time , TIMESTAMPDIFF(HOUR,t1.start_time,t1.end_time), 0) -- t2 completely around t1
+ IF(t2.start_time >= t1.start_time AND t2.end_time <= t1.end_time , TIMESTAMPDIFF(HOUR,t2.start_time,t2.end_time), 0) -- t2 completely within t1
+ IF(t2.start_time < t1.start_time AND t2.end_time > t1.start_time AND t2.end_time < t1.end_time , TIMESTAMPDIFF(HOUR,t1.start_time,t2.end_time), 0) -- t2 starts before t1 starts and overlaps partially
+ IF(t2.start_time < t1.end_time AND t2.end_time > t1.end_time AND t2.start_time > t1.start_time, TIMESTAMPDIFF(HOUR,t2.start_time,t1.end_time), 0) -- t2 starts before t1 ends and overlaps partially
) AS overlap
FROM
( SELECT
start_time,
end_time,
picked_by,
@group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id,
@parent_code:=picked_by AS parent_code
FROM (
SELECT d.waybill_no,
@group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id,
@parent_code1:=picked_by AS picked_by1,
MIN(spd.picked_date) start_time,
MAX(spd.picked_date) end_time,
TIMESTAMPDIFF(HOUR,MIN(spd.picked_date),MAX(spd.picked_date)) times,
spd.picked_by
FROM `shipping_order` d
LEFT JOIN shipping_pick_detail spd
ON d.`SHIPPING_ORDER_ID`=spd.SHIPPING_ORDER_ID
GROUP BY spd.picked_by,d.waybill_no) iop
LEFT JOIN ( SELECT @group_row:=1, @parent_code:='',@parent_code1:='' ) b
ON 1=1
ORDER BY picked_by ) t1
LEFT JOIN ( SELECT
start_time,
end_time,
picked_by,
@group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id,
@parent_code:=picked_by AS parent_code
FROM (
SELECT d.waybill_no,
@group_row:=CASE WHEN @parent_code=picked_by THEN @group_row+1 ELSE 1 END AS id,
@parent_code1:=picked_by AS picked_by1,
MIN(spd.picked_date) start_time,
MAX(spd.picked_date) end_time,
TIMESTAMPDIFF(HOUR,MIN(spd.picked_date),MAX(spd.picked_date)) times,
spd.picked_by
FROM `shipping_order` d
LEFT JOIN shipping_pick_detail spd
ON d.`SHIPPING_ORDER_ID`=spd.SHIPPING_ORDER_ID
GROUP BY spd.picked_by,d.waybill_no) iop
LEFT JOIN ( SELECT @group_row:=1, @parent_code:='',@parent_code1:='' ) b
ON 1=1
ORDER BY picked_by ) t2
ON t1.picked_by=t2.picked_by
AND t2.id > t1.id
AND (
(t2.start_time < t1.start_time AND t2.end_time > t1.end_time )
OR (t2.start_time >= t1.start_time AND t2.end_time <= t1.end_time )
OR (t2.start_time < t1.start_time AND t2.end_time > t1.start_time)
OR (t2.start_time < t1.end_time AND t2.end_time > t1.end_time )
)
GROUP BY
t1.start_time,
t1.end_time,t1.picked_by
) AS t
GROUP BY picked_by
已经过滤重复时间段
