MySQL 8.0 新特性窗口函数

最新推荐文章于 2024-07-25 11:20:42 发布

弘弘弘弘～

最新推荐文章于 2024-07-25 11:20:42 发布

阅读量106

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/zhj2003_/article/details/132570734

版权

3.2 非聚合窗口函数对当前行框架中的行进行操作的例子

四、命名窗口

4.1 命名窗口的注意事项

五、窗口函数限制

一、非聚合窗口函数

1.1 常用非聚合窗口函数

CREATE TABLE sales (
    `year` INT,
    country VARCHAR(25),
    product VARCHAR(25),
    profit INT,
    total_profit INT,
    country_profit INT
);

INSERT INTO sales (year, country, product, profit, total_profit, country_profit)
VALUES
    (2000, 'Finland', 'Computer', 1500, 7535, 1610),
    (2000, 'Finland', 'Phone', 100, 7535, 1610),
    (2001, 'Finland', 'Phone', 10, 7535, 1610),
    (2000, 'India', 'Calculator', 75, 7535, 1350),
    (2000, 'India', 'Calculator', 75, 7535, 1350),
    (2000, 'India', 'Computer', 1200, 7535, 1350),
    (2000, 'USA', 'Calculator', 75, 7535, 4575),
    (2000, 'USA', 'Computer', 1500, 7535, 4575),
    (2001, 'USA', 'Calculator', 50, 7535, 4575),
    (2001, 'USA', 'Computer', 1200, 7535, 4575),
    (2001, 'USA', 'Computer', 1500, 7535, 4575),
    (2001, 'USA', 'TV', 100, 7535, 4575),
    (2001, 'USA', 'TV', 150, 7535, 4575);

# cume_dist() 累计分布值
select country, cume_dist() over(order by country_profit) as count_city
from sales;

#----------------------------------------
create table if not exists Scores (
	id int,
	last_name varchar(15),
	score decimal(4,2),
    course_id int
);

insert into Scores
values
	(1, 'Amy', 91.5),
	(2, 'Bob', 95),
	(3, 'Alex', 93.5),
	(4, 'Scot', 95),
	(5, 'Tony', 91.5),
	(6, 'Windy', 97),
	(7, 'Sneak', 99.5),
	(8, 'Swift', 90);

# row_number() 为结果集中的每一行分配一个唯一的整数值，不考虑任何排序条件
# dense_rank() 计算结果集中每一行的密集排名（按指定的排序条件）
# 即使有相同的值，也不会跳过排名。
select row_number() over(order by score desc) as id, last_name, score, course_id,
	dense_rank() over(order by score desc) as score_rank
from Scores;

# rank（）计算结果集中每一行的排名（按指定的排序条件），如果有相同的值，则排名相同，下一个排名会
# 跳过相应数量的排名。
select id, last_name, score, rank() over(order by score desc) as score_rank
from Scores;
# ntile() 
# NTILE函数用于将结果集按照指定的数量（n）分成若干个组，并为每个行分配一个组号。
select id, last_name, score, ntile(4) over(order by score) as tile
from Scores;

二、窗口函数概念和语法

2.1 聚合函数与窗口函数的对比

A window function performs an aggregate-like operation on a set of query rows. However, whereas an aggregate operation groups query rows into a single result row, a window function produces a result for each query row:

The row for which function evaluation occurs is called the current row.

The query rows related to the current row over which function evaluation occurs comprise the window for the current row.

For example, using the sales information table, these two queries perform aggregate operations that produce a single global sum for all rows taken as a group, and sums grouped per country:

以上为MySQL8.0技术文档关于窗口函数概念的引入，部分地方翻译有误，所以直接拿来这部分的文档。

上述内容可以引入一个例子，便于理解

使用聚合函数sum() 查询总利润以及各国家的总利润

使用聚合窗口函数

sum(profit) over() 与sum(profit) 的作用相同

sum(profit) over(partition by country) 其中，[partition by columnName] 是按照列名进行分区

