专栏首页张俊红讲讲你不知道的窗口函数

讲讲你不知道的窗口函数

01|什么是窗口函数

我们都知道 SQL 中的聚合函数,聚合函数顾名思义就是聚集合并的意思,是对某个范围内的数值进行聚合,聚合后的结果是一个值或是各个类别对应的值。如下所示:

直接聚合得到的结果是所有店铺在这段时间内的所有销量之和,分组聚合(group by)得到的结果是每个店铺在这段时间内的销量之和。

这种聚合函数得到的数据行数是小于基础数据行数的,但是我们经常会有这样的需求,就是既希望看基础数据同时也希望查看聚合后的数据,这个时候聚合函数就满足不了我们了,窗口函数就派上用场了。窗口函数就是既可以显示原始基础数据也可以显示聚合数据。可能你还是不太理解,没关系,我也刚开始不太理解,我们看几个关于窗口函数的具体的应用就理解了。

02|聚合函数+over()

现在有如下的一张表 t 存储了每个店铺每天的销量:

shopname  sales  date      
A         1      2018/4/1  
B         3      2018/4/1  
C         5      2018/4/1  
A         7      2018/4/2  
B         9      2018/4/2  
C         2      2018/4/2  
A         4      2018/4/3  
B         6      2018/4/3  
C         8      2018/4/3 

如果我们想看一下每个店铺每天的销量和一段时间内所有店铺销量的平均值的话该怎么做呢?答案就是可以用窗口函数来实现。只需要除了基础的查询代码以外,还需要在你要聚合的字段后面加一个 over(),语句如下所示:

select
    shopname
    ,sales
    ,date
    ,avg(sales) over()
from
    t

最后结果如下所示:

shopname  sales  date      avg_window_0
A         1      2018/4/1  5
B         3      2018/4/1  5
C         5      2018/4/1  5
A         7      2018/4/2  5
B         9      2018/4/2  5
C         2      2018/4/2  5
A         4      2018/4/3  5
B         6      2018/4/3  5
C         8      2018/4/3  5

02|partition by子句

再想象一下,上面我们是拿每个店铺每天的销量和全部店铺全部天数的平均销量作比较,如果我们现在想让每个店铺每天的销量与自身全部天数的平均值作比较,该怎么实现呢?答案就是使用 partition by ,partition by的作用和 group by 是类似的,是进行分组聚合的,需要与 over() 搭配使用。

select
    shopname
    ,sales
    ,date
    ,avg(sales) over(partition by shopname)
from
    t

最后结果如下所示:

shopname  sales  date      avg_window_0
A         1      2018/4/1  4
B         3      2018/4/1  6
C         5      2018/4/1  5
A         7      2018/4/2  4
B         9      2018/4/2  6
C         2      2018/4/2  5
A         4      2018/4/3  4
B         6      2018/4/3  6
C         8      2018/4/3  5

03|order by子句

order by 就是按照某一列数值进行排序,主要与接下来的序列函数结合使用,当 order by 与聚合函数一起使用时,是顺序聚合的。什么叫顺序聚合呢?给大家举一个求和的聚合与 order by 结合使用的例子,就是类似于累计和的效果,具体代码如下:

select
    shopname
    ,sales
    ,date
    ,sum(sales) over(partition by shopname order by date)
from
    t

最后运行结果如下:

shopname  sales  date      sum_window_0
A         1      2018/4/1  1
A         7      2018/4/2  8
A         4      2018/4/3  12
B         3      2018/4/1  3
B         9      2018/4/2  12
B         6      2018/4/3  18
C         5      2018/4/1  5
C         2      2018/4/2  7
C         8      2018/4/3  15

当 order by 与序列函数一起使用时就是用于排序。

04|序列函数

什么是序列函数,就是可以将数据整理成一个有序的序列,然后我们可以在这个序列里面挑选我们想要的序列对应的数据。

4.1 ntile

