Ответ слишком прост для меня, поэтому я создал более общий динамический оператор SQL, который проверяет, имеет ли человек какие-либо перекрывающиеся даты.
SELECT DISTINCT T1.EmpID
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.EmpID = T2.EmpID
AND T1.JobID <> T2.JobID
AND (
(T1.DateFrom >= T2.DateFrom AND T1.dateFrom <= T2.DateTo)
OR (T1.DateTo >= T2.DateFrom AND T1.DateTo <= T2.DateTo)
OR (T1.DateFrom < T2.DateFrom AND T1.DateTo IS NULL)
)
AND NOT (T1.DateFrom = T2.DateFrom)
Похоже, что когда происходит обман, вы берете самую раннюю дату. Если это так, это работает?
select
a.id, username, email, min (ah.modified) as login_date
from accounts a join
account_history ah
on modified_acc_id = a.id
where ah.data::jsonb->>'message' = 'Logon'
group by a.id, username, email, ah.modified::date
Похоже, вам нужно количество дней для определенного периода времени. Если я правильно понимаю:
select count(*) as num_user_days_in_range
from (select a.username, date_trunc('day', ah.modified) as login_date
from accounts a join
account_history ah
on modified_acc_id = a.id
where ah.data::jsonb->>'message' = 'Logon'
group by a.username, login_date
) u
where login_date >= $date1 and login_date < $date2
DISTINCT ON
дает вам точно первый ряд упорядоченной группы. В вашем примере группа является частью id
и date
метки времени login_date
SELECT DISTINCT ON (id, login_date::date)
*
FROM (
-- <your query>
) s
ORDER BY id, login_date::date, login_date
Объяснение из ORDER BY
предложения:
Вы должны упорядочить по столбцам DISTINCT
в первую очередь. Но в вашем случае вы не хотите заказывать только по дате, но и по времени. Таким образом, после упорядочения по дате (что необходимо из-за ваших DISTINCT
столбцов) вы должны упорядочить также по отметке времени.
Таким образом, весь запрос может быть упрощен до (без подзапроса):
SELECT DISTINCT ON (a.id, ah.modified::date)
a.id,
username,
email,
ah.modified as login_date
FROM accounts a
JOIN account_history ah
ON modified_acc_id = a.id
WHERE ah.data::jsonb->>'message' = 'Logon'
ORDER BY a.id, ah.modified::date, ah.modified
использовать оконную функцию row_number()
select id,username,email,login_date from
(
select a.id, username, email, ah.modified as login_date,
row_number() over(partition by a.id, username,email order by ah.modified) rn
from accounts a join
account_history ah
on modified_acc_id = a.id
where ah.data::jsonb->>'message' = 'Logon'
) t where t.rn=1