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

Postgresql – Compute value of row based on previous row when only having 1 value in column

Given the following scheme and values I want to compute the value of each row starting by adding 1 to the previous row value. If the row value is null then set null. If there is a value then add 1 from the previous value.

How can I achieve this in sql (postgresql)?

I’ve tried recursive CTE but without success.

LE:

Input data:

symbol  date                   close    computed

TBBB    2024-02-22 14:30:00+00  19.05   
TBBB    2024-02-23 14:30:00+00  19.55   
TBBB    2024-02-24 14:30:00+00  20.6    
TBBB    2024-02-25 14:30:00+00  21.3    
TBBB    2024-02-26 14:30:00+00  20.43   20.4
TBBB    2024-02-27 14:30:00+00  20.21   
TBBB    2024-02-28 14:30:00+00  20.74   
TBBB    2024-02-29 14:30:00+00  20.09   
TBBB    2024-03-01 14:30:00+00  20.79   
TBBB    2024-03-02 14:30:00+00  20.87   
TBBB    2024-03-03 14:30:00+00  20.69   
TBBB    2024-03-04 14:30:00+00  20.19   
TBBB    2024-03-05 14:30:00+00  20.9    
TBBB    2024-03-06 14:30:00+00  20.99   
TBBB    2024-03-07 14:30:00+00  21.28   
TBBB    2024-03-08 14:30:00+00  21.27

Expected result:

symbol  date                    close   computed

TBBB    2024-02-22 14:30:00+00  19.05   
TBBB    2024-02-23 14:30:00+00  19.55   
TBBB    2024-02-24 14:30:00+00  20.6    
TBBB    2024-02-25 14:30:00+00  21.3    
TBBB    2024-02-26 14:30:00+00  20.43   20.4
TBBB    2024-02-27 14:30:00+00  20.21   21.4
TBBB    2024-02-28 14:30:00+00  20.74   22.4
TBBB    2024-02-29 14:30:00+00  20.09   23.4
TBBB    2024-03-01 14:30:00+00  20.79   24.4
TBBB    2024-03-02 14:30:00+00  20.87   25.4
TBBB    2024-03-03 14:30:00+00  20.69   26.4
TBBB    2024-03-04 14:30:00+00  20.19   27.4
TBBB    2024-03-05 14:30:00+00  20.9    28.4
TBBB    2024-03-06 14:30:00+00  20.99   29.4
TBBB    2024-03-07 14:30:00+00  21.28   30.4
TBBB    2024-03-08 14:30:00+00  21.27   31.4

The +1 from previous value is just for simplicity. In reality I would have a math formula there.

What I’ve tried:

Some simpler example and not related to the question: here I calculate the computed column based on the close. This was a initial example. Is not what I want

WITH computed_values AS (SELECT symbol,
                           date,
                           close,
                           CASE
                               WHEN LAG(computed, 5) OVER (PARTITION BY symbol ORDER BY date) IS NULL THEN null
                               ELSE (close * (2.0 / (period + 1))) +
                                    (LAG(computed, 5) OVER (PARTITION BY symbol ORDER BY date) * (1 - (2.0 / (period + 1))))
                               END AS computed
                    FROM (SELECT symbol,
                                 date,
                                 close,
                                 close AS computed,
                                 5     AS period
                          FROM my_table
                          WHERE symbol = 'TBBB' 
                          ORDER BY date) subquery)
SELECT *
FROM computed_values;

Generated by AI and not working

WITH NumberedRows AS (
    SELECT 
        symbol, 
        date, 
        close,
        ROW_NUMBER() OVER (ORDER BY date ASC) AS row_num
    FROM my_table
),
KnownClose AS (
    SELECT 
        symbol, 
        date, 
        close,
        row_num
    FROM NumberedRows
    WHERE close IS NOT NULL
),
RecursiveClose AS (
    SELECT 
        symbol,
        date,
        close,
        row_num
    FROM KnownClose
    UNION ALL
    SELECT
        nr.symbol,
        nr.date,
        rc.close - 2 AS close,
        nr.row_num
    FROM RecursiveClose rc
    JOIN NumberedRows nr ON nr.row_num = rc.row_num - 1
    WHERE nr.close IS NULL
    
    UNION ALL

    SELECT
        nr.symbol,
        nr.date,
        rc.close + 2 AS close,
        nr.row_num
    FROM RecursiveClose rc
    JOIN NumberedRows nr ON nr.row_num = rc.row_num + 1
    WHERE nr.close IS NULL
) SELECT symbol,
    date,
    close
FROM RecursiveClose
ORDER BY row_num;
Tags:

2

Answers


  1. 0

    This should work:

    
; with recursive NumberThem as (
   select symbol
        , dte
        , clse
        , computed
        , row_number() over (partition by symbol order by dte) as rn
   from MyTbl
   ),
   CTE as (
    select 
         symbol
       , dte
       , clse
       , a.rn
       , a.computed as rcomputed
    from NumberThem a
    where rn=1
    union all
    select 
        a.symbol
      , a.dte
      , a.clse
      , a.rn
      , coalesce(a.computed,CTE.rcomputed+1) as rcomputed
    from CTE 
         inner join
         NumberThem a
         on CTE.symbol=a.symbol
         and CTE.rn+1=a.rn
)
select * 
from CTE

    I ordered the rows by date for ease of referencing, then used a recursive CTE to start from the earliest date (row number) and access each row sequentially by incrementing it.

    Login or Signup to reply.
  2. 0

    You can do this purely using window functions. Unfortunately, Postgres does not support IGNORE NULLS, which would have made it simpler. Instead we need to hack it with array_agg and FILTER.

    • Compute a row-number.
    • Conditionally aggregate (in a running window) both the computed column and the row-number, only when computed is not null.
    • Take the last element of the computed array, add the current row-number and subtract the last element of the row-number array.
    WITH ranked AS (
    SELECT *,
      ROW_NUMBER() OVER (ORDER BY mt.date) AS rn
    FROM my_table mt
),
arrayed AS (
    SELECT *,
      array_agg(mt.computed) FILTER (WHERE mt.computed IS NOT NULL) OVER running_date AS array_computed,
      array_agg(mt.rn) FILTER (WHERE mt.computed IS NOT NULL) OVER running_date AS array_rn
    FROM ranked mt
    WINDOW running_date AS (ORDER BY mt.date ROWS UNBOUNDED PRECEDING)
)
SELECT *,
  coalesce(
    mt.computed,
    mt.array_computed[array_upper(mt.array_computed, 1)]
    + mt.rn
    - mt.array_rn[array_upper(mt.array_rn, 1)]
  ) AS new_computed
FROM arrayed mt;

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search