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

create a non-unique ID column, where a new ID is created every time a numeric sequence resets – Postgresql

I have the following table:

product price   date
banana  90  2022-01-01
banana  90  2022-01-02
banana  90  2022-01-03
banana  95  2022-01-04
banana  90  2022-01-05
banana  90  2022-01-06

I need to add a non-unique ID column to the table. Every time the price changes, I want the ID to change. This would result in the following table.

id  product price   date
A   banana  90  2022-01-01
A   banana  90  2022-01-02
A   banana  90  2022-01-03
B   banana  95  2022-01-04
C   banana  90  2022-01-05
C   banana  90  2022-01-06

By searching for answers in SO and Google, I was able to create a column (my_seq) that contains a sequence that resets every time (see sql fiddle for my query) the price changes. But I still don’t know how to create an ID column that resets every time the my_seq starts over.

my_seq  rn1 rn2 product price   date
1   1   1   banana  90  2022-01-01
2   2   2   banana  90  2022-01-02
3   3   3   banana  90  2022-01-03
1   1   4   banana  95  2022-01-04
1   4   5   banana  90  2022-01-05
2   5   6   banana  90  2022-01-06

sql-fiddle with DDL and my query

thanks

Tags:

3

Answers


  1. 0

    You are half-there already with the query in your Fiddle.

    Consider what you get if you subtract your rn1 & rn2 values – you get the values you need to group by.

    If you want an increasing sequence you can then apply a dense rank:

    with cte as (
  select *, 
    Row_Number() over(order by date)
    - Row_Number() over(partition by product, price order by date) rn
  from my_table
)
select Dense_Rank() over(order by rn) as my_seq,
  product, price, date
from cte;

    Modified fiddle

    Login or Signup to reply.
  2. 0

    It will generate sequence number whenever price change.

    with cte as (
select 
row_number  () over( w) as price_change,

product,price,date
from 
my_table_1
window w as (partition by product,price order by date)
)
select   
product,price,date,
row_number()over(partition by price_change order by price_change) as seq_no
from cte
order by seq_no,price_change

    output:

    product|price|date      |seq_no|
-------+-----+----------+------+
banana |   90|2022-01-01|     1|
banana |   90|2022-01-02|     1|
banana |   90|2022-01-03|     1|
banana |   90|2022-01-05|     1|
banana |   90|2022-01-06|     1|
banana |   95|2022-01-04|     2|
    Login or Signup to reply.
  3. 0 
    with cte as (
    select ((lag(price) over w1) <> price)::int changes
         , *
    from my_table
    window w1 as (partition by product order by date) )
select chr( ascii('A') + 
            (sum(coalesce(changes,0)) over w2)::int ) as your_seq
     , product
     , price
     , date
from cte
window w2 as (
  partition by product 
  order by date 
  groups between unbounded preceding and current row);
    1. The lag() window function references previous row within the same ordered group. It’s null for the first row, hence the coalesce() later
    2. Result of comparing to the previous price can be cast to ::int, resulting in 0 for when it’s unchanged and 1 where it changed.
    3. A sum() over (... groups between unbounded preceding and current row) is just a rolling sum collecting everything so far, up to the current row in the window. Rolling over 0‘s and 1‘s it’ll grow only for rows where the price changed.
    4. chr(ascii(start_char) + offset) can get you the alphabetical sequence, but it also limits it to alphabet length.

    Sample output from a demo with more tests:

    -- your_seq |  product  | price |    date
------------+-----------+-------+------------
-- A        | apple     |    90 | 2022-01-01
-- A        | apple     |    90 | 2022-01-02
-- A        | apple     |    90 | 2022-01-03
-- B        | apple     |    95 | 2022-01-04
-- C        | apple     |    90 | 2022-01-05
-- C        | apple     |    90 | 2022-01-06
-- A        | banana    |    90 | 2022-01-01
-- A        | banana    |    90 | 2022-01-02
-- A        | banana    |    90 | 2022-01-03
-- B        | banana    |    95 | 2022-01-04
-- C        | banana    |    90 | 2022-01-05
-- C        | banana    |    90 | 2022-01-06
-- A        | orange    |    80 | 2022-01-01
-- A        | papaya    |    20 | 2022-01-01
-- A        | papaya    |    20 | 2022-01-02
-- B        | papaya    |    30 | 2022-01-03
-- A        | pineapple |    40 | 2022-01-01
-- B        | pineapple |    25 | 2022-01-02
-- B        | pineapple |    25 | 2022-01-03
-- A        | satsuma   |    10 | 2022-01-01
-- A        | satsuma   |    10 | 2022-01-02
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search