时间戳

首页 > 解决方案 > 使用 row_partition 内的过滤器过滤和排名（使用 row_partition）

sql - 使用 row_partition 内的过滤器过滤和排名（使用 row_partition）

问题描述

我有一个表Jobs存储来自 * Users帖子的每个用户的一堆 Jobs 。每个作业都有一个状态。我的第一个目标是为每个用户确定第一个已完成（状态 = 已完成）的作业。我能够这样做使用：

SELECT
    user_id AS user_id,
    starts_time AS starts_time,
    id AS job_id
FROM (
    SELECT
        user_id,
        starts_time,
        id,
        --sort by starts time, and rank ascending
        Row_number() OVER (PARTITION BY User_id ORDER BY Starts_time ASC) AS Rn
    FROM
        jobs
    WHERE
        --status 2 is completed
        status = 2
    GROUP BY
        user_id,
        assignment_id,
        id ORDER BY
            user_id) AS jobs
WHERE
    rn = 1

这是它返回的内容：

user_id   | starts_time             |  job_id |
-----------------------------------------------
 123      | 2016-04-18 14:30:00+00  |   1292  |
 124      | 2016-04-18 19:00:00+00  |   2389  |
 128      | 2016-04-16 13:00:00+00  |   3201  |

就像某些上下文一样，在很多情况下，用户的第一份工作不是状态为“已完成”的工作。例如，他们会在看到已完成的工作之前发布具有以下任何一种状态的工作列表：（“未完成”、“无效”、“已取消”）

对于每个用户，我想确定在该用户看到他们第一个完成的工作之前有哪些工作。我希望上面的查询将是一个起点，从那我可以说为每个用户返回我的任何工作，这些用户的starts_time在第一个工作完成之前

*抱歉，如果这令人困惑，这是我第一次在 Stack Overflow 上发帖寻求帮助，感谢任何建设性的批评！

标签： sqlpostgresqldatetimegaps-and-islands

解决方案

对于每个用户，我想确定在该用户看到他们第一个完成的工作之前有哪些工作。

对于每个用户，您希望所有记录的第一个状态为“2”。您可以使用窗口函数：

select *
from (
    select j.*,
        bool_or(status = 2) over(partition by user_id order by starts_time) as flag
    from jobs j
) t
where not flag

bool_or检查当前行或任何前一行是否满足条件。

如果要保留第一个状态 2，则只需over()将窗口函数的子句更改为不考虑当前行：

select *
from (
    select j.*,
        bool_or(status = 2) over(
            partition by user_id 
            order by starts_time rows between unbounded preceding and 1 preceding
        ) as flag
    from jobs j
) t
where flag is distinct from true

推荐阅读