目的:编写一个语句查询出连续三条记录的人数不少于100的记录
记录:
记录中的visit_date是连续的,但是people的数量是不一样的, 输出结果:
思路:先将人数大于100的查询出来
select * from data WHERE people >= 100
输出来的结果:
接下来我需要在这个表格的基础上找出至少连续的三条记录。这里我使用的是以ip为一条记录。
思路:先使用row_number()函数,对数据进行一个排序,再将ip减去数据的秩的得到rn,如果数据是连续的,那么可以看到rn的值应该是相同的, 为什么是使用row_number()而不是rank()
SELECT*, ROW_NUMBER()OVER(ORDER BY ip) AS "rank",(ip - ROW_NUMBER () OVER (ORDER BY ip)) rnFROM(SELECT*FROMdataWHEREpeople >= 100) t
输出的结果:
由结果中可以看到,rn相等的数据为连续的记录,下面按照rn进行分组,并将组中的记录>=3的数据检索出来(group by 跟having)
SELECT*, count(*) cntFROM(SELECT*,(ip - ROW_NUMBER () over (ORDER BY ip)) rnFROM(SELECT*FROMdataWHEREpeople >= 100) t) bGROUP BYrnHAVINGcnt >= 3
输出的结果为:
这个是按照分组将连续记录至少为3条且数据人数大于100的检索出来了,但是有一个问题就是这里的数据是按组输出的,跟我们想要的结果的结果有些出入,这里我使用的是以rn字段来建立内连接的方式:
SELECTb.ip,b.visit_date,b.peopleFROM(SELECT*,(ip - ROW_NUMBER () OVER (ORDER BY ip)) rnFROM(SELECT*FROMdataWHEREpeople >= 100) t -- t表为数据大于100的记录表) b -- b表为数据大于100且带有rn的表INNER JOIN (SELECT*, COUNT(*) cntFROM(SELECT*,(ip - ROW_NUMBER () OVER (ORDER BY ip)) rnFROM(SELECT*FROMdataWHEREpeople >= 100) t -- t表为数据大于100的记录表) b -- b表为数据大于100且带有rn的表GROUP BYrnHAVINGcnt >= 3) c -- c表为b表通过rn分组后的表 ON b.rn = c.rn
输出结果:
到这里就完成了目标需求了。