mysql 分段id,MySQL：按照ID分组日期时间求和，过滤重叠时间

MySQL：按照ID分组日期时间求和，过滤重叠时间

xmh

•

2021年03月05日 pm20:23

•

数据库技术,

MySQL,

•

阅读 48

原始数据

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

已经过滤重复时间段