窗口函数在hive中用的比较多,很多数据库很早也都支持了,MySQL从8.0开始支持窗口函数。 窗口的概念非常重要,它可以理解为记录集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。 按照功能划分,可以把MySQL支持的窗口函数分为如下几类: 窗口函数的基本用法: OVER关键字用来指定函数执行的窗口范围,若后面括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行,窗口函数基于所有行进行计算; 显示每个用户按照订单下单时间从远到近排序的序号。 window_name:给窗口指定一个别名用法,SQL语句如下,结果和上面一样。 查找每个用户金额前三的订单。 查询每个用户首次交易时间、金额和最后一次交易时间、金额,在同一张表中进行显示。先通过distinct函数对用户进行去重,然后连接查询出每个用户首次购买时间和金额,通过row_number对用户分组然后按时间进行升序排列,再筛选出编号为1的就行。最后一次交易时间同理,只需要改变一下时间排序就行了。 序号函数在MySQL中有三个,如下: End函数名 OVER 子句
SELECT
user_name,
date,
amount,
row_number() over (PARTITION BY user_name ORDER BY date ASC) rownumbers
FROM
orders
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
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
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