常用函数：窗口函数与其他

qq_45269163

于 2024-07-07 08:29:20 发布

阅读量287

点赞数 11

分类专栏：大数据学习之路 # sql基础文章标签： sql mysql 数据库数据库开发大数据

本文链接：https://blog.csdn.net/qq_45269163/article/details/140210785

版权

大数据学习之路同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

sql基础

10 篇文章 0 订阅

订阅专栏

1、窗口函数

目的：为了实现分组聚合、排序、位置偏移等操作并保留原始数据内容，提高查询效率和代码可读性
功能：基于数据实现分区【partition by】，并对分区内部的数据进行基于窗口的排序【order by】、聚合【一对一】等操作并保留原始的数据行内容
语法：

func_name(args) over ( partition by col order by col [asc | desc] [window_size] )

说明
- partition by：指定按照某一列分区，分区类似于分组，相同分区的数据会放到一起
  - 关键字：每、每个、各个、不同、每种
  - 分组：group by + 聚合函数：分组必聚合：一组返回一条：每个部门薪资最高的
  - 分区：窗口函数 + partition by：只是将数据分类，不一定聚合：一组返回多条：每个部门薪资最高的前两个
- order by：指定每个分区内部按照哪一列进行排序
- window_size：指定窗口的大小，就是分区内部每条数据计算的范围
分类
- 窗口聚合函数：可以基于每个分区内部的窗口实现count/sum/max/min/avg等操作
- 窗口分析函数：可以基于每个分区实现排名标记等操作：row_number、rank、dense_rank、Nitle：排名问题
  - 每个部门薪资最高的前2名
  - 考虑重复排名问题
  - 查询每个部门薪资最高的前1/3的员工的信息
- 窗口偏移函数：可以基于每个分区内部实现相对位置的取值操作：first_value、last_value、lead、lag：同比环比问题、连续登录问题

2、窗口函数：窗口聚合

窗口聚合函数：sum、count、max、min、avg
- 函数：sum
- 功能：生成一列基于每个分区的窗口进行求和聚合的结果
- 语法：
- ```
sum(处理的列) over (partition by col order by col [window_size]) 
```
- 函数：count
- 功能：生成一列基于每个分区的窗口进行计数聚合的结果
- 语法：
- ```
count(处理的列) over (partition by col order by col [window_size]) 
```
- 函数：max
- 功能：生成一列基于每个分区的窗口进行求最大值聚合的结果
- 语法：
- ```
max(处理的列) over (partition by col order by col [window_size]) 
```
- 函数：min
- 功能：生成一列基于每个分区的窗口进行求最大值聚合的结果
- 语法：
- ```
min(处理的列) over (partition by col order by col [window_size]) 
```
- 函数：avg
- 功能：生成一列基于每个分区的窗口进行求平均值聚合的结果
- 语法：
- ```
avg(处理的列) over (partition by col order by col [window_size]) 
```

窗口范围

默认窗口

既有分区，又有排序：默认窗口是从分区第一行到当前行 【row between unbounded preceding and current row】

只有分区：默认窗口是从分区第一行到最后一行 => 整个分区 【row between unbounded preceding and unbounded following】

只有排序：从分区第一行到当前行，如果有重复排序值，直接累加

自定义窗口

rows between 起始位置 and 结束位置  / range between start and end

- preceding：前面的
- following：后面的
- current row：当前行
- unbounded preceding：从分区的第一行
- unbounded following：到分区的最后一行

rows between unbounded preceding and current row: 从分区的第一行到当前行
rows between 2 preceding and current row: 从前2行到当前行
rows between 3 preceding and 1 following: 从前3行到后1行， 一共5行
rows between current row and unbounded following:从当前行到最后一行

3、窗口函数：分析函数

窗口分析函数：row_number
- 功能：用于对每个分区内部进行编号，编号从1开始，不考虑重复值问题，如果值相同，编号不相同
- 场景：取每个分区内部的TopN
- 语法：
- ```
row_number() over (partition by col order by col)
```
- 示例：
- ```
-- 查询各科成绩前三名的记录（不考虑成绩并列情况）
SELECT
	t.* 
FROM
	( SELECT c_id, s_id, s_score, row_number ( ) over ( PARTITION BY c_id ORDER BY s_score DESC ) rk FROM score ) t 
WHERE
	t.rk IN ( 1, 2, 3 )
```
- 注意：窗口函数执行的顺序在group by之后，如果要对窗口函数的结果进行过滤，一般需要构建子查询
窗口分析函数：rank、dense_rank
- 功能：用于对每个分区内部进行编号，编号从1开始，考虑重复值问题，如果值相同，编号相同，留下空位
- 场景：取每个分区内部的TopN，允许并列排名，会留下空位
- 语法：
- ```
-- 考虑重复值问题，如果值相同，编号相同，留下空位
rank() over (partition by col order by col)
-- 考虑重复值问题，如果值相同，编号相同，**不留空位
dense_rank() over (partition by col order by col)
```
- 示例：
- ```
-- 按各科成绩进行排序，并显示排名
SELECT
	*,
	DENSE_RANK ( ) over ( PARTITION BY c_id ORDER BY s_score DESC ) rk_score 
FROM
	score


-- 查询学生的总成绩并进行排名
SELECT
	*,
	rank ( ) over ( ORDER BY sumscore DESC ) 
FROM
	( SELECT s_id, SUM( s_score ) sumscore FROM score GROUP BY s_id ) t
```
- 小结：row_number、rank、dense_rank三者的场景、功能、区别？
  - 功能：基于分区和排序基础上，对每个分区内部的数据进行编号
  - 场景：分区排名问题
  - 区别
    - row_number：不考虑重复值问题，值相同，排名不同
    - rank：考虑重复值问题，值相同，排名相同，留下空位
    - dense_rank：考虑重复值问题，值相同，排名相同，不留空位

