如何在pgAdmin4（PostgreSQL）中按年售书量降序生成作者表

一、核心逻辑说明 #

我们需要按年份分组，统计每位作者对应年份的图书总销量，再按年份、销量降序排列，最终生成统计结果表。

二、SQL语句实现 #

1. 创建静态统计表（数据为快照，需手动更新） #

执行以下SQL，生成包含年份、作者ID、作者姓名、年度总销量的静态表：

CREATE TABLE author_yearly_sales AS
SELECT
    b.year,
    a.id AS author_id,
    a.name AS author_name,
    SUM(b.sold_copies) AS total_sold
FROM AUTHORS a
JOIN BOOKS b ON a.id = b.author_id
GROUP BY b.year, a.id, a.name
ORDER BY b.year DESC, total_sold DESC;

2. 创建可刷新的物化视图（适合动态数据场景） #

如果后续原表数据更新后需要同步统计结果，推荐使用物化视图：

CREATE MATERIALIZED VIEW author_yearly_sales_mv AS
SELECT
    b.year,
    a.id AS author_id,
    a.name AS author_name,
    SUM(b.sold_copies) AS total_sold
FROM AUTHORS a
JOIN BOOKS b ON a.id = b.author_id
GROUP BY b.year, a.id, a.name
ORDER BY b.year DESC, total_sold DESC;

原表数据更新后，执行以下语句刷新统计结果：

REFRESH MATERIALIZED VIEW author_yearly_sales_mv;

三、pgAdmin4操作步骤 #

1. 打开pgAdmin4并连接目标PostgreSQL数据库。
2. 在左侧导航栏展开对应数据库，点击Query Tool（查询工具）。
3. 将上述SQL语句粘贴到查询编辑器中。
4. 点击工具栏的执行按钮（▶️图标），等待执行完成。
5. 执行后，在左侧导航栏的Tables（普通表）或Materialized Views（物化视图）下即可看到新创建的统计对象。

补充说明 #

• 静态表的数据是创建时的快照，原表数据变化不会自动同步，需重新执行创建语句或手动更新。
• 物化视图通过REFRESH语句即可同步最新数据，适合需要定期更新统计结果的场景。
• 若需包含无销量的年份记录，可调整为左连接并处理NULL值，新手可先从基础版本入手。

内容的提问来源于stack exchange，提问作者Valentína