每周和日内数据汇总

Question

我有这个查询来执行每日数据聚合(感谢Gordon )。

我每一行存储1分钟的股票市场数据，每天有511 1分钟行(从0900到1730)。我使用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个每日条，如何修改此查询？

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 etc.for 5 min聚合。

Answer 1

你说过去的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);