https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# general
s
This message was deleted.
k
Have you considered the 
LATEST
function? Something like
Copy code
select
FLOOR(__time TO DAY),
groupId,
LATEST("event")
FROM "fooBarTable"
GROUP BY 1,2
l
First of all, thanks for the answer ! Well, it was my first try, but I need to have all the columns in the result, with the latest value for whole line. And when I tried, the query becomes really slow (even slower than the inner join solution) if I add more than a few LATEST.
Copy code
select
__time,
groupId,
LATEST("event"),
LASTER("otherColumn1"),
LASTER("otherColumn2")
FROM "fooBarTable"
GROUP BY 1,2
I tried that and it is really not efficient. Plus, building the query on the fly in the code with the need to get all the columns names is not really handy.
k
Do you only need to get the latest of one dimension?
l
I need for each groupId the whole lines (with unknown number of dimensions columns) that have the latest known timestamp for that groupId.
another solution could be to have a lookup with the wanted timestamp for each groupId, and use the optimized join of lookups to get the results. With something like:
Copy code
select * from "fooBarTable" where LOOKUP(groupId, 'lookupName').value = __time
But I am scared by the size the lookup will need in memory. Plus, it is not that great for querying when lookup is really large
k
So then maybe you only need to do a 
LATEST
on 
groupId
and just include the rest in the group like
Copy code
select
FLOOR(__time TO DAY),
LATEST("groupId"),
"event",
"otherDim"
FROM "fooBarTable"
GROUP BY 1,3,4
l
well, that works. But as the number of fields in the group by increases, the query starts to be too slow.
at least, slower than the inner join solution
the lookup solution is the fastest for now. But I am not confortable with the need of a huge lookup multiplied by the number of tables.
I am trying to do it with window function. Didn't found something good for now.
j
How big is your original group by subquery to get the latest time per group? If small enough that subquery would be broadcast to all historicals and peons, in which case the join from the main table should be relatively quick, IMHO the quickest of the options presented so far. Using the new maxSubqueryBytes parameter you should be able to squeeze out more data in the broadcasts.
Open in Slack
PreviousNext
Powered by