Hey guys ... I have a database I'm working on and I'm a bit stuck for the best way to solve a database issue.
I am storing a series of values for a date range. The best example would be like a gas or electricity bill. So for the billing period between the 1st Jan and the 18th March, you used X and your bill comes to Y.
If I pull back a specific bill, thats what I should get. Easy.
But, I also need to be able to take a user specified date range and pull values for that range. This would require taking each bill in a related period, breaking it down to a daily average and then multiplying that value by the number of days from that bill are within the specified period.
My first thought is that I could just make a table that holds averages. It has an ID of a bill, and then an entry for each date the bill covers set to the value of the average for the bill. Then to pull a range is a simple query of avg($value ) of all entries where bill ID = x and date is between set values grouped by month. I don't know how to do this but I feel it should be possible.
It seems like overkill though. Maybe better is to save the average daily value with each bill. Then I could pull back the bill average and then multiply that value by the amount of days from that bill. This requires double handling though ... I think.
Or maybe this is possible in a single query.
Anyone have any suggestions on how to approach this?
One thing to note .... it's on mySQL now ... but it will eventually need to be on sqlite (it would be now but I cant seem to set it up).
Many thanks.
Daily averages over variable date range
Moderator: General Moderators
I would be inclined to calculate it each time you need the information. A general rule of relational database design is to avoid storing calculated values. There are conditions when you may choose to violate that rule, usually because of a really large table (tens of thousands of rows or more) and high usage (many queries per second), but not under more relaxed conditions. The calculation is pretty straightforward. Whether or not you use another table, you're still going to have to determine how many days of the user's request range are at each daily average. For each "bill" that has at least one day within the user's range, you can easily calculate the daily average "on the fly" by dividing the total bill for that period by the number of days, which is just the beginning date subtracted from the ending date, plus one. Multiply that by the number of days of that "month" that are within the user's request range, and do that for each of the "months" and you've got it. An algorithm that may take you a couple of tries before it works, but then it will run far more efficiently than storing a bunch of redundant date.