SQL如何删除相邻连续的重复行？（连续问题、偏移函数）

【题目】

如下为一张互联网企业用户访问商城的各页面的访问记录表

要求当用户连续访问同一页面时，只保留第一次访问记录，即得到如下结果：

 

字段说明：

用户ID：用户的账户

访问的页面：用户访问商城时查看的页面

访问页面时间：用户打开该页面的时间点

【解题思路一】：

根据题意的要求，把要求的结果在原表上用黄色标出，通过观察发现连续登录的某一个页面只保留第一次访问的记录。解题思路是要通过查询，利用信息差过滤掉同一个页面第一次登录后的连续访问记录。

1、利用自联结，得到两张相同的表，t1作为主表，t2作为从表，左联结2张表，并都按照用户分组，按照用户的访问时间升序排序

(select
用户ID
,访问的页面
,访问页面时间
,row_number() over (partition by 用户ID order by 访问页面时间 asc) as 访问序号
from 访问记录表)t1
left join
(select
用户ID
,访问的页面,访问页面时间
,row_number() over (partition by 用户ID order by 访问页面时间 asc) as 访问序号
from 访问记录表)t2
on t1.用户ID=t2.用户ID

2、制造信息差

因为要过滤掉同一个页面第一次登录后的访问记录，即要判断用户第一次访问的页面与后面第二次访问页面是否相同，即“t1的访问序号=t2的访问序号+1”。

(select
用户ID,访问的页面,访问页面时间
,row_number() over (partition by 用户ID order by 访问页面时间 asc) as 访问序号（与图片中的列名不一致）
from 访问记录表)t1
left join
(select
用户ID,访问的页面,访问页面时间
,row_number() over (partition by  用户ID order 访问页面时间  asc) as 访问序号
from 访问记录表)t2
on t1.用户ID=t2.用户ID
and t1.访问序号=t2.访问序号+1

3、取出符合条件的记录

在第2步的基础上加上筛选条件，即当t1的访问序号=t2的访问序号+1时，t1.访问的页面!=t2.访问的页面。另外，还需要考虑到增加一个条件 “t2.访问的页面 is null“，因为当t1=1时，t2是空值，要把t1=1取出，必须加上条件“t2.访问的页面 is null“。只有”t1.访问的页面!=t2.访问的页面“一个条件，会漏掉主表的第1条页面的记录。如上图所示

select
t1.用户ID
,t1.访问的页面
,t1.访问页面时间
from
(select
用户ID
,访问的页面
,访问页面时间
,row_number() over (partition by 用户ID order by 访问页面时间 asc) as 访问序号
from 访问记录表) t1
left join
(select
用户ID
,访问的页面
,访问页面时间
,row_number() over (partition by  用户ID order by访问页面时间  asc) as 访问序号
from 访问记录表) t2
on t1.用户ID=t2.用户ID
and t1.访问序号=t2.访问序号+1
where t2.访问的页面 is null
or t1.访问的页面!=t2.访问的页面;

【本题考点】

1、自联结。本题利用自联结，获得信息差。自联结是指使用表的别名实现表与其自身联结的查询方法。我们需要对一张表内的数据，进行一些对比，或者是比较，获得各列层次关系，通过一般的SQL写法，可能需要通过写多个子查询的方式才能解决。但是用自联结查询可以轻松解决，自联结查询就是以类似多表对比的方式，实现对同一张表内数据进行复杂的关系表示或关系处理。关键点在于虚拟化出一张表给一个别名。自联结得到的查询结果比较直观但是不适合操作大表，容易产生笛卡尔积，造成数据量巨大。

2、窗口函数排序

row_number()在SQL语句中非常的重要的窗口函数，一般与partition by，order by连用，组成

row_number() over (partition by … order by … )

 表示按照某个字段分组，按照某个字段的值来排序的顺序。详细用法见窗口函数的介绍。

【解题思路二】：

上面的操作步骤比较清晰和简单，但是感觉比较啰嗦，还有一种比较简洁的做法，利用lag()函数增加一列“上一个访问的页面”，利用本次访问的页面不等于上一个访问的页面作为条件，取出要求的结果，思路与第一个思路一致。

select
t.用户ID
,t.访问的页面
,t.访问页面时间
from
(select
用户ID
,访问的页面
,访问页面时间
,lag(访问的页面,1,0) over (partition by 用户ID order by 访问页面时间 asc) as 上一个访问的页面
from 访问记录表)t
where t.上一个访问的页面 is null
or t.访问的页面!=t.上一个访问的页面

本题要点】

此种解法用到了lag()函数，lag()函数是查询当前行向上偏移n行对应的结果
该函数有三个参数：第一个为待查询的参数列名，第二个为向上偏移的位数，第三个参数为超出最上面边界的默认值。，一般与over()连用，为窗口函数的一种。

lag(…) over (partition by… order by…)

下图为lag()函数向上偏移一行，两行，并超出边界用“0”表示的图示。

【此面试题的总结】：

此题重点考察的是计算逻辑和窗口函数。怎么理解数据，并取出需要的行数，需要很强的逻辑思路，属于面试题中比较难的题目。逻辑思路正确是写正确代码的前提。一个题目有多种实现的方式，不是只有一种代码可以实现，遇到问题换个思路和解法，多写多练就能很快的提高。

lag(字段名称 , 向上偏移量 , 超出范围时默认值) over (partion by …order by …)

lead(字段名称 , 向下偏移量 , 超出范围时默认值) over (partion by …order by …)