ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как выбрать первый приход и последний уход?
8 сообщений из 8, страница 1 из 1
Как выбрать первый приход и последний уход?
    #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
13.02.2020, 19:35
| Ответить | Цитировать | Написать  
Как выбрать первый приход и последний уход?
    #39926562
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
Victor256,

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

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

да, всякое может быть, в т.ч. и несколько входов/выходов подряд, например. Значит, буду делать в приложении. Спасибо за помощь.
...
Рейтинг: 0 / 0
14.02.2020, 12:10
| Ответить | Цитировать | Написать  
Как выбрать первый приход и последний уход?
    #39926955
Sergei.Agalakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergei.Agalakov
Участник
Это можно сделать и в базе, проблема только в корректной и полной постановке задачи. Предоставьте тестовые данные и ожидаемый ответ. Вам все равно потребуется это сделать вне зависимости от того, где вы хотите подготовить отчет.
...
Рейтинг: 0 / 0
15.02.2020, 00:58
| Ответить | Цитировать | Написать  
Как выбрать первый приход и последний уход?
    #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
19.02.2020, 15:05
| Ответить | Цитировать | Написать  
Как выбрать первый приход и последний уход?
    #39929386
Victor256
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Victor256
Гость
nedba,

Отлично, спасибо! Буду изучать и внедрять
...
Рейтинг: 0 / 0
21.02.2020, 12:15
| Ответить | Цитировать | Написать  
Как выбрать первый приход и последний уход?
    #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, 13:35
| Ответить | Цитировать | Написать  
Как выбрать первый приход и последний уход?
    #39929476
Swa111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Swa111
Гость
Victor256,

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

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

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

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

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

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


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