掌握SQL窗口函数：提升数据分析能力的关键技能

SQL窗口函数提供了一种灵活且强大的方法来在不改变结果集的基础上对数据进行复杂的计算。窗口函数在处理排名问题、Top N（排名前N）问题、前百分之N问题、累计问题、每组内比较问题和连续问题时尤其有用。

1. 排名问题

排名问题通常需要对结果集中的记录进行排序并分配排名。RANK()
、DENSE_RANK()
和ROW_NUMBER()
是用于处理排名问题的常用窗口函数。

1.1 使用RANK()
函数

RANK()
函数为每行分配一个唯一的排名。如果遇到相同值，排名相同，但后续排名会跳过。

示例：

假设有一张名为scores
的表，记录了学生的成绩：

CREATE TABLE scores (
  student_id INT,
  name VARCHAR(50),
  score INT
);

INSERT INTO scores (student_id, name, score) VALUES
(1, 'Alice', 95),
(2, 'Bob', 85),
(3, 'Charlie', 95),
(4, 'David', 80),
(5, 'Eva', 85);

对学生成绩进行排名：

SELECT name, score,
       RANK() OVER (ORDER BY score DESC) AS rank
FROM scores;

1.2 使用DENSE_RANK()
函数

DENSE_RANK()
类似于RANK()
，但不会跳过后续排名。

示例：

SELECT name, score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

1.3 使用ROW_NUMBER()
函数

ROW_NUMBER()
为结果集中的每一行分配一个唯一的序列号。

示例：

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number
FROM scores;

2. Top N（排名前N）问题

通过使用ROW_NUMBER()
或者其他排名函数结合子查询，可以提取排名前N的记录。

2.1 获取Top 3成绩

示例：

SELECT name, score
FROM (
  SELECT name, score,
         ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
  FROM scores
) AS ranked_scores
WHERE row_num <= 3;

3. 前百分之N问题

前百分之N问题通常涉及到找出前百分之几的数据。可以使用NTILE()
函数将数据分成N个部分。

3.1 找出成绩前50%的学生

示例：

SELECT name, score
FROM (
  SELECT name, score,
         NTILE(2) OVER (ORDER BY score DESC) AS percentile
  FROM scores
) AS divided_scores
WHERE percentile = 1;

4. 累计问题

累计问题通常指累加或累乘的问题，可以使用SUM()
或AVG()
等聚合函数配合窗口函数来解决。

4.1 累计求和

示例：

假设现在有一个表transactions
记录每天的收入：

CREATE TABLE transactions (
  transaction_date DATE,
  amount INT
);

INSERT INTO transactions (transaction_date, amount) VALUES
('2024-01-01', 100),
('2024-01-02', 200),
('2024-01-03', 150),
('2024-01-04', 300);

计算截至每一天的累计收入：

SELECT transaction_date, amount,
       SUM(amount) OVER (ORDER BY transaction_date) AS cumulative_sum
FROM transactions;

5. 每组内比较问题

每组内比较问题是指在分组数据中进行排序和排名，常用于在分组数据中找出每组的最大、最小值。

5.1 每组内排名

示例：

假设有一个表department_scores
，记录了不同部门的员工成绩：

CREATE TABLE department_scores (
  department VARCHAR(50),
  employee_name VARCHAR(50),
  score INT
);

INSERT INTO department_scores (department, employee_name, score) VALUES
('HR', 'Alice', 90),
('HR', 'Bob', 85),
('IT', 'Charlie', 95),
('IT', 'David', 88),
('IT', 'Eva', 92);

在每个部门中对员工成绩进行排名：

SELECT department, employee_name, score,
       RANK() OVER (PARTITION BY department ORDER BY score DESC) AS dept_rank
FROM department_scores;

6. 连续问题

连续问题通常涉及到识别连续的行或日期范围，例如识别连续增长的天数或连续出现的某个事件。

6.1 连续增长天数

示例：

假设在daily_sales
表中记录了每天的销售额：

CREATE TABLE daily_sales (
  sales_date DATE,
  sales_amount INT
);

INSERT INTO daily_sales (sales_date, sales_amount) VALUES
('2024-01-01', 100),
('2024-01-02', 150),
('2024-01-03', 120),
('2024-01-04', 180),
('2024-01-05', 160);

识别出连续增长的销售天数：

SELECT sales_date, sales_amount,
       SUM(is_increasing) OVER (ORDER BY sales_date) AS growth_period
FROM (
  SELECT sales_date, sales_amount,
         CASE WHEN sales_amount > LAG(sales_amount) OVER (ORDER BY sales_date)
              THEN 1 ELSE 0 END AS is_increasing
  FROM daily_sales
) AS sales_growth;

在这个查询中，使用了LAG()
函数来比较当前行和前一行的销售额，从而判断销售额是否增加。

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。

让AI工具成为你的得力助手，感受AI工具的无限可能，让复杂的任务变得简单，让你的工作更加轻松和高效。