Postgresql Group By

PostgreSQL GROUP BY: 聚合数据

欢迎回到我们的 PostgreSQL 教程系列！在这一课中，我们将重点关注 GROUP BY 子句，它是聚合数据和对行组进行计算的关键。有效地使用 GROUP BY 子句可以让你从数据中生成有意义的汇总。

GROUP BY 子句简介

PostgreSQL 中的 GROUP BY 子句与聚合函数（例如 COUNT、SUM、AVG、MAX、MIN）一起使用，将在指定列中具有相同值的行分组为汇总行。这是数据分析和报告的强大工具。

基本语法

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
  • SELECT：指定要检索的列和聚合函数。
  • FROM：指定要查询的表。
  • GROUP BY：指定用于分组结果的列。

在 PostgreSQL 中使用 GROUP BY

让我们通过一些实用示例，使用我们的图书馆和电影数据库，来探索 GROUP BY 子句的多种用法。

1. 计数分组中的行

示例：统计每个作者的书籍数量

SELECT author, COUNT(*) AS book_count
FROM books
GROUP BY author;

查看架构初始化

解释：

  • 按 author 分组书籍。
  • 统计每个作者的书籍数量。
  • 结果显示每个作者以及他们在 books 表中的书籍数量。

2. 计算总和

示例：计算每个作者的总页数

SELECT author, SUM(pages) AS total_pages
FROM books
GROUP BY author;

解释：

  • 按 author 分组书籍。
  • 汇总每个作者书籍的 pages。
  • 结果显示每个作者及其书籍的总页数。

3. 计算平均值

示例：查找每个作者书籍的平均价格

SELECT author, AVG(price) AS average_price
FROM books
GROUP BY author;

解释：

  • 按 author 分组书籍。
  • 计算每个作者书籍的平均 price。
  • 结果显示每个作者及其书籍的平均价格。

4. 查找最大值和最小值

示例：查找每个作者书籍的最高价和最低价

SELECT author, MAX(price) AS max_price, MIN(price) AS min_price
FROM books
GROUP BY author;

解释：

  • 按 author 分组书籍。
  • 查找每个作者书籍的最大值（MAX）和最小值（MIN） price。
  • 结果显示每个作者、他们书籍的最高价和最低价。

5. 按多个列分组

示例：统计每个作者在每个价格点的书籍数量

假设我们想查看每个作者在每个价格点的书籍数量。

SELECT author, price, COUNT(*) AS book_count
FROM books
GROUP BY author, price;

解释：

  • 按 author 和 price 分组书籍。
  • 统计每个作者在每个价格点的书籍数量。
  • 结果显示各个作者和价格的组合以及每个组合中的书籍数量。

6. 使用 GROUP BY 和 HAVING 子句

HAVING 子句允许你基于聚合函数过滤组。

示例：查找有超过一本书的作者

SELECT author, COUNT(*) AS book_count
FROM books
GROUP BY author
HAVING COUNT(*) > 1;

解释：

  • 按 author 分组书籍。
  • 统计每个作者的书籍数量。
  • HAVING 子句过滤出具有超过一本书的作者。

7. 将 GROUP BY 与 ORDER BY 结合使用

你可以使用 ORDER BY 对分组结果进行排序。

示例：列出作者及其总页数，按总页数降序排列

SELECT author, SUM(pages) AS total_pages
FROM books
GROUP BY author
ORDER BY total_pages DESC;

解释：

  • 按 author 分组书籍。
  • 汇总每个作者的 pages。
  • 按 total_pages 降序排列结果。

8. 分组和过滤数据

示例：计算按作者分组的平均价格，筛选出价格超过 10 美元的书籍

SELECT author, AVG(price) AS average_price
FROM books
WHERE price > 10
GROUP BY author;

解释：

  • 筛选出 price > 10 的书籍。
  • 按 author 分组剩余的书籍。
  • 计算每个作者书籍的平均 price。

9. 使用 JOIN 的 GROUP BY

你可以通过 JOIN 对多表数据进行分组。

示例：统计在 authors 表中的作者的书籍数量

