Technical Articles
How to get Previous Row value in a table in SAP HANA/HANA SQL
Hello everyone,
REQUIREMENT
To access previous row value in a table. When we have null value in the current row then we have to look backward for the previous rows till we find a value .
In this blog post we will discuss how to get previous row value in a table and to explore further on how to access next row value please have a look at my BLOG POST (Scenario 1).
Before proceeding further I would like to thank @konrad for his suggestion to improvise this blog post.
Scenario 2: – To access previous row value
DATA
We will use the same table in this case also as used in Scenario 1, which has four columns namely PRODUCT, LOCATION, QTY (QUANTITY), DATE.
This table contains the list of ordered quantity of a product (i.e. material) from a location (i.e. plant) for an entire year. If you look at the table, In Jan, we have ordered 10 quantity and there is null quantity ordered in Feb. Similarly, we have values of ordered quantity for the rest of the months.
Now, when I say we have to look backward/previous months whenever there is a null value for QTY, then for Feb and March, we should have values from Jan (which is the first non-null value month before Feb and March). Similarly, for May, Aug, Sept and Nov we have to look previous months for value.
In simple words,
- Feb and March will have values from Jan.
- May will have value from April.
- Aug and Sept will have values from July.
- Nov will have value from Oct.
To achieve this also, I have used Table Function, which can be further consumed in a Calculation view to get the result.
SOLUTION
Scenario 2: – To access previous row value
DETERMINE FROM_DATE and TO_DATE RANGES FOR GIVEN NON-EMPTY QTY
As you can see the output, value from Jan should be replicated to Feb and Mar, so FROM_DATE is Jan and TO_DATE is Mar, similarly, you can see values of other months too.
SELECT "PRODUCT",
"LOCATION",
"QTY",
"DATE" AS "FROM_DATE",
IFNULL( ADD_MONTHS( LEAD("DATE") OVER(PARTITION BY "PRODUCT", "LOCATION" ORDER BY "DATE") , -1), "DATE") AS "TO_DATE"
FROM "SCHEMA"."BASE_TABLE"
WHERE "QTY" IS NOT NULL
ORDER BY "DATE";
USE GENERATE_SERIES (or M_TIME_DIMENSION table) TO GENERATE TIME SERIES (monthly intervals)
Here, we will use generate series to generate a year to cover from Jan to Dec, to support a wider date range you can simply change the value of Dates passed in the function.
SELECT
"GENERATED_PERIOD_START" AS "DATE"
FROM
SERIES_GENERATE_DATE('INTERVAL 1 MONTH', '2021-01-01', '2022-01-01') TIME_SERIES;
FINAL Result
We can get the final result by Joining Time Series with the Ranges to get QTY for each date.
SELECT
"PRODUCT",
"LOCATION",
"QTY" ,
"GENERATED_PERIOD_START" AS "DATE"
FROM SERIES_GENERATE_DATE('INTERVAL 1 MONTH','2010-01-01','2050-01-01') TIME_SERIES JOIN (
SELECT
"PRODUCT",
"LOCATION",
"QTY",
"DATE" AS "FROM_DATE" ,
IFNULL( ADD_MONTHS( LEAD("DATE") OVER(PARTITION BY "PRODUCT","LOCATION" ORDER BY "DATE") , -1), "DATE") AS "TO_DATE"
FROM "SCHEMA"."BASE_TABLE"
WHERE "QTY" IS NOT NULL
ORDER BY "DATE" ) TAB ON TIME_SERIES."GENERATED_PERIOD_START" BETWEEN TAB."FROM_DATE" AND TAB."TO_DATE"
;
CODE: –
FUNCTION "XXXX"."YYYYY::TF_LOOKING_BACKWARD" ( )
RETURNS TABLE (
"PRODUCT" NVARCHAR(18),
"LOCATION" NVARCHAR(4),
"QTY" DECIMAL(15,6),
"DATE" NVARCHAR(10)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
/*****************************
Write your function logic
*****************************/
var_out = SELECT
"PRODUCT",
"LOCATION",
"QTY" ,
"GENERATED_PERIOD_START" AS "DATE"
FROM SERIES_GENERATE_DATE('INTERVAL 1 MONTH','2010-01-01','2050-01-01') TIME_SERIES JOIN (
SELECT
"PRODUCT",
"LOCATION",
"QTY",
"DATE" AS "FROM_DATE" ,
IFNULL( ADD_MONTHS( LEAD("DATE") OVER(PARTITION BY "PRODUCT","LOCATION" ORDER BY "DATE") , -1), "DATE") AS "TO_DATE"
FROM "SCHEMA"."BASE_TABLE"
WHERE "QTY" IS NOT NULL
ORDER BY "DATE" ) TAB ON TIME_SERIES."GENERATED_PERIOD_START" BETWEEN TAB."FROM_DATE" AND TAB."TO_DATE"
;
return :var_out;
END;
Hi Naim,
Very nice article!
I have a comment regarding your example - In the real life, usually you need to support a wider date range, which in addition dynamically changes.
To make the query more dynamic, I would suggest:
1. Determine FROM_DATE and TO_DATE ranges for given non-empty Qty
2. Use GENERATE_SERIES (or M_TIME_DIMENSION table) to generate Time Series (monthly intervals)
3. Join Time Series with the Ranges to get QTY for each date to get the final results:
Final results will be the same as yours, however it will reflect also new dates that will appear in the underlying table.
Regards,
Konrad
Hi Konrad,
Thanks for the comment and for sharing this information.
I have edited the blog as per your suggestion.
Once again thanks for the kind words.
Best Regards,
Naim Khan
Konrad's solution works very nicely and is likely more efficient than the extensive SQLScript approach.
The problem itself (filling "gaps" in data with the last non-NULL value based on some notion what "last" means) is not new at all.
This post on askTom from 2008 (!) https://asktom.oracle.com/pls/apex/asktom.search?tag=recursive-lag-function demonstrates