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

窗口函数语法:
SELECT key1, key2, value,
       rank() OVER (PARTITION BY key2
                    ORDER BY value DESC) AS rnk
FROM orders
ORDER BY key1,rnk
核心部分是:
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。

使用样例

  • 在整个公司的人员中,获取每个人的薪水在部门内排名
    * | 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
  • 在整个公司的人员中,获取每个人的薪水在部门内的占比
    * | 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
  • 按天统计,获取每天UV相对前一天的增长情况
    * | select day ,uv, uv *1.0 /(lag(uv,1,0) over() ) as diff_percentage from
    (
    select approx_distinct(ip) as uv, date_trunc('day',__time__) as day from log group by day order by day asc
    )

    响应结果:

    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