4、窗口函数：偏移函数

窗口偏移函数：first_value
- 功能：用于取分区窗口内某一列的第一个值
- 场景：主要用于计算一些转换率问题
- 语法：
- ```
first_value(col) over (partition by col order by col)
```
窗口偏移函数：last_value
- 功能：用于取分区窗口内某一列的最后一个值
- 注意：窗口范围的问题
- 语法：
- ```
last_value(col) over (partition by col order by col)
```
窗口偏移函数：lead
- 功能：用于获取分区内某一列向后偏移N个单位的值
- 语法：
- ```
lead(某一列，N个单位，取不到的默认值) over (partition by col order by col)
```
窗口偏移函数：lag
- 功能：用于获取分区内某一列向前偏移N个单位的值
- 语法：
- ```
lead(某一列，N个单位，取不到的默认值) over (partition by col order by col)
```

5、视图、索引、执行顺序

视图
- 什么是视图：
  - 视图是一种只读的表
  - MySQL中的视图是一种虚拟表，其内容可能是从一个或多个现有表中选择、过滤、聚合等操作所得到的结果集
    - 实际表：内部存储的是这张表的数据
    - 虚拟表：视图中存储是Select语句
  - 用户可以把视图当做表一样进行查询，但是视图与表不同，视图本身不存储任何的数据内容，视图只存储了SQL语句
  - 可以理解为视图只是一个SQL语句，每次对视图操作时，是先通过SQL语句生成了临时表，然后再对临时表操作
- 视图的优点：
  - 简化查询：视图可以将多个表的数据组合成一个虚拟的表，用户可以通过单独的 SQL 语句来查询虚拟表，从而简化查询操作
  - 提高安全：通过使用视图，可以授予用户对特定列或行的访问权限，同时保护敏感数据免遭未经授权的访问
  - 数据独立：当需要修改底层表的结构时，使用视图可以隐藏这些变化，使得上层应用程序不需要做出相应的调整
  - 逻辑分离：视图允许开发者将复杂的查询逻辑分离出来，使得应用程序代码更加简洁易懂
- 语法：
  - 创建视图
  - ```
  create [ or replace ] view 视图名称
  as
  select ……
```
- 列举视图
- ```
show tables ;
```
  - 查询视图
  - ```
  -- 基础查询
  select * from db_other.view_order_detail;
  
  -- 分组聚合排序
  select
      u_id,
      count(distinct p_id) as p_cnt,
      count(o_id) as o_cnt
  from view_order_detail
  group by u_id
  having o_cnt > 1
  order by o_cnt desc
  limit 1;
```
- 删除视图
- ```
drop view if exists db_other.view_order_detail;
```
索引
- 索引分类：主键索引、外键索引、唯一索引、联合索引等
- 语法：
  - 创建索引：
  - ```
  CREATE INDEX index_name ON table_name (column1, column2, ...);
```
- 删除索引
- ```
DROP INDEX index_name ON table_name;
```

MySQL关键词执行顺序

书写顺序

select
	distinct
	聚合函数,
	窗口函数
from 数据表A
	join 数据表B on 关联条件
where 分组前过滤
group by 分组字段
having 分组后过滤
order by 排序字段
limit 分页查询

执行顺序

1) from
2) join
3) on
4) where
5) group by
6) 聚合函数
7) having
8) 窗口函数
9) select
10) distinct
11) order by
12) limit

限制：窗口函数不能放在同一条SQL语句的where、group by、having后使用，但是可以放在order by 中使用

qq_45269163

关注

11
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
常用函数：窗口函数与其他

指定按照某一列分区，分区类似于分组，关键字：每、每个、各个、不同、每种分组：group by + 聚合函数：分组必聚合：一组返回一条：每个部门薪资最高的分区：窗口函数 + partition by：只是将数据分类，不一定聚合：一组返回多条：每个部门薪资最高的前两个：指定每个按照哪一列进行排序：指定窗口的大小，就是分区内部每条数据计算的范围：可以基于每个分区内部的窗口实现count/sum/max/min/avg等操作。
复制链接

扫一扫