0

I am tracking the sales price of various items, and I would like to calculate the average price of each item over a period of time.

My simplified tables look like this:

Items
------------------
|key  | title    |
------------------
|1    | Sproket 1|
|2    | Sproket 2|
------------------

And

Prices
----------------------------------------
|item_key | price | datetime           |
----------------------------------------
|1        | 2.99  | 2014-11-25 02:05:56|
|1        | 1.99  | 2014-12-13 02:05:56|
|2        | 9.99  | 2014-10-25 02:05:56|
|2        | 8.99  | 2014-11-13 02:05:56|
----------------------------------------

The Items table contains a single row for each item. The Prices table contains a single row for each price change. The script runs daily and checks for the current prices, if the current price is different than the most recent item price recorded in Prices than a new entry is made in Prices.

My question is, How do I calculate the average price of an item over 180 days?

I can't use AVG because there is not a price entry each day for each item.

  • That is, you want the AVG() of the last price for each day? – Rick James Apr 6 '15 at 18:30
  • If a price was 40.0 for 60 days and 10.0 for the next 120 days, should the average be calculated as 20.0 or as 25.0 ? – ypercubeᵀᴹ Apr 6 '15 at 20:43
  • If the price was $10.0 for 120 days and $40.0 60 days the average price should be: $20.00 – krkeegan Apr 7 '15 at 20:40
  • I have decided it is just too messy to do in SQL with @variables. Either have a price every day (as david suggests) or do the arithmetic in an application language. (I deleted my "Answer" as being wrong and not salvageable.) – Rick James Apr 7 '15 at 23:24
  • @RickJames, well, glad to know I didn't miss anything. I suspected the answer might be "it can't be done." Rick do you want to toss up a "it can't be done" answer that I can select as the answer? – krkeegan Apr 8 '15 at 17:13
0

Well, I started to write SQL to do the task. It got worse and worse. I needed several @variables and perhaps 3 levels of subqueries. So, I decided it was "impossible" in SQL, or at least very messy.

Instead, I would recommend either david's suggestion of having daily prices, which would make AVG work easily (probably no @variables or subqueries), or write it in some "real" programming language (PHP, VB, Java, ...).

One ugly was doing date arithmetic -- with bounds of 180 days ago and today. Walking through the data ASCENDING led to one problem; DESC lead to a different problem.

0

KISS

Assuming daily means every day including weekends and holidays.

  1. Insert price for all items everyday
  2. Use AVG() for value
  3. Delete prices over 180 days.
  • Agree, that would be a simple solution. But the number of rows in the table would increase dramatically, I don't know what the performance hit would be. There are currently 20,000 items, and the prices are generally stable. – krkeegan Apr 7 '15 at 20:44
  • 20K items times 180 rows = 3.6M rows. This is big, but manageable. (1 billion is huge.) – Rick James Apr 8 '15 at 18:44
0

Your probably going to have to use group_concat function to aggregate by key so you can do math on those groups of values.

  • When you say do math on the group of values, you mean math outside of mysql? – krkeegan Apr 7 '15 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.