Technical Articles
Using Point Characteristic Functions to Pivot Data in HANA
There are several articles in this blog space dealing with Table Pivoting, A recent article, universally titled Pivoting Data with SAP HANA, demonstrates a Python programming interface which uses Data Frames and the hana_ml machine learning library. However, this is not a method which can be used in the context of the SQL language, and therefore it is very limited in scope.
There are two other universally titled articles PIVOT Data in HANA and How to pivot dataset in SAP HANA which give an SQL solution, but they seem either too wordy or too unscientific for something that I think is really just a simple math problem.
There are also several older, archived articles which I feel are much too vague or overly procedural to be universally titled as the all-encompassing solution.
I recently ran across a table pivoting problem in my own application, and needed a solution in the form of an SQL select statement. If you are part of the older engineering crowd, then you may have run across this book which was published 25 years ago:
The authors devoted the entire book to a collection of essays on SQL programming techniques, many of which have been lost to history. This book presents an innovative methodology for SQL programming based on the use of Point Characteristic Functions.
Point characteristic functions are devices that allow engineers to encode conditional logic as scalar expressions within select, where, group by and set clauses. These techniques implement single statement solutions to problems that normally require multiple SQL statements. They also minimize the number of passes through underlying tables, and use SQL to obtain results not otherwise available within a relational implementation.
With an understanding of the old SQL-92 mechanics described in the book, I re-implemented their point characteristic functions using features available in the newer SQL used by HANA.
The secret to turning, or pivoting, long narrow tables into short, wide ones, lies in the proper encoding and use of these point characteristic functions. There are a wide variety of solutions enabled by these and other characteristic functions. The need for such pivoting occurs frequently in practice, primarily because, while the narrow table representation is better for data manipulation, the wide table form can be better for presenting data to reports.
We will get right to the problem I faced, and then explain the details. The application takes a set of Bank Deposit Rates for a number of months, stored in a long narrow table called DepositRates, and pivots the data to present the rate as columns by month:
Here is the input data
create table DepositRates (
id int not null,
month int not null,
rate decimal(6,6) null,
resetDate date null,
resetNote nvarchar(50) null);
create table Instruments (
id int not null,
name nvarchar(8) not null,
source nvarchar(8) not null);
insert into DepositRates values(1,1,0.008000,to_date('10/02/2019','MM/DD/YYYY'),'late reset');
insert into DepositRates values(1,2,0.025500,to_date('11/01/2019','MM/DD/YYYY'),NULL);
insert into DepositRates values(1,3,0.014100,to_date('11/29/2019','MM/DD/YYYY'),'early reset');
insert into DepositRates values(2,1,0.007115,to_date('10/01/2019','MM/DD/YYYY'),NULL);
insert into DepositRates values(2,2,0.007159,to_date('11/01/2019','MM/DD/YYYY'),NULL);
insert into DepositRates values(2,3,0.007135,to_date('12/01/2019','MM/DD/YYYY'),NULL);
insert into DepositRates values(3,1,0.022441,to_date('10/01/2019','MM/DD/YYYY'),NULL);
insert into DepositRates values(3,2,0.020109,NULL,'missing reset');
insert into DepositRates values(3,3,0.017743,to_date('12/01/2019','MM/DD/YYYY'),NULL);
insert into Instruments values(1,'HKDOND=','Reuters');
insert into Instruments values(2,'GBP3MD=','Euribor');
insert into Instruments values(3,'USD1MD=','Reuters');
select * from Instruments;
ID NAME SOURCE
-- ------- --------
1 HKCOMD= Reuters
2 GBP3MD= Euribor
3 USD1MD= Reuters
select * from DepositRates;
ID MONTH RATE RESETDATE RESETNOTE
-- ----- -------- ---------- ----------
1 1 0.008000 2019-10-02 late reset
1 2 0.025500 2019-11-01 NULL
1 3 0.014100 2019-11-29 early reset
2 1 0.007115 2019-10-01 NULL
2 2 0.007159 2019-11-01 NULL
2 3 0.007135 2019-12-01 NULL
3 1 0.022441 2019-10-01 NULL
3 2 0.020109 NULL missing reset
3 3 0.017743 2019-12-01 NULL
Here is the pivot query
select
i.name "name",
i.source "source",
max(r.rate*(nullif((1-abs(sign(r.month-1))),0))) "current rate",
max(r.rate*(nullif((1-abs(sign(r.month-2))),0))) "period-1 rate",
max(r.rate*(nullif((1-abs(sign(r.month-3))),0))) "period-2 rate"
from DepositRates r, Instruments i
where r.id = i.id
group by i.name,i.source
order by i.name;
And here is the result
name source current rate period-1 rate period-2 rate
------- ------- ------------ ------------- -------------
GBP3MD= Euribor 0.007115 0.007159 0.007135
HKDOND= Reuters 0.008000 0.025500 0.014100
USD1MD= Reuters 0.022441 0.020109 0.017743
The mechanics
As you can see, we have pivoted the table by flipping the rates stored as rows for each instrument, into columns. We pivot by a numeric month and present a numeric rate. Note that the expression (nullif((1-abs(sign(month-1))),0)) acts as a point characteristic function for month = 1. Specifically, it returns 1 if the month is equal to 1 and returns NULL otherwise. The function sign() returns -1,0,+1 for negative numbers, zero and positive numbers respectively; the function abs() returns the absolute value of its argument; and the function nullif() returns NULL if the value is 0. Consequently, only one, the first, of the three rate values participating in the max() aggregate retains its original value, with the other two being reduced to NULL. Thus, the expression max(rate*(nullif((1-abs(sign(month-1))),0))) in effect simply returns the month 1 rate amount, precisely as required. The expression max(rate*(nullif((1-abs(sign(month-2))),0))) acts in a similar way, implementing a characteristic function for month 2.
Breaking up the query, we can see the return value for the point characteristic function as follows:
select
id,
nullif((1-abs(sign(month-1))),0) as v1,
nullif((1-abs(sign(month-2))),0) as v2,
nullif((1-abs(sign(month-3))),0) as v3
from DepositRates
order by id,month;
ID V1 V2 V3
-- ---- ---- ----
1 1 NULL NULL
1 NULL 1 NULL
1 NULL NULL 1
2 1 NULL NULL
2 NULL 1 NULL
2 NULL NULL 1
3 1 NULL NULL
3 NULL 1 NULL
3 NULL NULL 1
Then, we apply the function to the presentation attribute called rate by multiplying it by the function result. Here is the result:
select
id,
rate*nullif((1-abs(sign(month-1))),0) as v1,
rate*nullif((1-abs(sign(month-2))),0) as v2,
rate*nullif((1-abs(sign(month-3))),0) as v3
from DepositRates
order by id,month;
ID V1 V2 V3
-- -------- -------- --------
1 0.008000 NULL NULL
1 NULL 0.025500 NULL
1 NULL NULL 0.014100
2 0.007115 NULL NULL
2 NULL 0.007159 NULL
2 NULL NULL 0.007135
3 0.022441 NULL NULL
3 NULL 0.020109 NULL
3 NULL NULL 0.017743
When max() and group by are applied, you can probably see that the final aggregate result is returned as expected.
Other variations
In the above example, we pivoted by a numeric data type and likewise presented a numeric data type. If you imagine that other combinations are possible, your observation is correct. We can pivot and present by any combination of numeric, date or character data types.
Here is a date data type presentation of resetDate. The add_days() function is applied to a point characteristic function that returns either 1, which keeps resetDate, or NULL, which discards resetDate. We rely on the behavior of add_days() to return NULL if the date adder is NULL. I used add_days() but could have used some other date function as well:
select
i.name "name",
i.source "source",
max(add_days(r.resetDate,1-nullif((1-abs(sign(r.month-1))),0))) "current reset",
max(add_days(r.resetDate,1-nullif((1-abs(sign(r.month-2))),0))) "period-1 reset",
max(add_days(r.resetDate,1-nullif((1-abs(sign(r.month-3))),0))) "period-2 reset"
from DepositRates r, Instruments i
where r.id = i.id
group by i.name,i.source
order by i.name;
name source current reset period-1 reset period-2 reset
------- ------- ------------- -------------- --------------
GBP3MD= Euribor 2019-10-01 2019-11-01 2019-12-01
HKDOND= Reuters 2019-10-02 2019-11-01 2019-11-29
USD1MD= Reuters 2019-10-01 NULL 2019-12-01
Here is a character data type presentation of resetNote which relies on the behavior of substring() to return NULL if the starting position is NULL:
select
i.name "name",
i.source "source",
max(substring(r.resetNote,nullif((1-abs(sign(r.month-1))),0),length(r.resetNote))) "current note",
max(substring(r.resetNote,nullif((1-abs(sign(r.month-2))),0),length(r.resetNote))) "period-1 note",
max(substring(r.resetNote,nullif((1-abs(sign(r.month-3))),0),length(r.resetNote))) "period-2 note"
from DepositRates r, Instruments i
where r.id = i.id
group by i.name,i.source
order by i.name;
name source current note period-1 note period-2 note
------- ------- ------------ ------------- -------------
GBP3MD= Euribor NULL NULL NULL
HKDOND= Reuters late reset NULL early reset
USD1MD= Reuters NULL missing reset NULL
The code also works properly when presented with missing data rows. If we assume that the row for instrument 1 for month 1 is missing, the max() aggregate corresponding to month 1 for instrument 1 would be comprised of two components, all of which, not belonging to month 1, would be NULL. The max() aggregate would then return NULL as the result, exactly as desired. The code also works properly if any values themselves are NULL, in exactly the same manner.
Folding, which is the inverse of pivoting, is also described in the book as an application of point characteristic functions. For now though, the immediate problem which I faced is now solved. If you have an interest in this subject, then I highly recommend finding a copy of the book.
Cheers.