4

I have a price table, contain 2.9 million rows of price figures belong to a total of 941 of company_id for three months. 0.00 represents missing data. As an example:

   +------------+---------------------+------------+
   | company_id | date_time           | price_open |
   +------------+---------------------+------------+
   |      4     | 13/12/2014 16:23:00 |    56.30   |
   |      4     | 13/12/2014 16:24:00 |    56.25   |
   |      4     | 13/12/2014 16:25:00 |    56.25   | <--- last price of `company_id` = `4`
   |      5     |  22/9/2014 08:00:00 |     0.00   | <--- first price of `company_id` = `5`
   |      5     |  22/9/2014 08:01:00 |     0.00   |
   |      5     |  22/9/2014 08:02:00 |     5.45   |
   |      5     |  22/9/2014 08:03:00 |     5.25   |
   |      5     |  22/9/2014 08:04:00 |     0.00   |
   |      5     |  22/9/2014 08:05:00 |     0.00   |
   +------------+---------------------+------------+

May I know if I can achieve the below by entirely using MySQL command? If yes, any guidance/help would be much appreciated.

  1. Loop through each company_id and sort price_open by its date_time, then
  2. if found any price_open = 0.00, replace this/these 0.00 value(s) with previous known price_open figure, but
  3. if the price_open = 0.00 is in the very first row or first few rows of a company_id, do not replace with previous known price_open figure. Instead, use the next known price_open figure - because it shouldn't mix up with other company_id.

Expected result will be as follow:

   +------------+---------------------+------------+
   | company_id | date_time           | price_open |
   +------------+---------------------+------------+
   |      4     | 13/12/2014 16:23:00 |    56.30   |
   |      4     | 13/12/2014 16:24:00 |    56.25   |
   |      4     | 13/12/2014 16:25:00 |    56.25   | <--- last price of `company_id` = `4`
   |      5     |  22/9/2014 08:00:00 |     5.45   | <--- first price of `company_id` = `5`
   |      5     |  22/9/2014 08:01:00 |     5.45   |
   |      5     |  22/9/2014 08:02:00 |     5.45   |
   |      5     |  22/9/2014 08:03:00 |     5.25   |
   |      5     |  22/9/2014 08:04:00 |     5.25   |
   |      5     |  22/9/2014 08:05:00 |     5.25   |
   +------------+---------------------+------------+

I have tried searching about this but those weren't really close to what I am trying to achieve.

Note: It doesn't matter if it has to be done in a new table rather than original price table.

Thank you very much in advanced! :-)

6
  • What are the queries that will be run? I'm thinking that by deleting all rows with price=0.00, and perhaps making the queries a little more complex, you can solve the "real problem" in a different way.
    – Rick James
    Feb 15, 2016 at 20:05
  • @RickJames thanks for your message. :) I need to calculate three types of moving average based on these 1-minute bar prices in order to run more tasks using these moving averages. But because of these zeros, the moving average became inaccurate, which affecting my system on whether the prices hikes/dives at a certain date_time are really that case. Imagine a graph with 1-minute price bars and suddenly it dived down to 0.00, and at the same time the moving average line at around that point also dived a little (which is not the true case and solely caused by the zeros).
    – merv
    Feb 15, 2016 at 20:17
  • Are the date_time values always 1 minute apart (except for start/end)?
    – Rick James
    Feb 15, 2016 at 20:23
  • @RickJames yes, you're right. :) And in case you need to know, not all 1-minute prices are being recorded in the table, because they weren't provided, which i have no control over that. So, for what I have i.e. these zeros, I would like to "fix" it instead of removing them. :) in this case, replacing the zeros using previous/next available known figure is the right way to do it.
    – merv
    Feb 15, 2016 at 20:33
  • Personally, I think you should have gaps in the moving average graph where there is missing data. That would more accurately represent a "trading stopped" situation. That's probably what you do overnight, anyway?
    – Rick James
    Feb 15, 2016 at 20:39

1 Answer 1

2

Add this composite index if you don't already have it -- it should probably be the PRIMARY KEY.

INDEX(company_id, date_time)

Run this a few times (until it stops modifying any rows)

UPDATE    tbl a
    JOIN  tbl b
       ON  b.company_id = a.company_id
      AND  b.date_time  = a.date_time + INTERVAL 1 MINUTE
    SET a.price_open = b.price_open
    WHERE  a.price_open  = 0.00
      AND  b.price_open != 0.00; 

In case I am wrong, suggest you copy the table over for testing:

CREATE TABLE tbl LIKE real_tbl;
ALTER TABLE tbl DROP PRIMARY KEY,
                ADD  PRIMARY KEY(company_id, date_time);  -- if needed
INSERT INTO tbl SELECT * FROM real_tbl;
run the update a few times
check the values
if satisfied, ...
RENAME TABLE real_tbl TO old, tbl TO real_tbl;
DROP TABLE old;

On the presumption that your SELECTs will be company-centric, it is better to have PRIMARY KEY(co, dt) than (dt, co).

1
  • Note: that code assumes the 'next' value is the one to use. A similar query could work the other direction -- to fill in at the end of the day.
    – Rick James
    Feb 15, 2016 at 20:37

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.