MySQL – Group Tick Data to Minute Data

greatest-n-per-groupMySQL

I have the following table, Input. Do you know how to group the tick data into minute interval as shown in the table, Desired Output using mySQL? I read the data directly from csv to mySQL

Criteria

  1. It will take the last price within the minute bar and output as its minute bar. ie If I have the these two tick data, 06:04:59 1444.25, 06:04:59 1444.00, my output will be 0604 1444.00.

  2. Convert the date, 2013-01-02 to 20130102

     Input            
        row Date        Time        Price   Vol
        1 2013-01-02    06:04:59    1444.25 3
        2 2013-01-02    06:04:59    1444.00 2
        3 2013-01-02    06:05:00    1446.25 1
        4 2013-01-02    06:05:01    1446.00 2
        5 2013-01-02    06:05:02    1446.35 4
        6 2013-01-02    06:07:01    1444.00 2
        7 2013-01-02    06:07:02    1444.25 4
        8 2013-01-03    16:08:31    1434.00 2
        9 2013-01-03    16:08:52    1434.25 4

    Desired output 

        row Date        Time        Price   Vol
    2 20130102  0604        1444.00 2
    5 20130102  0605        1446.35 4
    7 20130102  0607        1444.25 4
    9 20130103  1608        1434.25 4

I have an idea but I not sure how to put into mySQL code.
1. Convert the date to YYYYMMDD and time to HHMM
2. Remove all the duplicates having the same YYYYMMDD and HHMM and only keep the last entry

Thank you for your help and advices
L

(from Comment)

CREATE TABLE `es_test2` (
   `xdate` date NOT NULL,
   `time` time DEFAULT NULL,
   `price` decimal(8,2) DEFAULT NULL,
   `vol` int(8) unsigned DEFAULT NULL,
   `xcol` int(10) unsigned DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Best Answer

What you want is something like this which uses the extract(HOUR_MINUTE..)

SELECT t1.date, t1.time, t2.price, t2.vol
FROM (
  SELECT date,
    extract(HOUR_MINUTE from time) AS time,
    max(id) AS maxid
  FROM tbl
  GROUP BY date, extract(HOUR_MINUTE from time)
) AS t1
JOIN tbl AS t2
  ON t1.maxid = t2.id;

The inner selects and groups by the date,hour/min, and grabs the max(id) and count(*). The outer joins back to the table and resolves to find the price.

In other news, why are you storing date and time separately? You should be using a timestamp.

Related Question