Avoid counting duplicate clicks SQL-CodePudding

I have a data like the below:

date                      id         process      name
2022-01-01 12:23:33       12         security     John
2022-01-01 12:25:33       12         security     John
2022-01-01 12:27:33       12         security     John
2022-01-01 12:29:33       12         security     John
2022-01-01 14:04:45       12         security     John
2022-01-05 03:53:11       12         Infra        Sasha
2022-01-05 03:57:30       12         Infra        Sasha
2022-01-06 12:23:33       12         Infra        Sasha

The data with date difference of 10 mins for same values in the other fields are basically multi clicks and only the first one needs to be considered.

Expected result:

2022-01-01 12:23:33       12         security     John
2022-01-01 14:04:45       12         security     John
2022-01-05 03:53:11       12         Infra        Sasha
2022-01-06 12:23:33       12         Infra        Sasha

I know we can use datediff() but i don't know how to group them by data thats same in the rest of the fields. I don't know where to start this logic. Can someone help me get this please?

Thanks!

CodePudding user response：

You can use LAG() to peek at a value of a previous row, according to a subgroup an ordering within the each subgroup. For example:

select *
from (
  select *, lag(date) over(partition by id, process order by date) as pd from t
) x
where pd is null or date > pd   interval '10 minute'

Result:

 date                 id  process   name   pd                  
 -------------------- --- --------- ------ ------------------- 
 2022-01-05 03:53:11  12  Infra     Sasha  null                
 2022-01-06 12:23:33  12  Infra     Sasha  2022-01-05 03:57:30 
 2022-01-01 12:23:33  12  security  john   null                
 2022-01-01 14:04:45  12  security  john   2022-01-01 12:29:33

See running example at db<>fiddle.