Great, thank you Ben.
On Thu, Dec 13, 2012 at 11:52 AM, Ben Hood <[email protected]> wrote: > Hey Ian, > > To see this in context (please be aware that the code is under heavy > development for an upcoming new version so it is not stable production code > yet): > > > https://github.com/lshift/diffa/blob/v2/sql-driver/src/main/java/net/lshift/diffa/sql/PartitionAwareDriver.java > > This is the JOOQ-ization of the following query: > > select trunc(month,'YYYY') as year, md5(group_concat(digest order by month > separator '')) as digest > from ( > select trunc(day,'MM') as month, md5(group_concat(digest order by day > separator '')) as digest > from ( > select day, md5(group_concat(digest order by bucket separator '')) > as digest > from ( > select day, bucket, md5(group_concat(version order by id > separator '')) as digest > from ( > select trunc(entry_date,'DD') as day, a.id as id, > a.version as version, ceil(cast(count(*) as real) / 5) as bucket > from t2 a > join t2 b ON a.entry_date = b.entry_date and a.id >= b.id > group by trunc(entry_date,'DD'), a.id, a.version > order by trunc(entry_date,'DD'), bucket > ) > group by day, bucket > ) group by day > ) > group by trunc(day,'MM') > ) group by trunc(month,'YYYY') > > HTH, > > Ben > > > On Thu, Dec 13, 2012 at 5:47 PM, Ian Clarke <[email protected]> wrote: > >> Interesting Ben, thank you. Were you solving an OLAP-type problem too? >> I'd be interested to see how you used truncDay in a select and/or insert >> query. >> >> Ian. >> >> >> On Thu, Dec 13, 2012 at 11:46 AM, Ben Hood <[email protected]> wrote: >> >>> Hey Ian, >>> >>> Lukas would be more specific about best practice, but this is how we >>> solved the problem: >>> >>> Field<Date> truncDay = Factory.field("trunc({0}, {1})", >>> SQLDataType.DATE, YOUR_FIELD_GOES_HERE, inline("DD")); >>> >>> HTH, >>> >>> Ben >>> >>> >>> On Thu, Dec 13, 2012 at 4:34 PM, Ian Clarke <[email protected]>wrote: >>> >>>> I'd like to aggregate reporting data that is currently stored hourly in >>>> a table, such that it is also aggregated by day (I have a column that >>>> specifies the interval, HOUR, DAY, or WEEK). >>>> >>>> Postgres has a useful function called DATE_TRUNC for this, however I am >>>> using Jooq with MySql. >>>> >>>> How can I do this with Jooq? >>>> >>>> Many thanks, >>>> >>>> Ian. >>>> >>> >>> >> >> >> -- >> Ian Clarke >> Blog: http://blog.locut.us/ >> >> > -- Ian Clarke Blog: http://blog.locut.us/
