PostgreSQL:每个窗口提取最小值

Question

我的数据如下：

 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  

我想每个窗口提取最小值。即我希望我的输出为

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  

我正在尝试这样做：

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

但我无法捕获

1.2.3.4 abc 08:00

有任何线索？

Answer 1

使用lag()检查记录是否是"组"的第一个。

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＆lt;>小提琴

Answer 2

这个答案不如发布的答案，但它的灵感来自我在堆栈溢出上问的第一个问题，并想通过致敬：

oracle/sybase sql-根据先前的记录（不是简单的滞后）获取值

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