SQL排名|极客笔记

SQL排名

在数据分析和报表制作中经常会用到排名，例如产品销售排名、学生成绩排名等。在SQL中，我们可以通过使用窗口函数和一些排序函数来实现对数据进行排名操作。

窗口函数

窗口函数是SQL中用于实现分组操作并返回每个分组与数据行的相关计算结果的函数。在排名中，我们可以使用窗口函数来计算行的排名。

常用的窗口函数有：

RANK()：计算并返回某一行在分组中的排名。
DENSE_RANK()：计算并返回某一行在分组中的连续排名，即没有相同排名的行。
ROW_NUMBER()：计算并返回某一行在分组中的行号。

下面我们将分别使用这三个窗口函数来实现排名。

数据准备

在开始之前，我们先创建一个示例表来模拟产品销售数据。

CREATE TABLE sales (
    id INT PRIMARY KEY,
    product VARCHAR(255),
    sales_amount DECIMAL(10, 2)
);

INSERT INTO sales (id, product, sales_amount) VALUES
(1, 'Product A', 1000),
(2, 'Product B', 2000),
(3, 'Product C', 1500),
(4, 'Product D', 3000),
(5, 'Product E', 2500);

RANK()函数

RANK()函数可以用来计算某一行在分组中的排名，若有相同销售额的产品，则会有相同的排名。

下面是一个使用RANK()函数的示例：

SELECT id, product, sales_amount, RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM sales;

运行结果如下：

id |  product   | sales_amount | rank
---+------------+--------------+------
 4 | Product D  |      3000.00 |    1
 5 | Product E  |      2500.00 |    2
 2 | Product B  |      2000.00 |    3
 3 | Product C  |      1500.00 |    4
 1 | Product A  |      1000.00 |    5

以上结果表示按销售额降序排列后的排名。

DENSE_RANK()函数

DENSE_RANK()函数与RANK()函数类似，但它会跳过排名相同的行，保持连续的排名。

下面是一个使用DENSE_RANK()函数的示例：

SELECT id, product, sales_amount, DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS dense_rank
FROM sales;

运行结果如下：

id |  product   | sales_amount | dense_rank
---+------------+--------------+------------
 4 | Product D  |      3000.00 |          1
 5 | Product E  |      2500.00 |          2
 2 | Product B  |      2000.00 |          3
 3 | Product C  |      1500.00 |          4
 1 | Product A  |      1000.00 |          5

以上结果表示按销售额降序排列后的连续排名。

ROW_NUMBER()函数

ROW_NUMBER()函数用于计算并返回某一行在分组中的行号。

下面是一个使用ROW_NUMBER()函数的示例：

SELECT id, product, sales_amount, ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_number
FROM sales;

运行结果如下：

id |  product   | sales_amount | row_number
---+------------+--------------+------------
 4 | Product D  |      3000.00 |          1
 5 | Product E  |      2500.00 |          2
 2 | Product B  |      2000.00 |          3
 3 | Product C  |      1500.00 |          4
 1 | Product A  |      1000.00 |          5

以上结果表示按销售额降序排列后的行号。

排名的应用

有了排名函数，我们可以根据需要对数据进行各种排名操作。

例如，我们可以使用RANK()函数来计算每个产品的销售额排名，并只返回排名前三的产品：

SELECT id, product, sales_amount, RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM sales
WHERE rank <= 3;

运行结果如下：

id |  product   | sales_amount | rank
---+------------+--------------+------
 4 | Product D  |      3000.00 |    1
 5 | Product E  |      2500.00 |    2
 2 | Product B  |      2000.00 |    3

另外，我们还可以使用DENSE_RANK()函数来计算每个产品的销售额连续排名，并只返回排名前三的产品：

SELECT id, product, sales_amount, DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS dense_rank
FROM sales
WHERE dense_rank <= 3;

运行结果如下：

id |  product   | sales_amount | dense_rank
---+------------+--------------+------------
 4 | Product D  |      3000.00 |          1
 5 | Product E  |      2500.00 |          2
 2 | Product B  |      2000.00 |          3

当然，我们也可以使用ROW_NUMBER()函数来计算每个产品的行号，并只返回前三行：

SELECT id, product, sales_amount, ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_number
FROM sales
WHERE row_number <= 3;

运行结果如下：

id |  product   | sales_amount | row_number
---+------------+--------------+------------
 4 | Product D  |      3000.00 |          1
 5 | Product E  |      2500.00 |          2
 2 | Product B  |      2000.00 |          3