每周和日内数据汇总 [英] Weekly and intraday data aggregate

问题描述

我使用此查询将日内数据聚合为每日数据(感谢 Gordon Linoff).

I have this query to perform intraday data aggregation into daily data (thanks to Gordon Linoff).

我每行存储 1 分钟的股票市场数据，我每天有 511 个 1 分钟的行(从 0900 到 1730).我使用 MySQL 版本 5.6.11

I store stock market data 1 minute per row, I have 511 1 minute rows for every day (from 0900 up to 1730). I use MySQL vers 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 个每日柱线，如何修改此查询?

1) how to modify this query if I would need to aggregate data only to get last 100 daily bars?

2) 如何将数据聚合为每周数据，或聚合为 5 分钟数据?

2) How to get data aggregated into weekly, or aggregate into 5 minute data?

此版本适用于每周聚合(并且每月聚合也使用月而不是周)

This version works on weekly aggregation (and monthly aggregation too using month instead of week)

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;

我还有一些事情要解决:

I still have something to fix:

1) 关于每日聚合的第一次查询返回 100 个日历日，而不是 100 个每日聚合行.我需要从最近的向后开始排序 100 条记录.每周聚合也是如此，我需要 100 条每周记录.

1) first query about daily aggregation returns 100 calendar days, not 100 daily aggregated rows. I need to get 100 records sorted starting from most recent backward. Same for weekly aggregation where I need 100 weekly records.

2) 5 分钟或 n 分钟聚合怎么样?例如，从 0900 聚合到 0904，然后从 0905 到 0909 等聚合 5 分钟

2) What about 5 minutes or n minutes aggregation? In example, aggregate from 0900 up to 0904, then from 0905 up to 0909 etc.for 5 min aggregation

推荐答案

最后 100 个每日柱线是什么意思?如果您指的是过去 100 天:

What do you mean by the last 100 daily bars? If you mean the last 100 days:

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 和相关子查询以具有相同的表达式.例如:

If you want another grouping, then you need to change the group by and correlated subquery to have the same expression. For instance:

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);

这篇关于每周和日内数据汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！