PostgreSQL单查询实现特定用户最新地点志愿时长占比计算

需求是编写单条PostgreSQL查询，计算指定用户（比如userId=2）在最新插入记录对应的地点的总志愿时长，占该地点所有用户总志愿时长的百分比。现有entries表结构及示例数据如下：

CREATE TABLE entries (
  id INT,
  userId INT,
  volunteerHours DOUBLE PRECISION NOT NULL,
  location INT
);

INSERT INTO entries (id, userId, volunteerHours, location) VALUES (1, 1, 3.0, 2);
INSERT INTO entries (id, userId, volunteerHours, location) VALUES (2, 1, 3.0, 1);
INSERT INTO entries (id, userId, volunteerHours, location) VALUES (3, 1, 3.0, 1);
INSERT INTO entries (id, userId, volunteerHours, location) VALUES (4, 2, 3.0, 1);
INSERT INTO entries (id, userId, volunteerHours, location) VALUES (5, 2, 3.0, 1);

需要避免重复查询最新地点的问题，最终返回结果如0.50（即50%）。

优化后的SQL查询（简洁高效版） #

SELECT ROUND(
    (SUM(CASE WHEN userId = 2 THEN volunteerHours ELSE 0 END) / SUM(volunteerHours))::NUMERIC,
    2
) AS percentage
FROM entries
WHERE location = (
    SELECT location
    FROM entries
    WHERE userId = 2
    ORDER BY id DESC
    LIMIT 1
);

实现思路 #

1. 一次性获取最新地点：通过子查询筛选目标用户的所有记录，按id降序取第一条（假设id为自增主键，代表插入顺序），仅执行一次地点查询，避免重复计算。
2. 单扫描完成聚合计算：外层查询直接筛选该地点的所有记录，用CASE表达式统计目标用户的总志愿时长，同时统计该地点所有用户的总时长，直接计算占比。
3. 格式化结果：将占比转换为NUMERIC类型后保留两位小数，得到符合要求的百分比结果。

另一种CTE写法（可读性更强） #

如果需要更清晰的逻辑拆分，可以用CTE（公共表表达式）实现：

WITH user_latest_loc AS (
    SELECT location
    FROM entries
    WHERE userId = 2
    ORDER BY id DESC
    LIMIT 1
),
user_loc_total AS (
    SELECT SUM(volunteerHours) AS user_hours
    FROM entries
    WHERE userId = 2 AND location = (SELECT location FROM user_latest_loc)
),
loc_total AS (
    SELECT SUM(volunteerHours) AS loc_hours
    FROM entries
    WHERE location = (SELECT location FROM user_latest_loc)
)
SELECT ROUND((user_hours / loc_hours)::NUMERIC, 2) AS percentage
FROM user_loc_total, loc_total;

这种写法将“获取地点”“统计用户时长”“统计地点总时长”拆分为独立步骤，逻辑更直观，适合复杂场景的扩展。

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