Jonathan Meyer
06/10/2021, 5:33 PMSELECT * FROM table WHERE x in (SELECT ...)
and
2. SELECT * FROM (SELECT ...)
(i.e. working with the output of a subquery)SELECT SUM(agg_value) FROM (SELECT dateString, AVG(value) as agg_value FROM table GROUP BY dateString)
Or maybe there's another way to express this query ?date | 01 | 02 | 03
entity1 | 1 | 2 | 3
entity2 | 3 | 4 | 5
The idea is to AVG over entity1 and entity2 per day, then SUM those results over all daysMayank
Jonathan Meyer
06/10/2021, 5:41 PMJackie
06/10/2021, 6:23 PMJonathan Meyer
06/10/2021, 6:24 PMJackie
06/10/2021, 6:25 PMJonathan Meyer
06/10/2021, 6:26 PMJackie
06/10/2021, 6:26 PMSELECT AVG(value) FROM table GROUP BY dateString LIMIT 10000
(assuming the cardinality of dateString
is smaller than 10000), then do the sum on the client sideJonathan Meyer
06/10/2021, 6:27 PMJackie
06/10/2021, 6:31 PMMayank
Jackie
06/10/2021, 6:32 PMJonathan Meyer
06/10/2021, 6:33 PMOne option is to use Pinot + Presto if you have nested queries like this.Sounds a bit overkill for that use case as it's not currently available in my environment but thanks for the idea 🙂
Jackie
06/10/2021, 6:33 PMJonathan Meyer
06/10/2021, 6:36 PMMayank
Jonathan Meyer
06/10/2021, 6:49 PM