SQL-窗口函数合集

2024-06-13 00:28
文章标签 sql 合集 窗口 函数 database

本文主要是介绍SQL-窗口函数合集，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

目录

  • 1.窗口函数简介
  • 2.窗口的定义
  • 3.相关题目示例
    • 3.1 PERCENT_RANK()
      • 2346 以百分比计算排名
    • 3.2 FIRST_VALUE()/LAST_VALUE()/NTH_VALUE()
      • 2388 将表中的空值更改为前一个值

1.窗口函数简介

MySQL 开窗函数（Window Functions）是 MySQL 8.0 版本引入的一个强大特性，它可以用于计算聚合的同时提供数据行的上下文信息。开窗函数可以分为以下几类：

  • 聚合开窗函数：SUM(), AVG(), MIN(), MAX() 。
  • 排名开窗函数：ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK() 。
  • 首尾开窗函数：LEAD(), LAG(),LAST_VALUE(),FIRST_VALUE(),NTH_VALUE()。
  • 其他：CUME_DIST() 、NTILE()。

窗口函数示例1：

mysql> SELECTtime, subject, val,SUM(val) OVER (PARTITION BY subject ORDER BY timeROWS UNBOUNDED PRECEDING)AS running_total,AVG(val) OVER (PARTITION BY subject ORDER BY timeROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)AS running_averageFROM observations;
+----------+---------+------+---------------+-----------------+
| time     | subject | val  | running_total | running_average |
+----------+---------+------+---------------+-----------------+
| 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 |
+----------+---------+------+---------------+-----------------+

窗口函数示例2：

mysql> SELECTtime, subject, val,FIRST_VALUE(val)  OVER w AS 'first',LAST_VALUE(val)   OVER w AS 'last',NTH_VALUE(val, 2) OVER w AS 'second',NTH_VALUE(val, 4) OVER w AS 'fourth'FROM observationsWINDOW w AS (PARTITION BY subject ORDER BY timeROWS UNBOUNDED PRECEDING);
+----------+---------+------+-------+------+--------+--------+
| time     | subject | val  | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
| 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
| 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
| 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
| 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
| 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
| 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
| 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
| 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
+----------+---------+------+-------+------+--------+--------+

2.窗口的定义

窗口的单位(frame unit)：

  • ROWS：表示当前行和 frame 行之间的偏移量是行号之间的差异
  • RANGE：表示当前行和 frame 行之间的偏移量是行值与当前行值之间的差异

窗口的范围：

frame_between:BETWEEN frame_start AND frame_endframe_start, frame_end: {CURRENT ROW| UNBOUNDED PRECEDING| UNBOUNDED FOLLOWING| expr PRECEDING| expr FOLLOWING
}

窗口参数示例：

10 PRECEDING
INTERVAL 5 DAY PRECEDING
5 FOLLOWING
INTERVAL '2:30' MINUTE_SECOND FOLLOWING

注： 如果使用的是RANGE，则需要根据窗口排序中的列，选择对应的时间单位

常用的时间单位：MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR…

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);-> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);-> '2017-05-01'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',->                 INTERVAL 1 SECOND);-> '2021-01-01 00:00:00'
mysql> SELECT DATE_ADD('2018-12-31 23:59:59',->                 INTERVAL 1 DAY);-> '2019-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',->                 INTERVAL '1:1' MINUTE_SECOND);-> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2025-01-01 00:00:00',->                 INTERVAL '1 1:1:1' DAY_SECOND);-> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',->                 INTERVAL '-1 10' DAY_HOUR);-> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);-> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',->            INTERVAL '1.999999' SECOND_MICROSECOND);-> '1993-01-01 00:00:01.000001'

3.相关题目示例

3.1 PERCENT_RANK()

PERCENT_RANK()函数返回一个从0到1的数字。

对于指定的行,PERCENT_RANK()计算行的等级减1,除以评估的分区或查询结果集中的行数减1: (rank - 1) / (total_rows - 1) 在此公式中,rank是指定行的等级,total_rows是要计算的行数。

2346 以百分比计算排名

表: Students

+---------------+------+
| Column Name   | Type |
+---------------+------+
| student_id    | int  |
| department_id | int  |
| mark          | int  |
+---------------+------+

student_id 包含唯一值。
该表的每一行都表示一个学生的 ID，该学生就读的院系 ID，以及他们的考试分数。

