Rating curves / value transformations

[davetapley]

I'm looking at QuestDB for applications in hydrology where rating curves are commonly used.
It would be very useful if they were 'built in', and I think such functionality could be broadly useful outside just hydrology.

I'll give some examples below, but foremost I'm wondering if this would be considered for inclusion?
(I'm willing to contribute if it makes sense).

---

Given some data:

ts	val
1	2
2	65

I specify a rating curve as a relationship between domain and range as a series of fixed points, e.g:

domain	range
0	10
50	20
60	20
100	40

This is then applied by linearly interpolating between the closest domain for each val, e.g:

• 2 is 0.04 between 0 and 50 and so val' is 10 + ((20 - 10) * 0.04) = 10.4.
• 65 is 0.125 between 60 and 100 and so val' is 20 + ((40 - 20) * 0.125) = 22.5.

Any val outside the limits given in the rating curve's domain are undefined.

---

Implementing this currently with JOIN would be awful,
but I think if there were some special e.g. TRANSFORM function then it could be very elegant.

One other fact:
These rating tables are updated intermittently, so there also needs to be logic to select the correct table.
ASOF JOIN would be perfect for this 💫

---

Here's what I envisage, I can select data, join a rating table, and use some new TRANSFORM function to do the math:

CREATE TABLE data (val FLOAT, ts TIMESTAMP) timestamp(ts);
CREATE TABLE rating (id LONG, domain FLOAT, range FLOAT, effective TIMESTAMP) timestamp(effective);

SELECT ts, val, TRANSFORM(val, domain, range)
FROM data
ASOF JOIN rating
WHERE id = 123

ts	val	transform
1	2	10.4
2	65	22.5

[newskooler]

Hi Dave,
Thanks for so elegantly describing your proposal.
Is it fair to say that basically one defines an x-y plane curve of some sorts, and then provided specific values - they are matched to that curve?

[bziobrowski]

This type of calculation would also be possible with window functions.
PG example :

CREATE TABLE data (ts TIMESTAMP, val numeric);

CREATE TABLE rating (id integer, domain numeric, range numeric, effective TIMESTAMP);

insert into data values (now(),2), (now(),65);

insert into rating values (1, 0, 10, now()), (1,50,20,now()), (3, 60, 20, now()), (4,100,40,now());

select ts, val, range_low + ((val-dom_low)/(dom_high-dom_low))*(range_high-range_low) as transform
from  
(
   select data.ts, data.val, 
          lag(domain) over (partition by val order by domain) as dom_low, 
          domain as dom_high, 
          lag(range) over (partition by val order by domain) as range_low, 
          range    as range_high   
   from data 
   join rating on data.ts < rating.effective  (no asof join in PG)
) as stats 
where val >= dom_low and val <= dom_high ;

             ts                     | val |        transform
----------------------------+-----+-------------------------
 2022-04-13 09:17:11.567709 |   2  | 10.40000000000000000000
 2022-04-13 09:17:11.567709 |  65 | 22.50000000000000000000
(2 rows)

[newskooler]

Thanks!
Isn't that something which will be unlocked with python?
I don't work for QDB, but the list of such request (I assume) can be endless. If the focus is on getting python to work with QDB execution then it will automatically unlock all of these. That's my understanding at least.

[davetapley]

So yes this is something which can absolutely be done in Python or SQL, but it:

1. Would be slower than a 'native' implementation, and:
2. Prevents the use of aggregates and SAMPLE BY.

As it currently stands I can use ASOF to select the correct rating table for each value, but then I need a second query to load the rating tables, and then I have to do all the arithmetic in the application code.

... but honestly at that point I'll probably just go with https://github.com/timescale/timescaledb so I can use all the other existing Postgres functionality 😑

[bziobrowski]

@davetapley You could easily remove the need for custom transform function if you tweaked rating table a bit :

CREATE TABLE rating (id integer, domain_lo numeric, domain_hi numeric, range_lo numeric, range_hi numeric, effective TIMESTAMP);

then query becomes :

SELECT ts, val, range_lo + ((val-dom_lo)/(dom_hi-dom_lo))*(range_hi-range_lo) as transform
FROM data
ASOF JOIN rating
WHERE val >= domain_lo and val <= domain_hi ;

By the way - asof join matches one row from left table with at most one row from right table .

[davetapley]

Wow, that's amazing @bziobrowski, thanks!

I suppose it does risk the function being non-continuous, but that's a small price to pay for the efficiency 💫