SQL练习：求断点前的连续天数

背景：有客户购买车险，去另一家公司购买后又回到本公司，求转到其他公司购买保险前在本公司购买车险的连续年份。

--创建一个person对象
create table person(
       pname varchar2(10),
       pid number(20)
);

--添加数据记录
insert into person (pid,pname) values (2020,'a');
insert into person (pid,pname) values (2020,'a');
insert into person (pid,pname) values (2019,'a');
insert into person (pid,pname) values (2017,'a');
insert into person (pid,pname) values (2016,'a');
insert into person (pid,pname) values (2015,'a');
insert into person (pid,pname) values (2020,'n');
insert into person (pid,pname) values (2019,'n');
commit;

--查询表中记录
select * from person;

PNAME	PID
a	2020
a	2020
a	2019
a	2017
a	2016
a	2015
n	2020
n	2019

方法一

SELECT pname, MAX(rn1) - MIN(CASE 
		WHEN pid - RN > 1 THEN RN1
		WHEN RN IS NULL THEN RN1
	END) AS cc
FROM (
	SELECT pname, pid, lead(pid) OVER (PARTITION BY pname ORDER BY pid DESC) AS rn, dense_rank() OVER (PARTITION BY pname ORDER BY pid DESC) AS rn1
	FROM person
) d
GROUP BY pname;

结果

pname	cc
a	3
n	0

方法二

SELECT pname
	, COUNT(pname) - SUM(CASE 
		WHEN aa - bb = cc THEN 1
		ELSE 0
	END) - 1 AS num
FROM (
	SELECT pname, pid, MAX(pid) OVER (PARTITION BY pname ORDER BY pid DESC) AS aa, row_number() OVER (PARTITION BY pname ORDER BY pid DESC) AS bb
		, lead(pid) OVER (PARTITION BY pname ORDER BY pid DESC) AS cc
	FROM (
		SELECT pname, pid
		FROM person
		GROUP BY pname, pid
	) d
) dd
GROUP BY pname