Grouping by start of week

8 posts / 0 new
Last post
samc
samc's picture
Offline
Last seen: 3 years 8 months ago
Joined: Apr 3 2010
Senior Boarder

Posts: 23

Sam Crawford
Grouping by start of week

Afternoon,

I'm having difficulty migrating one form of query from MySQL to InfiniDB. This query summarises data in a table by the start of the week:

CREATE TABLE `avg_daily` (
`unit_id` int(11) DEFAULT NULL,
`dtime` date DEFAULT NULL,
`rtt_avg` int(11) DEFAULT NULL,
`successes` smallint(5) DEFAULT NULL,
`failures` smallint(5) DEFAULT NULL
) ENGINE=InfiniDB DEFAULT CHARSET=latin1;

mysql> SELECT DATE_ADD(dtime, INTERVAL DAYOFWEEK(dtime) DAY), COUNT(*) FROM avg_daily GROUP BY 1;
ERROR 122 (HY000): There was an internal error encountered in the Calpont Engine while processing this query. The query was cancelled. You may resubmit it if you like. The error came from PrimitiveServer.

I tried a few other examples that used DATE_FORMAT, but just get back unsupported aggregation column errors.

Any suggestions?

Thanks,

Sam

davestokes
davestokes's picture
Offline
Last seen: 3 years 1 month ago
Joined: Apr 24 2010
Gold Boarder

Posts: 116

David Stokes
Re:Grouping by start of week

Hi Sam,

Thank you for downloading and using InfiniDB.

The DAYOFWEEK function has not yet been rewritten to support distributed processing. This function is on the list to be updated but I do not know which upcoming release.

I also tried DATE_FORMAT(dtime,'%w') which will also return a day of the week (0 to 6). But the distributed function in this case wrongly returns a 'w' instead of the day of the week. I will file a bug report on this.

A list of the Distributed Functions is listed in section 2.2 of the InfiniDB Syntax Guide.

samc
samc's picture
Offline
Last seen: 3 years 8 months ago
Joined: Apr 3 2010
Senior Boarder

Posts: 23

Sam Crawford
Re:Grouping by start of week

Thanks Dave.

Any suggestions then as to how I can achieve my goal, or is just not possible yet?

Thanks,

Sam

samc
samc's picture
Offline
Last seen: 3 years 8 months ago
Joined: Apr 3 2010
Senior Boarder

Posts: 23

Sam Crawford
Re:Grouping by start of week

I've also tried this:

SELECT DATE_ADD('2010-01-01',INTERVAL WEEK(dtime,2) WEEK), COUNT(*) FROM avg_daily GROUP BY 1

Unfortunately it seems DATE_ADD is a little buggy. If I put a column (e.g. dtime above) in the INTERVAL statement, the value returned is NULL. If I run exactly the above from a MyISAM table, it works just fine.

Still hoping for suggestions!

Sam

zcheiban
zcheiban's picture
Offline
Last seen: 2 years 16 hours ago
Joined: Feb 8 2010
Senior Boarder

Posts: 28

Ziad Cheiban
Re:Grouping by start of week

At the risk of being repetitive :) , a well designed date dimension will allow you to aggregate the results by week without using DATE_ADD.

Download the "Date dimension spreadsheet" from Kimball group's [url=http://www.kimballgroup.com/html/booksDWLT2tools.html]webpage[/url] for an example.

samc
samc's picture
Offline
Last seen: 3 years 8 months ago
Joined: Apr 3 2010
Senior Boarder

Posts: 23

Sam Crawford
Re:Grouping by start of week

Appreciate the continued advice zcheiban, but I'm trying to build suitable weekly aggregate tables pre-aggregated at defined granularities (e.g. hourly, daily, weekly, monthly averages) from highly granular data. The idea is that these aggregated tables do not need any further aggregation to be performed on them at runtime, and the datetime column is purely for select ranges.

Also, it doesn't matter if the source table has columns with the datetime components split out or if it's a single datetime column... The source table is InfiniDB and I need to aggregate by start of week, and this is not possible. If you've got a method though, I'm all ears!

Thanks,

Sam

jtommaney
jtommaney's picture
Offline
Last seen: 2 weeks 5 days ago
Joined: Oct 27 2009
Expert Boarder

Posts: 58

Jim Tommaney
Re:Grouping by start of week

We are currently working on adding dayofweek, will keep you posted on availablity. In the meantime, the week() function may provide similar functionality.

ysql> select week(dtime), min(dtime),count(*) from avg_daily where dtime between '2009-07-05' and '2009-08-01' group by 1 order by 1;
+-----------------+----------------+----------+
| week(dtime) | min(dtime) | count(*) |
+-----------------+----------------+----------+
| 27 | 2009-07-05 | 12610160 |
| 28 | 2009-07-12 | 13019150 |
| 29 | 2009-07-19 | 13688050 |
| 30 | 2009-07-26 | 15485680 |
+-----------------+----------------+----------+
4 rows in set (1.67 sec)

Thanks - Jim Tommaney

samc
samc's picture
Offline
Last seen: 3 years 8 months ago
Joined: Apr 3 2010
Senior Boarder

Posts: 23

Sam Crawford
Re:Grouping by start of week

Hi Jim,

Great idea... providing there's data entries for every day of the week (which there will be in my case), then using MIN(dtime) will do the trick nicely.

Thanks again,

Sam