郑州做网站的多不多,临沂做网站优化,做任务佣金网站源码,网站开发工具是什么目录 1.窗口函数简介2.窗口的定义3.相关题目示例3.1 PERCENT_RANK()2346 以百分比计算排名 3.2 FIRST_VALUE()/LAST_VALUE()/NTH_VALUE()2388 将表中的空值更改为前一个值 1.窗口函数简介
MySQL 开窗函数#xff08;Window Functions#xff09;是 MySQL 8.0 版本引入的一个… 目录 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 fourthFROM 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.0000013.相关题目示例
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
;
