CODEWITHSUNDEEP
sqlpostgresql
kruparulz14
kruparulz14

Reputation: 163

POSTGRESQL: Extract min value per window

I have my data as below:

 IP        username   time1  
-----      ------      -----  
1.2.3.4      abc      01:00  
1.2.3.4      abc      02:00  
1.2.3.4      abc      03:00  
1.2.3.4      pqr      04:00  
1.2.3.4      pqr      05:00  
1.2.3.4      pqr      06:00  
1.2.3.4      pqr      07:00  
1.2.3.4      abc      08:00  
1.2.3.4      abc      09:00  
1.2.3.5      abc      10:00  
1.2.3.5      abc      11:00

I want to extract minimum value per window. i.e. I want my output as

IP        username   time1  
-----      ------      -----  
1.2.3.4      abc      01:00  
1.2.3.4      pqr      04:00  
1.2.3.4      abc      08:00  
1.2.3.5      abc      10:00

I am trying to do something like this: 

select ip, usrnm, time1  
from (select ROW_NUMBER() over(partition by ip, usrnm order by time1)as 
rownum,ip, usrnm, time1 from table1)t2 
where t2.rownum = 1

But I am not able to capture 

1.2.3.4 abc 08:00

Any clue?

Upvotes: 1

Views: 48

Answers (2)

Hambone
Hambone

Reputation: 16377

This answer is not as good as the one posted, but it's inspired by the first question I ever asked on Stack Overflow and wanted to pass on the tribute:

Oracle/Sybase SQL - get value based on a previous record (not a simple LAG)

with change_data as (
  select
    ip, username, time1,
    case
      when lag (username) over (partition by ip order by time1) = username
        then 0
      else 1
    end as newrecord
  from table1
),
groups as (
  select
    ip, username, time1,
    sum (newrecord) over (order by time1) as instance
  from change_data
)
select
  ip, username, min (time1) as min_time
from groups
group by
  ip, username, instance
order by
  min_time

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Use lag() to check if a record is the first of a "group".

SELECT ip,
       username,
       time1
       FROM (SELECT ip,
                    username,
                    time1,
                    coalesce(lag(ip) OVER (ORDER BY time1) <> ip
                              OR lag(username) OVER (ORDER BY time1) <> username,
                             true) x
                    FROM elbat) x
       WHERE x;

db<>fiddle

Upvotes: 2

Related Questions