窗口函数的简介和使用_窗口函数怎么使用-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_72638029/article/details/143381340

1、什么是窗口函数？

在SQL中，窗口函数用于对数据集中的某些记录进行分组，并在分组内计算一些聚合值，而不影响原始数据行的显示。窗口函数在SQL中特别有用，因为它可以在不改变行结构的情况下对数据进行排序、分区和聚合。

窗口函数主要由四部分组成：

函数类型（如：ROW_NUMBER, RANK, SUM, AVG等）— 指定计算方式。
OVER子句— 表明这是一个窗口函数。
PARTITION BY子句（可选）— 用于划分分组的依据。
ORDER BY子句（可选）— 定义分组内的排序方式。

ROW_NUMBER() 是一个常见的窗口函数，它为每组中的每一行分配一个唯一的行号。行号从1开始，按指定的排序规则依次分配。

窗口函数的基本语法是：

<窗口函数>() OVER ([PARTITION BY <分区列>] ORDER BY <排序列>)

2、窗口函数的使用

背景：产品提供了一批购买了签到福利套餐的用户，这些用户在购买后并没有进行签到。

解决问题：查询日志定位出签到弹窗有无弹出。我们需要在日志在找到用户的唯一标识，此时可以查询出用户最近一次的登录记录，然后拿到唯一标识，由于用户较多，并且一个用户有多条登录记录。所以我们去重并查询出最新的那条。

SELECT member_no, user_agent, login_time 
FROM (  
    SELECT member_no, user_agent, login_time,  
           ROW_NUMBER() OVER (PARTITION BY member_no ORDER BY login_time DESC) AS rn  
    FROM member_login_log  
    WHERE member_no IN ('M7492179',  
                          'S9440968',  
                          'S4557593',  
                          'S5591208',  
                          'S5560939',  
                          'S9729437',  
                          'S3555739',  
                          'S4836660',  
                          'S1584399',  
                          'S4582493')  
) sub  
WHERE sub.rn = 1;

内层查询
- ROW_NUMBER() OVER (PARTITION BY member_no ORDER BY login_time DESC) AS rn：
  - 使用 PARTITION BY member_no 将 member_login_log 表按 member_no 分组。
  - ORDER BY login_time DESC 按照 login_time 字段从最新到最旧排序。
  - ROW_NUMBER() 给每组记录按排序结果分配唯一行号，最新的一行编号为1，第二新编号为2，依此类推。
- WHERE member_no IN (...)：仅选择特定 member_no 的记录。
内层查询结果生成一个临时表 sub，其中每条记录带有字段 rn，表示该 member_no 的行号。
外层查询
- WHERE sub.rn = 1：
  - 从 sub 中选择 rn=1 的记录，即每个 member_no 最新的登录记录。
  - 最终结果只保留 member_no、user_agent 和 login_time 三个字段。

3、窗口函数的适用场景

排名分析：
- 比如 ROW_NUMBER、RANK、DENSE_RANK 函数，用于对数据按特定列进行排名，如获取各个部门销售额最高的员工。
时间序列分析：
- 用于按时间顺序处理数据（如每日累积销售额）。函数如 LAG 和 LEAD，可以取出前一行或后一行的数据，便于分析变化趋势。
分组内的聚合：
- 通过窗口函数在分组内计算一些统计量，而不破坏原始数据行。例如计算每个客户的订单总量或均值，使用 SUM、AVG 等窗口函数。
移动平均和累积：
- 可以使用窗口函数来计算移动平均或累计和，例如，分析股票的30日均线或累积收益。
复杂过滤条件下的子查询替代：
- 窗口函数能简化一些子查询，提升查询性能。例如，通过窗口函数计算分组内的行号、最大值、最小值等，然后在外层筛选符合条件的数据行。

4、窗口函数的优点

减少子查询和 JOIN 的使用：
- 在某些情况下，窗口函数可以代替复杂的子查询和 JOIN 操作，使查询更加直观且易于理解。
数据保留与灵活性：
- 与传统的聚合（如GROUP BY）不同，窗口函数不会减少查询结果中的行数。它能在保持原始数据的情况下，添加新的计算列，因此适合对原数据的增量分析。
多功能：
- 窗口函数集成了排序、分组和聚合等多种操作，功能全面，能够满足多样化的数据分析需求。
提升计算效率：
- 窗口函数在许多数据库系统中进行了优化，执行效率较高，能够处理大量数据且速度快。