SQL进阶技巧：断点分组应用之车辆班次问题分析

会飞的一十六 2024-08-08

点击上方「蓝字」关注我们

本文针对车辆班次问题进行详细了分析，其主要采用的方法还是断点重分组方法，该类问题解决的关键点在于根据问题描述的场景及数据特征找出断点，然后根据断点进行重新分组，注意需要判断断点处是归属于上一个周期还是下一个周期，如果排序按照顺序排序断点则归属到下一个阶段中，如果按照倒序排序则断点归属到上一个阶段中，在进行窗口中排序时候需要根据特征做调整。

需求描述

一班次，可能有多辆车，如果第一辆不出故障，这一班次就只有一辆车，如果出问题有第二辆车，如果出问题有后续多辆，直到把这一班次跑完，如果这一班次只有这一辆，既没before_id也没after_id,如果有多辆，那么第一辆有after id没before id，我需要输出每班次的车，用sql实现这个表有3个id 车辆id,beforeid,afterid。

注意：vehicle_id 是唯一且不重复的

这样的数据表示:1.第一班次有3辆车，车辆id分别为1、2和3，其中1是第一辆车，3是最后一辆车。2.第二班次只有2辆车，车辆id为4,5。3.第三班次只有一辆车，车辆id为6。

数据准备

 with vehicle as(
select 1 as id , null as beforeid , 2    as afterid union all
select 2 as id , 1    as beforeid , 3   as afterid union all
select 3 as id , 2    as beforeid , null as afterid union all
select 4 as id , null as beforeid , 5    as afterid union all
select 5 as id , 4    as beforeid , null as afterid union all
select 6 as id , null as beforeid , null as afterid  
 )

03问题分析

分析题意

本问题的难点是理清关系，分析问题的特征，根据题意可知如果有多辆，那么第一辆有after id没before id，也就是同一个班次如果有多辆车那么每辆车都会有afterid，从第一俩开始，直到最后一辆为NULL代表这一班次结束。也就是该问题的特征就是同一班次内如果有多辆，那么除了最后一辆的afterid不为null其余都有值。因此本文问题就转换为断点重分组问题，afterid为null值即为断点，同时该断点要和上一个记录属于同一个分组内，因此我们采用倒序排序。

第一步：找根据条件寻找断点。（根据条件或特征找断点是解决此类问题的最关键点）

 with vehicle as(
select 1 as id , null as beforeid , 2    as afterid union all
select 2 as id , 1    as beforeid , 3    as afterid union all
select 3 as id , 2    as beforeid , null as afterid union all
select 4 as id , null as beforeid , 5    as afterid union all
select 5 as id , 4    as beforeid , null as afterid union all
select 6 as id , null as beforeid , null as afterid
 )
 select id
      , beforeid
      , afterid
      , case when afterid is null then 1 else  0 end  break_point
 from vehicle;

步骤2：根据断点寻找分组标记

 with vehicle as(
select 1 as id , null as beforeid , 2    as afterid union all
select 2 as id , 1    as beforeid , 3    as afterid union all
select 3 as id , 2    as beforeid , null as afterid union all
select 4 as id , null as beforeid , 5    as afterid union all
select 5 as id , 4    as beforeid , null as afterid union all
select 6 as id , null as beforeid , null as afterid
 )
 select id
      , beforeid
      , afterid
      , sum(case when afterid is null then 1 else  0 end ) over(order by id desc) flag
 from vehicle

步骤3

调整班次顺序

with vehicle as(
select 1 as id , null as beforeid , 2    as afterid union all
select 2 as id , 1    as beforeid , 3    as afterid union all
select 3 as id , 2    as beforeid , null as afterid union all
select 4 as id , null as beforeid , 5    as afterid union all
select 5 as id , 4    as beforeid , null as afterid union all
select 6 as id , null as beforeid , null as afterid
 )
 select id
      , beforeid
      , afterid
      , flag
      , dense_rank() over (order by  flag desc) vehicle_shifts  --按照flag倒序排序进行等位排名
 from (select id
            , beforeid
            , afterid
            , sum(case when afterid is null then 1 else 0 end) over (order by id desc) flag
       from vehicle) t

小结

本文针对车辆班次问题进行详细了分析，其主要采用的方法还是断点重分组方法，该类问题解决的关键点在于根据问题描述的场景及数据特征找出断点，然后根据断点进行重新分组，注意需要判断断点处是归属于上一个周期还是下一个周期，如果排序按照顺序排序断点则归属到下一个阶段中，如果按照倒序排序则断点归属到上一个阶段中，在进行窗口中排序时候需要根据特征做调整。

往期精彩

数仓建模：如何做好业务阶段模型建设？

SQL进阶技巧：如何使用差值累计计算思想巧解数学中递归计算问题

数仓建模：如何设计通用的DWS层数据模型？

会飞的一十六

了解更多公众号内容

扫码二维码关注我们

点一点「在看」支持我们~

文章转载自会飞的一十六，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。