SQL - How to identify 1 hour time period islands in the given data?

user_10344006 0
Viewed 111

The objective is to accept the first complaint received and reject all the complaints received in the 1 hour following the first complaint. For example I have the data below.

ComplaintID DateTime
1 12/24/2019 1:07 PM
2 12/24/2019 1:20 PM
3 12/24/2019 1:40 PM
4 12/24/2019 2:00 PM
5 12/24/2019 2:10 PM
6 12/24/2019 2:12 PM
7 12/24/2019 2:50 PM
8 12/24/2019 2:55 PM
9 12/24/2019 3:00 PM
10 12/24/2019 3:08 PM
11 12/24/2019 4:00 PM
12 12/24/2019 4:50 PM
13 12/24/2019 7:00 PM
14 12/26/2019 7:01 PM

Required Output:

ComplaintID DateTime Status
1 12/24/2019 1:07 PM Accept
2 12/24/2019 1:20 PM Reject
3 12/24/2019 1:40 PM Reject
4 12/24/2019 2:00 PM Reject
5 12/24/2019 2:10 PM Accept
6 12/24/2019 2:12 PM Reject
7 12/24/2019 2:50 PM Reject
8 12/24/2019 2:55 PM Reject
9 12/24/2019 3:00 PM Reject
10 12/24/2019 3:08 PM Reject
11 12/24/2019 4:00 PM Accept
12 12/24/2019 4:50 PM Reject
13 12/24/2019 7:00 PM Accept
14 12/26/2019 7:01 PM Accept

I understand that this would be easy using a programming language, however I need a solution in SQL.

EDIT:

Based on @Gordon's suggestion, I implemented the following recursive query and it works! However, it seems inefficient on large data.

with RECURSIVE t AS (
    select row_number as rn,ts, lag(ts,1) over (order by row_number) as baseline from main_table where row_number<3
  UNION ALL
    SELECT 
    rn+1 as rn 
    ,(select ts from main_table where row_number=rn+1) as ts
    ,case when datediff('hour',ts,baseline)>24 then ts else baseline end as baseline
     from (select * FROM t order by rn desc limit 1 )t where rn<=(select count(*)-1 from main_table)
)

,real_baseline as 
(
select rn,ts,lead(baseline,1) over (order by rn) as real_baseline from t
)

select * 
,case when row_number() over (partition by real_baseline order by ts) =1 then 'Accept'
else 'Reject' end as status
from real_baseline
gaps-and-islands sql
3 Answers

Normally you could apply lead/lag, but not here. The problem with lead/lag being the unpredictable range needed. Likewise a recursive CTE seems to be unworkable as it needs the MIN function in the recursive part; that however is not allowed. Since a function is satisfactory perhaps the best is function returning a table. See fiddle.

create or replace function public.accept_reject_complaints()
 returns table( o_complaintid integer
              , o_datetime    timestamp 
              , o_status      text
              )
 language plpgsql
AS $$                 
declare
    l_current_end_ts timestamp = '-infinity'::timestamp;

    c_complaint_list cursor for
                     select complaintid, datetime     
                       from complaints
                      order by datetime;
begin
    for complaint_rec in c_complaint_list 
    loop
       if complaint_rec.datetime  > l_current_end_ts then 
          o_status = 'Accept'; 
          l_current_end_ts = complaint_rec.datetime  + interval '1 hour';
       else 
          o_status = 'Reject'; 
       end if; 
   
       o_datetime = complaint_rec.datetime;
       o_complaintid = complaint_rec.complaintid;
       return next; 
    end loop ;

end ; 
$$;

Unfortunately since it involves a cursor loop performance will be an issue with large data volumes.

user_76238560

This is Simple Approach . Truncate each datetime for Hour then in each hour take First or Minimum datetime as accept and other as rejected .

P.S I have used table_name as complaint change it .tested in Postgresql 8.

SELECT ComplaintID,DateTime,CASE WHEN row_number() over(partition by hour order by 
DateTime)=1 THEN 'Accept' else 'Reject' end as Status from 
(select ComplaintID,DateTime ,date_trunc('hour',DateTime)as hour  from complaint)A ;
user_107402370
  1. Using the continuity of ComplaintID, the query is :
with recursive cte as (
  select 1 ComplaintID, min(DateTime) DateTime,
    min(DateTime) prev
    from main_table
  union all
  select t2.ComplaintID, t2.DateTime,
    case when t1.prev + interval '1 hour' < t2.DateTime
         then t2.DateTime else t1.prev end
    from cte t1 join main_table t2
    on t1.ComplaintID+1 = t2.ComplaintID
)
select ComplaintID, DateTime,
  case when DateTime=prev
    then 'Accept' else 'Reject' end Status
  from cte
  order by ComplaintID

DB Fiddle

  1. Extracting each row that is Accept, the query is :
with recursive cte as (
  (
    select ComplaintID, DateTime, 'Accept' Status
      from main_table order by DateTime limit 1
  )
  union all
  (
    select t2.ComplaintID, t2.DateTime, 'Accept'
      from cte t1 join main_table t2
      on t1.DateTime + interval '1 hour' < t2.DateTime
      order by t2.DateTime limit 1
  )
)
select t1.ComplaintID, t1.DateTime, coalesce(t2.Status, 'Reject') Status
  from main_table t1 left join cte t2
  on t1.ComplaintID=t2.ComplaintID
  order by t1.ComplaintID

DB Fiddle

user_138410160
Related