常用的Mysql操作一
各分组数据进行排序
在实际的业务中,我们可能会遇到一种场景,需要对数据按照某个字段分组,并再对组内的数据进行排序,这种情况下,常用的group by就不能使用了,group by之后只能对数据进行聚合,仅能保留一条数据。
这时我们需要一个新的功能–窗口函数,语法为 FUNC_NAME() OVER (PARTITION BY 分组子句 ORDER BY 排序子句 ROWS/RANGE 窗口子句)。
- FUNC_NAME可以是sum、avg、min、max、count等聚合函数,也可以是lead、lag行比较函数,还可以是rank、row_number、dense_rank等专用窗口函数。在这种状态下,函数的作用变成分析函数,不再是普通的聚合函数。
注意:row_number不会考虑相同数值并列情况,按照1、2、3、4顺序一直排下去;rank和dense_rank会考虑相同数值并列情况,不同的是如果出现并列,rank会跳过,按照1、2、2、4、4、6的顺序,而dense_rank是按照1、2、2、3、3、4的顺序
举例:将本年度不同月份的数据进行分组并按照某个字段列出排名
select *,dense_rank() over (partition by time_col order by order_col desc) as ranking from tb_name
- 如果存在窗口子句,那么前面的分析函数作用范围会进一步缩小到窗口子句规定的窗口。窗口子句存在ROWS和RANGE两种方式,但是其限定范围的句子是相同的(功能不同,后面解释)。限定范围包括 CURRENT ROW(当前行)、UNBOUNDED PRECEDING(第一行)、UNBOUNDED FOLLOWING(最后一行)、N PRECEDING(当前行的前N行)、N FOLLOWING(当前行后N行)
2.1 看下面两个语句,分别代表了不同的涵义
select *,sum(compute_col) over (partition by group_col order by order_col rows between current row and 1 following) as tmp_col from tb_name
select *,sum(compute_col) over (partition by vorup_col order by order_col range between 2 prededing and current row) from tb_name
上面两句话,第一句的意思是计算组内当前行和该行后一行两个值的和;第二句代表计算分组内在当前行值和当前行值-2区间范围内所有行值的和,即取值在[当前行值-2,当前行值]内的所有行的值的和。
注意:range后可以使用时间函数如range between interval 3 day preceding and interval 2 day following