ntile 函数主要是用于将整表数据进行切片分组,默认是对表在不做任何操作之前进行切片分组的,比如现在整个表有9行数据,你要切片分成3组,那么就是第 1-3 行为一组,4-6 行为一组,7-9 行为一组。我们将店铺销量表切分成3组,代码如下:

select
    shopname
    ,date
    ,sales
    ,ntile(3) over()
from
    t

最后结果如下:

shopname  sales  date      ntile_window_0
A         1      2018/4/1  1
B         3      2018/4/1  1
C         5      2018/4/1  1
A         7      2018/4/2  2
B         9      2018/4/2  2
C         2      2018/4/2  2
A         4      2018/4/3  3
B         6      2018/4/3  3
C         8      2018/4/3  3

上面是把销量表切分成3组了,但是对我们实际应用中没什么实际作用啊,你想一下,你拿一个乱序分组有什么用?如果我们和 order by结合使用就有用了,比如我先按照 sales 升序排列,然后再进行切片分组,这个时候的切片就有意义了。也可以在分组内(partition by)近行切片分组,示例如下:

select
    shopname
    ,date
    ,sales
    ,ntile(3) over(partition by shopname order by sales)
from
    t

最后结果如下:

shopname  sales  date      ntile_window_0
A         1      2018/4/1  1
A         4      2018/4/3  2
A         7      2018/4/2  3
B         3      2018/4/1  1
B         6      2018/4/3  2
B         9      2018/4/2  3
C         2      2018/4/2  1
C         5      2018/4/1  2
C         8      2018/4/3  3

4.2 row_number

row_number() 从 1 开始,按照顺序(注意这里是顺序不是排序)生成该条数据在分组内的对应的序列数,row_number() 的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列。

因为 row_number() 是按照顺序生成对应的序列,而不是按照排序来生成序列的,所以 row_number() 一般需要与 order by 进行结合使用。

你现在想看一下,在一段时间内每个店铺 sales 对应最早的一次 date 是什么时候?该怎么看呢?这个时候就可用 row_number() 与 order by 相结合,代码如下:

select
    shopname
    ,date
    ,sales
    ,row_number() over(partition by shopname order by date)
from
    t

因为我们是要查看每个店铺最早的一次 date,所以需要对 date 进行升序排列。最后结果如下:

shopname  sales  date      row_number_window_0
A         1      2018/4/1  1
A         7      2018/4/2  2
A         4      2018/4/3  3
B         3      2018/4/1  1
B         9      2018/4/2  2
B         6      2018/4/3  3
C         5      2018/4/1  1
C         2      2018/4/2  2
C         8      2018/4/3  3

我们只需要把 num = 1 的部分取出来就是我们想要的结果。

4.3 lag和lead

lag 的英文意思是滞后,而 lead 的英文意思是超前。对应的 lag 是让数据向后移动,而 lead 是让数据向前移动。你可能不太理解,无所谓,直接来看实例。

现在你想看一下每个店铺这一次和上一次 date 的时间差,你该怎么看呢?可以借助 lag,代码如下:

select
    shopname
    ,date
    ,sales
    ,lag(date,1) over(partition by shopname order by date)
from
    t

最后结果如下:

shopname  sales  date      lag_window_0
A         1      2018/4/1  NULL
A         7      2018/4/2  2018/4/1
A         4      2018/4/3  2018/4/2
B         3      2018/4/1  NULL
B         9      2018/4/2  2018/4/1
B         6      2018/4/3  2018/4/2
C         5      2018/4/1  NULL
C         2      2018/4/2  2018/4/1
C         8      2018/4/3  2018/4/2

现在你想看一下每个店铺这次和下一次 date 之间的时间差,你又该怎么看呢?可以借助 lead,代码如下:

select
    shopname
    ,date
    ,sales
    ,lead(date,1) over(partition by shopname order by date)
from
    t

最后结果如下:

shopname  sales  date      lead_window_0
A         1      2018/4/1  2018/4/2
A         7      2018/4/2  2018/4/3
A         4      2018/4/3  NULL
B         3      2018/4/1  2018/4/2
B         9      2018/4/2  2018/4/3
B         6      2018/4/3  NULL
C         5      2018/4/1  2018/4/2
C         2      2018/4/2  2018/4/1
C         8      2018/4/3  2018/4/3

