4 ms·
> On a non-TS oriented DBMS, this query is usually slow and hard to write. I don't see why this is hard to write: SELECT max(time) as most_recent_time, transa
by einpoklum 6y ago
> On a non-TS oriented DBMS, this query is usually slow and hard to write.
I don't see why this is hard to write:
SELECT max(time) as most_recent_time, transaction_price
INTO newtable
FROM oldtable
WHERE time > $my_datetime
GROUP BY time;
As for being slow - can you provide some references about this being slow in non-time-series columnar DBMSes? MonetDB, Vectorwise, Vertica?
- beagle3 6y ago"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.