MySQL – Calculate Average Value Over Time with Value Changes

computed-columnMySQLoptimizationquery-performancetimestamp

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.

Best Answer

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.

Related Solutions

Mysql – Correct way to write this query

Write yourself a view that produces, in a single column, the value of price that you're interested in. It might be a UNION of a few different criteria. When you're done you'll have a view of all items with two columns: the id and the price.

Join that view to whatever else you need (perhaps including the original price), and voila.

Tip: skip IF and just use AND and OR, per the SQL standard. And eschew LEFT JOIN unless you know -- and know why -- you can't use a proper inner join. If the database is pervasively unreliable in that regard, it will lead to more pain than all the outer joins in the world can alleviate.

Mysql – Database design for tracked inventory that could get “transformed”

I'm guessing that you want two things: A full history of each item, and a "current" location of each item. Let's make that two database tables.

When items are "combined", let's give the combination a separate serial number.

CREATE TABLE History (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    item_id ...,   -- serial number of item
    combined_id ...,  -- pseudo-serialnumber of combination (or 0 if item by self)
    dt DATE TIME,   -- when the change occurred
    PRIMARY KEY(id),
    INDEX(item_id, dt)  -- handy for listing history of an item
) ENGINE=InnoDB;

CREATE TABLE Current (
    item_id ...,   -- serial number of item
    combined_id ...,  -- what it is currently in
    dt DATE TIME,   -- when it was constructed
    PRIMARY KEY(item_id),
    INDEX(combined_id)   -- to identify current components
) ENGINE=InnoDB;

CREATE TABLE Combos (
    combined_id INT UNSIGNED AUTO_INCREMENT NOT NULL, -- for generating ids
    etc -- description or whatever
    PRIMARY KEY(combined_id)
) ENGINE=InnoDB;

When you "combine" a bunch of items:

INSERT into Combos to get a new combined_id.
Change Current as needed
INSERT multiple rows into History.

Best Answer

Related Solutions

Mysql – Correct way to write this query

Mysql – Database design for tracked inventory that could get “transformed”

Related Question