Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Как выбрать первый приход и последний уход?  [new]
Victor256
Member

Откуда:
Сообщений: 48
Приветствую. Использую PostgreSQL 10.
Есть таблица:
CREATE TABLE "events" (
    "id" bigint DEFAULT nextval('events_id_seq') NOT NULL,
    "firm_id" integer NOT NULL,
    "employee_id" integer NOT NULL,
    "date_time" timestamp(0),
    "event_type" smallint,
    CONSTRAINT "events_pkey" PRIMARY KEY ("id"),
);

event_type - это тип события, а именно 1 = приход на работу, 2 = уход.

Требуется выбрать за заданный период, для заданных firm_id и employee_id:
1) время первого прихода и последнего ухода каждый день;
2) время нахождения на работе (каждый день), которое равно промежутку времени первого прихода и последнего ухода. Если в промежутке сотрудник уходил, это время нужно вычесть.

Подскажите, можно ли решить данную задачу только средствам БД, если да то как? Или лучше выбирать промежутки, а подсчёт делать в приложении?
13 фев 20, 19:35    [22079542]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать первый приход и последний уход?  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1455
Victor256,

а если человек пришел сегодня, а ушел уже завтра? Или пришел, но не ушел. Или умудрился уйти не приходя?

Средствами БД задачу решить можно, в случае полноты и непротиворечивости данных.
14 фев 20, 09:51    [22079758]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать первый приход и последний уход?  [new]
Victor256
Member

Откуда:
Сообщений: 48
Щукина Анна,

да, всякое может быть, в т.ч. и несколько входов/выходов подряд, например. Значит, буду делать в приложении. Спасибо за помощь.
14 фев 20, 12:10    [22079867]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать первый приход и последний уход?  [new]
Sergei.Agalakov
Member

Откуда:
Сообщений: 570
Это можно сделать и в базе, проблема только в корректной и полной постановке задачи. Предоставьте тестовые данные и ожидаемый ответ. Вам все равно потребуется это сделать вне зависимости от того, где вы хотите подготовить отчет.
15 фев 20, 00:58    [22080421]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать первый приход и последний уход?  [new]
nedba
Member

Откуда: СПб
Сообщений: 189
Victor256,
CREATE TABLE "events" (
    "id" bigserial NOT NULL,
    "firm_id" integer NOT NULL,
    "employee_id" integer NOT NULL,
    "date_time" timestamp(0),
    "event_type" smallint,  --ALTER TABLE public.events ALTER COLUMN event_type TYPE text; ('in', 'out')
    CONSTRAINT "events_pkey" PRIMARY KEY ("id")
);



INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 1, '01-01-2020 9:00', 'in');
//обедал днем
INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 1, '01-01-2020 12:20', 'out');
INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 1, '01-01-2020 13:03', 'in');
INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 1, '01-01-2020 20:00', 'out');

INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 2, '01-01-2020 9:04', 'in');
INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 2, '01-01-2020 12:20', 'out');
INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 2, '01-01-2020 13:04', 'in');
INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 2, '01-01-2020 19:50', 'out');

//пришел сам, ушел когда дверь была подперта грузчиками
INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 3, '01-01-2020 9:01', 'in');

//входил вместе с коллегами, выходил сам
INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 4, '01-01-2020 19:08', 'out');
INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 4, '01-01-2020 21:01', 'out');
INSERT INTO public.events(
	firm_id, employee_id, date_time, event_type)
	VALUES (100, 4, '02-01-2020 21:01', 'out');


select * from public.events

1	100	1	"2020-01-01 09:00:00"	"in"
2 100 1 "2020-01-01 12:20:00" "out"
3 100 1 "2020-01-01 13:03:00" "in"
4 100 1 "2020-01-01 20:00:00" "out"
5 100 2 "2020-01-01 09:04:00" "in"
6 100 2 "2020-01-01 12:20:00" "out"
7 100 2 "2020-01-01 13:04:00" "in"
8 100 2 "2020-01-01 19:50:00" "out"
9 100 3 "2020-01-01 09:01:00" "in"
10 100 4 "2020-01-01 19:08:00" "out"
11 100 4 "2020-01-01 21:01:00" "out"
12 100 4 "2020-02-01 21:01:00" "out"

select coalesce(t_in.firm_id,t_out.firm_id) firm_id, coalesce(t_in.employee_id, t_out.employee_id) employee_id, t_in.come, t_out.gone
from
(select min(date_time) come, firm_id, employee_id, event_type from public.events
where event_type = 'in'
group by to_char(date_time,'YYYYMMDD'), firm_id, employee_id, event_type) t_in

full join

(select max(date_time) gone, firm_id, employee_id, event_type from public.events
where event_type = 'out'
group by to_char(date_time,'YYYYMMDD'), firm_id, employee_id, event_type) t_out

on (t_in.firm_id, t_in.employee_id) = (t_out.firm_id, t_out.employee_id)

order by 1,2,3,4



firm_id	employee_id	come	gone
100 1 "2020-01-01 09:00:00" "2020-01-01 20:00:00"
100 2 "2020-01-01 09:04:00" "2020-01-01 19:50:00"
100 3 "2020-01-01 09:01:00" null
100 4 null "2020-01-01 21:01:00"
100 4 null "2020-02-01 21:01:00"
19 фев 20, 15:05    [22083089]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать первый приход и последний уход?  [new]
Victor256
Member

Откуда:
Сообщений: 48
nedba,

Отлично, спасибо! Буду изучать и внедрять
вчера, 12:15    [22084458]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать первый приход и последний уход?  [new]
DSKalugin
Member

Откуда: Мать городов русских
Сообщений: 355
предыдущий и мой вариант без учета выходов "на перекур"

with window_time as (
select
  e.firm_id,
  date_trunc('day', e.date_time) as work_day,
  e.employee_id,
  min(case when e.event_type=1 then e.date_time else null end) as begin_work , -- первый приход
  max(case when e.event_type=2 then e.date_time else null end) as end_work  -- последний уход
from public.events e 
group by 1,2,3 -- группировка по фирме , рабочему дню и сотруднику
)
select
  firm_id,
  work_day,
  employee_id,
  begin_work ,
  end_work,
  age(end_work, begin_work) as work_time  -- интервал между последним уходом и первым приходом за день
from window_time
order by 1,2,3 


Сообщение было отредактировано: вчера, 13:37
вчера, 13:35    [22084540]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать первый приход и последний уход?  [new]
Swa111
Member

Откуда:
Сообщений: 123
Victor256,

у нас это сделано средствами бд в хранимке, на вход подается поток событий
код сотрудника; дата время; код турникета; направление

турникеты сгруппированы в периметры охраны, периметры охраны могут быть вложенными (цод внутри здания)

после загрузки событий они сортиуются по сотруднику и по времени, и читаются последовательно.

При чтении вход группируется с выходом образую проход который записывается в отдельную таблицу.

Если есть только выход то в большенстве случаев вход проставляется по графику, но запись помечается как не достоверная, то же самое с выходом, если вход был на одной площадке выход на другой то создается два прохода один на весь период другой с нулевой продолжительностью, оба помечаются как не достоверные. Так же внесено понятие максимальная продолжительность дня, если время прохода больше нее то запись делится на две с восстановлением по графику, обе записи не достоверные.

Сбор отчета уже идет по готовой таблице. При этом есть две колонки достоверное и не достоверное время. Если у руководства возникают вопросы прикладывают логи проходов.
вчера, 14:10    [22084583]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить