-2

I have a table with prices per products that could be updated daily.

The update process is on-event, so if the price does not change there are no new entries for that day and that product.

I need to calculate the average last 30 days price per each product considering the day without a price equal to the previous last valorized day.

For instance, if there is a new price on 5/12 and another one on 20/12 from 5 to 19 December the price shall be considered equal to the one saved on 5/12.

Any simple good idea to do it with a MySQL query?

For instance, I can enrich the explanation with this example:

Table name: product_prices
Fields: id (PRIMARY KEY), product_ID (external key); product_price (FLOAT); creation (DATETIME)

Table data
---------------------------------------------------------
|id   |  product_id   |   product_price    |   creation |
----------------------------------------------------------
|1    |  30           |       15.2         |       1/12 |
|2    |  53           |         7.5        |       1/12 |
|3    |  30           |         15.4       |       5/12 |
|4    |  30           |         16         |      20/12 |
----------------------------------------------------------

at 31/12 the average price for product_id == 30 shall be (3*15.2+15*15.4+12*16) = 15.62

fede72bari
  • 343
  • 5
  • 17
  • Read this. https://stackoverflow.com/help/how-to-ask – Eric Jun 04 '19 at 16:01
  • The 1st to the 4th is four days !?! And where does 11*15 come from !?! – Strawberry Jun 04 '19 at 17:21
  • @Strawberry Yes sorry, i was correcting that elaboration but somebody else was editing so that I coudln't realize that my correction was not saved. Here the steps: 1. the average is always on 30 days, since it is done on 31/12 in this case it starts on 2/12. days 2,3,4 at 15.2 (3 days); days from 5/12 to 19/2 at 15.4 price (15 days); days from 20/12 to 31/12 at 16 price (12 days). Days check: 3+15+12 = 30 days. Average result = 15.62. – fede72bari Jun 05 '19 at 09:06
  • Of the two, my first comment is really the more important – Strawberry Jun 05 '19 at 17:08

1 Answers1

1

Check out this solution from Bill Karwin:

COALESCE()

I believe it does what you need.

caszboy
  • 48
  • 1
  • 8
  • Thanks, I gave a glance and I think that it is a similar problem but still different: 1. in that case all the rows are present, but some of them have the needed field set to NULL 2. in my case the rows between two daily updates could be not present, for sure they are not present if there were not price updates 3. in my case i do not need to fill the missing rows copying the value of the last one; I need to calculate and averge including the prices of the days with no updates. Inserting new rows will increase the DB size from 10 to 25 times. – fede72bari Jun 05 '19 at 08:57
  • Most DB have a calendar table somewhere. I would use that as the base table to then join your data to and use coalesce() to fill in the blank rows. After, create a new variable to run the average. I'm sure there is more of a streamlined way to do it, but that is what I have done with "event" tables in the past when developing dashboards. If you don't have a date table, I would create one https://stackoverflow.com/a/26944861/9892527 – caszboy Jun 05 '19 at 16:23
  • thank you ... thinking to do it directly in PHP, seems easier, simpler to me. I thought it was a common problem for which there were devoted mtSQL functions to be used and that I didn't know. – fede72bari Jun 07 '19 at 08:23
  • I agree. It's not too bad in MySQL if the data is static, but to make it dynamic, it takes a little bit of work. I mainly use MySQL in DOMO and I'm used to being forced to create in MySQL. If you don't have a calendar, then you can create one in a temp table, join your data, use COALESCE() example and then find your average. Your AVG will be a where statement dependent on the year and month...Do you have the ability to create temp tables on your server? – caszboy Jun 07 '19 at 12:58
  • I am not so skilled in MySQL; that's why I was thinking to manage the computation with arrays in PHP. Thanks for this exchange of perspectives. – fede72bari Jun 10 '19 at 09:38