Oracle的lag（）函数（分析函数）的应用

 

 公交车上刷了一下知乎，看到一个问题：【怎么处理才能把左边的表变成右边？】

其实问题提问的很模糊，没有业务描述，只说结果，接下来也就是按照答者的理解吧

答者认为该题需求：用户连续访问同一个页面时，只保留最早的一条而已。

 

思路：我们冗余列来制作数据行之间的信息差，第一次我们需要明确数据的顺序（知道前后），这里新增【用户浏览时间序号】。
问题中消除重复，是为了消除同一个页面连续访问记录只保留最早的一条，我们需要明确 用户+页面内的前后，
即【用户浏览某页面时间序号】， 即通过两列信息差，我们可以找到自己想要的数据。

 

具体SQL逻辑如下：  第一步：在原始数据中通过冗余列制作信息差，作为数据筛选的依据： 

select 用户，页面，浏览时间，

ROW_NUMBER() OVER(PARTITION BY 用户 ORDER BY 浏览时间 ASC) AS 用户浏览时间序号,

ROW_NUMBER() OVER(PARTITION BY 用户,页面 ORDER BY 浏览时间 ASC) AS 用户浏览某页面时间序号

from 用户浏览日志

  数据输出：########### 结果集 

用户，页面，浏览时间，用户浏览时间序号，用户浏览某页面时间序号

甲，1，a，1，1

甲，1，b，2，2

甲，1，c，3，3

甲，2，d，4，1

甲，1，e，5，4 

 第二步，基于有信息差的结果集，来筛选出自己想要的数据：

   select 用户，页面，浏览时间 from (select 用户，页面，浏览时间，

ROW_NUMBER() OVER(PARTITION BY 用户 ORDER BY 浏览时间 ASC) AS 用户浏览时间序号,

ROW_NUMBER() OVER(PARTITION BY 用户,页面 ORDER BY 浏览时间 ASC) AS 用户浏览某页面时间序号

from 用户浏览日志) tt where tt.用户浏览某页面时间序号=1 or tt.用户浏览时间序号<>tt.用户浏览某页面时间序号 

最终输出：########### 结果集

  用户，页面，浏览时间

甲，1，a

甲，2，d

甲，1，e 

------------ 上述答案有漏洞--------------------- 

 

 

 

 

然后：

Youmple 提出：如果 甲～1～e 后面紧接着 甲～1～f ，那是不是 甲～1～f 也会进入结果集。那明显是错的啊

感谢评论指出了，这一版答案的漏洞，我们优化一下。 

我们可以余出一列：【前一页浏览页面】

  lag(页面,1) OVER(PARTITION BY 用户 ORDER BY 浏览时间 ASC) AS 前一页浏览页面。 

数据输出： 

用户，页面，浏览时间，前一个浏览的页面

甲，1，a，null

甲，1，b，1

甲，1，c，1

甲，2，d，1

甲，1，e，2

甲，1，f，1 

同时过滤的数据条件修改为：  where tt.页面<> coalesce(tt.前一个浏览的页面,0) 

 

 

 

 是不是很容易，只需要做两次简单的排序，就解决来问题。  遇到问题，要多思考，办法肯定有，SQL一样可以写出花。

 

 原文：https://www.zhihu.com/question/496875292/answer/2210351015