043-经典SQL|常考SQL场景之小鹏汽车充电每辆车连续快充次数数据库|sql

今天手撕小鹏汽车每辆车连续快充次数题
场景 【043-经典SQL|常考SQL场景之小鹏汽车充电每辆车连续快充次数】小鹏汽车充电有两种类型，快充、慢充，有如下数据：

车辆ID充电时间充电类型 a20200601 19:21:091 a20200611 11:30:091 a20200621 21:10:090 a20200701 19:01:091 a20200701 20:30:091 a20200701 21:00:090 a20200702 20:30:001 a20200703 09:01:091 a20200704 12:05:091 b20200706 12:20:090

其中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) asmax_continuous_cnt from (select card_id from card_charger_details group by card_id) a left 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) b on a.card_id = b.card_id and 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