MySQL_开窗函数_分析函数

一.LEAD()函数

描述

• LEAD中传入name列作为参数，将以 ORDER BY 排序后的顺序，返回当前行的下一行name 列所对应的值，并在新列中显示
• 注意：最后一列没有下一列结果所以这里显示NULL
• LEAD() 中传入的列名与排序的列可以不同
  

用法和参数

<analytic function> OVER (...)

LEAD(x,y)

• 参数1 跟传入一个参数时的情况一样：一列的列名
• 参数2 代表了偏移量，如果传入2 就说明要以当前行为基准，向前移动两行作为返回值
• lead函数也可以接收三个参数，第三个参数用来传入默认值，应用场景是当使用lead函数返回null的时候，可以用第三个参数传入的默认值进行填充

实例

• 使用lead函数查看下一条数据的字段

# 统计id 为1的网站，每天访问的人数以及下一天访问的人数
SELECT
    users,
    LEAD(users) OVER (ORDER BY day)
FROM statistics
WHERE website_id = 1;

• 使用lead函数计算增量

# 统计当日与次日之间点击次数差异
SELECT
    day,
    clicks,
    LEAD(clicks) OVER (ORDER BY day) - clicks
FROM statistics;

二.LAG()函数

描述

• LAG(x)函数与LEAD(x)用法类似，区别是，LEAD返回当前行后面的值，LAG返回当前行之前的值
• LEAD(...) 和 LAG(...),之间可以互相替换，可以在ORDER BY的时候通过DESC 来改变排序方式，使LEAD(...) 和 LAG(...)返回相同结果
  

用法和参数

LAG(x,y)

• 参数1 跟传入一个参数时的情况一样：一列的列名
• 参数2 代表了偏移量，如果传入2 就说明要以当前行为基准，向前移动两行作为返回值
• lead函数也可以接收三个参数，第三个参数用来传入默认值，应用场景是当使用lead函数返回null的时候，可以用第三个参数传入的默认值进行填充

实例

• 使用LAG函数查看前一条字段的某个值

# 统计id为3的网站每天的点击数量，前一天的点击数量
SELECT
    day,
    clicks,
    LAG(clicks) OVER (ORDER BY day)
FROM statistics
WHERE website_id = 3;

三.FIRST_VALUE()和LAST_VALUE函数

简述

• FIRST_VALUE()：返回指定列的第一个值
• LAST_VALUE()：返回指定列的最后一个值

实例

# 统计id为2的网站每天用户访问情况，以及最少用户访问人数。
SELECT
    website_id,
    day,
    first_value(users) OVER (ORDER BY users)
FROM statistics
WHERE website_id = 2;

LAST_VALUE的特例

• 当OVER子句中包含ORDER BY时，如果我们不显式定义window frame，SQL会自动带上默认的window frame语句：RANGE UNBOUNDED PRECEDING, 意味着我们的查询范围被限定在第一行到当前行（current row)

• 如果想通过LAST_VALUE 与ORDER BY配合得到所有数据排序后的最后一个值，需要吧window frame语句RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING或者ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

# 统计id为1的网站的广告展示情况，返回每日日期，广告展示次数，以及访问用户最多的一天广告展示的次数
SELECT
  day,
  impressions,
  LAST_VALUE(impressions) OVER(
    ORDER BY users
    ROWS BETWEEN UNBOUNDED PRECEDING
      AND UNBOUNDED FOLLOWING) AS `last_value`
FROM statistics
WHERE website_id = 1;

四.NTH_VALUE(x,n) 函数

描述

NTH_VALUE(x,n) 函数返回 x列，按指定顺序的第n个值

实例

# 将数据按照开业日期排序,返回开业日期排在第二位的值
SELECT
    *,
    nth_value(opened, 2) OVER (
        ORDER BY opened
        ROWS BETWEEN
        UNBOUNDED PRECEDING
        AND UNBOUNDED FOLLOWING)
FROM website;