PostgreSQL 实现distinct关键字给单独的几列去重

PostgreSQL 实现distinct关键字给单独的几列去重

在 PostgreSQL 中，我们可以使用 DISTINCT 关键字来去除查询结果中的重复行，默认情况下，DISTINCT 会作用于整个查询结果的所有列，如果我们只想对查询结果中的某几列进行去重，该如何实现呢？本文将介绍如何在 PostgreSQL 中实现这一功能。

1、使用子查询

我们可以通过创建一个子查询来实现对单独几列的去重，我们需要确定要去重的列，然后创建一个子查询，将这些列作为子查询的返回值，在外部查询中，我们可以使用 DISTINCT 关键字对子查询的结果进行去重。

示例：

假设我们有一个名为 students 的表，包含以下字段：id（学生ID）、name（学生姓名）、class（班级）和 score（分数），我们想要查询每个班级的最高分。

SELECT class, MAX(score) as max_score
FROM (
    SELECT class, score
    FROM students
) as subquery
GROUP BY class;

在这个例子中，我们首先创建了一个子查询，只包含 class 和 score 两列，在外部查询中，我们对子查询的结果按照 class 进行分组，并使用 MAX() 函数获取每组的最高分。

2、使用窗口函数

PostgreSQL 提供了窗口函数（Window Function），可以让我们更方便地对查询结果进行排序、分组等操作，我们可以使用窗口函数结合 DISTINCT 关键字来实现对单独几列的去重。

示例：

假设我们有一个名为 orders 的表，包含以下字段：order_id（订单ID）、customer_id（客户ID）和 amount（金额），我们想要查询每个客户的总金额。

SELECT customer_id, SUM(amount) as total_amount
FROM (
    SELECT customer_id, amount, DISTINCT ON (customer_id) *
    FROM orders
) as subquery
GROUP BY customer_id;

在这个例子中，我们首先创建了一个子查询，只包含 customer_id、amount 和所有其他字段，我们使用 DISTINCT ON () 语法对子查询的结果按照 customer_id 进行去重，在外部查询中，我们对子查询的结果按照 customer_id 进行分组，并使用 SUM() 函数计算每组的总金额。

3、使用聚合函数和 CASE 语句

我们还可以使用聚合函数（Aggregate Function）和 CASE 语句来实现对单独几列的去重，我们需要确定要去重的列，然后使用 CASE 语句对这些列进行判断，如果某个列的值与其他行相同，则将其设置为 NULL；否则，保留原始值，我们可以使用聚合函数对这些列进行汇总。

示例：

假设我们有一个名为 products 的表，包含以下字段：product_id（产品ID）、category_id（类别ID）、price（价格）和 discount（折扣），我们想要查询每个类别的最高价格和最低价格。

SELECT category_id, MAX(CASE WHEN price = max_price THEN NULL ELSE price END) as max_price, MIN(CASE WHEN price = min_price THEN NULL ELSE price END) as min_price, max_price, min_price
FROM (
    SELECT category_id, price, DISTINCT ON (category_id) *, LAG(price) OVER (PARTITION BY category_id ORDER BY price) as prev_price, LEAD(price) OVER (PARTITION BY category_id ORDER BY price) as next_price, price <> LAG(price) OVER (PARTITION BY category_id ORDER BY price) as is_first, price <> next_price as is_last
    FROM products
) as subquery
GROUP BY category_id;

在这个例子中，我们首先创建了一个子查询，只包含 category_id、price、所有其他字段以及用于判断价格是否为最高或最低的辅助字段，我们使用 DISTINCT ON () 语法对子查询的结果按照 category_id 进行去重，在外部查询中，我们对子查询的结果按照 category_id 进行分组，并使用 CASE 语句和聚合函数计算每组的最高价格和最低价格。

4、使用递归公共表达式（Recursive CTE）和窗口函数

递归公共表达式（Recursive CTE）是 PostgreSQL 提供的一种强大的查询功能，可以让我们更方便地处理树形结构的数据，结合窗口函数，我们可以实现对单独几列的去重。

示例：

假设我们有一个名为 categories 的表，包含以下字段：category_id（类别ID）、parent_id（父类别ID）和 name（名称），我们想要查询每个类别及其所有子类别的名称，我们希望每个类别的名称是唯一的。

WITH RECURSIVE cte AS (
    SELECT category_id, parent_id, name, DISTINCT ON (category_id) *, LAG(name) OVER (PARTITION BY category_id ORDER BY name) as prev_name, name <> LAG(name) OVER (PARTITION BY category_id ORDER BY name) as is_first, name <> next_name as is_last, ARRAY[parent_id] as path, ARRAY[parent_id] || parent_id as full_path
    FROM categories
    WHERE parent_id IS NULL OR parent_id = ANY(ARRAY[parent_id]) 如果当前类别没有父类别或者父类别已经在路径中，则将其添加到路径中
    UNION ALL
    SELECT c.category_id, c.parent_id, c.name, c.*, cte.prev_name, c.name <> cte.prev_name as is_first, c.name <> cte.next_name as is_last, cte.path || c.category_id as path, cte.full_path || c.category_id as full_path
    FROM categories c INNER JOIN cte ON c.parent_id = cte.category_id AND NOT c.name = cte.prev_name 如果当前类别的父类别已经在路径中且名称与前一个类别不同，则将其添加到路径中
)
SELECT * FROM cte;

在这个例子中，我们首先创建了一个递归公共表达式 cte，用于遍历 categories 表中的所有类别及其子类别，我们在递归公共表达式中使用 DISTINCT ON () 语法对每个类别的名称进行去重，我们从递归公共表达式中选择所有字段作为查询结果。