Cumulative monthly reporting - 【StackMirror】|mysql|cumulative-sum

I have a MySQL table of photovoltaic electricity generation data (pvdata) from which I need to produce a monthly summary table. A simplified table is shown:

id date time pvdata
1    2012-01-01 10:00 50
1    2012-01-31 12:00 60
1    2012-02-10 13:00   70
2    2012-02-08 10:00 12
2    2012-03-20 10:00 17

The monthly summary table needs to show the cumulative generation for all systems in the database, regardless of whether I have received data for that month, so for example month 3 below contains the total generation from id = 1 (data received in month 2).

Also there may be more than one data point for an id in the same month, so the report must report the max(data) for the month.

year month cum_data
2012    1 60
2012    2 82
2012    3 87

I am pretty new to this, so have struggled for a while. The best I can come up with shows the cumulative total for the month, but without including the cumulative total for ids for which there is no data in the current month:

CREATE TEMPORARY TABLE intermed_gen_report  
SELECT year(date) AS year, month(date) AS month, id, max(pvdata) AS maxpvdata
FROM pvdata
GROUP BY id, year(date), month(date)
ORDER BY year(date), month(date);

SELECT year, month, SUM(maxpvdata) AS cum_data
FROM intermed_gen_report
GROUP BY year, month
ORDER BY year, month;

Giving:

year month cum_data
2012    1 60
2012    2 82
2012    3 17

2012-04-04 17:38
by solar1

So your query doesn't actually have to accumulate your data - it's already accumulated (in other words, you dont' have to add together all the months previous to this one for pvdata - whatever is the max pvdata is the value to use) right - Aerik 2012-04-04 17:52

Yes, the data in the table is cumulative, so it would be an easy exercise if there was data for each ID every month. Unfortunately this is not the case but my report must still show the total amount for each ID every month - solar1 2012-04-10 09:17

I think the problem is one kind of like this http://www.richnetapps.com/using-mysql-generate-daily-sales-reports-filled-gaps/ - you will want to create a table (possibly temporary) with dates (or year / month values). However that example leaves zeros where there is no data - I think you will want to do a join on a subselect that returns the most recent data before that date (or year/ month value).

2012-04-04 17:57
by Aerik

I agree I think with what Aerik suggests. You will want to join your data of what is usually called a 'date dimension table'. You can find lots of examples on how to populate said table. This is a common technique in data warehousing.

You can also do what you need in one select using sub selects. Take a look at some of the previous threads like: generate days from date range

2012-04-04 18:39
by ficuscr