3684.找出连续三行重复的数据
问题1：
假设ORACLE数据库的T_TEST3表中有两个字段，分别是 ID（number类型）、 DIG（varchar2类型） ,请找出表中所有连续出现过三次及以上的整行数据。
问题2：
假设ORACLE数据库的T_TEST3表中有两个字段，分别是 ID（number类型）、 DIG（varchar2类型） ,请找出表中所有连续出现过三次及以上的DIG列的数据。
构建测试数据：
##注意：既然题目强调“整行数据”，则说明表中没有主键，所以测试数据不要搞成有序的，例如ID列不要刚好是从小到大，一定要乱序。
–应得出结果：(1,‘a’) 3次、(3,‘c’) 3次、(5,‘e’) 4次
create table t_test3 (ID number,DIG varchar2(20));
insert into t_test3 values (1,‘a’);
insert into t_test3 values (5,‘e’);
insert into t_test3 values (5,‘e’);
insert into t_test3 values (5,‘e’);
insert into t_test3 values (5,‘e’);
insert into t_test3 values (1,‘a’);
insert into t_test3 values (5,‘e’);
insert into t_test3 values (2,‘b’);
insert into t_test3 values (3,‘c’);
insert into t_test3 values (3,‘c’);
insert into t_test3 values (3,‘c’);
insert into t_test3 values (4,‘d’);
insert into t_test3 values (3,‘c’);
insert into t_test3 values (5,‘e’);
insert into t_test3 values (4,‘d’);
insert into t_test3 values (4,‘d’);
insert into t_test3 values (1,‘a’);
insert into t_test3 values (4,‘d’);
insert into t_test3 values (1,‘a’);
insert into t_test3 values (1,‘a’);
insert into t_test3 values (1,‘a’);
insert into t_test3 values (5,‘e’);
commit;
select * from t_test3;
答案1：
WITH T1 AS (
–注:原表没有主键字段，且ID列是乱序，所以需要手动增加一个RN列当作主键列，而不应该使用原有的ID列进行排序，否则就改变了表中原有的数据顺序。
SELECT ROWNUM AS RN, ID, DIG
FROM T_TEST3
),
T2 AS (
–RN2列的逻辑是先按ID, DIG分组，再按RN排序，这样就使ID, DIG两列连续出现同样的数据时，对应的RN2值一定是连续的。
–又因为RN和RN2都是有序的，所以在ID, DIG两列连续出现同样的数据时，这些行的数据对应的RN值-RN2值（即RN_DIFF列）一定是相同的。
–如果看不懂上面说的逻辑，在最后的SQL里直接执行SELECT * FROM T2; 一目了然。
SELECT RN, ID, DIG,
ROW_NUMBER() OVER (PARTITION BY ID, DIG ORDER BY RN) AS RN2,
RN - ROW_NUMBER() OVER (PARTITION BY ID, DIG ORDER BY RN) AS RN_DIFF
FROM T1
ORDER BY RN
)
SELECT ID, DIG, COUNT() AS CNT
FROM T2
GROUP BY ID, DIG, RN_DIFF
HAVING COUNT() >= 3
ORDER BY ID, DIG;
查询结果：
ID DIG CNT
1 a 3
3 c 3
5 e 4
答案2：
WITH T1 AS (
–注:原表没有主键字段，且ID列是乱序，所以需要手动增加一个RN列当作主键列，而不应该使用原有的ID列进行排序，否则就改变了表中原有的数据顺序。
SELECT ROWNUM AS RN, ID, DIG
FROM T_TEST3
),
T2 AS (
–RN2列的逻辑是先按DIG分组，再按RN排序，这样就使DIG列连续出现同样的数据时，对应的RN2值一定是连续的。
–又因为RN和RN2都是有序的，所以在DIG列连续出现同样的数据时，这些行的数据对应的RN值-RN2值（即RN_DIFF列）一定是相同的。
–如果看不懂上面说的逻辑，在最后的SQL里直接执行SELECT * FROM T2; 一目了然。
SELECT RN, ID, DIG,
ROW_NUMBER() OVER (PARTITION BY DIG ORDER BY RN) AS RN2,
RN - ROW_NUMBER() OVER (PARTITION BY DIG ORDER BY RN) AS RN_DIFF
FROM T1
ORDER BY RN
)
SELECT DIG, COUNT() AS CNT
FROM T2
GROUP BY DIG, RN_DIFF
HAVING COUNT() >= 3
ORDER BY DIG;
查询结果：
DIG CNT
a 3
c 3
e 4
答案3：
– 必须加rownum,否则窗口函数会自动排序
with t as(
select id,dig,
case when id=lag(id,1,0) over(order by rownum) then 1 else 0 end delta1,
case when id=lag(id,2,0) over(order by rownum) then 1 else 0 end delta2,
case when dig=lag(dig,1,‘0’) over(order by rownum) then 1 else 0 end delta3,
case when dig=lag(dig,2,‘0’) over(order by rownum) then 1 else 0 end delta4
from t_test3
order by rownum),
t1 as (select t.id,t.dig,(t.delta1+t.delta2+t.delta3+t.delta4) delta_sum from t)
select distinct t1.id,t1.dig from t1 where t1.delta_sum>=4;
