按日期和用户分组获取首个与最后一个job_id

我有两张表：jobs和users

jobs表结构示例 #

users表结构示例 #

我需要按日期和用户分组，在同一行中获取每个用户每日的首个job_id和最后一个job_id，预期结果如下：

预期结果 #

解决方案 #

可以通过关联两张表，结合窗口函数实现需求，以下提供两种兼容主流数据库的写法：

写法一：基于ROW_NUMBER()分组筛选

WITH job_user AS (
    SELECT 
        u.user_id,
        DATE(j.created_at) AS date,
        j.id AS job_id,
        -- 按创建时间升序标记行号
        ROW_NUMBER() OVER (PARTITION BY u.user_id, DATE(j.created_at) ORDER BY j.created_at ASC) AS rn_asc,
        -- 按创建时间降序标记行号
        ROW_NUMBER() OVER (PARTITION BY u.user_id, DATE(j.created_at) ORDER BY j.created_at DESC) AS rn_desc
    FROM users u
    JOIN jobs j ON u.job_id = j.id
)
SELECT 
    user_id,
    date,
    MAX(CASE WHEN rn_asc = 1 THEN job_id END) AS first_id,
    MAX(CASE WHEN rn_desc = 1 THEN job_id END) AS last_id
FROM job_user
GROUP BY user_id, date;

写法二：基于FIRST_VALUE()/LAST_VALUE()窗口函数

SELECT DISTINCT
    u.user_id,
    DATE(j.created_at) AS date,
    FIRST_VALUE(j.id) OVER (
        PARTITION BY u.user_id, DATE(j.created_at) 
        ORDER BY j.created_at ASC
    ) AS first_id,
    LAST_VALUE(j.id) OVER (
        PARTITION BY u.user_id, DATE(j.created_at) 
        ORDER BY j.created_at ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_id
FROM users u
JOIN jobs j ON u.job_id = j.id;

两种写法均能得到预期结果，写法一兼容更多数据库版本，写法二适合支持完整窗口函数特性的数据库（如MySQL 8.0+、PostgreSQL等）。

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