Sample Header Ad - 728x90

SQL (Postgres) Filling up missing rows with values from previous row and calculating on result set

0 votes
1 answer
2470 views
I have got this dataset:
id | block_number | value | name 
----+--------------+-------+------
  2 |           47 |   100 | one
  3 |           52 |   200 | one
  4 |           58 |   120 | one
  5 |           60 |   180 | one
` created from
create table test (id serial, block_number int, value int, name varchar);
and
insert into test (block_number, value, name) values (47, 100, 'one'), (52, 200, 'one'), (58, 120, 'one'), (60, 180, 'one');
I would like to fill up the rows (not actually add them to the table but just use them for further processing), so that the column 'block_number' shows up consecutively using the value of the previous row for 'value' like so:
block_number | value | name 
-------------+-------+------
          47 |   100 | one
          48 |   100 | one
          49 |   100 | one
          50 |   100 | one
          51 |   100 | one
          52 |   200 | one
          53 |   200 | one
          54 |   200 | one
          55 |   200 | one
          56 |   200 | one
          57 |   200 | one
          58 |   120 | one
          59 |   120 | one
          60 |   180 | one
Then I would like to multiply each of these rows with a given number and sum up the total of that, for example given number would be 2 then the result of the above would be 4,240. I haven't gotten very far with this, only thing I could come up with is a statement that generates the missing rows. However, I am failing to find out how I could set the value of each row to the one of the previous row:
SELECT x.block_number, x.value, t.value
           FROM (
              SELECT generate_series(min(block_number), max(block_number)) AS block_number
              FROM   my_table
               ) x
           LEFT   JOIN my_table t USING (block_number)
           ORDER  BY x.block_number;
Any help greatly appreciated. ### UPDATE: ### I tried to reconcile this with @mustaccio's hint here: https://dba.stackexchange.com/posts/327530/edit#:~:text=Carry%20over%20long%20sequence%20of%20missing%20values%20with%20Postgres and I came up with this:
SELECT block_number
     , first_value(value) OVER (PARTITION BY block_number, grp ORDER BY block_number) AS value
     
FROM  (
   SELECT 
        count(value) OVER (PARTITION BY block_number ORDER BY block_number) AS grp
        , value
        , block_number
   FROM   (
   
   SELECT x.block_number, t.value
           FROM (
              SELECT generate_series(min(block_number), max(block_number)) AS block_number
              FROM   test
               ) x
           LEFT   JOIN test t USING (block_number)
           ORDER  BY x.block_number
) test
   
   
   ) sub;
but it still doesn't get me the missing values:
block_number | value 
--------------+-------
           47 |   100
           48 |      
           49 |      
           50 |      
           51 |      
           52 |   200
           53 |      
           54 |      
           55 |      
           56 |      
           57 |      
           58 |   120
           59 |      
           60 |   180
Any suggestions? ### UPDATE 2 ### Thanks to @mustaccio's answer the first part is solved. I tried to amend the statement so that it does the calculation as mentioned above: Multiplying the 'value' in each row with a given number (here: 2) and then summing up all those values. What I tried:
SELECT SUM(a.value * 2)
FROM (

SELECT  block_number, value, first_value(value) OVER (PARTITION BY grp) 
FROM (
 SELECT 
  sum(CASE WHEN value IS NOT NULL THEN 1 END) OVER (ORDER BY block_number) AS grp
  , value
  , block_number
 FROM   (
   SELECT x.block_number, t.value
   FROM (
    SELECT generate_series(min(block_number), max(block_number)) AS block_number
    FROM   test
   ) x
   LEFT   JOIN test t USING (block_number)
 ) y
) z
) a;
However, that gives me a result of 1,200 instead of the expected 4,060. Any ideas?
Asked by Westcoaster (3 rep)
May 25, 2023, 03:50 PM
Last activity: May 26, 2023, 02:34 PM