一、前言
本文将从一道面试题入手,以一种易人理解的方式,将对SQL窗口函数部分进行详细讲解。
开窗函数,顾名思义,就是从现有的表上,额外多加一列出来展示,这也是从视觉上来讲最直观的体验。
二、思维拓展
注:MYSQL中无开窗函数
开窗函数分为两大类:
1、分组聚合开窗函数
例. sum() over(partition by ... ) 分组聚合,无order by
2、分组排序开窗函数(具体不同将在另一篇博文中详细说明)
例.rank() over (partition by ... order by ...) 计算排序 (1 2 3 3 5)
dense_rank() over (partition by ... order by ...) 密集排序 (1 2 3 3 4)
row_number() over (partition by ... order by ...) 行序排序 (1 2 3 4 5)
三、面试题
从以下格式的表中,筛选出每日每个URL访问的第三个用户
元数据格式:URL USER TIME(时间戳)
四、实现
1、time时间戳转换成时间
2、截取转换后的time的yyyy-mm-dd(substr或left实现)
3、获取当前日期,转换成时间,截取yyyy-mm-dd
4、第一个查询查询当天的所有url、user、time通过where 转换后的时间戳日期 = 当前的时间戳日期
5、使用分组排序开窗函数,获得url、用户。where 排名为3条件
代码部分(由于同一天中可能会有用户在同一时间同时进行访问):
select url,user
(select
user,url, dense_rank() over(partition by url order by time asc) as rank
from
(select
url,user,time
FROM
a
where substr(from_unixtime(time),0,10)=substr(from_unixtime(unix_timestamp()),0,10))Current_data ) // ---每天时间
where rank = 3