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.

Improve this question
5
  • That is, you want the AVG() of the last price for each day?
    – Rick James
    Commented Apr 6, 2015 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ᵀᴹ
    Commented Apr 6, 2015 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
    Commented Apr 7, 2015 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
    Commented Apr 7, 2015 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
    Commented Apr 8, 2015 at 17:13

3 Answers 3

Reset to default
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.

Improve this answer
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.
Improve this answer
2
  • 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
    Commented Apr 7, 2015 at 20:44
  • 20K items times 180 rows = 3.6M rows. This is big, but manageable. (1 billion is huge.)
    – Rick James
    Commented Apr 8, 2015 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.

Improve this answer
1
  • When you say do math on the group of values, you mean math outside of mysql?
    – krkeegan
    Commented Apr 7, 2015 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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