Increment column value on certain condition in SQL query on Postgresql

Question

I want aggregate my walks with animals by weeks groupping my rows in 1 group if break between weeks was greater than 2 weeks.

I have my table:

Create table test.walk (animal text, week integer)

with row for each walk i want to group:

insert into test.walk values ('DOG', 2)
insert into test.walk values ('DOG', 3)
insert into test.walk values ('DOG', 4)
insert into test.walk values ('CAT', 1)
insert into test.walk values ('CAT', 1)
insert into test.walk values ('CAT', 11)
insert into test.walk values ('CAT', 12)
insert into test.walk values ('CAT', 13)

I struggle with dense_rank() and lag() window functions, but have no luck to get additional column where I should get 3 distinct values as a result.

Here is my desired output:

What combination of window functions should I use to get two WALK_NO for CAT? (becase after week 1 cat waited for me longer that 2 weeks)

Tommy Lee Jones w Sciganym · Accepted Answer · 2018-06-11 13:43:12Z

6

Use lag() and cumulative sum:

with query as (
select w.*,
       sum(case when week < prev_week + 2 then 0 else 1 end) over (partition by animal order by week) as grp
from (select w.*,
             lag(week) over (partition by animal order by week) as prev_week
      from test.walk w
     ) w
)
select
    animal, week, grp,
    dense_rank() over (order by animal, grp) as grp2
from query

Note: This will restart the counting over for each animal -- which seems to be the actual intention of what you want to do. The definition of the problem is a bit tricky if you want the groups separated by animal, but to be incremental. One method is:

select w.*,
       sum(case when prev_week = week then 0 else 1 end) over (order by min_week, animal, week) as grp
from (select w.*,
             lag(week) over (partition by animal order by week) as prev_week,
             min(week) over (partition by animal) as min_week
      from test.walk w
     ) w;

edited Jun 11, 2018 at 13:43

Tommy Lee Jones w Sciganym

1601 silver badge10 bronze badges

answered Jun 11, 2018 at 10:50

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Tommy Lee Jones w Sciganym Over a year ago

Thanks @Gordon Linoff but 1st query returns 4 values in grp column, -there should be 3. And DOG rows should be in one group. 2nd query has similiar problem...

Gordon Linoff Over a year ago

@TommyLeeJoneswSciganym . . . I neglected the "2 week part". Good morning.

Tommy Lee Jones w Sciganym Over a year ago

in second query in grp column we have 7 groups, but It should be 3.

Gordon Linoff Over a year ago

@TommyLeeJoneswSciganym . . . With the change I just made?

Tommy Lee Jones w Sciganym Over a year ago

I had to use additional dense_rank() with the first query you provided but you helped a lot! Thanks @Gordon

|

Collectives™ on Stack Overflow

Increment column value on certain condition in SQL query on Postgresql

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related