Фильтровать и ранжировать (используя row_partition) с фильтром внутри row_partition

У меня есть таблица Jobs, в которой хранится куча заданий каждого пользователя из сообщений *Users. У каждой работы есть статус. Моя первая цель — определить первое завершенное (статус = завершенное) задание для каждого пользователя. Я смог сделать это, используя:

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  |

Как некоторый контекст, есть много случаев, когда первое задание пользователя не является заданием со статусом «завершено». Например, они будут публиковать список вакансий, которые имеют один из следующих статусов, прежде чем они увидят завершенную работу: ("Незаполнено", "Аннулировано", "Отменено").

Для каждого пользователя я хочу установить, какие задания были выполнены до того, как этот пользователь увидел свою первую завершенную работу. Я надеялся, что приведенный выше запрос станет отправной точкой, и поэтому я могу просто сказать, верните мне любое задание для каждого пользователя, у которого start_time предшествует первому выполненному заданию.

* Извините, если это сбивает с толку, я впервые обращаюсь за помощью в Stack Overflow, любая конструктивная критика приветствуется!

sql postgresql datetime gaps-and-islands

22.12.2020 22:56

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Для каждого пользователя я хочу установить, какие задания были выполнены до того, как этот пользователь увидел свою первую завершенную работу.

Для каждого пользователя вы хотите, чтобы все записи имели первый статус «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

Вау, это сработало блестяще, спасибо, я ценю это! Не могли бы вы, возможно, кратко объяснить, как оконная функция работает в этом контексте? Это что-то вроде оператора If? И можно ли повозиться, чтобы включить в результат и фактическую выполненную работу?

— 22.12.2020 23:57

22.12.2020 23:05