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)

score 6 · Accepted Answer · edited Jun 11 '18 at 13:43

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 '18 at 13:43

Tommy Lee Jones w Sciganym

160
9

answered Jun 11 '18 at 10:50

Gordon Linoff

1,242,037
58
646
786

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... – Tommy Lee Jones w Sciganym Jun 11 '18 at 11:57
@TommyLeeJoneswSciganym . . . I neglected the "2 week part". Good morning. – Gordon Linoff Jun 11 '18 at 12:11
in second query in grp column we have 7 groups, but It should be 3. – Tommy Lee Jones w Sciganym Jun 11 '18 at 12:13
1

@TommyLeeJoneswSciganym . . . With the change I just made? – Gordon Linoff Jun 11 '18 at 12:16
I had to use additional dense_rank() with the first query you provided but you helped a lot! Thanks @Gordon – Tommy Lee Jones w Sciganym Jun 11 '18 at 12:27
@TommyLeeJoneswSciganym . . . The second does seem to do exactly what you want . . . but the `dense_rank()` is also a very reasonable solution. – Gordon Linoff Jun 11 '18 at 12:29
In second I get 7 groups.. maybe different Postgresql version..? I use 9.5 – Tommy Lee Jones w Sciganym Jun 11 '18 at 12:30
In second I had to correct condition to: when week < prev_week + 2 – Tommy Lee Jones w Sciganym Jun 11 '18 at 12:32

Increment column value on certain condition in SQL query on Postgresql

1 Answers1

Linked