Теперь когда у меня есть Postgres 🐘 на компе (пост как установить тут) и она подключена к DBeaver,я могу показать вам как писать оконки на SQL. В одном из тестовых было следующее задание. Когда люди входят/выходят из здания, то это логгируется. Записывается время входа/выхода (pass_date), номер сотрудника (tab_id), адрес офиса (pass_name) и pass_direction (1 – вход, 0 - выход).
Нужно посчитать чистые рабочие часы сотрудников по дням без времени “на покурить” и обеды, когда они выходили из здания. Они могут ходить на улицу по несколько раз за день. И это тоже нужно учесть. Иногда сотрудники несколько раз прикладывают свой пропуск или пропускают по своему пропуску коллег, такое нужно исключать.

Тут нам поможет оконная функция LAG. Она используется для сравнения текущего значения колонки с предыдущим значением (в скобках смещение на 1 указала). Вот так мы узнаем время предыдущей записи:

lag (pass_date,1) over (partition by tab_id order by pass_date)

pass_date - это столбец, значение которого нужно сравнить с предыдущим значением.
partition by tab_id – берем партиции по номеру сотрудника и сортируем по дню order by pass_date

Когда мы из pass_date вычтем получившееся значение, то это будет разница между текущим временем (текущей строчкой) и временем предыдущего действия (вход либо выход) - time_diff

pass_date - lag (pass_date,1) over (partition by tab_id order by pass_date) as time_diff

Далее суммируем показатели текущего и предыдущего действия PASS_DIRECTION, тоже используя схему с lag

pass_direction +lag(pass_direction,1) over (partition by tab_id order by pass_date) as sum_enter

А теперь завернем все в подзапрос и прикрутим условие с фильтром. Мы суммируем время только когда выполняются два условия: sum_enter = 1 и pass_direction =0

sum(time_diff) filter (where (sum_enter = 1) and pass_direction =0)

В итоге:

select

  working_date,

  tab_id,

  sum(time_diff) filter (where (sum_enter = 1) and pass_direction = 0)

from

  (

  select

    date(pass_date) as working_date,

    pass_date,

    tab_id,

    pass_direction,

    -- разница между текущим временем и временем предыдущего действия 

    pass_date - lag (pass_date,  1) over (partition by tab_id order by pass_date) as time_diff,

    -- суммируем показатели текущего и предыдущего действия (0+1 - то, что нужно), а если 1+1=2 - такое нам не надо

    pass_direction + lag(pass_direction, 1) over (partition by tab_id order by pass_date) as sum_enter

  from

    test.newtable

  order by

    pass_date  

) t1

group by 1, 2

💡 Тренируйте оконки, это прямо уже must have на собесах! Файл с данными забирайте для тренировки! 👌