Re:Launch HN: QuestDB (YC S20) – Fast open source time serie...
by beagle3 on Wednesday July 29, 2020 @09:01AM
(#23986148)
"most recent" is min(time) where time>=$my_datetime ;
I don't understand your query - max(time) .. GROUP BY time means that every record is its own group, so max(time)==time; also, you will have a result for every single point in time after the requested time.
There is no way in standard SQL (without window extensions) to do this with just one clause; you need the price associated with the min(time) associated with time>=$my_datetime ; You need either a subquery, a join, or some other correlated query. e.g.
SELECT time, price FROM trades WHERE time IN (SELECT min(time) FROM trades WHERE time>=$my_datetime)
And that's for one record; It gets more complicated when you try to do a batch.
> As for being slow - can you provide some references about this being slow in non-time-series columnar DBMSes? MonetDB, Vectorwise, Vertica?
I've timed kdb2 against Vertica a decade or so ago, and kdb2 was about x10 faster, I didn't do a proper benchmark but monetdb was in the x10-x100 slower than kdb2 at the time.
I don't understand your query - max(time) .. GROUP BY time means that every record is its own group, so max(time)==time; also, you will have a result for every single point in time after the requested time.
There is no way in standard SQL (without window extensions) to do this with just one clause; you need the price associated with the min(time) associated with time>=$my_datetime ; You need either a subquery, a join, or some other correlated query. e.g.
SELECT time, price FROM trades WHERE time IN (SELECT min(time) FROM trades WHERE time>=$my_datetime)
And that's for one record; It gets more complicated when you try to do a batch.
> As for being slow - can you provide some references about this being slow in non-time-series columnar DBMSes? MonetDB, Vectorwise, Vertica?
I've timed kdb2 against Vertica a decade or so ago, and kdb2 was about x10 faster, I didn't do a proper benchmark but monetdb was in the x10-x100 slower than kdb2 at the time.