sql窗口函数是在不减少结果集行数的前提下，基于当前行相关行集合进行计算的函数。1. 它通过over()子句定义窗口范围，支持partition by分区和order by排序；2. 常用类型包括聚合函数（如sum、avg）、排序函数（如rank、row_number）和偏移函数（如lag、lead）；3. 可用于计算移动平均、累计总和、top n排名等复杂分析；4. 优化技巧包括索引创建、合理定义窗口范围、物化中间结果及查询重写；5. 与group by不同在于其保留原始行数且支持细粒度行间计算。
SQL窗口函数，简单来说，就是能在查询结果的“窗口”内进行计算的函数。它不像聚合函数那样会改变结果集的行数，而是为每一行返回一个值，这个值是基于与当前行相关的行集合计算出来的。
SQL窗口函数，能让你在不改变结果集行数的前提下，进行复杂的统计分析。掌握它，能极大地提升你的SQL功力。
窗口函数的基本语法：函数名(参数) OVER (PARTITION BY 列名 ORDER BY 列名 ASC|DESC)
解决方案
-
理解基本概念：窗口和分区
- 窗口：窗口函数计算的行集合。你可以把它想象成一个在结果集上滑动的“窗口”，窗口函数就在这个窗口内进行计算。
- 分区：PARTITION BY子句将结果集分成多个分区。窗口函数会分别在每个分区内进行计算。如果没有PARTITION BY子句，则整个结果集被视为一个分区。
-
常用窗口函数
-
聚合函数作为窗口函数：SUM(), AVG(), MIN(), MAX(), count()等。
-
示例：计算每个部门的工资总额和每个员工的工资占部门工资总额的比例。
SELECT department, employee_name, salary, SUM(salary) OVER (PARTITION BY department) AS department_total_salary, salary * 1.0 / SUM(salary) OVER (PARTITION BY department) AS salary_ratio FROM employees;
-
-
排序函数：RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()。
-
RANK()：为每个分区中的每行分配一个排名，排名可能是不连续的。
-
DENSE_RANK()：与RANK()类似，但排名是连续的。
-
ROW_NUMBER()：为每个分区中的每行分配一个唯一的序号。
-
NTILE(n)：将每个分区中的行分成n组，并为每行分配一个组号。
-
示例：按销售额对客户进行排名。
SELECT customer_name, sales_amount, RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank FROM sales;
-
-
偏移函数：LAG(), LEAD()。
-
LAG(column, offset, default)：访问结果集中当前行之前offset行的column的值。如果offset超出范围，则返回default。
-
LEAD(column, offset, default)：访问结果集中当前行之后offset行的column的值。如果offset超出范围，则返回default。
-
示例：计算每个月的销售额与上个月的销售额的差额。
SELECT month, sales_amount, LAG(sales_amount, 1, 0) OVER (ORDER BY month) AS previous_month_sales, sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY month) AS sales_difference FROM monthly_sales;
-
-
-
OVER()子句详解
- PARTITION BY：定义分区。
- ORDER BY：定义排序方式。
- ROWS或RANGE：定义窗口的范围。这部分比较高级，用于更精细地控制窗口的大小。例如，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示窗口包括当前行和之前的所有行。
-
实际应用场景
- 计算移动平均值。
- 计算累计总和。
- 查找每个类别中的前N个记录。
- 计算同比、环比增长率。
窗口函数性能优化技巧有哪些？
窗口函数本身在某些情况下可能会影响查询性能，尤其是在处理大数据集时。
- 索引优化： 确保PARTITION BY和ORDER BY子句中使用的列都有合适的索引。索引可以显著提高排序和分区操作的效率。
- 避免不必要的分区： 仔细评估是否真的需要分区。如果整个结果集都可以作为一个窗口处理，则省略PARTITION BY子句可以减少计算开销。
- 合理选择窗口范围： 如果使用了ROWS或RANGE子句，确保窗口范围定义合理。过大的窗口范围会增加计算量。
- 物化中间结果： 对于复杂的窗口函数查询，可以考虑将中间结果物化到临时表中，然后再进行后续计算。这可以避免重复计算。
- 数据库版本和配置： 不同数据库版本对窗口函数的支持和优化程度可能不同。确保使用最新版本的数据库，并根据实际情况调整数据库配置。
- 查询重写： 有时候，可以通过重写查询来避免使用窗口函数，或者使用更高效的窗口函数替代方案。例如，某些情况下可以使用自连接来替代窗口函数。
- 并行处理： 如果数据库支持并行处理，可以尝试启用并行执行计划，以提高窗口函数的计算速度。
- 监控和分析： 使用数据库提供的监控工具来分析查询执行计划，找出性能瓶颈，并进行针对性优化。
窗口函数和GROUP BY的区别是什么？
- 结果集行数：GROUP BY会将结果集分组，并对每个组返回一行结果。窗口函数则不会改变结果集的行数，而是为每一行返回一个值。
- 计算范围：GROUP BY对整个组进行聚合计算。窗口函数可以在与当前行相关的行集合（窗口）内进行计算。
- 应用场景：GROUP BY主要用于分组统计。窗口函数主要用于在不改变结果集行数的前提下进行复杂的统计分析，例如排名、移动平均值等。
如何用窗口函数实现Top N问题？
Top N问题是窗口函数的一个经典应用场景。
- 使用RANK()或DENSE_RANK()函数：首先，使用RANK()或DENSE_RANK()函数为每个分区中的行分配排名。
- 过滤排名结果：然后，使用WHERE子句过滤排名结果，只保留排名在前N名的行。
示例：查询每个部门工资最高的前3名员工。
WITH RankedEmployees AS ( SELECT department, employee_name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank FROM employees ) SELECT department, employee_name, salary FROM RankedEmployees WHERE salary_rank <= 3;
这个例子首先使用WITH子句创建一个名为RankedEmployees的公共表表达式（CTE）。在CTE中，使用DENSE_RANK()函数为每个部门的员工分配排名，排名依据是工资从高到低。然后，在主查询中，从RankedEmployees CTE中选择排名在前3名的员工。
