每5分钟一个点，用SQL查询截止每个时间点数据的累积量
2020-06-10 本文已影响0人
和平菌
SELECT time, @total:=@total + value AS total, cnt FROM(
SELECT
CASE WHEN MINUTE(business_time) < 5 THEN CONCAT(SUBSTRING(business_time, 1,14), '05:00')
WHEN MINUTE(business_time) < 10 THEN CONCAT(SUBSTRING(business_time, 1,14), '10:00')
WHEN MINUTE(business_time) < 15 THEN CONCAT(SUBSTRING(business_time, 1,14), '15:00')
WHEN MINUTE(business_time) < 20 THEN CONCAT(SUBSTRING(business_time, 1,14), '20:00')
WHEN MINUTE(business_time) < 25 THEN CONCAT(SUBSTRING(business_time, 1,14), '25:00')
WHEN MINUTE(business_time) < 30 THEN CONCAT(SUBSTRING(business_time, 1,14), '30:00')
WHEN MINUTE(business_time) < 35 THEN CONCAT(SUBSTRING(business_time, 1,14), '35:00')
WHEN MINUTE(business_time) < 40 THEN CONCAT(SUBSTRING(business_time, 1,14), '40:00')
WHEN MINUTE(business_time) < 45 THEN CONCAT(SUBSTRING(business_time, 1,14), '45:00')
WHEN MINUTE(business_time) < 50 THEN CONCAT(SUBSTRING(business_time, 1,14), '50:00')
WHEN MINUTE(business_time) < 55 THEN CONCAT(SUBSTRING(business_time, 1,14), '55:00')
ELSE CONCAT(SUBSTRING(business_time, 1, 11),
CASE WHEN HOUR(business_time) >= 9
THEN HOUR(business_time)+1
ELSE CONCAT('0',HOUR(business_time)+1)
END,':00:00') END AS TIME, SUM(VALUE) AS VALUE, COUNT(1) AS cnt
FROM index_data
WHERE item_name='ABC'
AND business_time>='2020-06-10 00:00:00' AND business_time<='2020-06-10 14:45:43'
GROUP BY TIME
) AS temp,(SELECT @total := 0) AS r
ORDER BY time;
同理cnt也可以做累加，然后求平均值
