Question 89699 in PostgreSQL Question posted in
The official documentation can be found here.

Is there a way to partition by incremental series in Postgressql? – Postgresql

In postgressql is there a way to attain the result below by using partition by or any other way?

last_name  year  increment  partition  

Doe        2000     1           1
Doe        2001     2           1
Doe        2002     3           1
Doe        2003    -1           2
Doe        2004     1           3
Doe        2005     2           3
Doe        2006     3           3
Doe        2007    -1           4
Doe        2008    -2           4
Tags:

3

Answers


  1. 0 
    SELECT last_name, 
       year, 
       increment, 
       SUM(CASE WHEN increment < 0 THEN 1 ELSE 0 END) OVER (PARTITION BY last_name ORDER BY year) AS partition
FROM your_table
ORDER BY last_name, year;
    Login or Signup to reply.
  2. 0

    It seems that you want to group the consecutive positive/ negative values together, one option is to use a difference between two row_number functions, this will make the partition but with unordered group numbers.

    select *,
  row_number() over (partition by last_name order by year) -
  row_number() over (partition by last_name,
    case when increment>=0 then 1 else 2 end order by year) as prt
from tbl 
order by last_name, year

    If you want the partitions in order (1, 2, 3…) you could try another approach using lag and running sum as the following:

    select last_name, year, increment,
  1 + sum(case when sign(increment) <> sign(pre_inc) then 1 else 0 end) over
  (partition by last_name order by year) as prt
from
(
  select *,
    lag(increment, 1 , increment) over
    (partition by last_name order by year) pre_inc
  from tbl
) t
order by last_name, year

    See demo

    Login or Signup to reply.
  3. 0

    If the increment column does encrease over the column year, it will be marked as 1; otherwise, it will be marked as 0. Then, we group the successive data using "LAG", regardless of whether the increment is positive or negative.

    with cte as (
  select * ,
  row_number() over (partition by last_name order by year) as row_num,
  case when increment >= LAG(increment,1,0) over (partition by last_name order by year) 
  then 1 else 0 end rank_num
  from mytable
),
cte2  as (
  select *, LAG(rank_num,1,1) over (partition by last_name order by year) as pre 
  from cte
  order by year
)
select last_name, year, increment, 1+sum(case when pre <> rank_num then 1 else 0 end) over
    (partition by last_name order by year) as partition
from cte2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search