- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有什么
<强>1。我的域“User_Order”
CREATE TABLE `user_order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`created_date` datetime DEFAULT NULL,
`fees` decimal(19,2) NOT NULL,
`instrument` varchar(255) NOT NULL,
`is_limit_order` bit(1) NOT NULL,
`market_id` bigint(20) NOT NULL,
`order_id` varchar(255) NOT NULL,
`order_status` varchar(255) NOT NULL,
`order_type` varchar(255) NOT NULL,
`price` decimal(20,8) NOT NULL,
`quantity` decimal(20,8) NOT NULL,
`remaining_quantity` decimal(20,8) NOT NULL,
`updated_date` datetime DEFAULT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UK_t5ah1x4wm9314qclf90dy0lyu` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
<强>2。我们在我的表中有以下记录。
| id | created_date | fees | instrument | is_limit_order | market_id | order_id | order_status | order_type | price | quantity | remaining_quantity | updated_date | user_id |
+----+---------------------+--------+------------+----------------+-----------+------------------+--------------+------------+-------------+------------+--------------------+---------------------+---------+
| 1 | 2017-09-15 05:08:57 | 0.0100 | INBTCINR | | 1 | 12:05:52-0000001 | EXECUTED | BUY | 10.00000000 | 2.00000000 | 0.00000000 | 2017-09-15 05:08:57 | 3 |
| 2 | 2017-09-15 05:09:34 | 0.0100 | INBTCINR | | 1 | 12:25:43-0000001 | EXECUTED | SELL | 10.00000000 | 2.00000000 | 0.00000000 | 2017-09-15 05:09:34 | 4 |
| 3 | 2017-09-15 05:11:18 | 0.0100 | INBTCINR | | 1 | 12:05:52-0000002 | CANCELLED | BUY | 2.00000000 | 1.00000000 | 1.00000000 | 2017-09-15 05:11:18 | 3 |
| 4 | 2017-09-15 05:12:43 | 0.0100 | INBTCINR | | 1 | 12:05:52-0000003 | EXECUTED | BUY | 4.00000000 | 2.00000000 | 0.00000000 | 2017-09-15 05:12:43 | 3 |
| 5 | 2017-09-15 05:23:10 | 0.0100 | INBTCINR | | 1 | 12:25:43-0000002 | EXECUTED | SELL | 4.00000000 | 2.00000000 | 0.00000000 | 2017-09-15 05:23:10 | 4 |
| 6 | 2017-09-15 08:34:17 | 0.0100 | INBTCINR | | 1 | 12:25:43-0000003 | OPEN | BUY | 2.00000000 | 1.00000000 | 1.00000000 | 2017-09-15 08:34:17 | 4 |
| 7 | 2017-09-15 08:36:32 | 0.0100 | INBTCINR | | 1 | 12:05:52-0000004 | CANCELLED | SELL | 1.00000000 | 2.00000000 | 2.00000000 | 2017-09-15 08:36:32 | 3 |
| 8 | 2017-09-15 09:24:22 | 0.0100 | INBTCINR | | 1 | 12:25:43-0000004 | OPEN | BUY | 2.00000000 | 1.00000000 | 1.00000000 | 2017-09-15 09:24:22 | 4 |
| 9 | 2017-09-15 09:30:19 | 0.0100 | INBTCINR | | 1 | 12:25:43-0000005 | OPEN | BUY | 1.00000000 | 2.00000000 | 2.00000000 | 2017-09-15 09:30:19 | 4 |
| 10 | 2017-09-15 09:32:10 | 0.0100 | INBTCINR | | 1 | 12:05:52-0000005 | CANCELLED | SELL | 1.00000000 | 2.00000000 | 2.00000000 | 2017-09-15 09:32:10 | 3 |
| 11 | 2017-09-15 10:02:57 | 0.0100 | INBTCINR | | 1 | 12:05:52-0000006 | OPEN | SELL | 2.00000000 | 4.00000000 | 4.00000000 | 2017-09-15 10:02:57 | 3 |
| 12 | 2017-09-15 10:16:19 | 0.0100 | INBTCINR | | 1 | 12:25:43-0000006 | OPEN | BUY | 4.00000000 | 2.00000000 | 2.00000000 | 2017-09-15 10:16:19 | 4 |
<强>3。我正在为每 5(秒 300)分钟间隔记录编写以下查询。
开始日期 = '2017-09-11 00:00:00'结束日期 = '2017-09-15 23:59:59'
select FROM_UNIXTIME(floor(min(UNIX_TIMESTAMP(created_date))/300)*300)
as timestampDate,
sum(quantity) as volume,sum(price*quantity)/sum(quantity) as wavg_price,
substring_index(min(concat('created_date','_',price)),
'_',-1) as open,
max(price) as high,
min(price) as low,
substring_index(max(concat ('created_date','_',price)),
'_',-1) as 'close'
from user_order
where created_date between '2017-09-11 00:00:00' AND '2017-09-15 23:59:59'
group by floor(unix_timestamp(created_date)/300)
order by created_date;
上述查询的结果:-
timestampDate | volume | wavg_price | open | high | low | close |
+---------------------+------------+-------------------------+-------------+-------------+-------------+-------------+
| 2017-09-15 05:05:00 | 4.00000000 | 10.00000000000000000000 | 10.00000000 | 10.00000000 | 10.00000000 | 10.00000000 |
| 2017-09-15 05:10:00 | 3.00000000 | 3.33333333333333333333 | 2.00000000 | 4.00000000 | 2.00000000 | 4.00000000 |
| 2017-09-15 05:20:00 | 2.00000000 | 4.00000000000000000000 | 4.00000000 | 4.00000000 | 4.00000000 | 4.00000000 |
| 2017-09-15 08:30:00 | 1.00000000 | 2.00000000000000000000 | 2.00000000 | 2.00000000 | 2.00000000 | 2.00000000 |
| 2017-09-15 08:35:00 | 2.00000000 | 1.00000000000000000000 | 1.00000000 | 1.00000000 | 1.00000000 | 1.00000000 |
| 2017-09-15 09:20:00 | 1.00000000 | 2.00000000000000000000 | 2.00000000 | 2.00000000 | 2.00000000 | 2.00000000 |
| 2017-09-15 09:30:00 | 4.00000000 | 1.00000000000000000000 | 1.00000000 | 1.00000000 | 1.00000000 | 1.00000000 |
| 2017-09-15 10:00:00 | 4.00000000 | 2.00000000000000000000 | 2.00000000 | 2.00000000 | 2.00000000 | 2.00000000 |
| 2017-09-15 10:15:00 | 2.00000000 | 4.00000000000000000000 | 4.00000000 | 4.00000000 | 4.00000000 | 4.00000000 |
+---------------------+------------+-------------------------+-------------+-------------+-------------+-------------+
<强>4。上面的结果只给出了表中的记录,即使我需要每个时间间隔的所有记录都有默认值或以前的记录值(开盘价、收盘价、高价、低价及其时间戳)
预期输出:-
日期范围='2017-09-11 00:00:00'和'2017-09-15 23:59:59'
timestampDate | volume | wavg_price | open | high | low | close |
+---------------------+------------+-------------------------+-------------+-------------+-------------+-------------+
| 2017-09-11 00:05:00 | 4.00000000 | 10.00000000000000000000 | 10.00000000 | 10.00000000 | 10.00000000 | 10.00000000 |
| 2017-09-11 00:10:00 | 3.00000000 | 3.33333333333333333333 | 2.00000000 | 4.00000000 | 2.00000000 | 4.00000000 |
| 2017-09-11 00:15:00 | 2.00000000 | 4.00000000000000000000 | 4.00000000 | 4.00000000 | 4.00000000 | 4.00000000 |
................................................
.................................................
.................................................
| 2017-09-15 23:05:00 | 1.00000000 | 2.00000000000000000000 | 2.00000000 | 2.00000000 | 2.00000000 | 2.00000000 |
| 2017-09-15 23:10:00 | 1.00000000 | 2.00000000000000000000 | 2.00000000 | 2.00000000 | 2.00000000 | 2.00000000 |
..........................................................
..........................................................
..........................................................
| 2017-09-15 23:50:00 | 1.00000000 | 2.00000000000000000000 | 2.00000000 | 2.00000000 | 2.00000000 | 2.00000000 |
| 2017-09-15 23:55:00 | 1.00000000 | 2.00000000000000000000 | 2.00000000 | 2.00000000 | 2.00000000 | 2.00000000 |
我想要什么
谢谢
最佳答案
要获取丢失的记录,您需要
LEFT JOIN
从额外表到您已有的查询NULL
做一些处理。如果您使用的是 MariaDB,则有一个方便的“序列”生成器。对于第 1 步。在 MySQL 中,我建议您预先构建一个表,例如,从 0 到一百万的数字,然后使用 + INTERVAL ... SECOND
从中创建 5 分钟的日期时间
关于mysql - ohlc(开盘价、最高价、最低价和收盘价)在不同时间内部(1,5,15,60,120)分钟,1天甚至没有记录的两个日期范围之间记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46251104/
我有一个表,如下所示: dpTicker dpDate dpOpen dpHigh dpLow dpClose dpVolume dpAd
我正在处理烛台图表数据。我有一个数据库表(MySql) created value 2017-09-25 17:55:04 3322.09 2017-09-25
类似于create an OHLC data from Date, time, price using C# ,如何将基本交易数据转换为 OHLC(或开盘价、最高价、最低价、收盘价)的理论并将其应用于
我有以下格式的连续数据流,我想根据定义的时间片(如 5 分钟、10 分钟、30 分钟等)将其转换为开盘价、最高价、最低价和收盘价。同时 buy_quantity sell_quantity 也应该根据
我正在尝试使用 Convert data to OHLC (Open, High, Low, Close) in JavaScript? 中的脚本将一些图表数据转换为 OHLC。唯一的问题是我的数据与
我有什么 1。我的域“User_Order” CREATE TABLE `user_order` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `cre
我是一名优秀的程序员,十分优秀!