来自 Tic 查询的 MySQL OHLC

Question

我有一个表格，其中包含各种公司的单独价格列表，例如。

+--------+---------+--------+------+

|符号|时间 | T价格|数量|

+--------+---------+--------+------+

|空空导弹 | 101549 | 101549 1303.15 | 1303.15 100 |

|空空导弹 | 120405 | 120405 1308.23 | 1308.23 2 |

|空空导弹 | 132142 | 132142 1302.16 | 1302.16 11 |

|空空导弹 | 145120 | 1307.75 | 1307.75 1 |

+--------+---------+--------+------+

(有多个符号，但我可以得到这些没有问题)

我正在使用以下内容

SELECT tic.Symbol, 
       tic.TDate,
      (Case when 
            tic.TTime = Min(tic.TTime) 
      then tic.TPrice  end) as `Open`,
      Max(tic.TPrice) AS High,
      Min(tic.TPrice) AS Low,
      (Case when 
            tic.TTime = Max(tic.TTime)
      then tic.TPrice end ) as `Close`,
      Sum(Volume) AS Volume,
      Max(tic.TTime) 
      FROM  tblfinalasxtic AS tic GROUP BY Symbol LIMIT 10;

我遇到一个重大问题

仅当全天只有一个时间或同时只有多个条目时才会生成收盘价。如果时间范围如示例所示，没有收盘价..

开放工作正常，我的假设是最大值可以工作，但不行。

Answer 1

基本上我得出了这样的解决方案

1:/为 tic 值创建一个表 ** 确保创建 ID 列 **

CREATE TABLE price_tic (
            `id` INT(10) NULL AUTO_INCREMENT,
            `T` VARCHAR(3) NULL,
            `SysDateTime` DATETIME NULL,
            `TIC` FLOAT NULL,
            `Volume` FLOAT NULL,
            PRIMARY KEY (`id`)
            )
            COLLATE='latin1_bin'
            ENGINE=MEMORY
            ROW_FORMAT=DEFAULT

2:/接下来为开盘价、成交量、持仓量等创建一个临时表

CREATE TEMPORARY TABLE symbol_ohl
            (ExchDate DATE,
            ExchTime TIME )
            ENGINE = MEMORY
            AS(SELECT 
                   DATE_FORMAT(`SysDateTime`,'%Y-%m-%d') AS ExchDate,
                   DATE_FORMAT(`SysDateTime`,'%H:%i:00') AS ExchTime,
                   DATE_FORMAT(`SysDateTime`,'%Y-%m-%d %H:%i:00') AS ExchDateTime,
                   `TIC` AS `OPEN`,
                   MAX(`TIC`) AS `HIGH`,
                   MIN(`TIC`) AS `LOW`,
                   SUM(Volume) AS `Volume`,
                   COUNT(`TIC`) AS `OpnInt`
                   FROM symbol_tic
                   GROUP BY ExchDateTime
                   ORDER BY ExchDateTime)

3:/接下来为收盘价创建一个临时表，该表使用 ID 号与其自身连接，以便您可以正确排序。

CREATE TEMPORARY TABLE symbol_cls
             (SysDate DATE,
             SysTime TIME, 
             ExchTime TIME )
             ENGINE = MEMORY
             AS(SELECT
                    `TIC` AS `CLOSE`,
                    DATE_FORMAT(`SysDateTime`,'%Y-%m-%d') AS `SysDate`,
                    DATE_FORMAT(`SysDateTime`,'%H:%i:00') AS SysTime,
                    DATE_FORMAT(`SysDateTime`,'%H:%i:00') AS ExchTime,
                    DATE_FORMAT(`SysDateTime`,'%Y-%m-%d %H:%i:00') AS ExchDateTime
                    FROM symbol_tic
                    INNER JOIN
                    (SELECT MAX(id) AS id
                    FROM symbol_tic 
                    GROUP BY DATE_FORMAT(`SysDateTime`,'%Y-%m-%d %H:%i:00')) 
                    ids ON symbol_tic.id = ids.id)

4:/最后合并生成的临时表，我出于各种原因使用 Replace INTO，但你明白了

REPLACE INTO symbol_working
        (`ExchDate`, `ExchTime`, `SysDate`, `SysTime`, `ExchDateTime`,
        `SysDateTime`, `OPEN`, `HIGH`, `LOW`, `CLOSE`, `Volume`, `OpnInt`)
        SELECT
                symbol_ohl.`ExchDate` AS ExchDate,
                symbol_ohl.`ExchTime` AS ExchTime,
                symbol_cls.`SysDate` AS 'SysDate',
                symbol_cls.`SysTime` AS 'SysTime',
                symbol_ohl.`ExchDateTime` As ExchDateTime,
                CONCAT(symbol_cls.`SysDate`, ' ',symbol_cls.`SysTime`) As SysDateTime,
                symbol_ohl.`OPEN`,
                symbol_ohl.`HIGH`,
                symbol_ohl.`LOW`,
                symbol_cls.`CLOSE`,
                symbol_ohl.Volume AS `Volume`,
                symbol_ohl.OpnInt AS `OpnInt`
                FROM 
                (symbol_ohl JOIN symbol_cls 
                ON (symbol_ohl.ExchDateTime = symbol_cls.ExchDateTime))

希望这对某人有帮助。

这工作得很快，我在 S&P emini 的日常输出上运行它，运行需要几秒钟。