MySQL窗口_序号函数 mysql 序号函数

窗口函数在hive中用的比较多，很多数据库很早也都支持了，MySQL从8.0开始支持窗口函数。

窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

按照功能划分，可以把MySQL支持的窗口函数分为如下几类：

• 排名函数：row_number()、rank() 、dense_rank()
• 分布函数：percent_rank()、cume_dist()
• 前后函数：lag()、lead()
• 头尾函数：first_val()、last_val()
• 其他函数：nth_value()、nfile()

窗口函数的基本用法：

函数名 OVER 子句

OVER关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；

• WINDOW_NAME：给窗口指定一个别名。如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读；
• PARTITION BY 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；
• ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号；
• FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。

显示每个用户按照订单下单时间从远到近排序的序号。

SELECT
  user_name,
  date,
  amount,
  row_number() over (PARTITION BY user_name ORDER BY date ASC) rownumbers 
FROM
  orders

window_name：给窗口指定一个别名用法，SQL语句如下，结果和上面一样。

SELECT
  user_name,
  date,
  amount,
  row_number () over w rownumbers 
FROM
  orders
  WINDOW w AS (PARTITION BY user_name ORDER BY date ASC)

查找每个用户金额前三的订单。

SELECT * FROM
    (SELECT
      id,
    user_name,
    date,
    amount,
    row_number () over (PARTITION BY user_name ORDER BY amount DESC) rownumbers
  FROM
    orders) f1
  WHERE
    f1.rownumbers<=3

查询每个用户首次交易时间、金额和最后一次交易时间、金额，在同一张表中进行显示。先通过distinct函数对用户进行去重，然后连接查询出每个用户首次购买时间和金额，通过row_number对用户分组然后按时间进行升序排列，再筛选出编号为1的就行。最后一次交易时间同理，只需要改变一下时间排序就行了。

SELECT 
  DISTINCT o.user_name ,
  f.date 首次交易时间,
  f.amount 首次交易金额,
  l.date 最近交易时间,
  l.amount 最近交易金额
FROM
  orders o
LEFT JOIN
  (SELECT * FROM
    (SELECT
    user_name,
    date,
    amount,
    row_number() over (PARTITION BY user_name ORDER BY date ASC) rownumbers
  FROM
    orders) f1
  WHERE
    f1.rownumbers=1) f
  on f.user_name=o.user_name
LEFT JOIN
  (SELECT * FROM
    (SELECT
    user_name,
    date,
    amount,
    row_number () over (PARTITION BY user_name ORDER BY date desc) rownumbers
  FROM
    orders) l1
  WHERE
    l1.rownumbers=1) l
  on l.user_name=o.user_name

序号函数在MySQL中有三个，如下：

• row_number()按顺序输出表的行号；
• rank()按顺序输出排名结果，每当出现一个并列结果，则下一个排名数字向后递增一位；
• dense_rank()按数字输出排名的名次。当出现并列结果时，下一个名次是下一个连续的整数值。名次之间没有间隔。

SELECT
  user_name,
  date,
  amount,
  row_number() over(PARTITION BY user_name order by amount desc) AS rownumbers,
  rank() over(PARTITION BY user_name order by amount desc) AS RANKs,
  DENSE_RANK() over(PARTITION BY user_name order by amount desc) DENSE_RANKs
FROM
  orders

End