CODEWITHSUNDEEP
hana
Tobias
Tobias

Reputation: 5433

SAP HANA - SQL - calculate columns on the same table without recursive functions

I have the following simple table with approx. 40k items:

|date|consum|

Based on this table, I want to calculate some new columns. At the end, the table should look like:

|date|consum|row|row_relativ|min|max|upper|lower|

Unfortunately the SAP HANA Database does not support recursive table statements...

Here are the calculation rules:

  1. row = row_number() over(order by dates)
  2. row_relative = row*35040/32768
  3. min = floor(row_relative)
  4. max = ceil(row_relative)
  5. min = select the consum from the row with the min value
  6. max = select the consum from the row with the max value

At the moment I have a stored procedure with 5 variables. In each variable I calculate 1 new column. It is very very dirty... How can I make it different? I can't use recursive functions... :/

Here is my SQL snippet... have fun =)

var1 = select *, row_number() over(order by dates) as row from XYZ order by dates limit 34944;
var2 = select *, (row * 34944/32768) as row_relative from :var1;
var3 = select *, floor(row_relative) as min, ceil(row_relative) as max from :var2;

var4 = select 
    p.*,
    (select consumfrom :var3 where row = p.min) as lower,
    (select consumfrom :var3 where row = p.max) as upper
from :var3 p;

var5 = select 
    p.*,
    (p.lower* (1-p.row_relative+p.min)+p.upper * (p.row_relativ - p.min)) as new_consum
from :var4 p;

Upvotes: 0

Views: 3256

Answers (1)

Eralper
Eralper

Reputation: 6612

You can use following CTE Common Table Expression in your SQLScript BTW, it is very interesting that nearly the same SQL Server CTE syntax matches the SAP HANA SQLScript syntax :)

with cte as (
select
    date,
    consum,
    row_number() over (order by date) as row
from rawdata
)
select
    date,
    consum,
    row,
    row * 35040 / 32768 as row_relative,
    floor( (row * 35040 / 32768) ) as min,
    ceil( (row * 35040 / 32768) ) as max,
    (select cte2.consum from cte cte2 where row = floor( (cte.row * 35040 / 32768) ) ) as min2,
    (select cte2.consum from cte cte2 where row = ceil( (cte.row * 35040 / 32768) ) ) as max2
from cte

Upvotes: 0

Related Questions