/*
SQLyog Enterprise Trial - MySQL GUI v8.14
MySQL - 5.1.34-community-log
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;create table `aikidofxtick_t` (
`Symbol` varchar (48),
`StartTime` float ,
`OpenPrx` float ,
`HighPrx` float ,
`LowPrx` float ,
`LastPrx` float
);
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.29059e+012','81.226','81.271','81.211','81.247');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.29059e+012','81.249','81.264','81.208','81.244');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.29059e+012','81.247','81.278','81.224','81.263');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.29059e+012','81.267','81.282','81.23','81.245');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.29059e+012','81.247','81.285','81.232','81.27');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4955','81.513','81.437','81.4545');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4545','81.488','81.437','81.4625');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4535','81.48','81.43','81.4495');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4545','81.478','81.419','81.4365');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.445','81.47','81.41','81.45');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4575','81.531','81.441','81.5135');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5125','81.551','81.492','81.5335');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.532','81.547','81.485','81.5085');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5125','81.553','81.495','81.5335');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5435','81.561','81.507','81.532');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.54','81.555','81.499','81.5175');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.524','81.542','81.494','81.519');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.519','81.534','81.484','81.509');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.508','81.544','81.493','81.5145');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5115','81.529','81.469','81.509');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5095','81.532','81.472','81.4965');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5015','81.536','81.48','81.502');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5255','81.567','81.504','81.5485');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.545','81.561','81.486','81.507');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4935','81.518','81.459','81.4805');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4785','81.496','81.429','81.4465');
SQLyog Enterprise Trial - MySQL GUI v8.14
MySQL - 5.1.34-community-log
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;create table `aikidofxtick_t` (
`Symbol` varchar (48),
`StartTime` float ,
`OpenPrx` float ,
`HighPrx` float ,
`LowPrx` float ,
`LastPrx` float
);
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.29059e+012','81.226','81.271','81.211','81.247');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.29059e+012','81.249','81.264','81.208','81.244');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.29059e+012','81.247','81.278','81.224','81.263');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.29059e+012','81.267','81.282','81.23','81.245');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.29059e+012','81.247','81.285','81.232','81.27');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4955','81.513','81.437','81.4545');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4545','81.488','81.437','81.4625');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4535','81.48','81.43','81.4495');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4545','81.478','81.419','81.4365');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.445','81.47','81.41','81.45');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4575','81.531','81.441','81.5135');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5125','81.551','81.492','81.5335');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.532','81.547','81.485','81.5085');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5125','81.553','81.495','81.5335');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5435','81.561','81.507','81.532');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.54','81.555','81.499','81.5175');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.524','81.542','81.494','81.519');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.519','81.534','81.484','81.509');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.508','81.544','81.493','81.5145');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5115','81.529','81.469','81.509');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5095','81.532','81.472','81.4965');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5015','81.536','81.48','81.502');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.5255','81.567','81.504','81.5485');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.545','81.561','81.486','81.507');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4935','81.518','81.459','81.4805');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPrx`, `HighPrx`, `LowPrx`, `LastPrx`) values('AUD_JPY','1.2906e+012','81.4785','81.496','81.429','81.4465');
每1min, 5min, 15min, 30min, 60min, 4H, 8H, day 的最高价 最低价 开盘价 收盘价
不知道我的问题谁清楚没?
SELECT Symbol,
FROM_UNIXTIME(StartTime/1000,'%Y%m%d-%H') AS ST,
OpenPrx,MAX(HighPrx) AS HighPrx,
MIN(LowPrx) AS LowPrx,
LastPrx
FROM AikidoFxTick_t t0
WHERE t0.Symbol='CAD_JPY'
GROUP BY ST
ORDER BY ST DESC
LIMIT 200;
这是我查出的每小时的数据 数据样例如下
Symbol ST OpenPrx HighPrx LowPrx LastPrx
CAD_JPY 20101213-14 83.296 83.339 83.205 83.3015
CAD_JPY 20101213-12 83.125 83.33 83.077 83.129
CAD_JPY 20101213-09 83.1495 83.221 83.041 83.153
CAD_JPY 20101213-06 83.151 83.26 83.103 83.165
CAD_JPY 20101211-07 83.16 83.185 83.095 83.1475
CAD_JPY 20101211-04 83.1705 83.235 83.07 83.165
CAD_JPY 20101211-01 83.128 83.23 83.03 83.123
CAD_JPY 20101210-23 82.8025 83.233 82.755 82.8325
CAD_JPY 20101210-20 82.6975 82.941 82.575 82.6775
`HighPrx` float ,
`LowPrx` float ,
`LastPrx` float
这4个字段是什么意思?
HighPrx 最高价 (该段时间内最高的价格)
LowPrx 最低价(该段时间内最低的价格)
LastPrx 收盘价 (该段时间内最后的价格) 比如区间取60min
12:10 价格是 85.23
12:45 价格是 86.52
12.56 价格是 86.52
则开盘价是 85.23 收盘价是 86.52
'1.29059e+012'
'1.29059e+012'
'1.29059e+012'
'1.29059e+012'
'1.29059e+012'
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
'1.2906e+012',
楼主你最好自己先测试一下,然后基于这套数据写出你的结果。
select Symbol,FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(60*60)) As StartTime,
(select OpenPrx from aikidofxtick_t
where FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(60*60))=FROM_UNIXTIME(a.StartTime/1000-(a.StartTime/1000)%(60*60))
order by StartTime limit 1) as OpenPrx,
Max(HighPrx) As HighPrx,
min(LowPrx) as LowPrx,
(select LastPrx from aikidofxtick_t
where FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(60*60))=FROM_UNIXTIME(a.StartTime/1000-(a.StartTime/1000)%(60*60))
order by StartTime desc limit 1) as LastPrx
from aikidofxtick_t a
Group by FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(60*60))
(select OpenPrx from aikidofxtick_t
where FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(60*60))=FROM_UNIXTIME(a.StartTime/1000-(a.StartTime/1000)%(60*60))
order by StartTime limit 1) as OpenPrx,子查询放在select 语句里 好像查不出来,查的时候太卡了 效率低
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
`Symbol` varchar(16) NOT NULL,
`StartTime` float NOT NULL,
`OpenPrx` float NOT NULL,
`HighPrx` float NOT NULL,
`LowPrx` float NOT NULL,
`LastPrx` float NOT NULL,
KEY `AikidoFxTickIndex1` (`Symbol`),
KEY `AikidoFxTickIndex2` (`StartTime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
+----------+
| COUNT(*) |
+----------+
| 412877 |
+----------+
1 row in set (0.00 sec)因为里面的数据量很大 所以需要 对子查询进行优化
版主 你的sql语句能不能优化啊、
SQLyog Enterprise Trial - MySQL GUI v8.14
MySQL - 5.1.34-community-log
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;create table `aikidofxtick_t` (
`Symbol` varchar (48),
`StartTime` float ,
`OpenPRx` float ,
`HighPrx` float ,
`LowPrx` float ,
`LastPrx` float
);
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29059e+012','83.161','83.178','83.146','83.168');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2906e+012','83.305','83.322','83.288','83.2985');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29061e+012','83.428','83.454','83.42','83.444');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29062e+012','83.371','83.395','83.356','83.361');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29063e+012','83.478','83.525','83.468','83.5025');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29064e+012','83.5505','83.563','83.538','83.55');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29065e+012','83.5','83.511','83.485','83.4945');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29066e+012','83.4755','83.485','83.466','83.4755');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29067e+012','83.5085','83.517','83.49','83.498');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29068e+012','83.4655','83.476','83.454','83.4655');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29069e+012','83.593','83.612','83.573','83.5825');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2907e+012','83.55','83.562','83.529','83.5405');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29071e+012','83.6225','83.635','83.613','83.624');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29072e+012','83.586','83.608','83.573','83.591');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29073e+012','83.6065','83.621','83.594','83.6065');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29074e+012','83.755','83.765','83.745','83.7555');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29075e+012','83.736','83.754','83.727','83.739');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29076e+012','83.9255','83.943','83.916','83.9335');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29077e+012','83.915','83.933','83.907','83.9155');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29078e+012','83.851','83.864','83.84','83.8515');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29079e+012','84.099','84.114','84.089','84.1045');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2908e+012','84.056','84.067','84.015','84.034');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29081e+012','84.098','84.108','84.088','84.0995');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.291e+012','84.104','84.114','84.094','84.104');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29101e+012','84.117','84.124','84.107','84.1155');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29102e+012','83.9765','83.986','83.955','83.9745');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29103e+012','84.0305','84.066','84.011','84.059');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29104e+012','84.107','84.137','84.1','84.1225');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29105e+012','84.321','84.331','84.299','84.322');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29106e+012','84.321','84.331','84.307','84.3195');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29107e+012','84.2445','84.264','84.234','84.251');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29108e+012','84.2725','84.293','84.263','84.2815');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29109e+012','84.14','84.151','84.132','84.141');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2911e+012','84.0295','84.048','84.022','84.0315');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29111e+012','83.982','83.992','83.96','83.9735');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29112e+012','83.798','83.812','83.788','83.8015');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29114e+012','83.5535','83.572','83.544','83.556');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29115e+012','83.634','83.644','83.615','83.625');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29116e+012','83.636','83.649','83.62','83.625');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29117e+012','83.682','83.692','83.671','83.68');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29118e+012','83.616','83.626','83.601','83.6105');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29119e+012','83.481','83.491','83.462','83.473');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2912e+012','83.742','83.755','83.729','83.745');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29172e+012','82.625','82.636','82.612','82.6225');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29173e+012','82.5805','82.603','82.567','82.5905');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29174e+012','83.116','83.126','83.09','83.1');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29175e+012','83.2765','83.287','83.238','83.2555');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29176e+012','83.4885','83.5','83.474','83.488');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29177e+012','83.5045','83.525','83.495','83.514');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29178e+012','83.6455','83.683','83.636','83.665');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29179e+012','83.8925','83.901','83.873','83.883');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2918e+012','83.864','83.872','83.853','83.8605');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29181e+012','83.89','83.905','83.867','83.885');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29182e+012','83.9745','83.982','83.945','83.9565');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29183e+012','84.151','84.172','84.143','84.1635');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29184e+012','84.1195','84.153','84.088','84.0945');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29185e+012','84.0215','84.048','84.01','84.0335');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29186e+012','83.958','83.968','83.926','83.9435');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29187e+012','83.7545','83.769','83.745','83.755');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29188e+012','83.8335','83.844','83.814','83.8245');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29189e+012','83.8325','83.845','83.814','83.8245');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2919e+012','83.966','83.975','83.935','83.952');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29191e+012','83.7935','83.832','83.783','83.8185');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29192e+012','83.8465','83.862','83.837','83.8485');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29193e+012','83.716','83.727','83.701','83.7115');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29194e+012','83.765','83.79','83.752','83.778');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29195e+012','83.714','83.741','83.702','83.727');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29196e+012','83.706','83.717','83.694','83.706');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29197e+012','83.682','83.702','83.664','83.689');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29198e+012','83.574','83.585','83.548','83.565');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29199e+012','83.5745','83.603','83.565','83.587');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.292e+012','83.9595','83.978','83.944','83.9655');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29201e+012','83.903','83.919','83.894','83.9085');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29202e+012','83.944','83.955','83.923','83.932');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29219e+012','83.997','84.033','83.982','84.014');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2922e+012','83.976','83.997','83.965','83.981');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29221e+012','83.9525','83.965','83.941','83.9545');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29222e+012','84.1265','84.143','84.118','84.1345');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29122e+012','84.336','84.349','84.324','84.3335');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29123e+012','84.0745','84.085','84.053','84.0695');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29124e+012','84.183','84.192','84.169','84.1805');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29125e+012','84.1695','84.179','84.156','84.1645');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29126e+012','84.1085','84.118','84.1','84.1095');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29127e+012','84.0915','84.101','84.072','84.083');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29128e+012','84.0305','84.045','84.021','84.035');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29129e+012','84.1355','84.159','84.111','84.1505');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2913e+012','84.3495','84.358','84.329','84.339');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29131e+012','84.017','84.028','83.997','84.0065');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29132e+012','83.77','83.789','83.761','83.7815');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29133e+012','83.8795','83.89','83.866','83.8755');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29134e+012','83.728','83.751','83.718','83.735');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29135e+012','83.7205','83.73','83.704','83.713');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29136e+012','83.6725','83.682','83.663','83.6725');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29137e+012','83.6685','83.676','83.65','83.6575');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29138e+012','83.607','83.615','83.594','83.6065');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29139e+012','82.5975','82.673','82.588','82.6645');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2914e+012','82.752','82.763','82.733','82.743');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29141e+012','82.926','82.948','82.914','82.9265');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29159e+012','82.666','82.689','82.65','82.674');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2916e+012','82.8345','82.843','82.821','82.831');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29161e+012','82.8675','82.877','82.849','82.8675');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29162e+012','82.91','82.919','82.901','82.911');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29163e+012','82.8425','82.854','82.831','82.8455');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29164e+012','82.891','82.901','82.873','82.8825');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29165e+012','82.8695','82.883','82.851','82.8645');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29166e+012','82.7145','82.731','82.703','82.7135');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29167e+012','82.585','82.611','82.574','82.5975');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29168e+012','82.64','82.655','82.626','82.6405');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29169e+012','82.5595','82.575','82.543','82.5555');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.2917e+012','82.488','82.5','82.466','82.476');
insert into `aikidofxtick_t` (`Symbol`, `StartTime`, `OpenPRx`, `HighPrx`, `LowPrx`, `LastPrx`) values('USD_JPY','1.29171e+012','82.584','82.594','82.566','82.585');
SELECT Symbol,FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400)) AS ST,(SELECT OpenPrx FROM AikidoFxTick_t WHERE FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))=FROM_UNIXTIME(a.StartTime/1000-(a.StartTime/1000)%(86400)) AND Symbol='USD_JPY' AND StartTime<=1.29233886E12 ORDER BY StartTime LIMIT 1) AS OpenPrx,MAX(HighPrx) AS HighPrx,MIN(LowPrx) AS LowPrx,(SELECT LastPrx FROM AikidoFxTick_t WHERE FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))=FROM_UNIXTIME(a.StartTime/1000-(a.StartTime/1000)%(86400)) AND Symbol='USD_JPY' AND StartTime<=1.29233886E12 ORDER BY StartTime DESC LIMIT 1) AS LastPrx FROM AikidoFxTick_t AS a WHERE Symbol='USD_JPY' AND StartTime<=1.29233886E12 GROUP BY FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400)) LIMIT 200;结果应该是Symbol ST OpenPrx HighPrx LowPrx LastPrx
USD_JPY 2010-11-24 09:00:00 83.161003112793 83.563 83.146 83.5500030517578
USD_JPY 2010-11-25 09:00:00 83.5 83.635 83.454 83.5910034179688
USD_JPY 2010-11-26 09:00:00 83.6064987182617 84.114 83.594 84.0995025634766
USD_JPY 2010-11-29 09:00:00 84.1039962768555 84.331 83.955 84.2509994506836
USD_JPY 2010-11-30 09:00:00 84.2724990844727 84.293 83.521 83.625
USD_JPY 2010-12-01 09:00:00 83.681999206543 84.349 83.462 84.1804962158203
USD_JPY 2010-12-02 09:00:00 84.1695022583008 84.358 83.761 83.8755035400391
USD_JPY 2010-12-03 09:00:00 83.7279968261719 83.751 82.588 82.9264984130859
USD_JPY 2010-12-05 09:00:00 82.6660003662109 82.689 82.65 82.6740036010742
USD_JPY 2010-12-06 09:00:00 82.8345031738281 82.919 82.574 82.6405029296875
USD_JPY 2010-12-07 09:00:00 82.5595016479492 83.5 82.466 83.4879989624023
USD_JPY 2010-12-08 09:00:00 83.5045013427734 84.172 83.495 84.0335006713867
USD_JPY 2010-12-09 09:00:00 83.9580001831055 83.975 83.701 83.7115020751953
USD_JPY 2010-12-10 09:00:00 83.7649993896484 83.978 83.548 83.931999206543
USD_JPY 2010-12-12 09:00:00 83.9970016479492 84.033 83.982 84.0139999389648
USD_JPY 2010-12-13 09:00:00 83.9759979248047 84.143 83.941 84.1344985961914
Symbol='USD_JPY' AND StartTime<=1.29233886E12 ORDER BY StartTime LIMIT 1) AS OpenPrx,MAX(HighPrx) AS HighPrx,MIN(LowPrx) AS LowPrx,(SELECT LastPrx FROM AikidoFxTick_t WHERE FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))=FROM_UNIXTIME(a.StartT
ime/1000-(a.StartTime/1000)%(86400)) AND Symbol='USD_JPY' AND StartTime<=1.29233886E12 ORDER BY StartTime DESC LIMIT 1) AS LastPrx FROM AikidoFxTick_t AS a WHERE Symbol='USD_JPY' AND StartTime<=1.29233886E12 GROUP BY FROM_UNIXTIME(StartTime/1000-(Sta
rtTime/1000)%(86400)) LIMIT 200;
+----+--------------------+----------------+-------+---------------------------------------+--------------------+---------+-------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------------+-------+---------------------------------------+--------------------+---------+-------+--------+----------------------------------------------+
| 1 | PRIMARY | a | ref | AikidoFxTickIndex1,AikidoFxTickIndex2 | AikidoFxTickIndex1 | 18 | const | 5534 | Using where; Using temporary; Using filesort |
| 3 | DEPENDENT SUBQUERY | AikidoFxTick_t | range | AikidoFxTickIndex1,AikidoFxTickIndex2 | AikidoFxTickIndex2 | 4 | NULL | 412877 | Using where |
| 2 | DEPENDENT SUBQUERY | AikidoFxTick_t | range | AikidoFxTickIndex1,AikidoFxTickIndex2 | AikidoFxTickIndex2 | 4 | NULL | 412877 | Using where |
+----+--------------------+----------------+-------+---------------------------------------+--------------------+---------+-------+--------+----------------------------------------------+
3 rows in set (0.03 sec)mysql>
-> (select OpenPrx from aikidofxtick_t
-> where StartTime-StartTime%(24*60*60*1000)=a.StartTime-a.StartTime%(24*60*60*1000)
-> order by StartTime limit 1) as OpenPrx,
-> Max(HighPrx) As HighPrx,
-> min(LowPrx) as LowPrx,
-> (select LastPrx from aikidofxtick_t
-> where StartTime-StartTime%(24*60*60*1000)=a.StartTime-a.StartTime%(24*60*60*1000)
-> order by StartTime desc limit 1) as LastPrx
-> from aikidofxtick_t a
-> Group by StartTime-StartTime%(24*60*60*1000);
+---------+---------------------+------------------+---------+--------+------------------+
| Symbol | StartTime | OpenPrx | HighPrx | LowPrx | LastPrx |
+---------+---------------------+------------------+---------+--------+------------------+
| USD_JPY | 2010-11-24 08:00:00 | 83.161003112793 | 83.563 | 83.146 | 83.5500030517578 |
| USD_JPY | 2010-11-25 08:00:00 | 83.5 | 83.635 | 83.454 | 83.5910034179688 |
| USD_JPY | 2010-11-26 08:00:00 | 83.6064987182617 | 84.114 | 83.594 | 84.0995025634766 |
| USD_JPY | 2010-11-29 08:00:00 | 84.1039962768555 | 84.331 | 83.955 | 84.2509994506836 |
| USD_JPY | 2010-11-30 08:00:00 | 84.2724990844727 | 84.293 | 83.521 | 83.625 |
| USD_JPY | 2010-12-01 08:00:00 | 83.681999206543 | 84.349 | 83.462 | 84.1804962158203 |
| USD_JPY | 2010-12-02 08:00:00 | 84.1695022583008 | 84.358 | 83.761 | 83.8755035400391 |
| USD_JPY | 2010-12-03 08:00:00 | 83.7279968261719 | 83.751 | 82.588 | 82.9264984130859 |
| USD_JPY | 2010-12-05 08:00:00 | 82.6660003662109 | 82.689 | 82.65 | 82.6740036010742 |
| USD_JPY | 2010-12-06 08:00:00 | 82.8345031738281 | 82.919 | 82.574 | 82.6405029296875 |
| USD_JPY | 2010-12-07 08:00:00 | 82.5595016479492 | 83.5 | 82.466 | 83.4879989624023 |
| USD_JPY | 2010-12-08 08:00:00 | 83.5045013427734 | 84.172 | 83.495 | 84.0335006713867 |
| USD_JPY | 2010-12-09 08:00:00 | 83.9580001831055 | 83.975 | 83.701 | 83.7115020751953 |
| USD_JPY | 2010-12-10 08:00:00 | 83.7649993896484 | 83.978 | 83.548 | 83.931999206543 |
| USD_JPY | 2010-12-12 08:00:00 | 83.9970016479492 | 84.033 | 83.982 | 84.0139999389648 |
| USD_JPY | 2010-12-13 08:00:00 | 83.9759979248047 | 84.143 | 83.941 | 84.1344985961914 |
+---------+---------------------+------------------+---------+--------+------------------+
16 rows in set (0.01 sec)mysql>
-> (select OpenPrx from aikidofxtick_t
-> where StartTime between a.StartTime-a.StartTime%(24*60*60*1000) and a.StartTime-a.StartTime%(24*60*60*1000)+24*60*60*1000-1
-> order by StartTime limit 1) as OpenPrx,
-> Max(HighPrx) As HighPrx,
-> min(LowPrx) as LowPrx,
-> (select LastPrx from aikidofxtick_t
-> where StartTime between a.StartTime-a.StartTime%(24*60*60*1000) and a.StartTime-a.StartTime%(24*60*60*1000)+24*60*60*1000-1
-> order by StartTime desc limit 1) as LastPrx
-> from aikidofxtick_t a
-> Group by StartTime-StartTime%(24*60*60*1000);
+---------+---------------------+------------------+---------+--------+------------------+
| Symbol | StartTime | OpenPrx | HighPrx | LowPrx | LastPrx |
+---------+---------------------+------------------+---------+--------+------------------+
| USD_JPY | 2010-11-24 08:00:00 | 83.161003112793 | 83.563 | 83.146 | 83.5500030517578 |
| USD_JPY | 2010-11-25 08:00:00 | 83.5 | 83.635 | 83.454 | 83.5910034179688 |
| USD_JPY | 2010-11-26 08:00:00 | 83.6064987182617 | 84.114 | 83.594 | 84.0995025634766 |
| USD_JPY | 2010-11-29 08:00:00 | 84.1039962768555 | 84.331 | 83.955 | 84.2509994506836 |
| USD_JPY | 2010-11-30 08:00:00 | 84.2724990844727 | 84.293 | 83.521 | 83.625 |
| USD_JPY | 2010-12-01 08:00:00 | 83.681999206543 | 84.349 | 83.462 | 84.1804962158203 |
| USD_JPY | 2010-12-02 08:00:00 | 84.1695022583008 | 84.358 | 83.761 | 83.8755035400391 |
| USD_JPY | 2010-12-03 08:00:00 | 83.7279968261719 | 83.751 | 82.588 | 82.9264984130859 |
| USD_JPY | 2010-12-05 08:00:00 | 82.6660003662109 | 82.689 | 82.65 | 82.6740036010742 |
| USD_JPY | 2010-12-06 08:00:00 | 82.8345031738281 | 82.919 | 82.574 | 82.6405029296875 |
| USD_JPY | 2010-12-07 08:00:00 | 82.5595016479492 | 83.5 | 82.466 | 83.4879989624023 |
| USD_JPY | 2010-12-08 08:00:00 | 83.5045013427734 | 84.172 | 83.495 | 84.0335006713867 |
| USD_JPY | 2010-12-09 08:00:00 | 83.9580001831055 | 83.975 | 83.701 | 83.7115020751953 |
| USD_JPY | 2010-12-10 08:00:00 | 83.7649993896484 | 83.978 | 83.548 | 83.931999206543 |
| USD_JPY | 2010-12-12 08:00:00 | 83.9970016479492 | 84.033 | 83.982 | 84.0139999389648 |
| USD_JPY | 2010-12-13 08:00:00 | 83.9759979248047 | 84.143 | 83.941 | 84.1344985961914 |
+---------+---------------------+------------------+---------+--------+------------------+
16 rows in set (0.01 sec)
SELECT Symbol,FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400)) AS ST,
(SELECT OpenPrx FROM AikidoFxTick_t WHERE FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))=ST ORDER BY StartTime LIMIT 1) AS OpenPrx,
MAX(HighPrx) AS HighPrx,
MIN(LowPrx) AS LowPrx,
(SELECT LastPrx FROM AikidoFxTick_t WHERE FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))=ST ORDER BY StartTime DESC LIMIT 1) AS LastPrx
FROM AikidoFxTick_t AS a
WHERE Symbol='USD_JPY'
AND StartTime<=1.29233886E12
GROUP BY FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))
LIMIT 200;
mysql> explain SELECT Symbol,FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400)) AS ST,
-> (SELECT OpenPrx FROM AikidoFxTick_t WHERE FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))=ST ORDER BY StartTime LIMIT 1) AS OpenPrx,
-> MAX(HighPrx) AS HighPrx,
-> MIN(LowPrx) AS LowPrx,
-> (SELECT LastPrx FROM AikidoFxTick_t WHERE FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))=ST ORDER BY StartTime DESC LIMIT 1) AS LastPrx
-> FROM AikidoFxTick_t AS a
-> WHERE Symbol='USD_JPY'
-> AND StartTime<=1.29233886E12
-> GROUP BY FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))
-> LIMIT 200;
+----+--------------------+----------------+-------+---------------------------------------+--------------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------------+-------+---------------------------------------+--------------------+---------+-------+------+----------------------------------------------+
| 1 | PRIMARY | a | ref | AikidoFxTickIndex1,AikidoFxTickIndex2 | AikidoFxTickIndex1 | 18 | const | 5534 | Using where; Using temporary; Using filesort |
| 3 | DEPENDENT SUBQUERY | AikidoFxTick_t | index | NULL | AikidoFxTickIndex2 | 4 | NULL | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | AikidoFxTick_t | index | NULL | AikidoFxTickIndex2 | 4 | NULL | 1 | Using where |
+----+--------------------+----------------+-------+---------------------------------------+--------------------+---------+-------+------+----------------------------------------------+
3 rows in set (0.00 sec)mysql>
mysql> SELECT Symbol,FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400)) AS ST,
-> (SELECT OpenPrx FROM AikidoFxTick_t WHERE FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))=ST ORDER BY StartTime LIMIT 1) AS OpenPrx,
-> MAX(HighPrx) AS HighPrx,
-> MIN(LowPrx) AS LowPrx,
-> (SELECT LastPrx FROM AikidoFxTick_t WHERE FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))=ST ORDER BY StartTime DESC LIMIT 1) AS LastPrx
-> FROM AikidoFxTick_t AS a
-> WHERE Symbol='USD_JPY'
-> AND StartTime<=1.29233886E12
-> GROUP BY FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400))
-> LIMIT 200;
+---------+---------------------+-------------------+---------+--------+-------------------+
| Symbol | ST | OpenPrx | HighPrx | LowPrx | LastPrx |
+---------+---------------------+-------------------+---------+--------+-------------------+
| USD_JPY | 2010-11-24 09:00:00 | 81.2259979248047 | 83.667 | 83.132 | 1.01135003566742 |
| USD_JPY | 2010-11-25 09:00:00 | 82.5599975585938 | 83.705 | 83.394 | 0.760405004024506 |
| USD_JPY | 2010-11-26 09:00:00 | 111.667999267578 | 84.194 | 83.594 | 0.849229991436005 |
| USD_JPY | 2010-11-29 09:00:00 | 1.32363998889923 | 84.415 | 83.828 | 1.55584001541138 |
| USD_JPY | 2010-11-30 09:00:00 | 1.61711001396179 | 84.301 | 83.425 | 1.28869998455048 |
| USD_JPY | 2010-12-01 09:00:00 | 1.02602994441986 | 84.416 | 83.381 | 1.56120002269745 |
| USD_JPY | 2010-12-02 09:00:00 | 1.29086995124817 | 84.379 | 83.496 | 1.0037100315094 |
| USD_JPY | 2010-12-03 09:00:00 | 0.75586998462677 | 83.852 | 82.535 | 1.30835998058319 |
| USD_JPY | 2010-12-05 09:00:00 | 1.00349998474121 | 82.866 | 82.65 | 1.57650005817413 |
| USD_JPY | 2010-12-06 09:00:00 | 1.33887004852295 | 83.003 | 82.462 | 0.994679987430573 |
| USD_JPY | 2010-12-07 09:00:00 | 1.34513998031616 | 83.671 | 82.333 | 1.0108699798584 |
| USD_JPY | 2010-12-08 09:00:00 | 1.30964994430542 | 84.31 | 83.474 | 62.8950004577637 |
| USD_JPY | 2010-12-09 09:00:00 | 1.6070499420166 | 84.1 | 83.505 | 1.57749998569489 |
| USD_JPY | 2010-12-10 09:00:00 | 0.983160018920898 | 84.025 | 83.449 | 0.994780004024506 |
| USD_JPY | 2010-12-12 09:00:00 | 1.2989000082016 | 84.038 | 83.969 | 0.746900022029877 |
| USD_JPY | 2010-12-13 09:00:00 | 62.7239990234375 | 84.172 | 83.896 | 84.1019973754883 |
+---------+---------------------+-------------------+---------+--------+-------------------+
16 rows in set (33.00 sec)mysql>
mysql> SELECT Symbol,FROM_UNIXTIME(StartTime/1000-(StartTime/1000)%(86400)) AS ST,
-> (SELECT OpenPrx FROM AikidoFxTick_t ORDER BY StartTime LIMIT 1) AS OpenPrx,
-> MAX(HighPrx) AS HighPrx,
-> MIN(LowPrx) AS LowPrx,
-> (SELECT LastPrx FROM AikidoFxTick_t WHERE StartTime-StartTime%(24*60*60*1000)=a.StartTime-a.StartTime%(24*60*60*1000)
-> ORDER BY StartTime DESC LIMIT 1) AS LastPrx
-> FROM AikidoFxTick_t AS a
-> WHERE Symbol='USD_JPY'
-> AND StartTime<=1.29233886E12
-> GROUP BY ST
-> LIMIT 200;
+---------+---------------------+------------------+---------+--------+-------------------+
| Symbol | ST | OpenPrx | HighPrx | LowPrx | LastPrx |
+---------+---------------------+------------------+---------+--------+-------------------+
| USD_JPY | 2010-11-24 09:00:00 | 81.2259979248047 | 83.667 | 83.132 | 1.01135003566742 |
| USD_JPY | 2010-11-25 09:00:00 | 81.2259979248047 | 83.705 | 83.394 | 0.760405004024506 |
| USD_JPY | 2010-11-26 09:00:00 | 81.2259979248047 | 84.194 | 83.594 | 0.849229991436005 |
| USD_JPY | 2010-11-29 09:00:00 | 81.2259979248047 | 84.415 | 83.828 | 1.55584001541138 |
| USD_JPY | 2010-11-30 09:00:00 | 81.2259979248047 | 84.301 | 83.425 | 1.28869998455048 |
| USD_JPY | 2010-12-01 09:00:00 | 81.2259979248047 | 84.416 | 83.381 | 1.56120002269745 |
| USD_JPY | 2010-12-02 09:00:00 | 81.2259979248047 | 84.379 | 83.496 | 1.0037100315094 |
| USD_JPY | 2010-12-03 09:00:00 | 81.2259979248047 | 83.852 | 82.535 | 1.30835998058319 |
| USD_JPY | 2010-12-05 09:00:00 | 81.2259979248047 | 82.866 | 82.65 | 1.57650005817413 |
| USD_JPY | 2010-12-06 09:00:00 | 81.2259979248047 | 83.003 | 82.462 | 0.994679987430573 |
| USD_JPY | 2010-12-07 09:00:00 | 81.2259979248047 | 83.671 | 82.333 | 1.0108699798584 |
| USD_JPY | 2010-12-08 09:00:00 | 81.2259979248047 | 84.31 | 83.474 | 62.8950004577637 |
| USD_JPY | 2010-12-09 09:00:00 | 81.2259979248047 | 84.1 | 83.505 | 1.57749998569489 |
| USD_JPY | 2010-12-10 09:00:00 | 81.2259979248047 | 84.025 | 83.449 | 0.994780004024506 |
| USD_JPY | 2010-12-12 09:00:00 | 81.2259979248047 | 84.038 | 83.969 | 0.746900022029877 |
| USD_JPY | 2010-12-13 09:00:00 | 81.2259979248047 | 84.172 | 83.896 | 84.1019973754883 |
+---------+---------------------+------------------+---------+--------+-------------------+
16 rows in set (12.03 sec)mysql>
如果之查 lastprx的话用12秒版主 这样的情况是不是表结果不合理啊?