PostgreSQL中高效计算带条件的百分比的方法

嘿，刚从T-SQL转PostgreSQL对吧？我来给你个更高效简洁的写法，比你之前用子查询关联的方式要清爽多了，而且性能也更好！

核心思路 #

PostgreSQL支持条件聚合，我们可以在一次GROUP BY操作里同时算出每个学科的总尝试次数和完成（C状态）的次数，不用像T-SQL那样分两次分组再做JOIN，这样只需要一次表扫描，效率更高。

先创建测试表（对应你的临时表） #

首先我们先把你的测试数据转换成PostgreSQL的临时表写法：

CREATE TEMP TABLE soo AS
SELECT 'PHYSICS' AS topic, 'C' AS status
UNION ALL
SELECT 'PHYSICS' AS topic, 'C' AS status
UNION ALL
SELECT 'PHYSICS' AS topic, 'I' AS status
UNION ALL
SELECT 'MATHS' AS topic, 'C' AS status
UNION ALL
SELECT 'MATHS' AS topic, 'C' AS status
UNION ALL
SELECT 'MATHS' AS topic, 'I' AS status
UNION ALL
SELECT 'MATHS' AS topic, 'C' AS status
UNION ALL
SELECT 'CHEMISTRY' AS topic, 'I' AS status
UNION ALL
SELECT 'CHEMISTRY' AS topic, 'I' AS status
UNION ALL
SELECT 'CHEMISTRY' AS topic, 'C' AS status;

高效查询语句 #

接下来就是关键的计算语句，一行搞定聚合和百分比计算：

SELECT
  topic,
  ROUND(100.0 * COUNT(CASE WHEN status = 'C' THEN 1 END) / COUNT(*), 2) AS percent
FROM soo
GROUP BY topic
ORDER BY topic;

代码解释 #

• COUNT(CASE WHEN status = 'C' THEN 1 END)：只有当status为C时才返回1，否则返回NULL；而COUNT函数会自动忽略NULL值，所以这就精准统计了每个学科的完成次数。
• COUNT(*)：统计每个学科的总尝试次数，这个和T-SQL里的用法一致。
• 100.0：这里用浮点数是为了避免PostgreSQL的整数除法（整数除以整数会得到整数，比如2/3会得到0，加.0就会转成浮点计算，得到0.666...）。
• ROUND(..., 2)：把结果保留两位小数，和你期望的输出格式完全匹配。

直接用样本数据的写法 #

如果不想创建临时表，也可以用CTE（公共表表达式）直接包裹你的样本数据：

WITH sample_data AS (
  SELECT 'Physics' AS topic, 'C' AS status
  UNION ALL SELECT 'Maths' AS topic, 'I' AS status
  UNION ALL SELECT 'Chemistry' AS topic, 'C' AS status
  UNION ALL SELECT 'Chemistry' AS topic, 'I' AS status
  UNION ALL SELECT 'Chemistry' AS topic, 'I' AS status
  UNION ALL SELECT 'Maths' AS topic, 'C' AS status
  UNION ALL SELECT 'Maths' AS topic, 'C' AS status
  UNION ALL SELECT 'Physics' AS topic, 'I' AS status
  UNION ALL SELECT 'Physics' AS topic, 'C' AS status
  UNION ALL SELECT 'Maths' AS topic, 'C' AS status
)
SELECT
  topic,
  ROUND(100.0 * COUNT(CASE WHEN status = 'C' THEN 1 END) / COUNT(*), 2) AS percent
FROM sample_data
GROUP BY topic
ORDER BY topic;

运行后就能得到你想要的结果：

topic   | percent
----------+---------
 Chemistry|   33.33
 Maths    |   75.00
 Physics  |   66.66

对比你之前的T-SQL写法，这个PostgreSQL版本不仅代码更简洁，而且减少了一次表扫描和JOIN操作，在数据量大的时候性能优势会很明显。

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