汇总日内数据

Question

与我之前的问题一样，我使用这个查询来执行从日内数据到每日数据的数据聚合

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;

但是由于它使用的是日历日，所以每天返回的聚集行数少于100行。

1)如何修改该查询，获取100条最新记录？

2)如何聚合到5分钟的数据，而不是每天(即第一行聚合从0900到0904，然后从0905到0909，等等)？

我使用的是mySQL 5.6.11，数据存储如下

​

​

我每行存储1分钟的股票市场数据，每天有511个1分钟的行(从0900到1730)。

Answer 1

粗糙且未经测试的示例。

SELECT symbol, Sub1.aDay, 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 
(
    SELECT DATE_SUB(CURDATE(), INTERVAL (Tens.a * 10 + Units.a) DAY) AS aDay
    FROM (SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Units,
    (SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Tens
) Sub1
LEFT JOIN A2A a1
ON Sub1.aDay = a1.date
GROUP BY symbol, Sub1.aDay
ORDER BY symbol, Sub1.aDay;

这是一个子查询，生成从0到99的数字，并从当前日期减去该数字作为天数。给出了100天的范围。然后根据日期将您的主表连接到该列表中。