如何在PostgreSQL的FILTER子句中计算百分比？

问题概述 #

需要统计不同状态（Active/Inactive）下各部门的请求数量，以及各状态/部门数量占总记录数的百分比，但使用((COUNT(*)/TOTAL::float)*100) FILTER (WHERE dept = 'AFG')时出现语法错误：syntax error at or near "FILTER"。

基础表结构及数据：

CREATE TABLE req (
    id INT,
    active VARCHAR(5),
    dept VARCHAR(3)
);

INSERT INTO req VALUES
(1, 'true', 'AFG'),
(2, 'true', 'AFG'),
(3, 'true', 'AFG'),
(4, 'true', 'POD'),
(5, 'true', 'POD'),
(6, 'true', 'KMN'),
(7, 'true', 'AGO'),
(8, 'true', 'AGO'),
(9, 'false', 'AGO'),
(10, 'true', 'AGO'),
(11, 'true', 'AGO'),
(12, 'true', 'SUD'),
(13, 'true', 'SUD'),
(14, 'true', 'MOL');

错误原因 #

FILTER是聚合函数的专属修饰子句，必须直接紧跟在聚合函数（如COUNT()、SUM()）之后，不能放在聚合计算表达式的末尾。你写的语法把FILTER放在了除法+乘法的表达式后面，不符合PostgreSQL的语法规则，导致报错。

正确解决方案 #

使用CTE（公共表表达式）先计算基础统计数据，再基于基础数据生成百分比行，避免重复查询，逻辑更清晰：

WITH base_stats AS (
    SELECT
        CASE active WHEN 'true' THEN 'Active Request' ELSE 'Inactive Request' END AS Title,
        COUNT(*) FILTER (WHERE dept = 'AFG') AS AFG,
        COUNT(*) FILTER (WHERE dept = 'AGO') AS AGO,
        COUNT(*) FILTER (WHERE dept = 'KMN') AS KMN,
        COUNT(*) FILTER (WHERE dept = 'MOL') AS MOL,
        COUNT(*) FILTER (WHERE dept = 'POD') AS POD,
        COUNT(*) FILTER (WHERE dept = 'SUD') AS SUD,
        COUNT(*) AS TOTAL
    FROM req
    GROUP BY active
),
total_records AS (
    SELECT SUM(TOTAL) AS total_all FROM base_stats
)
-- 输出基础统计行
SELECT Title, AFG, AGO, KMN, MOL, POD, SUD, TOTAL
FROM base_stats
UNION ALL
-- 输出百分比行
SELECT
    Title || ' (percent)',
    ROUND((AFG::FLOAT / tr.total_all) * 100, 1) AS AFG,
    ROUND((AGO::FLOAT / tr.total_all) * 100, 1) AS AGO,
    ROUND((KMN::FLOAT / tr.total_all) * 100, 1) AS KMN,
    ROUND((MOL::FLOAT / tr.total_all) * 100, 1) AS MOL,
    ROUND((POD::FLOAT / tr.total_all) * 100, 1) AS POD,
    ROUND((SUD::FLOAT / tr.total_all) * 100, 1) AS SUD,
    ROUND((TOTAL::FLOAT / tr.total_all) * 100, 1) AS TOTAL
FROM base_stats, total_records tr
ORDER BY
    CASE WHEN Title LIKE '%(percent)' THEN 2 ELSE 1 END,
    Title;

结果说明 #

执行上述SQL后，会得到与目标格式一致的结果：

如果需要用逗号作为小数分隔符，可以将ROUND的结果转换为字符串并替换小数点，例如：

REPLACE(ROUND((AFG::FLOAT / tr.total_all) * 100, 1)::TEXT, '.', ',') AS AFG

内容的提问来源于stack exchange，提问作者executable