Гость
Войти | Профиль | Очистить
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как выбрать первый приход и последний уход? / 8 сообщений из 8, страница 1 из 1
13.02.2020, 19:35
    #39926415
Victor256
Victor256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать первый приход и последний уход?
Приветствую. Использую PostgreSQL 10.
Есть таблица:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
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) время нахождения на работе (каждый день), которое равно промежутку времени первого прихода и последнего ухода. Если в промежутке сотрудник уходил, это время нужно вычесть.

Подскажите, можно ли решить данную задачу только средствам БД, если да то как? Или лучше выбирать промежутки, а подсчёт делать в приложении?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.02.2020, 09:51
    #39926562
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать первый приход и последний уход?
Victor256,

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

Средствами БД задачу решить можно, в случае полноты и непротиворечивости данных.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.02.2020, 12:10
    #39926623
Victor256
Victor256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать первый приход и последний уход?
Щукина Анна,

да, всякое может быть, в т.ч. и несколько входов/выходов подряд, например. Значит, буду делать в приложении. Спасибо за помощь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2020, 00:58
    #39926955
Sergei.Agalakov
Sergei.Agalakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать первый приход и последний уход?
Это можно сделать и в базе, проблема только в корректной и полной постановке задачи. Предоставьте тестовые данные и ожидаемый ответ. Вам все равно потребуется это сделать вне зависимости от того, где вы хотите подготовить отчет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.02.2020, 15:05
    #39928489
nedba
nedba
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать первый приход и последний уход?
Victor256,
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
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")
);




Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
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');



Код: sql
1.
select * from public.events


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
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"

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
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




Код: plaintext
1.
2.
3.
4.
5.
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"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2020, 12:15
    #39929386
Victor256
Victor256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать первый приход и последний уход?
nedba,

Отлично, спасибо! Буду изучать и внедрять
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2020, 13:35
    #39929446
DSKalugin
DSKalugin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать первый приход и последний уход?
предыдущий и мой вариант без учета выходов "на перекур"

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2020, 14:10
    #39929476
Swa111
Swa111
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать первый приход и последний уход?
Victor256,

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

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

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

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

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

Сбор отчета уже идет по готовой таблице. При этом есть две колонки достоверное и не достоверное время. Если у руководства возникают вопросы прикладывают логи проходов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как выбрать первый приход и последний уход? / 8 сообщений из 8, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 417ms
Закрыть
start [/forum/topic.php?fid=53&tablet=1&tid=1994809]:
 0ms
get settings:
 12ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 36ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 43ms
get tp. blocked users:
 2ms
others: 287ms
total:  417ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]