数据分析常用窗口函数

在开始之前，想象一下你在处理一个有几千行销售记录的表。你的任务是：找出每个类别里谁是第一名销售员，谁是第二名，依此类推。或者，你想给所有查询结果的行编号，方便追踪顺序。这些用窗口函数都能轻松搞定。

窗口函数，就是SQL里那种能对一部分行（我们叫它“窗口”）做操作的函数。和聚合函数不一样，聚合函数（比如SUM()或者AVG()）会把多行合成一行，窗口函数不会动原来的行，只是在每行后面加上计算出来的新值。

和聚合函数的区别

聚合函数会把数据“压缩”成分组：

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

结果：只剩下按部门分的几行。

窗口函数就不一样——原来的行都还在，只是多了个新字段，比如ROW_NUMBER()：

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

这样你能拿到所有原始行，还多了个rank_within_department列，每个员工在自己部门里的编号。

常用窗口函数

OVER()的语法

每个窗口函数最重要的部分就是那个神奇的OVER()。它决定了函数到底对哪一块数据（窗口）操作。OVER()里可以指定分组（PARTITION BY）和/或排序（ORDER BY）。

通用语法：

<窗口函数>() OVER (
   [PARTITION BY <分组字段>] 
   [ORDER BY <排序字段>]
)

参数说明：

• PARTITION BY：把行分组。比如“按部门分组”。
• ORDER BY：指定行的处理顺序。比如“按工资从高到低排序”。

ROW_NUMBER()函数

ROW_NUMBER()会给窗口里的每一行从1开始编号。比如你想在临时表里简单地给每行加个编号，或者想知道某条记录的顺序，这就很有用。

例子。表sales（销售）：

查询：

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

结果：

怎么实现的：

1. 数据按product_category分组。
2. 每组按revenue（降序）排序。
3. 每组里的行都分配一个顺序编号。

RANK()函数

RANK()用来排名。和ROW_NUMBER()不一样，它会考虑相同的值，如果有并列，排名会跳号。

例子：

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

结果：

DENSE_RANK()函数

DENSE_RANK()和RANK()很像，但有一点不同：不会跳过排名，即使有并列。

例子。加一条收入一样的销售：

查询：

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

结果：

用法举例：行编号

任务：给orders表里的所有订单按日期排序编号。

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

结果：你能看到所有订单，按执行顺序编号。

用法举例：每个类别的Top 3销售员

任务：找出每个商品类别里收入前三的销售员。

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

用法举例：找出收入相同的销售员

任务：查查每个类别里有没有收入一样的销售员。

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

现在你可以看到哪些排名“粘”在一起了。