sql - 每个用户 ID 的 first_value 和 last_value

Question

我正在尝试为我的 scheduled_jobs 表中的每个用户获取某个日期的第一个预定开始时间和最后一个预定结束时间。

我可以让它为一个用户工作，但是当每天为所有用户尝试时，我可以获得最后一次，但是第一次无法正确显示所有 user_id 的第一个 user_id 的时间。

这是我的代码:

SELECT DISTINCT on (user_id)
    user_id, first_value(scheduled_jobs.at) over (order by user_id, scheduled_jobs.at ASC),
    last_value(scheduled_jobs.to) over (order by user_id, scheduled_jobs.at DESC)
FROM scheduled_jobs
WHERE scheduled_jobs.at between CURRENT_DATE+INTERVAL'3 day' and CURRENT_DATE +INTERVAL '4 day'

当前结果示例:

user_id | first_value         | last_value
  19    | 2018-10-29 07:00:00 | 2018-10-29 17:00:00
  30    | 2018-10-29 07:00:00 | 2018-10-29 15:00:00
  37    | 2018-10-29 07:00:00 | 2018-10-29 16:30:00 

Last_value 为每个 user_id 正确显示，但是 first_value 始终显示所有第一个 user_id 的值。

我已尝试使用 JOIN 和 USING 查询将它们拆分为不同的 SELECT 查询，但 first_value 的结果仍然不正确。

Answer 1

您需要一个 PARTITION BY 子句，它根据 user_id 生成帧

SELECT DISTINCT on (user_id)
    user_id, 
    first_value(sj.at) OVER (PARTITION BY user_id ORDER BY sj.at ASC),
    last_value(sj.to) OVER (PARTITION BY user_id ORDER BY sj.at DESC)
FROM 
    scheduled_jobs sj
WHERE 
    sj.at BETWEEN CURRENT_DATE + 3 and CURRENT_DATE + 4

---

另外:请小心使用last_value。有时它不会按预期工作。 See here

您应该使用 first_value 和 DESC 排序:

first_value(scheduled_jobs.at) over (partition by user_id order by scheduled_jobs.at DESC)