盘点一道窗口函数的数据分析面试题
盘点一道窗口函数的数据分析面试题
云母屏风烛影深，长河渐落晓星沉。
大家好，我是热心读者。前几天在群里看到有人问了这样一道题，我觉得对一些新手了解窗口函数很有裨益，因此拿出来以飨读者。
至于为什么要拿窗口函数来说事儿呢？因为目前的数分面试，只要考sql，窗口函数是100%会问的。从另一个侧面来讲，窗口函数是检验你的SQL的试金石，一验一个准，比目前的核酸检测水平都高。
好了，闲言少叙，我们来解题。
窗口函数定义
按照分类来讲，一类是专门的窗口函数：row_number(),rank(),dense_rank()等;一类是聚合函数：sum(),avg()等。
按照功能来讲，窗口函数是在不损失行数的背景下，按照指定维度进行分组，按照指定维度进行排序的一种排序函数，聚合等作用的函数，窗口函数的熟练程度决定了你SQL的熟练程度，而在面试中是一道必考题，在业务实践中也是一道迈不过去的坎儿。
窗口函数表达式
这里以row_number()为例，来说明一下表达式的含义，因为万变不离其宗，对于基础我们要了然于心。
row_number() over([parition by 维度] order by 维度 asc [desc])
[partition by 维度] 该部分可以省略，表按照某指定维度进行分组
order by 维度 该部分不允许为空，表按照某维度进行升序(或降序)排序
row_number()函数是用来分组排序的，排序不重复，此处大家可以百度一下跟rank和dense_rank排序的区别。
我们先“由俭入奢”，从最常规的开始处理。题目被我稍作修改
窗口函数实战——基础版
如图：
根据题意我们会发现，这里是完成的排序是按照id和cat字段分组，按照time字段进行排序，发现了这个规律我们就套用窗口函数的基本语法，即可完成题目的要求，下面是脚本：
#第一步 构造数据
insert into test values('2020-10-02 12:30:45','A','AAA');
insert into test values('2020-10-02 12:30:55','A','AAA');
insert into test values('2020-10-02 14:39:45','A','BBB');
insert into test values('2020-10-02 14:40:55','A','BBB');
insert into test values('2020-10-02 15:30:05','A','AAA');
insert into test values('2020-10-02 16:30:45','B','AAA');
insert into test values('2020-10-02 17:04:45','B','BBB');
# 脚本
select
time,
id,
cat,
row_number() over(partition by id,cat order by time asc) as rnk
from test
order by time asc
# 加入order by 语句为了让数据展示的更清楚，别无他用
看下效果：
窗口函数实战——进阶版
具体的题目是这样的：
具体的题目描述如下：
第一列是事件发生的时间，第二列是用户id，第三列是事件分组，第四列是我想要打的排序，如果事件分组和上一个事件不一致，或者用户id不一致的话，就要重新计数
根据图片和描述的情景，我们发现跟上一道题有一点点差别，就是数据顺序已经按照时间排好了序，如果id和cat相同，则进行顺序排序；如果id和cat不同，则要重新从1进行排序。
思路：
1、要想实现上述效果，也就是需要第三列辅助列跟id和cat一起进行分组，而第三列辅助列应该满足以下：
按照id和cat分组连续，则第三列辅助列应该为相同的值；如果id和cat发生了改变，则第三列辅助列应该随之改变，并且需要保证第三列辅助列在每个分组内的值唯一。
2、如果有了第三列辅助列，那么我们套用窗口函数公式即可将题意中顺序排出来。
以下为脚本：
# 1 构造数据 以题一中数据为例
# 2 脚本 a 构造连续排序
# 备注 原题中时间标记为1 2 3 ,而实际中时间肯定为标准的时间戳形式，因此需要通过连续数来构造
# 加with temp1 as这个伪脚本为了下面脚本引用方便而已，实际运行中请忽略
with temp1 as
select
time,
id,
category,
concat_ws('-',id,category) as add_col ,
row_number() over(order by time asc) as order_rnk
-- 按时间顺序计算连续排序，构造连续数序列
-- 此处省略partition by 说明实际中partition可以省略
from test
示意如图：
而我们通过这个连续数序列想要判断哪些数是连续的，哪些数又是不连续的，接着看脚本
with temp2 as
select
time,
id,
category,
add_col,
order_rnk,
order_rnk-lag(order_rnk,1,order_rnk-1) over(partition by add_col order by time asc) as order_rnk_lag1
from temp1
# 这里需要解释下几个知识点：
# 窗口函数lag可以理解为拖后的意思，即按照指定维度分组，指定维度排序，将某列向下平移n行，空值用第三个参数默认
# 因而本文的意思就是将order_rnk这个连续数序列按照add_col这个辅助列，组内向下平移1行，如果是空值，
# 用 order_rnk-1设置为默认值
# 为什么要这样处理：因为我们第一步配置的连续数序列，相邻两行作差，如果差值为1，则连续，否则说明组内出现了不连续的情况
示意如图：
通过红色框，我们可以看出来，我们把同一个add_col内的不连续区分了开来，此时，我们就可以有“山重水复疑无路，柳暗花明又一村”的感觉了，我们把add_col 和order_cnk_lag1再作为一个分组的依据，再排序，题意可解，脚本如下：
with temp3 as
select
time,
id,
catgory,
row_number() over(parition by concat(add_col,order_cnk_lag1) order by time asc) as rnk
from temp2
以上。
小伙伴们，快快用实践一下吧！
------------------- End -------------------