SELECT a.name AS author_name, COUNT(b.title) AS book_count
FROM authors a
LEFT JOIN books b ON a.name = b.author
GROUP BY a.name;

解释：

  • 在 authors 和 books 之间执行 LEFT JOIN。
  • 按 a.name（作者的名字）对结果进行分组。
  • 统计每个作者的书籍数量，包括那些没有书籍的作者。

10. 使用表达式进行分组

你可以在 GROUP BY 子句中使用表达式。

示例：按价格范围对书籍进行分组

让我们将书籍按价格范围分组：低于 10 美元，10 至 12 美元，超过 12 美元。

SELECT
    CASE
        WHEN price < 10 THEN 'Under $10'
        WHEN price BETWEEN 10 AND 12 THEN '$10 - $12'
        ELSE 'Over $12'
    END AS price_range,
    COUNT(*) AS book_count
FROM books
GROUP BY price_range;

解释：

  • 使用 CASE 表达式创建 price_range 类别。
  • 按 price_range 对书籍进行分组。
  • 统计每个价格范围内的书籍数量。

11. 将 DISTINCT 与 GROUP BY 结合使用

尽管 GROUP BY 通常可以替代 DISTINCT，但在某些情况下，你可以同时使用它们。

示例：列出 books 表中的唯一作者

SELECT DISTINCT author
FROM books;

或者使用 GROUP BY：

SELECT author
FROM books
GROUP BY author;

解释：

  • 这两个查询都会返回 books 表中的唯一作者列表。

12. 使用日期函数的分组

如果我们有日期信息，我们可以按日期部分进行分组。由于我们的架构不包括 publication_date 列，因此我们将跳过这个示例。

结论

PostgreSQL 中的 GROUP BY 子句是一个强大的工具，用于聚合和汇总数据。通过掌握 GROUP BY 和聚合函数，你可以从数据中获得有意义的洞察。

要点总结：

  • 使用 GROUP BY 将在一个或多个列中具有相同值的行分组。
  • 像 COUNT、SUM、AVG、MAX 和 MIN 这样的聚合函数对分组数据执行计算。
  • HAVING 子句基于聚合值过滤组。
  • 将 GROUP BY 与 ORDER BY 结合使用以对分组结果进行排序。
  • 你可以按表达式分组，并使用 CASE 语句进行自定义分组。

架构初始化

要运行提供的示例，请确保你的 PostgreSQL 数据库设置如下架构：

-- Create the books table
CREATE TABLE books (
    title VARCHAR(255),
    author VARCHAR(255),
    price NUMERIC(10, 2),
    pages INTEGER
);
 
-- Insert sample data into the books table
INSERT INTO books (title, author, price, pages) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 180),
('This Side of Paradise', 'F. Scott Fitzgerald', 7.99, 305),
('To Kill a Mockingbird', 'Harper Lee', 7.99, 281),
('Go Set a Watchman', 'Harper Lee', 8.99, 278),
('1984', 'George Orwell', 8.99, 328),
('Animal Farm', 'George Orwell', 5.99, 112),
('Pride and Prejudice', 'Jane Austen', 6.99, 279),
('Sense and Sensibility', 'Jane Austen', 5.99, 226),
('The Catcher in the Rye', 'J.D. Salinger', 9.99, 214),
('Moby-Dick', 'Herman Melville', 11.99, 635),
('War and Peace', 'Leo Tolstoy', 12.99, 1225);
 
-- Create the authors table
CREATE TABLE authors (
    name VARCHAR(255)
);
 
-- Insert sample data into the authors table
INSERT INTO authors (name) VALUES
('F. Scott Fitzgerald'),
('Harper Lee'),
('George Orwell'),
('Jane Austen'),
('J.D. Salinger'),
('Herman Melville'),
('Leo Tolstoy'),
('Mark Twain');  -- Added an author without books for JOIN examples
 
-- Create the movies table
CREATE TABLE movies (
    title VARCHAR(255)
);
 
-- Insert sample data into the movies table
INSERT INTO movies (title) VALUES
('The Shawshank Redemption'),
('The Godfather'),
('The Dark Knight'),
('Pulp Fiction'),
('The Lord of the Rings');
IndexPostgresql Insert