熟悉 SQL 的开发者知道，同一个结果往往有多种实现。本教程使用三种方式生成一份常见报表。根据场景，专家可能选择其中任何一种；总体建议是选择最容易阅读的写法。
首先使用公共表表达式 CTE，它很适合组织子查询。然后在聚合中使用 CASE，筛选所需数值。最后使用 FILTER 简化语法，实现与 CASE 基本相同的工作。三者都能完成任务，但本例中 FILTER 更清晰。
我们要从 invoices 表生成月度收入报表，目标结果类似：
mnth | billed | uncollected | collected
------------+-----------------------+--------------+--
2023-02-01 | 1498.06 | 1498.06 | 0
2023-01-01 | 2993.95 | 1483.04 | 1510.91
2022-12-01 | 1413.17 | 382.84 | 1030.33
2022-11-01 | 1378.18 | 197.52 | 1180.66
2022-10-01 | 1342.91 | 185.03 | 1157.88
2022-09-01 | 1299.90 | 88.01 | 1211.89
2022-08-01 | 1261.97 | 85.29 | 1176.68
开始前，关闭扩展显示。扩展显示适合阅读单条记录，但聚合结果以表格展示更清楚：
\x
invoices 表
先查看底层数据与表结构：
\d invoices
输出：
Table "public.invoices"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------------+-----------+----------+--------------------------------------
id | bigint | | not null | nextval('invoices_id_seq'::regclass)
account_id | integer | | |
net_total_in_cents | integer | | |
invoice_period | daterange | | |
status | text | | |
created_at | timestamp(6) without time zone | | not null |
updated_at | timestamp(6) without time zone | | not null |
Indexes:
"invoices_pkey" PRIMARY KEY, btree (id)
"index_invoices_on_account_id" btree (account_id)
这里有两个设计选择：
invoice_period使用日期范围。下界是账期开始，上界是结束。使用lower(invoice_period)提取起始值。net_total_in_cents以分为单位，而不是元，并保存为整数而非浮点数，避免出现不足一分的小数金额。
查看几条记录：
SELECT * FROM invoices LIMIT 10;
返回：
id | account_id | net_total_in_cents | invoice_period | status | created_at | updated_at
----+------------+--------------------+-------------------------+--------+----------------------------+----------------------------
1 | 4 | 1072 | [2022-02-01,2022-02-28) | paid | 2023-02-01 17:28:37.420197 | 2023-02-01 17:28:37.420197
2 | 6 | 955 | [2022-02-01,2022-02-28) | paid | 2023-02-01 17:28:37.422361 | 2023-02-01 17:28:37.422361
3 | 7 | 322 | [2022-02-01,2022-02-28) | paid | 2023-02-01 17:28:37.423726 | 2023-02-01 17:28:37.423726
4 | 9 | 778 | [2022-02-01,2022-02-28) | paid | 2023-02-01 17:28:37.425423 | 2023-02-01 17:28:37.425423
5 | 10 | 338 | [2022-02-01,2022-02-28) | paid | 2023-02-01 17:28:37.426731 | 2023-02-01 17:28:37.426731
6 | 12 | 386 | [2022-02-01,2022-02-28) | paid | 2023-02-01 17:28:37.428382 | 2023-02-01 17:28:37.428382
7 | 16 | 66 | [2022-02-01,2022-02-28) | paid | 2023-02-01 17:28:37.430613 | 2023-02-01 17:28:37.430613
8 | 18 | 283 | [2022-02-01,2022-02-28) | paid | 2023-02-01 17:28:37.432194 | 2023-02-01 17:28:37.432194
9 | 25 | 508 | [2022-02-01,2022-02-28) | paid | 2023-02-01 17:28:37.43536 | 2023-02-01 17:28:37.43536
10 | 26 | 746 | [2022-02-01,2022-02-28) | paid | 2023-02-01 17:28:37.436662 | 2023-02-01 17:28:37.436662
(10 rows)
使用 CTE
先看公共表表达式，也常称为 WITH 子句。
下面看起来复杂，但可以理解为把三个不同查询组织为一个。totals、collected 和 invoiced 分别计算每个月的对应值，最终再按 mnth 连接：
WITH totals AS (
SELECT
lower(i.invoice_period) as mnth,
SUM(i.net_total_in_cents) / 100.0 as net_total
FROM invoices i
GROUP BY 1
), collected AS (
SELECT
lower(i.invoice_period) as mnth,
SUM(i.net_total_in_cents) / 100.0 as amount
FROM invoices i
WHERE status = 'paid'
GROUP BY 1
), invoiced AS (
SELECT
lower(i.invoice_period) AS mnth,
sum(i.net_total_in_cents) / 100.0 AS amount
FROM invoices i
WHERE status = 'invoiced'
GROUP BY 1
)
SELECT totals.mnth,
totals.net_total as billed,
COALESCE(invoiced.amount, 0) as uncollected,
COALESCE(collected.amount, 0) as collected
FROM totals
LEFT JOIN invoiced ON totals.mnth = invoiced.mnth
LEFT JOIN collected on totals.mnth = collected.mnth
ORDER BY 1 desc;
输出每个月一条记录，包含发票总金额、未收金额和已收金额。可以拆出各个 WITH 子查询单独运行，观察其结果，帮助理解整体 SQL。
用 CASE 进行条件筛选
另一种方法是在聚合中使用 CASE 按条件筛选。本例中的查询比 CTE 版本短得多：
SELECT LOWER(i.invoice_period) as mnth,
SUM(i.net_total_in_cents) / 100.0 as billed,
SUM (CASE WHEN status = 'invoiced' THEN i.net_total_in_cents END) / 100.00 as uncollected,
SUM (CASE WHEN status = 'paid' THEN i.net_total_in_cents END) / 100.00 as collected
FROM invoices i
GROUP BY 1
ORDER BY 1 desc;
可以将 CASE 看成 IF／THEN：条件满足时，将 net_total_in_cents 交给 SUM。此类 CASE 在数据分析中很常见，但还有 FILTER 这个选项。
使用 FILTER
FILTER 的作用与 CASE 类似，但 SQL 更容易阅读：
SELECT LOWER(i.invoice_period) as mnth,
SUM (i.net_total_in_cents) / 100.0 as billed,
SUM (i.net_total_in_cents) FILTER (WHERE status = 'invoiced') / 100.00 as uncollected,
SUM (i.net_total_in_cents) FILTER (WHERE status = 'paid') / 100.00 as collected
FROM invoices i
GROUP BY 1
ORDER BY 1 desc;
如果希望 Postgres 语句尽可能清晰、简洁，FILTER 是值得掌握的工具。
原文：Using FILTER vs CTEs and CASE WHEN。作者／维护方：Crunchy Data 教程团队。本文为中文翻译，代码及命令保留原文。
原站版权声明：© 2018–2026 Crunchy Data Solutions, Inc.
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容