SQL开窗函数之基本用法和聚合函数

开窗函数

当我们需要进行一些比较复杂的子查询时，聚合函数就会非常的麻烦，因此可以使用开窗函数进行分组再运用函数查询。窗口函数既可以显示聚集前的数据，也可以显示聚集后的数据，可以在同一行中返回基础行的列值和聚合后的结果列

常见运用场景： 对班里同学成绩进行排序
常见的窗口函数

开窗函数基本形式

func_name(<parameter>) 
OVER(
[PARTITION BY <part_by_condition>] 
[ORDER BY <order_by_list> ASC|DESC]
[rows between ?? And ??]
)

字段解释

• func_name：需要进行计算的函数，在这里可以填入所需要的各种函数
• over()：对数据进行操作
  • partition by：根据某字段对数据进行分组
  • order by：每个窗口的数据根据某字段进行升序或降序排列
    • e.g. over(partition by xxx order by yyy): 按xxx进行分组，按列yyy进行排序
  • rows between ... and ...：细分窗口，选择当前分区的一个子集，通常当做滑动窗口使用
    • current row：当前行
    • unbounded preceding：分区中的第一行
    • unbounded following：分区中的最后一行
    • expr preceding：（当前行 - expr的值）的行
    • expr following：（当前行 + expr的值）的行

注：
排序子句后面缺少窗口子句，窗口规范默认是 rows between unbounded preceding and current row
排序子句和窗口子句都缺失，窗口规范默认是 rows between unbounded preceding and unbounded following

Partition by

Sales表

select *,
max(sales) over(partition by idate) as '每个月一个员工最大的销量',
max(sales) over(partition by iname) as '每个员工在这几个月里最大的销量'
from Sales;

Order by

Sales表

select *,
row_number() over(order by sales desc) as '按销量进行排序',
row_number() over(partition by idate order by sales desc) as '每个月按销量进行排序'
from Sales;

Rows between and

weather表

select *, 
avg(temperature) over(order by recordDate rows between current row and 1 following) as '今天和明天天气的均值',
avg(temperature) over(order by recordDate rows between 1 preceding and current row) as '昨天和今天天气的均值',
avg(temperature) over(order by recordDate rows between 2 preceding and 1 preceding) as '前天和昨天天气的均值',
avg(temperature) over(order by recordDate rows between current row and unbounded following) as '今天到表里未来天气的均值',
avg(temperature) over(order by recordDate rows between unbounded preceding and current row) as '第一条记录到今天的天气均值'
from weather;

聚合函数

Sales表

-- 这里举例了几个常见的聚合函数
select *, 
max(sales) over(partition by month(idate)) as max,
min(sales) over(partition by month(idate)) as min, 
avg(sales) over(partition by month(idate)) as avg,
sum(sales) over(partition by month(idate)) as sum 
from Sales;

这里主要用到的两个例子建表如下

-- Sales表
CREATE TABLE Sales
( 
idate date, 
iname char(2), 
sales int
); 
-- 向表中插入数据 
INSERT INTO Sales VALUES 
('2021/1/1', '丁一', 200), 
('2021/2/1', '丁一', 180), 
('2021/2/1', '李四', 100), 
('2021/3/1', '李四', 150), 
('2021/2/1', '刘猛', 180), 
('2021/3/1', '刘猛', 150), 
('2021/1/1', '王二', 200), 
('2021/2/1', '王二', 180), 
('2021/3/1', '王二', 300), 
('2021/1/1', '张三', 300), 
('2021/2/1', '张三', 280), 
('2021/3/1', '张三', 280); 

-- weather表
drop table if exists weather;
create table weather(
    id int,
    recordDate date,
    temperature int
);
insert into weather
values (1,'2015-01-01',10),
       (2,'2015-01-02',25),
       (3,'2015-01-03',20),
       (4,'2015-01-04',30);

参考来源
MySQL开窗函数
MySQL模块：开窗函数
MySQL基础(六)——MySQL之开窗函数