4.4 first_value和last_value

first_value 和 last_value 都是顾名思义,就是获取第一个值和最后一个值。但是不是真正意义上的第一个或最后一个,而是截至到当前行的第一个或最后一个。

现在你想看一下每个店铺的首次 date 和最后一次 date,你会怎么看呢?就可以直接借助first_value 和 last_value,代码如下:

select
    shopname
    ,date
    ,sales
    ,first_value(date) over(partition by shopname order by date)
    ,last_value(date) over(partition by shopname order by date)
from
    t

最后结果如下:

shopname  sales  date      first_value_window_0   last_value_window_0
A         1      2018/4/1  2018/4/1               2018/4/1
A         7      2018/4/2  2018/4/1               2018/4/2
A         4      2018/4/3  2018/4/1               2018/4/3
B         3      2018/4/1  2018/4/1               2018/4/1
B         9      2018/4/2  2018/4/1               2018/4/2
B         6      2018/4/3  2018/4/1               2018/4/3
C         5      2018/4/1  2018/4/1               2018/4/1
C         2      2018/4/2  2018/4/1               2018/4/2
C         8      2018/4/3  2018/4/1               2018/4/3

本文分享自微信公众号 - 张俊红(zhangjunhong0428),作者:张俊红

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 数据结构-栈和队列

    我们把类似于弹夹那种先进后出的数据结构称为栈,栈是限定仅在表尾进行插入和删除操作的线性表,我们把允许插入和删除的一端称为栈顶,另一端称为栈底,不含任何数据元素的...

    张俊红
  • 信用卡“坏账”客户分析(一)

    总第92篇 01|背景: 随着人们的消费观念的升级,所谓的“花明天的钱,圆今天的梦”。银行以及私营企业推出了各种各样的消费金融服务,具有代表性的是各大银行的信用...

    张俊红
  • python中的小魔法(一)

    ? 总第101篇 if-else的简洁写法 #常规写法 if a>b: c=a else: c=b #简洁写法 c=a if a>b...

    张俊红
  • 2018年最值得关注的15大技术趋势

    通常情况下,技术趋势是很难准确预测的,因为预测未来本身就极其困难。但是我们还是可以从过往的一些显著数据指标来推测新的一年里科技行业的发展趋势。

    华章科技
  • 2018年最值得关注的15大技术趋势

    ? 本文转自36氪 通常情况下,技术趋势是很难准确预测的,因为预测未来本身就极其困难。但是我们还是可以从过往的一些显著数据指标来推测新的一年里科技行业的发展趋...

    CDA数据分析师
  • 【2020HBU天梯赛训练】7-12 2018我们要赢

    2018年天梯赛的注册邀请码是“2018wmyy”,意思就是“2018我们要赢”。本题就请你用汉语拼音输出这句话。

    韩旭051
  • shell脚本案例分享 - 业务系统日志自定义保留或删除需求

    需求说明:  线上某些业务系统的日志不定期产生, 有的每天产生, 有的好几天才产生, 因为系统只有在用的时候才产生日志,日志文件均存放在以当天日期命名的目录下....

    洗尽了浮华
  • 官宣!抓住年终的尾巴提升一下自己

    眼瞅着2018年只剩下两个多月了,小伙伴们年初定的小目标都实现了吗? 是不是该看的书还没来得及翻开?该学习的知识也没来得及学习?喏!现在机会来了,抓住201...

    吴延宝
  • 盘点 2018 年自然语言处理 10 个激动人心的想法

    这篇文章翻译自 KDnuggets, 作者: Sebastian Ruder, 是 insight_centre 的 NLP PhD,aylien 的研究员

    杨熹
  • Gartner 10大预测:The Future Is a Digital Thing(30PPT)

    大数据文摘

扫码关注云+社区

领取腾讯云代金券