数据分析学习之SQL-第七关什么是窗口函数

窗口函数是SQL中用于实现联机分析处理（OLAP）的特殊函数，能够在不减少原表行数的情况下，同时完成分组和排序操作，常用于组内排名、TopN问题及组内数据比较等场景。

一、窗口函数的核心作用

窗口函数通过OVER()子句定义数据窗口，实现以下功能：

组内排名：如按部门分组后对员工业绩排名。
TopN问题：如找出每个部门业绩前3名的员工。
组内比较：如筛选每组中高于平均值的数据。
移动平均：如计算时间序列数据的3日移动平均值。

二、窗口函数的语法结构

基本语法由三部分组成：

函数名() OVER ( [PARTITION BY 分组列名] [ORDER BY 排序列名 [ASC|DESC]])

PARTITION BY：可选，指定分组列，省略时对全表操作。
ORDER BY：可选，指定排序规则，影响排名结果。

三、窗口函数的类型1. 专用窗口函数

RANK()：排名，相同值会跳过后续名次（如1,2,2,4）。
DENSE_RANK()：密集排名，相同值不跳过名次（如1,2,2,3）。
ROW_NUMBER()：行号，即使值相同也按顺序编号（如1,2,3,4）。

2. 聚合函数作为窗口函数

SUM()/AVG()/COUNT()/MAX()/MIN()：在窗口内计算聚合值，不减少行数。SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avgFROM sales_data;

四、典型应用场景1. 排名问题-- 每个部门按业绩排名SELECT employee_id, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rankFROM employees;2. TopN问题-- 找出每个部门薪资前3的员工SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS ranking FROM employees) AS rankedWHERE ranking <= 3;

3. 组内比较-- 找出每个部门薪资高于部门平均值的员工SELECT e.employee_id, e.department, e.salary, dept_avg.avg_salaryFROM employees eJOIN ( SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) dept_avg ON e.department = dept_avg.departmentWHERE e.salary > dept_avg.avg_salary;

或使用窗口函数：

SELECT employee_id, department, salaryFROM ( SELECT *, AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary FROM employees) AS with_avgWHERE salary > avg_dept_salary;

4. 移动平均-- 计算3日移动平均销售额SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avgFROM daily_sales;

五、注意事项

位置限制：窗口函数只能出现在SELECT子句中。
性能影响：大数据量时，PARTITION BY和ORDER BY可能影响查询效率。
窗口范围：通过ROWS BETWEEN可自定义窗口范围（如当前行及前2行）。
省略分组：省略PARTITION BY时，窗口覆盖全表。-- 全表按薪资排名SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS overall_rankFROM employees;

六、总结

窗口函数通过OVER()子句实现灵活的数据分析，核心优势在于：

保留原表行数：适合需要原始数据+分析结果的场景。
多功能集成：结合分组、排序和聚合，简化复杂查询。
高效处理：相比子查询，窗口函数通常性能更优。

掌握窗口函数可显著提升SQL处理复杂分析问题的能力，尤其在排名、TopN和组内比较等场景中应用广泛。

您可能感兴趣问答

Collapsible

热门标签

热点问答