#sql #postgresql #postgresql-9.4
#sql #postgresql #postgresql-9.4
Вопрос:
У меня есть приведенные ниже таблицы:
1) Таблица моей компании
id | c_name | c_code | status
---- ------------ ---------- --------
1 | AAAAAAAAAA | AA1234 | Active
2) Моя пользовательская таблица
id | c_id | u_name | status | emp_id
---- ------------ ---------- -------- --------
1 | 1 | XXXXXXXX | Active | 1
2 | 1 | YYYYYYYY | Active | 2
3) Моя таблица посещаемости
id | u_id | swipe_time | status
---- -------- ------------------------ --------
1 | 1 | 2020-08-20 16:00:00 | IN
2 | 1 | 2020-08-20 20:00:00 | OUT
3 | 1 | 2020-08-20 21:00:00 | IN
4 | 1 | 2020-08-21 01:00:00 | OUT
5 | 1 | 2020-08-21 16:00:00 | IN
6 | 1 | 2020-08-21 19:00:00 | OUT
Мне нужно рассчитать посещаемость, сгруппированную по дате, u_id, как показано ниже:
Примечание: Параметрами запроса будут «С даты», «На сегодняшний день» и «Идентификатор компании»
u_id | u_name | date | in_time | out_time | hrs
----- ----------- ------------- ---------------------- ---------------------- -----
1 | XXXXXXXX | 2020-08-20 | 2020-08-20 16:00:00 | 2020-08-21 01:00:00 | 7
1 | XXXXXXXX | 2020-08-21 | 2020-08-21 16:00:00 | 2020-08-21 19:00:00 | 4
2 | YYYYYYYY | null | null | null | 0
Возможно ли это в PostgreSQL?
Комментарии:
1. Решение SQL @a_horse_with_no_name
2. Спасибо за код. Да, @a_horse_with_no_name, это сложнее. Здесь я хочу перечислить всех пользователей в одной компании с нулевыми значениями, как указано в последней таблице. Как нам это сделать?
3. Есть ли у вас уровень, на котором у вас есть доступ к совместимому или интерпретируемому языку до того, как данные достигнут места назначения. Если да, сделайте это там. 1. Логика намного проще в обращении. 2. способ значительно упростить отладку, если что-то пойдет не так. 3. Могу выполнить модульное тестирование, если требуется, лично я придерживаюсь минималистичной логики извлечения в sql.
Ответ №1:
Сложная часть состоит в том, чтобы расширить одну строку, которая охватывает два (календарных) дня, до двух строк и правильно распределить часы «следующего» дня.
Первая часть заключается в получении сводной таблицы, которая объединяет пары ВВОДА / вывода в одну строку.
Простой (но не очень эффективный) подход заключается в:
select ain.u_id,
ain.swipe_time as time_in,
(select min(aout.swipe_time)
from attendance aout
where aout.u_id = ain.u_id
and aout.status = 'OUT'
and aout.swipe_time > ain.swipe_time) as time_out
from attendance ain
where ain.status = 'IN'
Следующий шаг — разбить строки, содержащие более одного дня, на две строки.
Это предполагает, что у вас никогда не бывает пары ВВОДА / вывода, которая охватывает более двух дней!
with inout as (
select ain.u_id,
ain.swipe_time as time_in,
(select min(aout.swipe_time)
from attendance aout
where aout.u_id = ain.u_id
and aout.status = 'OUT'
and aout.swipe_time > ain.swipe_time) as time_out
from attendance ain
where ain.status = 'IN'
), expanded as (
select u_id,
time_in::date as "date",
time_in,
time_out
from inout
where time_in::date = time_out::date
union all
select i.u_id,
x.time_in::date as date,
x.time_in,
x.time_out
from inout i
cross join lateral (
select i.u_id,
i.time_in,
i.time_in::date 1 as time_out
union all
select i.u_id,
i.time_out::date,
i.time_out
) x
where i.time_out::date > i.time_in::date
)
select *
from expanded;
Приведенное выше возвращает следующее для вашего примера данных:
u_id | date | time_in | time_out
----- ------------ --------------------- --------------------
1 | 2020-08-20 | 2020-08-20 16:00:00 | 2020-08-20 20:00:00
1 | 2020-08-20 | 2020-08-20 21:00:00 | 2020-08-21 00:00:00
1 | 2020-08-21 | 2020-08-21 00:00:00 | 2020-08-21 01:00:00
1 | 2020-08-21 | 2020-08-21 16:00:00 | 2020-08-21 19:00:00
Как это работает?
Итак, сначала мы выбираем все те строки, которые начинаются и заканчиваются в один и тот же день с помощью этой части:
select u_id,
time_in::date as "date",
time_in,
time_out
from inout
where time_in::date = time_out::date
Вторая часть объединения разделяет строки, охватывающие два дня, с помощью перекрестного соединения, которое генерирует одну строку с исходным временем начала и полуночью, а другую — с полуночи до исходного времени окончания:
select i.u_id,
x.time_in::date as date,
x.time_in,
x.time_out
from inout i
cross join lateral (
-- this generates a row for the first of the two days
select i.u_id,
i.time_in,
i.time_in::date 1 as time_out
union all
-- this generates the row for the next day
select i.u_id,
i.time_out::date,
i.time_out
) x
where i.time_out::date > i.time_in::date
В конце новые «расширенные» строки затем агрегируются путем группировки их по пользователю и дате и оставляются присоединенными к users таблице, чтобы также получить имя пользователя.
with inout as (
select ain.u_id,
ain.swipe_time as time_in,
(select min(aout.swipe_time)
from attendance aout
where aout.u_id = ain.u_id
and aout.status = 'OUT'
and aout.swipe_time > ain.swipe_time) as time_out
from attendance ain
where ain.status = 'IN'
), expanded as (
select u_id,
time_in::date as "date",
time_in,
time_out
from inout
where time_in::date = time_out::date
union all
select i.u_id,
x.time_in::date as date,
x.time_in,
x.time_out
from inout i
cross join lateral (
select i.u_id,
i.time_in,
i.time_in::date 1 as time_out
union all
select i.u_id,
i.time_out::date,
i.time_out
) x
where i.time_out::date > i.time_in::date
)
select u.id,
u.u_name,
e."date",
min(e.time_in) as time_in,
max(e.time_out) as time_out,
sum(e.time_out - e.time_in) as duration
from users u
left join expanded e on u.id = e.u_id
group by u.id, u.u_name, e."date"
order by u.id, e."date";
Что затем приводит к:
u_id | date | time_in | time_out | duration
----- ------------ --------------------- --------------------- ----------------------------------------------
1 | 2020-08-20 | 2020-08-20 16:00:00 | 2020-08-21 00:00:00 | 0 years 0 mons 0 days 7 hours 0 mins 0.0 secs
1 | 2020-08-21 | 2020-08-21 00:00:00 | 2020-08-21 19:00:00 | 0 years 0 mons 0 days 4 hours 0 mins 0.0 secs
Столбец «продолжительность» — это столбец, interval который вы можете отформатировать по своему вкусу.
Комментарии:
1. Большое вам спасибо за ваши усилия по предоставлению подробного ответа. Сейчас я пытаюсь это понять и буду использовать.
2. Это работает отлично, когда записи «IN» и «OUT» верны. Но я сталкиваюсь с проблемой, если пользователь пропустил один «ВЫХОД». Пожалуйста, помогите мне в этом. Например, (1, 1, ‘2020-08-20 16:00:00’, ‘ В’), (2, 1, ‘2020-08-20 17:10:00’, ‘ В’), (3, 1, ‘2020-08-20 20:00:00’, ‘ ВЫХОД’);
3. a_horse_with_no_name @, не могли бы вы проверить приведенный выше случай и помочь мне в этом?
Ответ №2:
Использование функции окна вывода делает его несколько более простым и читаемым. Для сбалансированных событий посещаемости на ВХОДЕ и ВЫХОДЕ это будет работать нормально, в противном случае для часов посещаемости будут нулевые значения. Это имеет смысл, потому что либо человек еще не ушел, либо еще не посещал, либо данные о посещаемости повреждены.
select
u.id u_id, u.u_name,
t.date_in date, t.t_in in_time, t.t_out out_time,
extract('hour' from t.t_out - t.t_in) hrs
from users u
left outer join
(
select u_id,
date_trunc('day', swipe_time) date_in,
swipe_time t_in,
lead(swipe_time, 1) over (partition by u_id order by u_id, swipe_time) t_out,
status
from attendance
) t
on u.id = t.u_id
where t.status = 'IN';