Блог

у меня есть таблица postgres, как показано ниже

Результирующая таблица, которую мы пытаемся достичь, выглядит следующим образом:

Запрос, который я уже пробовал, приведен ниже:

 select * from 
(
select  "User Name" , "Event" , "Date" , "IP Address" 
from log_activities log_in 
where "Event" = 'User Logged In'
)log_in
left join 
(
select  "User Name" , "Event" , "Date" , "IP Address" 
from log_activities log_out 
where "Event" = 'User Logged Out'
)log_out
on
log_in."User Name" = log_out."User Name" 
and TO_DATE(log_in."Date" ,'DD/MM/YYYY') = TO_DATE(log_out."Date" ,'DD/MM/YYYY')
and log_in."Date"  < log_out."Date" 
and log_in."IP Address" = log_out."IP Address"
 

Вы можете использовать lead аналитическую функцию и CASE..WHEN следующим образом:

 select t.username, 
       t.event as login_event, 
       t.date as login_date,
       case when t.lead_event = 'Log Out' then t.lead_event end as logout_event, 
       case when t.lead_event = 'Log Out' then t.lead_date end as logout_date
from (select t.*,
             lead(event) over (partition by username order by date) as lead_event,
             lead(date) over (partition by username order by date) as lead_date
      from log_activities t
     ) t
where t.event = 'Log in';
 

Я рассматриваю это как «Я хочу, чтобы все события входа в систему. Затем я хочу, чтобы следующий выход из системы был следующим событием «. Если это так, lead() кажется наиболее полезным подходом:

 select la.username, la.event as login_event, la.date as login_date,
       la.next_event as logout_event, la.next_date as logout_date
from (select la.*
             lead(event) over (partition by username order by date) as next_event,
             lead(date) over (partition by username order by date) as next_date
      from log_activities la
     ) la
where event = 'Log in';
 

Это похоже на проблему с пробелами и островами. Я бы рекомендовал подсчитать количество входов в систему для создания групп, а затем агрегации:

 select username, 
    'Log in' as log_in_event,
    min(date) as log_in_date,
    max(event) filter(where event = 'Log Out') as log_out_event,
    max(date)  filter(where event = 'Log Out') as log_out_date
from (
    select la.*,
        count(*) 
            filter(where event = 'Log in') 
            over(partition by username order by date) as grp
    from log_activities la
) la
group by username, grp