MySQL按时间维度查询价格平均值：实时折线图数据准备

在MySQL中，处理按不同时间维度查询价格平均值的问题涉及到SQL查询技巧和日期函数的巧妙运用。针对提供的需求，我们有两个主要的任务：一是计算近7天的价格平均值，二是计算近一个月内每隔5天的价格平均值。这些查询将生成用于折线图的数据集，其中x轴表示日期，y轴则表示相应时间段内的价格平均值。 首先，对于近7天的价格平均值，我们使用了递归自连接技术。创建了一个内部查询（`aaa`）来生成一个日期序列，从当前日期往前推7天，每天递减一天。然后，外部查询通过LEFT JOIN与这个日期序列连接，找出每一天的价格。如果某天没有数据，使用IFNULL函数将价格设为0。这样，即使在没有记录的日期，也会显示0作为平均值。 ```sql SELECT IFNULL(bbb.price, 0) AS price, aaa.date FROM ( SELECT @cdate := DATE_ADD(@cdate, INTERVAL -1 DAY) AS date FROM ( SELECT @cdate := DATE_ADD(CURDATE(), INTERVAL 1 DAY) FROM price_log LIMIT 7 ) a ) aaa LEFT JOIN ( SELECT AVG(price) AS price, DATE(gmt) AS `date` FROM price_log WHERE id = '2022083010451739092484626960-CSY' AND DATE(gmt) BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() GROUP BY DATE(gmt) ) bbb ON bbb.date = aaa.date ``` 对于近一个月每隔5天的价格平均值，我们同样采用了递归生成日期序列的方法，但这次是每隔5天。内部查询（`aaa`）生成的是每个月的第一天和倒数第五天的日期，外部查询则计算在这两个日期之间的价格平均值，并同样处理缺失数据。这里的关键是使用DATE_SUB和INTERVAL来计算指定间隔的日期，以及CEILING函数来确保每个组的天数是5的倍数。 ```sql SELECT IFNULL(bbb.avg_price, 0) AS avg_price, aaa.gmt FROM ( SELECT @cdate := DATE_ADD(@cdate, INTERVAL -5 DAY) AS gmt FROM ( SELECT @cdate := DATE_ADD(CURDATE(), INTERVAL 5 DAY) FROM price_log LIMIT 6 ) a ) aaa LEFT JOIN ( SELECT DATE_SUB(CURDATE(), INTERVAL interval_5_day * 5 DAY) AS gmt, AVG(price) AS avg_price, COUNT(price) AS c, SUM(price) AS s FROM ( SELECT CEILING(DATEDIFF(CURDATE(), gmt) / 5) AS interval_5_day, AVG(price) AS avg_price, COUNT(price) AS c, SUM(price) AS s FROM price_log WHERE id = '2022083010451739092484626960-CSY' AND gmt BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE() GROUP BY CEILING(DATEDIFF(CURDATE(), gmt) / 5) ) subquery GROUP BY gmt ) bbb ON bbb.gmt = aaa.gmt ``` 这两个查询的结果集合将为折线图提供所需的日期和对应的价格平均值，可以方便地展示价格随时间的变化趋势。通过组合这两个查询，用户可以根据需求快速生成所需的时间序列分析报告。