全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 钉钉智能硬件
日志服务

窗口函数

更新时间:2017-12-21 19:18:29

窗口函数简介

窗口函数用来跨行计算。普通的SQL聚合函数只能用来计算一行内的结果,或者把所有行聚合成一行结果。窗口函数,可以跨行计算,并且把结果填到到每一行中。

窗口函数语法:

  1. SELECT key1, key2, value,
  2. rank() OVER (PARTITION BY key2
  3. ORDER BY value DESC) AS rnk
  4. FROM orders
  5. ORDER BY key1,rnk

核心部分是:

  1. rank() OVER (PARTITION BY KEY1 ORDER BY KEY2 DESC)

其中rank()是一个聚合函数,可以使用分析语法中的任何函数,也可以使用本文档列出的函数。PARTITION BY 是值按照哪些桶进行计算。

窗口中使用的特殊聚合函数

函数 含义
rank() 在窗口内,按照某一列排序,返回在窗口内的序号
row_number() 返回在窗口内的行号
first_value(x) 返回窗口内的第一个value,一般用法是窗口内数值排序,获取最大值
last_value(x) 含义和first value相反
nth_value(x, offset) 窗口内的第offset个数
lead(x,offset,defaut_value) 窗口内x列某行之后offset行的值,如果不存在该行,则取default_value
lag(x,offset,defaut_value) 窗口内x列某行之前offset行的值,如果不存在该行,则取default_value

使用样例

1. 在整个公司的人员中,获取每个人的薪水在部门内排名

  1. * | select department, persionId, sallary , rank() over(PARTITION BY department order by sallary desc) as sallary_rank order by department,sallary_rank

响应结果:

department persionId sallary sallary_rank
dev john 9000 1
dev Smith 8000 2
dev Snow 7000 3
dev Achilles 6000 4
Marketing Blan Stark 9000 1
Marketing Rob Stark 8000 2
Marketing Sansa Stark 7000 3

2. 在整个公司的人员中,获取每个人的薪水在部门内的占比

  1. * | select department, persionId, sallary *1.0 / sum(sallary) over(PARTITION BY department ) as sallary_percentage

响应结果:

department persionId sallary sallary_percentage
dev john 9000 0.3
dev Smith 8000 0.26
dev Snow 7000 0.23
dev Achilles 6000 0.2
Marketing Blan Stark 9000 0.375
Marketing Rob Stark 8000 0.333
Marketing Sansa Stark 7000 0.29

3. 按天统计,获取每天UV相对前一天的增长情况

  1. * | select day ,uv, uv *1.0 /(lag(uv,1,0) over() ) as diff_percentage from
  2. (
  3. select approx_distinct(ip) as uv, date_trunc('day',__time__) as day from log group by day order by day asc
  4. )

响应结果:

day uv diff_percentage
2017-12-01 00:00:00 100 null
2017-12-02 00:00:00 125 1.25
2017-12-03 00:00:00 150 1.2
2017-12-04 00:00:00 175 1.16
2017-12-05 00:00:00 200 1.14
2017-12-06 00:00:00 225 1.125
2017-12-07 00:00:00 250 1.11
本文导读目录