小鹏汽车SQL面试题: 汽车充电每辆车连续快充最大次数

小鹏汽车充电每辆车连续快充最大次数

需求: 小鹏汽车充电每辆车连续快充最大次数
输入
id charge_time charge_type
XP1001 2023/11/20 08:45:00 快充
XP1001 2023/11/21 20:45:00 快充
XP1001 2023/11/22 08:45:00 快充
XP1001 2023/11/23 08:45:00 慢充
XP1001 2023/11/25 08:45:00 快充
XP1002 2023/11/25 08:45:00 快充
XP1002 2023/11/25 12:45:00 快充
XP1002 2023/11/25 23:45:00 慢充
XP1003 2023/11/25 23:45:00 慢充
XP1003 2023/11/26 23:45:00 慢充
输出
id cnts
XP1001 3
XP1002 2
XP1003 0

1.数据准备

CREATE TABLE charging_data (
    id VARCHAR(50),
    charge_time DATETIME,
    charge_type VARCHAR(10)
);

INSERT INTO charging_data (id, charge_time, charge_type)
VALUES
    ('XP1001', '2023-11-20 08:45:00', '快充'),
    ('XP1001', '2023-11-21 20:45:00', '快充'),
    ('XP1001', '2023-11-22 08:45:00', '快充'),
    ('XP1001', '2023-11-23 08:45:00', '慢充'),
    ('XP1001', '2023-11-25 08:45:00', '快充'),
    ('XP1002', '2023-11-25 08:45:00', '快充'),
    ('XP1002', '2023-11-25 12:45:00', '快充'),
    ('XP1002', '2023-11-25 23:45:00', '慢充'),
    ('XP1003', '2023-11-25 23:45:00', '慢充'),
    ('XP1003', '2023-11-26 23:45:00', '慢充');


select * from charging_data;

2.解题思路

• 分析大维度和小维度是什么，这里大维度是每个汽车的ID，小维度是快充类型
• 开窗函数构造排名
• 大维度排名减去小维度排名，生成组ID，观察小维度的变化时刻
• 按照需求，聚合计算
• 筛选出复合条件的值

3.解题代码

T1：开窗函数构造排名，大维度是每个汽车的ID，小维度是快充类型，注意观察这里的开窗分组字段

   select
        id,
       charge_time,
       charge_type,
       row_number() over (partition by id order by charge_time) as rm1,
       row_number() over (partition by id,charge_type order by charge_time) as rm2
from charging_data

结果：可以看到在大维度下XP1001，第四次充电是慢充，打断了连续快充
T2：相减可以观察得更清楚，且会生成连续组号（这里cast是类型转换，因为某些MySQL版本开窗出来的类型值不方便相减，hive或者其他数据库可以适当变化）

select
    id,
    charge_time,
    charge_type,
    cast(rm1 as signed ) -cast(rm2 as signed ) as diff
from t1

结果：

T3：按照需求聚合

 select id,sum(if(charge_type = '快充',1,0)) as cnt from t2 group by id,group_id

T4：筛选答案

select id,max(cnt) as max_fast_charge  from  t3 group by id

答案