оконная функция postgres sql - попытка консолидации array_agg на основе даты и employee_id

В настоящее время я использую postgres, и есть эта оконная функция sql, которая используется для генерации времени прихода и ухода сотрудников на основе даты. Но это ошибочно.

CREATE OR REPLACE VIEW view_test
 AS
 SELECT row_number() OVER () AS id,
    a.created_at::date AS created_at,
    date_part('year'::text, a.created_at) AS year,
    date_part('month'::text, a.created_at) AS month,
    date_part('day'::text, a.created_at) AS day,
    date_part('dow'::text, a.created_at) AS dow,
    a.company_id,
    a.employee_id,
    e.employee_type,
    array_agg(
        CASE
            WHEN a.activity_type = 1
            THEN a.created_at::time(0) 
            ELSE NULL::time
        END) AS time_in,
    array_agg(
        CASE
            WHEN a.activity_type = 2 
            THEN a.created_at::time(0) 
            ELSE NULL::time
        END) AS time_out
   FROM attendance_table a
     LEFT JOIN employee_table e ON e.id = a.employee_id
  GROUP BY a.created_at, date_part('day'::text, a.created_at),  
           a.employee_id, a.company_id, e.employee_type
  ORDER BY date_part('year'::text, a.created_at), date_part('month'::text, a.created_at), 
           date_part('day'::text, a.created_at), a.employee_id;

это сгенерирует этот результат

создает это данные

Я пытаюсь создать таким образом, где time_in и time_out объединены на основе created_at (date) и employee_id. Идея в том, чтобы для каждой даты я знал время прихода и ухода сотрудника. предостережение: {NULL} не должен появляться, если в массиве есть записи.

пытается генерировать эти данные

вид и поля данных, которыми я пытаюсь манипулировать

https://docs.google.com/spreadsheets/d/1hn3w0mnezrV6_f-fPAKPZHuqdDjBBc_ArcgeBmgblq4/edit?usp=sharing

Как я могу изменить представление sql функции Windows выше, чтобы time_in и time_out объединялись на основе created_at (date) и employee_id? (последний снимок экрана - это желаемый результат) предостережение: {NULL} не должен появляться, если в массиве есть записи.

ОБНОВЛЕНИЕ (очень близкий ответ, но не совсем решенный):

CREATE OR REPLACE VIEW view_group
 as 
 SELECT
    created_at,
    year,
    month,
    day,
    dow,
    company_id,
    employee_id,
    array_agg(check_in) as time_in,
    array_agg(check_out) as time_out
FROM
    view_test, unnest(time_in) check_in, unnest(time_out) check_out
GROUP BY 1,2,3,4,5,6,7

он производит это:

почти готово

удается объединить массив с помощью unnest. Но обратите внимание, что NULL все еще там. Как удалить NULL (но если ничего нет, должно быть NULL)? или что-то разумное и возможное.


postgresql sql window-functions group-by array-agg
person Axil    schedule 23.11.2020    source источник
comment
Пожалуйста, добавьте исходные данные. Кроме того, пожалуйста, не используйте изображения, потому что невозможно скопировать данные с изображений для собственных тестов.   -  person S-Man    schedule 23.11.2020
comment
Почему вы хотите возвращать массивы времени вместо времени?   -  person S-Man    schedule 23.11.2020

Ответы (1)


arrow_upward
1
arrow_downward

Не зная ваших исходных данных, я могу работать только с вашим текущим результатом.

Чтобы превратить ваш результат в ожидаемый, вы можете просто добавить групповой шаг:

SELECT
    created_at,
    year,
    month,
    day,
    dow,
    company_id,
    employee_id,
    MAX(time_in) as time_in,
    MAX(time_out) as time_out
FROM
    -- <your query>
GROUP BY 1,2,3,4,5,6,7

Изменить: ДО добавлены исходные данные

Вам нужен простой шаг поворота. Этого можно добиться с помощью условного агрегирования (предложения GROUP BY и FILTER):

demo: db ‹› fiddle

SELECT 
    created_at::date,
    company_id,
    employee_id,
    MAX(created_at::time) FILTER (WHERE activity_type = 1),
    MAX(created_at::time) FILTER (WHERE activity_type = 2)
FROM
   attendance
GROUP BY created_at::date, company_id, employee_id
person S-Man    schedule 23.11.2020
comment
Я поместил эту информацию в пример данных и полей здесь: - person Axil; 24.11.2020
comment
Сделал тест, так и получилось, дата и employee_id сгруппированы. на основе этих двух полей нет дополнительных строк. но time_in и time_out не объединяются, результат time_in и time_out отображается как {NULL}. Это следующее важное поле, где мне нужно консолидировать все сгруппированные результаты. - person Axil; 24.11.2020
comment
(также {NULL}) НЕ должен появляться, если в массиве есть данные). Возможно, есть способ удалить NULL при начальном просмотре. - person Axil; 24.11.2020
comment
Я нашел что-то похожее на это. stackoverflow .com / questions / 51101596 /. Однако приведенный пример отличается в одном поле, а я хотел бы сгруппировать по 2 полям (date (created_at) и employee_id) - person Axil; 24.11.2020
comment
Может быть, использовать array_agg (time_in)? но жалобы - не удалось найти тип массива для типа данных time без часового пояса [] - person Axil; 24.11.2020
comment
Добавлено решение на основе ваших исходных данных - person S-Man; 24.11.2020
comment
Спасибо. Мне нравится поворотное решение. - person Axil; 25.11.2020