PostgreSQL中按用户+级别取最早尝试记录并求和的SQL实现

需求说明 #

在PostgreSQL中拥有users、levels、attempts三张表，需实现：

• 为每个用户的每个级别筛选出created_at最早的attempts记录
• 计算每个用户的这些筛选记录的rate字段总和

表结构定义 #

CREATE TABLE IF NOT EXISTS users
(
    id       BIGSERIAL PRIMARY KEY,
    nickname VARCHAR(255) UNIQUE
);

CREATE TABLE IF NOT EXISTS levels
(
    id    BIGSERIAL PRIMARY KEY,
    title VARCHAR(255)   NOT NULL,
);

CREATE TABLE IF NOT EXISTS attempts
(
    id         BIGSERIAL PRIMARY KEY,
    rate       INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL,
    level_id   BIGINT REFERENCES levels (id),
    user_id    BIGINT REFERENCES users (id)
);

示例attempts数据 #

id | rate | created_at                 | level_id | user_id
------------------------------------------------------------
1  | 10   | 2022-10-21 16:53:13.818000 | 1        | 1       
2  | 20   | 2022-10-21 11:53:13.818000 | 1        | 1       
3  | 30   | 2022-10-21 14:53:13.818000 | 1        | 1       
4  | 40   | 2022-10-21 10:53:13.818000 | 2        | 1          -- (nickname = 'Joe')
5  | 100  | 2022-11-21 10:53:13.818000 | 1        | 2          -- (nickname = 'Max') 

期望查询结果 #

nickname | sum
-----------------
Max      | 100
Joe      | 60

现有初步SQL（需补充筛选逻辑） #

select u.nickname, sum(a.rate) as sum
from attempts a
         inner join users u on a.user_id = u.id
         inner join levels l on l.id = a.level_id
         -- on a.created_at is the earliest for level and user
group by u.id
order by sum desc

解决方案 #

方法一：使用窗口函数ROW_NUMBER() #

通过窗口函数为每个用户+级别的分组内记录按created_at排序，筛选出最早记录后求和：

SELECT u.nickname, SUM(filtered.rate) AS sum
FROM (
    SELECT 
        user_id, 
        rate,
        -- 按用户和级别分组，按创建时间升序编号，最早记录编号为1
        ROW_NUMBER() OVER (PARTITION BY user_id, level_id ORDER BY created_at ASC) AS rn
    FROM attempts
) AS filtered
JOIN users u ON filtered.user_id = u.id
WHERE filtered.rn = 1 -- 筛选每个用户每个级别的最早记录
GROUP BY u.id, u.nickname
ORDER BY sum DESC;

方法二：使用PostgreSQL特有的DISTINCT ON #

利用PostgreSQL专属特性直接获取每个用户+级别分组内的最早记录：

SELECT u.nickname, SUM(a.rate) AS sum
FROM (
    -- 按用户和级别分组，返回每组中创建时间最早的第一条记录
    SELECT DISTINCT ON (user_id, level_id)
        user_id, rate
    FROM attempts
    ORDER BY user_id, level_id, created_at ASC
) AS a
JOIN users u ON a.user_id = u.id
GROUP BY u.id, u.nickname
ORDER BY sum DESC;

说明 #

• 方法一兼容性更强，适用于多数支持窗口函数的数据库
• 方法二更简洁，是PostgreSQL专属优化写法

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