I have table like.

I need select deviation_id by several weeks in a row like:

How can i do this in PostgreSQL?

CodePudding user response：

As week number is an integer

GROUP BY week_number / 3

would group 3 weeks together.

1, 2 or 3 / 3 = 0
4, 5 or 6 / 3 = 1
7, 8 or 9 / 3 = 2
...  

If your groups are irregular you will have to use a case statement as xQbert suggests, but you will have to code for all possible groups up to week_number = 53.

CodePudding user response：

Thank you for feed back. I think i found a decision on my question.

select tt."repeat", tt.deviation_id, min(tt.week_number), max(tt.week_number)
from (
select tt.*
from (
    select tt.*, count(tt."repeat")over(partition by tt."repeat") as "count"
    from (
        select tt.deviation_id, tt.week_number, --сount(tt.deviation_id)
            row_number() over(partition by tt.deviation_id order by tt.week_number)-tt.week_number as "repeat"
        from public.tasks_task tt 
        where tt."year" =2022 and tt.orgunit_id =3000 and tt.deviation_id in ('262','264'))tt)tt
where tt."count">1) tt
group by 1,2
order by 2,3