黑马Python数据分析笔记：窗口函数与自关联查询详解

一、窗口函数概述

窗口函数是MySQL 8.x版本引入的重要特性，主要用于在不改变原表行数的前提下为数据表新增一列。新增列的内容取决于所使用的具体窗口函数。

二、基本语法结构

窗口函数的标准写法如下：

窗口函数 OVER ([PARTITION BY 分组字段] ORDER BY 排序字段 [ASC | DESC])

• PARTITION BY：用于指定分组依据。若省略，则默认将整个表作为一组进行计算。
• ORDER BY：定义组内数据的排序方式。若未指定，则表示对组内所有行统一处理；若指定，则从第一行到当前行逐行累计计算。

三、常用窗口函数及作用

以下是一些常见的窗口函数及其功能说明：

• ROW_NUMBER()：生成连续的行号序列，如 1, 2, 3, 4……
• RANK()：实现“跳跃式”排名（稀疏排名），相同值并列且占用多个名次，后续排名跳过相应数量。
• DENSE_RANK()：实现“紧密式”排名（密集排名），相同值并列但不跳过后续名次。

四、通俗实例解析

假设有一组成绩数据：100, 90, 90, 60，使用不同函数得到的排名结果如下：

• ROW_NUMBER() → 1, 2, 3, 4
• RANK() → 1, 2, 2, 4
• DENSE_RANK() → 1, 2, 2, 3

# 准备数据 -> 建库, 切库, 查表
drop database day03;
create database day03;
use day03;
show tables;

# 准备数据 -> 建表, 添加数据.
create table employee (empid int,ename varchar(20) ,deptid int ,salary decimal(10,2));

insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'赵云',10,4500.00);
insert into employee values(2,'张飞',10,3500.00);
insert into employee values(2,'关羽',10,4500.00);

insert into employee values(3,'曹操',20,1900.00);
insert into employee values(4,'许褚',20,4800.00);
insert into employee values(5,'张辽',20,6500.00);
insert into employee values(6,'徐晃',20,14500.00);

insert into employee values(7,'孙权',30,44500.00);
insert into employee values(8,'周瑜',30,6500.00);
insert into employee values(9,'陆逊',30,7500.00);

# 查看数据.
select * from employee;

五、关键细节总结

1. 窗口函数本质上是在原有表基础上增加一列，该列的具体内容由所选函数决定。
2. 当未指定 PARTITION BY 时，操作范围为全表数据；一旦指定，则按分组字段划分区域，在每个组内部独立运算。
3. 若未使用 ORDER BY，则聚合或排名操作会作用于整个分组的所有记录；若使用了，则从组内首行开始，逐行累积至当前行。
4. 除了上述三种排名函数外，还可尝试结合其他函数使用，例如：
   - 聚合类：COUNT(), SUM(), AVG(), MAX(), MIN()
   - 分桶类：NTILE(n) 将每组数据均分为 n 份
   - 位移类：LAG() 获取前几行值，LEAD() 获取后几行值
   - 极值类：FIRST_VALUE(), LAST_VALUE() 取组内的首个或末尾值

六、核心掌握点

对于窗口函数的学习，建议重点掌握以下两个应用场景：

1. 分组内的排名分析
2. 基于分组排名提取TopN记录

# 案例1: 分组排名,  需求: 按照部门id(deptid)分组, 按照工资(salary)降序排名.
# 场景1: 如何给表新增1列.
select *, '夯哥' from employee;
select *, 10 / 3 from employee;
select *, deptid + 100 from employee;

# 场景2: 引入 窗口函数.
select
    *,
    # sum(salary) over () as total_sum                                          # 没写partition by, 统计全表
    # sum(salary) over (partition by deptid) as total_sum                       # 写了partition by, 统计全组
    sum(salary) over (partition by deptid order by salary desc) as total_sum    # 写了order by, 统计全组
from
    employee;

# 场景3: 分组排名:  按照部门id(deptid)分组, 按照工资(salary)降序排名.
select
    *,
    row_number() over(partition by deptid order by salary desc) as rn,
    rank() over(partition by deptid order by salary desc) as rk,
    dense_rank() over(partition by deptid order by salary desc) as dr
from
    employee;

七、分组排名求TopN

通过结合 DENSE_RANK() 或 ROW_NUMBER() 与 PARTITION BY，可以轻松实现“每组取前N条”的需求。

