来自Tic的MySQL OHLC查询

Question

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

+-+-+

Symbol| TTime | TPrice |音量

+-+-+

AAM | 101549 | 1303.15 | 100

AAM | 120405 | 1308.23 |2

AAM | 132142 | 1302.16 | 11

AAM | 145120 | 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:/ Next为Open High Low、Volume、OI等创建临时表

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的每日输出上运行它，它需要几秒钟的时间。