编写一个解决方案，以百分比的形式报告每个学生在其部门的排名，其中排名的百分比使用以下公式计算:

(student_rank_in_the_department - 1) * 100 / (the_number_of_students_in_the_department - 1)。 percentage 应该 四舍五入到小数点后两位。

student_rank_in_the_department 由 mark 的降序决定，mark 最高的学生是 rank 1。如果两个学生得到相同的分数，他们也会得到相同的排名。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1:

输入:
Students 表:

+------------+---------------+------+
| student_id | department_id | mark |
+------------+---------------+------+
| 2          | 2             | 650  |
| 8          | 2             | 650  |
| 7          | 1             | 920  |
| 1          | 1             | 610  |
| 3          | 1             | 530  |
+------------+---------------+------+

输出:

+------------+---------------+------------+
| student_id | department_id | percentage |
+------------+---------------+------------+
| 7          | 1             | 0.0        |
| 1          | 1             | 50.0       |
| 3          | 1             | 100.0      |
| 2          | 2             | 0.0        |
| 8          | 2             | 0.0        |
+------------+---------------+------------+

解释:
对于院系 1:

  • 学生 7:percentage = (1 - 1)* 100 / (3 - 1) = 0.0
  • 学生 1:percentage = (2 - 1)* 100 / (3 - 1) = 50.0
  • 学生 3:percentage = (3 - 1)* 100 / (3 - 1) = 100.0
    对于院系 2:
  • 学生 2: percentage = (1 - 1) * 100 / (2 - 1) = 0.0
  • 学生 8: percentage = (1 - 1) * 100 / (2 - 1) = 0.0

答案：

select 	student_id,department_id,round((percent_rank() over (partition by department_id order by mark desc))*100,2) as percentage
from Students

3.2 FIRST_VALUE()/LAST_VALUE()/NTH_VALUE()

FIRST_VALUE() 函数的作用是返回子集中第一行的指定列数据，该函数的语法如下：


FIRST_VALUE(expr)
OVER ([partition_definition] [order_definition] [frame_clause]
)

其中，expr 为要获取数据的列明或者表达式，partition_definition 和 partition_definition 与 ROW_NUMBER() 函数一致；

frame_clause 的语法如下：

frame_unit {<frame_start>|<frame_between>}

LAST_VALUE() 和 FIRST_VALUE() 十分类似，区别在于 LAST_VALUE() 返回的是子集中的最后一条数据的指定列数据

NTH_VALUE() 的作用是获取指定 frame 中的第
N
个记录行的指定数据，对应的函数语法如下所示：


NTH_VALUE(expr, N)
OVER ([partition_definition] [order_definition] [frame_clause]
)

2388 将表中的空值更改为前一个值

表: CoffeeShop

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| drink       | varchar |
+-------------+---------+

id 是该表的主键（具有唯一值的列）。
该表中的每一行都显示了订单 id 和所点饮料的名称。一些饮料行为 null。

编写一个解决方案将 drink 的 null 值替换为前面最近一行不为 null 的 drink。保证表第一行的 drink 不为 null。

返回 与输入顺序相同的 结果表。

查询结果格式示例如下。

示例 1:

输入:
CoffeeShop 表:

+----+-------------------+
| id | drink             |
+----+-------------------+
| 9  | Rum and Coke      |
| 6  | null              |
| 7  | null              |
| 3  | St Germain Spritz |
| 1  | Orange Margarita  |
| 2  | null              |
+----+-------------------+

输出:

+----+-------------------+
| id | drink             |
+----+-------------------+
| 9  | Rum and Coke      |
| 6  | Rum and Coke      |
| 7  | Rum and Coke      |
| 3  | St Germain Spritz |
| 1  | Orange Margarita  |
| 2  | Orange Margarita  |
+----+-------------------+

解释:
对于 ID 6，之前不为空的值来自 ID 9。我们将 null 替换为 “Rum and Coke”。
对于 ID 7，之前不为空的值来自 ID 9。我们将 null 替换为 “Rum and Coke”。
对于 ID 2，之前不为空的值来自 ID 1。我们将 null 替换为 “Orange Margarita”。
请注意，输出中的行与输入中的行相同。

答案：


select id,first_value(drink) over(partition by group_id order by row_id) as drink
from(select *,sum(IF(drink is null, 0, 1)) over(order by row_id) as group_idfrom(select *,row_number() over() as row_idfrom coffeeshop) t0) t1
;

