常见大厂面试题（SQL）02

最新推荐文章于 2024-08-16 16:19:12 发布

疲惫的花卷

最新推荐文章于 2024-08-16 16:19:12 发布

阅读量373

点赞数 2

文章标签：面试职场和发展

本文链接：https://blog.csdn.net/2202_75300139/article/details/136466370

版权

该篇文章介绍了如何通过SQL查询小鹏汽车充电记录，计算每辆车的连续快充最大次数，使用了窗口函数和条件计数来实现数据处理。

摘要由CSDN通过智能技术生成

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

原表charging_data

id	charge_time	charge_type
XP1001	2023/11/20 8:45	快充
XP1001	2023/11/21 20:45	快充
XP1001	2023/11/22 8:45	快充
XP1001	2023/11/23 8:45	慢充
XP1001	2023/11/25 8:45	快充
XP1002	2023/11/25 8:45	快充
XP1002	2023/11/25 12:45	快充
XP1002	2023/11/25 23:45	慢充
XP1003	2023/11/25 23:45	慢充
XP1003	2023/11/26 23:45	慢充

需要输出的结果

id	cnts
XP1001	3
XP1002	2
XP1003	0

数据导入

-- 小鹏充电
drop database if exists db_1;
create database if not exists db_1;
use db_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', '慢充')
;

# todo 需求: 小鹏汽车充电每辆车连续快充最大次数

解析

代码实现

with t1 as (
    select
        *,
        row_number() over (partition by id order by charge_time) as rn1,
        row_number() over (partition by id, charge_type order by charge_time) as rn2,
        (row_number() over (partition by id order by charge_time)) - (row_number() over (partition by id, charge_type order by charge_time)) as diff
    from charging_data
)
, t2 as (
    select
        id,
        diff,
        count(if(charge_type='快充', 1, null)) as cnts
    from t1
    group by id, diff
)
select
    id,
    max(cnts) as cnts
from t2
group by id
;