GROUP BY syntax

Mucello

I'm working on a painfully complicated project dealing with weather data. I have hourly observations, that is, obs that are taken once an hour. I need to gets those observations in a daily summary so I did a "GROUP BY TO_DAYS(ob)" to handle that. No problem.

But now I need to summarize them by month based on those daily summaries. I know I can do GROUP BY YEAR(ob),MONTH(ob) but that will give me a summary based on the hourly obs. I want the monthly summary to be based on the daily summary.

The only way I can think to do this would be two GROUP BY clauses but I don't think that's legal syntax. I could do the query once, load that into an array and have PHP do all the calculations. But that would be too processor intensive I'm afraid. I'd like to do it all in one nice or messy MySQL query.

Can anyone help?

-Mark

AstroTeg

I think what you're going to need to do is actually build a table with the summary data in it and THEN query for your monthly summary off of it. You have two possible approaches to do this depending on what you'd like to do.

First approach is to just create an temporary table in memory. You will take a slight performance hit depending on how much data you have to insert into it. Insert the data and then you can issue your monthly summarize query against that set. Once done, go ahead and delete the temporary table.

Another thought process might be to generate and store a table to maintain the daily summaries. Maybe have a script setup to run automatically to do this for you. Then when someone wants a monthly summary, the you just have your monthly summary query hit the daily summary table and look up the data as needed. The reason I think this will work is I'm taking a guess that once an observation is made for a particular moment in time, there isn't a huge urge to go back and change or update that data. If this is the case, creating summary tables shouldn't be a problem since you don't need to go back and refresh any of the data and the summary table will be like caching the data for your monthly summaries which just means an improvement in performance. Make sense?

Mucello

Thanks for the quick reply.

That makes sense. We had thought of having a cron job run that would create the daily summary tables. But the problem with that is quality control. We have other scripts that quality control all the data and sometimes it flags a few data points as bad. In that case, we manually update that data point or change it to NULL. So if we do that, the tables wouldn't be in synch. It doesn't happen often but it does from time to time.

I think the temporary table might be the solution. We never thought of the apporach. Now is that something I have to setup myself... I mean, do I need to explicitly create a new table, insert data, and drop table whem I'm done. Or is there a cooler way of creating a temporary table that lasts the lifetime of the session or something?

-Mark

AstroTeg

Last a lifetime of the PHP session? The temporary table typically will last the lifetime of the open database connection it was made on unless you explicitly delete the temporary table. You working with a lot of data or just a little?

If you have just a little, then the temporary table approach will be fine. But if you have a lot, you're just going to be chewing on CPU cycles and server RAM. If you have a lot of both, then its not a huge issue.

Mucello

Well, I'm looking at anywhere from 720 rows of data (for one month's worth) to 8640 rows (for one year's worth) of hourly observations. In general, most people would probably pull a year or more at a time of monthly summaries.

I'm not familiar with creating temporary tables. Is there a reference somewhere of how I can go about doing that?

Thanks so much for all your help,
Mark

Mucello

This is great stuff! I got the temporary daily tables working and my monthly query pulls from the temporary daily. It's actually pretty fast too, which was my biggest concern. Only about 15 or so seconds to pull a medium-sized dataset.

Thanks for the idea!!!!
-Mark

Sxooter

Multiple group bys are legal, you might be able to do something like this:

select <list> from table group by to_days(field), field2;

If you need to use a subselect, then you'll likely be ready to switch to firebird or postgresql where you have them and don't have to use temporary tables to get that kind of stuff done.