这篇关于SQL-窗口函数合集的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1055774。

相关文章

mysql索引四（组合索引）

mysql索引四（组合索引）

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；组合索引，即一个索引包含多个列。 因为有事，下面内容全部转自：https://www.cnblogs.com/farmer-cabbage/p/5793589.html 为了形象地对比单列索引和组合索引，为表添加多个字段： 　　 CREATE TABLE mytable( ID INT NOT NULL, use
阅读更多...
mysql索引三（全文索引）

mysql索引三（全文索引）

前面分别介绍了mysql索引一（普通索引）、mysql索引二（唯一索引）。 本文学习mysql全文索引。 全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。 在MySql中，创建全文索引相对比较简单。例如：我们有一个文章表（article），其中有主键ID（
阅读更多...
mysql索引二（唯一索引）

mysql索引二（唯一索引）

前文中介绍了MySQL中普通索引用法，和没有索引的区别。mysql索引一（普通索引） 下面学习一下唯一索引。 创建唯一索引的目的不是为了提高访问速度，而只是为了避免数据出现重复。唯一索引可以有多个但索引列的值必须唯一，索引列的值允许有空值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE，把它定义为一个唯一索引。 添加数据库唯一索引的几种
阅读更多...
mysql索引一（普通索引）

mysql索引一（普通索引）

mysql的索引分为两大类，聚簇索引、非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不同。聚簇索引能够提高多行检索的速度、非聚簇索引则对单行检索的速度很快。         在这两大类的索引类型下，还可以降索引分为4个小类型：         1，普通索引：最基本的索引，没有任何限制，是我们经常使用到的索引。         2，唯一索引：与普通索引
阅读更多...
【操作系统】信号Signal超详解|捕捉函数

【操作系统】信号Signal超详解|捕捉函数

🔥博客主页： 我要成为C++领域大神🎥系列专栏：【C++核心编程】 【计算机网络】 【Linux编程】 【操作系统】 ❤️感谢大家点赞👍收藏⭐评论✍️ 本博客致力于知识分享，与更多的人进行学习交流 ​ 如何触发信号 信号是Linux下的经典技术，一般操作系统利用信号杀死违规进程，典型进程干预手段，信号除了杀死进程外也可以挂起进程 kill -l 查看系统支持的信号
阅读更多...
【服务器运维】MySQL数据存储至数据盘

【服务器运维】MySQL数据存储至数据盘

查看磁盘及分区 [root@MySQL tmp]# fdisk -lDisk /dev/sda: 21.5 GB, 21474836480 bytes255 heads, 63 sectors/track, 2610 cylindersUnits = cylinders of 16065 * 512 = 8225280 bytesSector size (logical/physical)
阅读更多...
java中查看函数运行时间和cpu运行时间

java中查看函数运行时间和cpu运行时间

android开发调查性能问题中有一个现象，函数的运行时间远低于cpu执行时间，因为函数运行期间线程可能包含等待操作。native层可以查看实际的cpu执行时间和函数执行时间。在java中如何实现？ 借助AI得到了答案 import java.lang.management.ManagementFactory;import java.lang.management.Threa
阅读更多...
SQL Server中，查询数据库中有多少个表，以及数据库其余类型数据统计查询

SQL Server中，查询数据库中有多少个表，以及数据库其余类型数据统计查询

sqlserver查询数据库中有多少个表 sql server 数表:select count(1) from sysobjects where xtype='U'数视图:select count(1) from sysobjects where xtype='V'数存储过程select count(1) from sysobjects where xtype='P' SE
阅读更多...
SQL Server中，always on服务器的相关操作

SQL Server中，always on服务器的相关操作

在SQL Server中，建立了always on服务，可用于数据库的同步备份，当数据库出现问题后，always on服务会自动切换主从服务器。 例如192.168.1.10为主服务器，12为从服务器，当主服务器出现问题后，always on自动将主服务器切换为12，保证数据库正常访问。 对于always on服务器有如下操作： 1、切换主从服务器：假如需要手动切换主从服务器时（如果两个服务
阅读更多...
SQL Server中，isnull（）函数以及null的用法

SQL Server中，isnull（）函数以及null的用法

SQL Serve中的isnull()函数：          isnull(value1,value2)         1、value1与value2的数据类型必须一致。         2、如果value1的值不为null，结果返回value1。         3、如果value1为null，结果返回vaule2的值。vaule2是你设定的值。        如
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2