常考SQL场景之小鹏汽车充电每辆车连续快充次数

本文主要是介绍常考SQL场景之小鹏汽车充电每辆车连续快充次数，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

今天手撕小鹏汽车每辆车连续快充次数题

场景

小鹏汽车充电有两种类型，快充、慢充，有如下数据：

车辆ID   充电时间           充电类型 
a        20200601 19:21:09  1
a        20200611 11:30:09  1
a        20200621 21:10:09  0
a        20200701 19:01:09  1
a        20200701 20:30:09  1
a        20200701 21:00:09  0
a        20200702 20:30:00  1
a        20200703 09:01:09  1
a        20200704 12:05:09  1
b        20200706 12:20:09  0

其中1为快充，0为慢充，求每辆车最长连续快充次数，以上例子结果为

a 3
b 0

建表

为了方便，我直接在SQL-Server上建表了
如下

CREATE TABLE dbo.card_charger_details (
card_id varchar(2) NOT NULL ,   -- 车辆ID
charge_time datetime2(7) NULL , -- 充电时间
charger_type_id tinyint NULL    -- 充电类型
)

分析

根据车辆排序，和根据车辆，充电类型排序
这两个排序相减得出一个间隔
统计这个次数，取最大值而且是快充类型的即可

-- 两个排序之间的间隔
select *,(a.rn1-a.rn2) as diff_rn  -- 间隔差 from (select card_id,charge_time ,charger_type_id ,row_number() over (partition by card_id order by charge_time asc ) as rn1 ,row_number() over (partition by card_id,charger_type_id order by charge_time asc) as rn2 from card_charger_details t        )a order by card_id, rn1, rn2

在这里插入图片描述

实现


select a.card_id,isnull(max(b.continuous_cnt),0) as  max_continuous_cnt from (select card_id from card_charger_details group by card_id) aleft join ( select card_id ,charger_type_id,diff_rn,count(1) continuous_cnt from (select *,(a.rn1-a.rn2) diff_rn from (select card_id,charge_time, charger_type_id , row_number() over (partition by card_id order by charge_time asc ) as rn1 , row_number() over (partition by card_id,charger_type_id order by charge_time asc) as rn2 from card_charger_details t    ) as a )b  group by card_id,charger_type_id,diff_rn) bon a.card_id = b.card_idand charger_type_id = 1  -- 限定快充类型group by a.card_id

结果
在这里插入图片描述

附上插入数据语句


-- ----------------------------
-- Records of card_charger_details
-- ----------------------------
INSERT INTO [dbo].[card_charger_details] ([card_id], [charge_time], [charger_type_id]) VALUES (N'a', N'2020-06-01 19:21:09.0000000', N'1')
GO
GO
INSERT INTO [dbo].[card_charger_details] ([card_id], [charge_time], [charger_type_id]) VALUES (N'a', N'2020-06-11 11:30:09.0000000', N'1')
GO
GO
INSERT INTO [dbo].[card_charger_details] ([card_id], [charge_time], [charger_type_id]) VALUES (N'a', N'2020-06-21 21:10:09.0000000', N'0')
GO
GO
INSERT INTO [dbo].[card_charger_details] ([card_id], [charge_time], [charger_type_id]) VALUES (N'a', N'2020-07-01 19:01:09.0000000', N'1')
GO
GO
INSERT INTO [dbo].[card_charger_details] ([card_id], [charge_time], [charger_type_id]) VALUES (N'a', N'2020-07-01 20:30:09.0000000', N'1')
GO
GO
INSERT INTO [dbo].[card_charger_details] ([card_id], [charge_time], [charger_type_id]) VALUES (N'a', N'2020-07-01 21:00:09.0000000', N'0')
GO
GO
INSERT INTO [dbo].[card_charger_details] ([card_id], [charge_time], [charger_type_id]) VALUES (N'a', N'2020-07-02 20:30:00.0000000', N'1')
GO
GO
INSERT INTO [dbo].[card_charger_details] ([card_id], [charge_time], [charger_type_id]) VALUES (N'a', N'2020-07-03 09:01:09.0000000', N'1')
GO
GO
INSERT INTO [dbo].[card_charger_details] ([card_id], [charge_time], [charger_type_id]) VALUES (N'a', N'2020-07-04 12:05:09.0000000', N'1')
GO
GO
INSERT INTO [dbo].[card_charger_details] ([card_id], [charge_time], [charger_type_id]) VALUES (N'b', N'2020-07-06 12:20:09.0000000', N'0')
GO
GO
INSERT INTO [dbo].[card_charger_details] ([card_id], [charge_time], [charger_type_id]) VALUES (N'a', N'2020-07-06 11:10:09.0000000', N'0')
GO
GO