常考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) 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