与group by country 不同的地方，我们可以比较看出，使用窗口函数的查询结果对于同一城市的每一行并没有合并，而使用group by 会把同一个城市的查询结果合并为一行。如果我们想给同一个城市下产品的利润进行排名，使用group by 是很难进行排序的，而窗口函数的好处不止如此，我们可以结合上述讲解的窗口函数完成各种各样的需求。

2.2 部分聚合窗口函数的示例

# sum()
# 用于计算某一列在指定分区内按照排序条件进行累加的结果，并将结果作为一个新的列sum_value返回。
select id, last_name, score, course_id,
	sum(score) over(partition by course_id) as sum_score
from Scores;

# avg()
select row_number() over w as id,course_id,
	avg(score) over w as avg_score
from Scores
window w as (partition by course_id);

# count()
select row_number() over(partition by course_id) as id, course_id,
	count(1) over(partition by course_id) as count_num
from Scores;

# min() max()
select country, product, `year`, profit,
	min(profit) over w as min_profit,
	max(profit) over w as max_profit
from sales 
window w as (partition by `year`, product);

2.3 窗口函数的语法

这部分不容易理解，直接上例子

在使用窗口函数时，over（）部分声明的是对窗口的规范，第一种是直接写了一个含有规范的over(),第二种则是在最后写了一个window w as()，对于第二种的复用性更好，如果我们查询的字段是好几个窗口函数并且对于窗口的约束都是相同的，那么使用方法二是更加简洁的。

order by 与partition by 的语法相同，不再介绍。详细语法可以参考MySQL8.0技术文档。

三、窗口函数框架规范

3.1 框架子句

在窗口函数定义时，还包括框架子句。帧是当前分区的子集，frame子句指定如何定义该子集。帧是根据当前行确定的，这使得帧能够在分区内移动，这取决于当前行在其分区内的位置。

例如：

通过将帧定义为从分区开始到当前行的所有行，您可以计算每行的运行总数。
通过将帧定义为扩展当前行两侧的N行，可以计算滚动平均值。

下面的查询演示了使用移动帧来计算每组时间顺序值中的运行总数，以及从当前行和紧接在它之前和之后的行计算的滚动平均值:

CREATE TABLE observations (
  time TIME,
  subject VARCHAR(255),
  val INT,
  running_total INT,
  running_average DECIMAL(10,4)
);

INSERT INTO observations (time, subject, val, running_total, running_average)
VALUES
  ('07:00:00', 'st113', 10, 10, 9.5000),
  ('07:15:00', 'st113', 9, 19, 14.6667),
  ('07:30:00', 'st113', 25, 44, 18.0000),
  ('07:45:00', 'st113', 20, 64, 22.5000),
  ('07:00:00', 'xh458', 0, 0, 5.0000),
  ('07:15:00', 'xh458', 10, 10, 5.0000),
  ('07:30:00', 'xh458', 5, 15, 15.0000),
  ('07:45:00', 'xh458', 30, 45, 20.0000),
  ('08:00:00', 'xh458', 25, 70, 27.5000);
  
SELECT
         time, subject, val,
         SUM(val) OVER (PARTITION BY subject ORDER BY time
                        ROWS UNBOUNDED PRECEDING)
           AS running_total,
         AVG(val) OVER (PARTITION BY subject ORDER BY time
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
           AS running_average
       FROM observations;

对于列，在第一行之前或在最后一行之后没有帧行。在这些情况下，AVG()计算可用行的平均值。

3.2 非聚合窗口函数对当前行框架中的行进行操作的例子

SELECT
         time, subject, val,
         first_value(val)  over w as 'first',
         last_value(val) over w as 'last',
         nth_value(val, 2) over w as 'second'
       FROM observations
       WINDOW w AS (PARTITION BY subject ORDER BY time
                    ROWS UNBOUNDED PRECEDING);
select row_number() over(partition by score) as id
from scores;