PostgreSQL Postgres 9.1 – 分组行的编号

在本文中，我们将介绍如何在PostgreSQL数据库的版本9.1中对行进行分组编号的方法。分组编号是一种常用的技术，用于标识和区分数据库中的不同记录，尤其是在执行分析和报表生成时非常有用。

使用ROW_NUMBER()函数

PostgreSQL提供了ROW_NUMBER()函数，可以在查询结果中为每个分组的行分配一个唯一的编号。它可以用来为每个分组的行创建一个连续的整数序列。以下是使用ROW_NUMBER()函数的基本语法示例：

SELECT column1, column2, ..., ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name) AS row_number
FROM your_table;

让我们通过一个具体的示例来演示如何使用ROW_NUMBER()函数。假设我们有一个销售信息表，包含了销售员的名字、销售额和日期。我们想要按照销售额的降序对每个销售员的销售进行编号。

首先，我们创建一个示例表并插入一些数据：

CREATE TABLE sales (
    salesperson_id SERIAL PRIMARY KEY,
    salesperson_name VARCHAR(50) NOT NULL,
    amount_money DECIMAL NOT NULL,
    sale_date DATE NOT NULL
);

INSERT INTO sales (salesperson_name, amount_money, sale_date)
VALUES ('Alice', 1000.00, '2021-01-01'),
       ('Bob', 2500.00, '2021-01-01'),
       ('Alice', 1500.00, '2021-01-02'),
       ('Charlie', 3500.00, '2021-01-02'),
       ('Bob', 2000.00, '2021-01-03');

然后，我们可以使用ROW_NUMBER()函数对每个销售员的销售额进行编号：

SELECT salesperson_name, amount_money, sale_date,
       ROW_NUMBER() OVER (PARTITION BY salesperson_name ORDER BY amount_money DESC) AS row_number
FROM sales;

运行上述查询，将得到以下结果：

 salesperson_name | amount_money | sale_date  | row_number
------------------+--------------+------------+------------
 Bob              |      2500.00 | 2021-01-01 |          1
 Bob              |      2000.00 | 2021-01-03 |          2
 Alice            |      1500.00 | 2021-01-02 |          1
 Alice            |      1000.00 | 2021-01-01 |          2
 Charlie          |      3500.00 | 2021-01-02 |          1

如上所示，每个销售员的销售额已经按照降序排列，并通过ROW_NUMBER()函数进行了编号。

使用DENSE_RANK()函数

除了ROW_NUMBER()函数外，PostgreSQL还提供了DENSE_RANK()函数，它也可以用来为分组的行分配编号。不同于ROW_NUMBER()函数每个分组的唯一编号，DENSE_RANK()函数可以为相同值的行分配相同的编号。

以下是使用DENSE_RANK()函数的基本语法示例：

SELECT column1, column2, ..., DENSE_RANK() OVER (PARTITION BY column_name ORDER BY column_name) AS dense_rank
FROM your_table;

让我们通过一个示例来演示如何使用DENSE_RANK()函数。假设我们有一个学生表，包含学生的姓名和成绩。我们想要按照成绩的降序为每个班级的学生进行编号。

首先，我们创建一个示例表并插入一些数据：

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    score INTEGER NOT NULL,
    class INTEGER NOT NULL
);

INSERT INTO students (student_name, score, class)
VALUES ('Alice', 90, 1),
       ('Bob', 80, 1),
       ('Charlie', 85, 2),
       ('David', 95, 2),
       ('Eve', 75, 2);

然后，我们可以使用DENSE_RANK()函数对每个班级的学生成绩进行编号：

SELECT student_name, score, class,
       DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) AS dense_rank
FROM students;

运行上述查询，将得到以下结果：

 student_name | score | class | dense_rank
--------------+-------+-------+------------
 David        |    95 |     2 |          1
 Charlie      |    85 |     2 |          2
 Eve          |    75 |     2 |          3
 Alice        |    90 |     1 |          1
 Bob          |    80 |     1 |          2