To find the last value in the dataset of 15 minutes interval

Question

   ID Timestamp Value                                                               
   -- --------- -----
   1   11:59.54   10
   1   12.04.00   20
   1   12.12.00   31
   1   12.16.00   10
   1   12.48.00   05

I want the result set as

   ID Timestamp Value
   -- --------- -----
   1   11:59.54   10
   1   12:00:00   10
   1   12.04.00   20
   1   12.12.00   31
   1   12:15:00   31
   1   12:16.00   10
   1   12:30:00   10
   1   12:45:00   10
   1   12.48.00   05

Answer 1

The idea is as follows. Use SERIES_GENERATE() to generate the missing time stamps with the 15 minute intervals and and union it with the existing data your table T. Now you would want to use LAST_VALUE with IGNORE NULLS. IGNORE NULLS is not implemented in HANA, therefore you have to do a bit of a workaround. I use COUNT() as a window function to count the non null values. I do the same on the original data and then join both on the count. This way I repeat the last non-null value.

select X.ID, X.TIME, Y.VALUE from (
    select ID, TIME, value, 
        count(VALUE) over (order by TIME rows between unbounded preceding and current row) as CNT 
    from (
        --add the missing 15 minute interval timestamps
        select 1 as ID, GENERATED_PERIOD_START as TIME, NULL as VALUE 
        from SERIES_GENERATE_TIME('INTERVAL 15 MINUTE', '12:00:00', '13:00:00')
        union all 
        select ID, TIME, VALUE from T
    )
) as X join (
    select ID, TIME, value, 
           count(value) over (order by TIME rows between unbounded preceding and current row) as CNT
    from T
) as Y on X.CNT = Y.CNT