CODEWITHSUNDEEP
sqlapache-sparkapache-spark-sql
Clock Slave
Clock Slave

Reputation: 7977

Calculate values of rows in a table using previous values

I have the two tables which are structured like this - 

table 1
+---+---+
| id|val|
+---+---+
|  1|1.1|
|  2|1.2|
|  3|1.3|
|  4|1.4|
|  5|1.5|
|  6|1.6|
+---+---+

table2
+---+---+
| id|val|
+---+---+
|  7|  0|
|  8|  0|
|  9|  0|
+---+---+

I need to write a SQL query that calculates the populates the val column in table2, such that 

val = prev_value^2 + prev_value^0.5

where prev_value is simply the value of the val column in the previous row(id -1). So for the first row in table2 the value of val should be

val = 1.6^2 + 1.6^0.5 = 3.82

This result 3.82 will be used to calculate the val for the row with id == 8 in table2 and so on.

I tried doing the following,

select df2.*
       , power(df1.val, 2) + power(df1.val, 0.5) as sum_val 
from  df1, df2 
where df1.id = df2.id - 1

but this returns only one row

+---+---+------------------+
| id|val|           sum_val|
+---+---+------------------+
|  7|  0|3.8249110640673525|
+---+---+------------------+

which is the correct output but I am looking for a way to get all the rows in table2 computed at once.

Is there a way to do this in SQL? I am using SQL queries in pyspark.

Upvotes: 0

Views: 240

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Doing this as an update on a second table is a bit tricky. Here is one solution:

with recursive cte as (
      (select t1.val as val, 0 as lev
       from table1 t1
       order by id desc
       limit 1
      ) union all
      select sqrt(cte.val) + power(cte.val, 2), lev + 1
      from cte 
      where lev < (select count(*) from table2)
     )
update table2 t2 join
       (select t2.id, row_number() over (order by id) as seqnum
        from table2 t2
       ) tt2
       on tt2.id = t2.id join
       cte
       on tt2.seqnum = cte.lev
    set t2.val = cte.val;

Here is a db<>fiddle.

Upvotes: 1

Akina
Akina

Reputation: 42844

An example for MySQL 8.x.

The query fills all values of val field having zero or NULL values with calculated ones.

If starting value(s) needs to be filled they're filled with zero(es).

id values may start from any value.

The gaps in id values sequence are not allowed (edit recursion part of CTE if you need to remove this restriction).

WITH RECURSIVE
cte AS ( ( SELECT id, COALESCE(val, 0) val
           FROM test
           ORDER BY id 
           LIMIT 1 )
         UNION ALL
           SELECT cte.id + 1, CASE WHEN COALESCE(test.val,0) = 0
                                   THEN pow(cte.val,2)+pow(cte.val,0.5)
                                   ELSE test.val
                                   END
           FROM cte
           LEFT JOIN test ON test.id = cte.id + 1
           WHERE cte.id < ( SELECT MAX(id)
                            FROM test ) )
UPDATE test
  JOIN cte USING (id)
SET test.val = cte.val
WHERE COALESCE(test.val,0) = 0;

fiddle

Upvotes: 1

Related Questions