当前位置:   article > 正文

MySQL8.0开窗函数_mysqlrange between

mysqlrange between

作为一名数据分析师,日常工作中经常会遇到类似这样的需求:
怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比?累计求和如何计算?
对于这样的需求,使用传统的SQL实现起来比较困难。这类需求都有一个共同的特点,需要在单表中满足某些条件的结果集内部做一些函数操作,不是简单的表连接,也不是简单的聚合可以实现的,通常费了大半天时间写出来一堆长长的晦涩难懂的SQL,且性能低下,难以维护。要解决此类问题,最方便的就是使用开窗函数

开窗函数简介

MySQL从8.0开始支持开窗函数,这个功能在大多商业数据库如Oracle和PostgreSQL中早已支持,也叫分析函数。

什么是开窗

它可以理解为记录集合,开窗函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算,只不过它更具灵活性,它对数据的每一行,都使用与该行相关的行进行计算并返回计算结果。

开窗函数和普通聚合函数的区别

  • 聚合函数是将多条记录聚合为一条;而开窗函数是每条记录都会执行,有几条记录执行完还是几条。
  • 聚合函数也可以用于开窗函数中。
create table order_tab(
	order_id int,
	user_no varchar(3),
	amount int,
	create_date date
);

insert into order_tab values
(1,'001',100,'2019-01-01'),
(2,'001',300,'2019-01-02'),
(3,'001',500,'2019-01-02'),
(4,'001',800,'2019-01-03'),
(5,'001',900,'2019-01-04'),
(6,'002',500,'2019-01-03'),
(7,'002',600,'2019-01-04'),
(8,'002',300,'2019-01-10'),
(9,'002',800,'2019-01-16'),
(10,'002',800,'2019-01-22');

select * from order_tab;
+----------+---------+--------+-------------+
| order_id | user_no | amount | create_date |
+----------+---------+--------+-------------+
|        1 | 001     |    100 | 2019-01-01  |
|        2 | 001     |    300 | 2019-01-02  |
|        3 | 001     |    500 | 2019-01-02  |
|        4 | 001     |    800 | 2019-01-03  |
|        5 | 001     |    900 | 2019-01-04  |
|        6 | 002     |    500 | 2019-01-03  |
|        7 | 002     |    600 | 2019-01-04  |
|        8 | 002     |    300 | 2019-01-10  |
|        9 | 002     |    800 | 2019-01-16  |
|       10 | 002     |    800 | 2019-01-22  |
+----------+---------+--------+-------------+
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34

示例:查询每个用户的订单总金额

select *,sum(amount) over(partition by user_no) sum_amount 
from order_tab;
+----------+---------+--------+-------------+------------+
| order_id | user_no | amount | create_date | sum_amount |
+----------+---------+--------+-------------+------------+
|        1 | 001     |    100 | 2019-01-01  |       2600 |
|        2 | 001     |    300 | 2019-01-02  |       2600 |
|        3 | 001     |    500 | 2019-01-02  |       2600 |
|        4 | 001     |    800 | 2019-01-03  |       2600 |
|        5 | 001     |    900 | 2019-01-04  |       2600 |
|        6 | 002     |    500 | 2019-01-03  |       3000 |
|        7 | 002     |    600 | 2019-01-04  |       3000 |
|        8 | 002     |    300 | 2019-01-10  |       3000 |
|        9 | 002     |    800 | 2019-01-16  |       3000 |
|       10 | 002     |    800 | 2019-01-22  |       3000 |
+----------+---------+--------+-------------+------------+
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

我们知道聚合函数对一组值执行计算并返回一个值,但有时候一组数据只返回一个结果值并不能满足需求。
示例:查询每个用户按时间顺序的累计订单金额

select *,sum(amount) over(partition by user_no order by create_date) sum_amount
from order_tab;
+----------+---------+--------+-------------+------------+
| order_id | user_no | amount | create_date | sum_amount |
+----------+---------+--------+-------------+------------+
|        1 | 001     |    100 | 2019-01-01  |        100 |
|        2 | 001     |    300 | 2019-01-02  |        900 |
|        3 | 001     |    500 | 2019-01-02  |        900 |
|        4 | 001     |    800 | 2019-01-03  |       1700 |
|        5 | 001     |    900 | 2019-01-04  |       2600 |
|        6 | 002     |    500 | 2019-01-03  |        500 |
|        7 | 002     |    600 | 2019-01-04  |       1100 |
|        8 | 002     |    300 | 2019-01-10  |       1400 |
|        9 | 002     |    800 | 2019-01-16  |       2200 |
|       10 | 002     |    800 | 2019-01-22  |       3000 |
+----------+---------+--------+-------------+------------+
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

开窗函数分类

在这里插入图片描述
按照函数功能不同,MySQL支持的开窗函数分为如下几类:

  • 序号函数:row_number() / rank() / dense_rank()
  • 分布函数:percent_rank() / cume_dist()
  • 前后函数:lag() / lead()
  • 头尾函数:first_value() / last_value()
  • 其他函数:nth_value() / nfile()

开窗函数使用

语法

开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [<窗口分区>])

    开窗函数的一个概念是当前行,当前行属于某个窗口,窗口由
    over关键字用来指定函数执行的窗口范围,如果后面括号中什么都不写,则意味着窗口包含满足where条件的所有行,开窗函数基于所有行进行计算;如果不为空,则有三个参数来设置窗口:

    • partition by子句:按照指定字段进行分区,两个分区由边界分隔,开窗函数在不同的分区内分别执行,在跨越分区边界时重新初始化。
    • order by子句:按照指定字段进行排序,开窗函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用,也可以单独使用。
    • frame子句:当前分区的一个子集,用来定义子集的规则,通常用来作为滑动窗口使用。

    示例:查询每个用户按下单时间顺序,前一行和后一行记录的平均订单金额

    select *,avg(amount) over(partition by user_no order by create_date 
      声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/AllinToyou/article/detail/702294
      推荐阅读
      相关标签
        

      闽ICP备14008679号