作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有这个查询来将日内数据聚合为每日数据(感谢 Gordon Linoff)。
我每行存储 1 分钟的股市数据,每天有 511 个 1 分钟行(从 0900 到 1730)。我使用 MySQL 5.6.11 版本
数据如下图
SELECT symbol, date, MAX(high) AS high, MIN(low) as low, SUM(volume) as volume,
(select open from a2a a2 where a1.symbol = a2.symbol and a1.date = a2.date order by time limit 1) as open,
(select close from a2a a2 where a1.symbol = a2.symbol and a1.date = a2.date order by time desc limit 1) as close
FROM a2a a1
GROUP BY symbol, date
ORDER BY symbol, date;
我的问题:
1) 如果我只需要聚合数据以获得最后 100 个每日柱线,如何修改此查询?
2)如何将数据聚合成周数据,或者聚合成5分钟数据?
编辑:此版本适用于每周聚合(每月聚合也使用月而不是周)
SELECT symbol, date, week(date), MAX(high) AS high, MIN(low) as low, SUM(volume) as volume,
(select open from a2a a2 where a1.symbol = a2.symbol and week(a1.date) = week(a2.date) order by time limit 1) as open,
(select close from a2a a2 where a1.symbol = a2.symbol and week(a1.date) = week(a2.date) order by time desc limit 1) as close
FROM a2a a1
WHERE date >= date_sub(CURDATE(), interval 100 week)
GROUP BY symbol, week(date)
ORDER BY symbol, date;
我还有一些问题需要解决:
1) 关于每日聚合的第一个查询返回 100 个日历日,而不是 100 个每日聚合行。我需要从最近的向后开始排序 100 条记录。对于每周聚合也是如此,我需要 100 条每周记录。
2) 5 分钟或 n 分钟聚合怎么样?例如,从 0900 聚合到 0904,然后从 0905 到 0909 等聚合 5 分钟
最佳答案
最近 100 条每日柱线是什么意思?如果您指的是过去 100 天:
SELECT symbol, date, MAX(high) AS high, MIN(low) as low, SUM(volume) as volume,
(select open from a2a a2 where a1.symbol = a2.symbol and a1.date = a2.date order by time limit 1) as open,
(select close from a2a a2 where a1.symbol = a2.symbol and a1.date = a2.date order by time desc limit 1) as close
FROM a2a a1
WHERE date >= date_sub(CURDATE(), interval 100 day)
GROUP BY symbol, date
ORDER BY symbol, date;
如果您想要其他分组,则需要更改group by
和相关子查询以具有相同的表达式。例如:
SELECT symbol, week(date), MAX(high) AS high, MIN(low) as low, SUM(volume) as volume,
(select open from a2a a2 where a1.symbol = a2.symbol and a1.week(date) = a2.week(date) order by time limit 1) as open,
(select close from a2a a2 where a1.symbol = a2.symbol and a1.week(date) = a2.week(date) order by time desc limit 1) as close
FROM a2a a1
WHERE date >= date_sub(CURDATE(), interval 100 day)
GROUP BY symbol, week(date)
ORDER BY symbol, week(date);
关于mysql - 每周和日内数据汇总,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/21046648/
我正在尝试测试日内交易向临时 VWAP 的回归。我已经编译了一个(希望如此)可重现的例子来展示我所做的。目前我使用 rollapply,但它根据观察在滚动窗口上应用 VWAP 计算。理想情况下,我想使
我是一名优秀的程序员,十分优秀!