解决思路通常包括：

• 先利用窗口函数为每组数据打上排名标签
• 再通过外层查询筛选出排名小于等于N的记录

# 场景4: 分组排名求TopN,  需求: 找出每组工资最高的2人的信息(考虑并列).
# 如下代码, 思路没问题, 但是语法格式有问题, 因为where后边的字段必须是表中 已有的字段.
select
    *,
    rank() over(partition by deptid order by salary desc) rk
from
    employee
where
    rk <= 2;

实际解决方案示例：

# 思路1: 用 子查询 解决.
select * from (
    select
        *,
        rank() over(partition by deptid order by salary desc) rk
    from
        employee
) t1 where rk <= 2;

八、CTE 公用表表达式

CTE（Common Table Expression）是一种SQL中的临时结果集命名机制，能够显著提升复杂查询的可读性和维护性，并支持递归逻辑。

一句话理解： CTE 相当于给一个子查询赋予名称，使其可以在主查询中多次引用，使逻辑更清晰、结构更简洁。

# 思路2: 用CTE 公共表表达式, 可以把常用的数据集封装成新表, 方便操作.
/*
格式:
    with 表名1 as (select .....),
         表名2 as (select ....),
         表名3 as ....
    select * from t1 ....;          # 这里正常写SQL, 使用上述的 表名即可.
 */
with t1 as (select *, rank() over(partition by deptid order by salary desc) rk from employee)
select * from t1 where rk <= 2;

# 扩展: 1个需求表示 CTE表达式的强大之处.
with t1 as (select * from employee),
     t2 as (select * from employee where deptid=10),
     t3 as (select * from employee where deptid=20),
     t4 as (select * from employee where deptid=30),
     t5 as (select *, sum(salary) over() as total_salary from employee)
select * from t5;

九、自关联查询详解

1. 概念解释

自关联查询，也称自连接查询，指的是同一张表与其自身进行连接操作。常用于处理具有层级关系或需成对比较的数据场景，例如：

• 员工与直属经理的关系（员工ID 与 经理ID 同属一张员工表）
• 树形结构中的父子节点（如分类目录、组织架构）
• 城市间距离矩阵等成对关系数据

核心思想： 将一张表通过别名（Alias）视为两张不同的表来使用，从而实现自我连接。

2. 应用案例：省市区行政区域表设计

传统做法可能会将省、市、区分别建立三张独立的表，但这会导致结构冗余且扩展困难。更优方案是将所有层级信息整合到一张表中，配合自关联完成多级查询。

合并后的表结构包含三个关键字段：

• 区域自身ID：唯一标识本级区域
• 区域名：如“河南省”、“郑州市”
• 父级ID：指向其上级区域的ID，顶级区域（如省）父ID设为0

示例数据：

3. 数据导入提示

将.sql格式的数据文件导入数据库时，请遵循以下高效步骤：

右键点击目标.sql文件 → 选择“运行” → 在弹出窗口中选择目标数据库源 → 点击“应用”并执行“运行”。
注意：避免手动复制SQL语句逐条执行，效率极低。

# 查表.
show tables;
# 查看表数据
select * from areas;

# 1. 查看河南省的信息
select * from areas where title = '河南省';

# 2. 查看河南省所有的市.
select * from areas where pid = '410000';

# 3. 查看新乡市所有的县区.
select * from areas where pid = '410700';

# 4. 查看所有省, 所有市, 所有县区的信息.
select
    province.id, province.title,        # 省级的id, 名字
    city.id, city.title,                # 市级的id, 名字
    county.id, county.title             # 县区级的id, 名字
from
    areas as county         # 县区表
join
    areas as city on county.pid = city.id       # 市级表
join
    areas as province on city.pid = province.id # 省级表
;


# 5. 精准查找信息.
select
    province.id, province.title,        # 省级的id, 名字
    city.id, city.title,                # 市级的id, 名字
    county.id, county.title             # 县区级的id, 名字
from
    areas as county         # 县区表
join
    areas as city on county.pid = city.id       # 市级表
join
    areas as province on city.pid = province.id # 省级表
where
    county.id = '230221';         # 身份证号前6位

4. 补充知识点

在使用 COUNT() 函数时需特别注意：它仅统计指定列中的非空值（即忽略NULL值）。