SQL基础学习笔记--7--SQL高级处理

7.1 窗口函数

 

Mysql 8.0 版本开始支持了窗口函数

窗口函数就是在满足某种条件的记录集合上执行的特殊函数

 

7.1.1 分类

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

• 序号函数：row_number() / rank() / dense_rank()
• 分布函数：percent_rank() / cume_dist()
• 前后函数：lag() / lead()
• 头尾函数：first_val() / last_val()
• 其他函数：nth_value() / ntile()
• 聚合函数作为窗口函数

 

7.1.2 基本用法

窗口函数的基本用法如下：

 函数名（[expr]） over子句

 

其中，over是关键字，用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下四种语法来设置窗口：

 

• window_name：给窗口指定一个别名，如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读。上面例子中如果指定一个别名w，则改写如下：

select * from
(
    select row_number()over w as row_num,
    order_id,user_no,amount,create_date
    from order_tab
    WINDOW w AS (partition by user_no order by amount desc)
)t ;

• partition子句：窗口按照那些字段进行分组，窗口函数在不同的分组上分别执行。上面的例子就按照用户id进行了分组。在每个用户id上，按照order by的顺序分别生成从1开始的顺序编号。

 

• order by子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用，也可以单独使用。上例中二者同时使用，如果没有partition子句，则会按照所有用户的订单金额排序来生成序号。

 

• frame子句：frame是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额，则可以设置如下frame子句来创建滑动窗口。

7.1.3 序号函数

 

序号函数——row_number() / rank() / dense_rank()。

• 用途：显示分区中的当前行号
• 使用场景：希望查询每个用户订单金额最高的前三个订单

-- 将asset表根据uid进行分组,并根据usdc进行降序排列,num就是序号
-- row_number ()对相同数值进行排序时(usdc),随机给序号,如 1-2-3-4-5-6
-- rank() 对相同的数值排序,序号会并列并跳过中间并列的序号,如1-2-2-2-5-6
-- dense_rank()对相同的数值排序,序号会并列并递加,如1-2-2-2-3-3-4
SELECT
	uid,
	row_number () over (
		PARTITION BY uid
		ORDER BY
			usdc DESC
	) AS row_num,
	usdc,
	rank () over (
		PARTITION BY uid
		ORDER BY
			usdc DESC
	) AS rank_num,
	dense_rank () over (
		PARTITION BY uid
		ORDER BY
			usdc DESC
	) AS denserank_num
FROM
	asset;

7.1.4 分布函数

分布函数——percent_rank()/cume_dist()。

• 用途：和之前的RANK()函数相关，每行按照如下公式进行计算：

其中，row_num为RANK()函数产生的序号，rows为当前组的记录总行数。

• 应用场景：percent_rank()感觉不太常用,cume_dist()用的多

-- percent_rank()
-- w 是窗口函数的别名
-- per_num的值是由(row_num- 1) / (rows - 1)计算得来的
SELECT
	rank () over w AS row_num,
	percent_rank () over w AS per_num,
	uid,
	usdc
FROM
	asset window w AS (
		PARTITION BY uid
		ORDER BY
			usdc DESC
	)

-- cume_dist () 
-- 分组内小于等于当前rank值的行数/分组内总行数
-- 分组内大于等于当前金额的比例
SELECT
	rank () over w AS row_num,
	cume_dist () over w AS cum_num,
	uid,
	usdc
FROM
	asset window w AS (
		PARTITION BY uid
		ORDER BY
			usdc DESC
	)

7.1.5 前后函数

前后函数——lead(n)/lag(n)。

• 用途：分区中位于当前行前n行（lead）/后n行(lag)的记录值。
• 使用场景：查询上一个订单距离当前订单的时间间隔。

-- datediff(time1,time2) 获取两个时间的时间差(以天为单位)
-- 内层通过lag函数拉取上次订单时间,外层通过datediff计算时间差
SELECT
	id,
	uid,
	usdc,
	create_time,
	last_time,
	datediff(create_time, last_time) AS diff_time
FROM
	(
		SELECT
			id,
			uid,
			usdc,
			create_time,
			lag (create_time, 1) over w AS last_time
		FROM
			asset window w AS (
				PARTITION BY uid
				ORDER BY
					create_time
			)
	) tt

7.1.6 头尾函数

 

头尾函数——first_val(expr)/last_val(expr)。

 

• 用途：得到分区中的第一个/最后一个指定参数的值。
• 使用场景：查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额。

-- 其实就是(每个用户)首次订单金额和当前(最后一次)的订单金额
SELECT
	id,
	uid,
	usdc,
	create_time,
	first_value (usdc) over w AS FIRST_usdc,
	last_value (usdc) over w AS last_usdc
FROM
	asset window w AS (
		PARTITION BY uid
		ORDER BY
			create_time
	)

7.1.7 其他函数

 

其他函数——nth_value(expr,n)/ntile(n）。

nth_value(expr,n)

• 用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名。
• 应用场景：每个用户订单中显示本用户金额排名第二和第三的订单金额。

-- 获取用户订单金额排序的相应金额
SELECT
	id,
	uid,
	usdc,
	create_time,
	nth_value (usdc, 1) over w AS one_nth,
	nth_value (usdc, 2) over w AS two_nth,
	nth_value (usdc, 3) over w AS three_nth
FROM
	asset window w AS (PARTITION BY uid ORDER BY usdc)

ntile(n）

• 用途：将分区中的有序数据分为n个桶，记录桶号。
• 应用场景：将每个用户的订单按照订单金额分成3组。

SELECT
	id,
	uid,
	usdc,
	create_time,
	ntile (3) over w AS nf
FROM
	asset window w AS (PARTITION BY uid ORDER BY usdc)

7.1.8 聚合函数作为窗口函数

 

• 用途：在窗口中每条记录动态应用聚合函数(sum/avg/max/min/count)，可以动态计算在指定的窗口内的各种聚合函数值。
• 应用场景：每个用户按照订单id，截止到当前的累计订单金额/平均订单金额/最大订单金额/最小订单金额/订单数是多少

SELECT
	*
FROM
	(
		SELECT
			id,
			uid,
			usdc,
			create_time,
			SUM(usdc) over w AS sum1,
			AVG(usdc) over w AS avg1,
			MAX(usdc) over w AS max1,
			MIN(usdc) over w AS min1,
			COUNT(usdc) over w AS count1
		FROM
			asset window w AS (
				PARTITION BY uid
				ORDER BY
					create_time
			)
	) t
WHERE
	uid = '1574444774212150'
ORDER BY
	create_time DESC LIMIT 1

7.2 GROUPING运算符

7.2.1  ROLLUP--同时得出合计与小计

-- 获取每种商品的价格之和(小计)
-- 获取所有商品的价格之和(总计)
SELECT
	product_type,
	SUM(sale_price) AS sum_price,
	regist_date
FROM
	`product`
GROUP BY
	product_type,
	regist_date WITH ROLLUP

MySQL中不支持GROUPING运算符，仅支持ROLLUP