By the way, if “ts” is sorted, then Calcite can deduce that cast((“ts" - timestamp '1970-01-01 00:00:00') minute as integer) / 15 is also sorted. Maybe that is something that Kylin can exploit in its execution plan.
Julian On Apr 27, 2015, at 11:40 AM, Julian Hyde <[email protected]> wrote: > The answer on SO for MySQL is this*: > > SELECT ROUND(UNIX_TIMESTAMP(ts)/(15 * 60)) AS timekey > FROM table > GROUP BY timekey; > The nearest equivalent in Calcite is this: > > SELECT cast(("hire_date" - timestamp '1970-01-01 00:00:00') minute as > integer) / 15 AS timekey > FROM table > GROUP BY cast(("hire_date" - timestamp '1970-01-01 00:00:00') minute as > integer) / 15; > > But doing date arithmetic in queries is an anti-pattern in ROLAP systems such > as Kylin. A time dimension table is the preferred solution. I suggest that > you add minute to your time dimension table (values 0 .. 59). You could also > consider adding a quarter_hour column (values 0 .. 3). Then to find events in > the same 15 minute interval you could group by date_id, hour, quarter_hour. > > Julian > > * I renamed the column from “timestamp” to “ts” because TIMESTAMP is a > reserved keyword. > > On Apr 27, 2015, at 6:13 AM, Ethan Wang <[email protected]> wrote: > >> Looks like to me based on the stuff calcite has, you may create a calendar >> table first: >> >> tbl_calendar >> id min max >> 1 0 15 >> 2 16 30 >> 3 31 45 >> 4 46 60 >> >> >> select * from >> main, >> tbl_calendar >> where >> main.timestamp >= tbl_calendar.min >> and main.timestamp < tbl_calendar.max >> group by >> tbl_calendar.id >> >> >> >> >> >>> On Apr 27, 2015, at 5:19 AM, Li Yang <[email protected]> wrote: >>> >>> Hi >>> >>> Given a timestamp column, I want to group by every 15 minutes like [1]. >>> How can this be best done with calcite? >>> >>> Looked at the reference, but answer is not obvious. >>> >>> >>> Thanks >>> Yang >>> >>> >>> [1] >>> http://stackoverflow.com/questions/2793994/group-mysql-query-by-15-min-intervals >>> [2] https://github.com/apache/incubator-calcite/blob/master/doc/REFERENCE.md >> >
