Say I have a table with dimensions “color” and “size”, and a metric “price”. I would like the top 10 color-size pairings based on price, but then broken down into 1 minute buckets.
I can get the top 10 easily enough with:
SELECT color, size, SUM(price) FROM table GROUP BY 1,2 ORDER BY 3 LIMIT 10 DESC
However, how do I get the SUM(price) of those top 10 pairings in 1 minute buckets?
I’ve tried numerous things (GROUPINGS, adding FLOOR(__time, MINUTE) as a group by), but nothing quite works.
Any help is appreciated!
Hey! I think you will want to GROUP BY TIME_FLOOR of something - that will give you a row per 10-minute interval.
(can’t remember it it’s " or '…!!)
Thanks for the response, Peter!
The issue is that if I do:
SELECT color, size, TIME_FLOOR(__time,‘PT10M’), SUM(price) FROM table GROUP BY 1,2,3 ORDER BY 3 LIMIT 10 DESC
it just gives me the top 10 but now includes the time bucket as part of that top 10 (it only returns 10 results), whereas I want the top 10 grouped by 1,2 and then have the timeseries for each of those results, if that makes sense.
Maybe a JOIN would work?
Yeah I think so… something like
red, big, 10:00, 6
red, big, 10:05, 4
red, big, 10:10, 1
red, small, 10:00, 54
red, small, 10:10, 19
blue, big, 10:00, 43
Subquery may work - inner being the TOP 10 and the outer being the TIME_FLOOR?
I wonder also if GROUPING SETS might give you something… hmmm… scratches head