mysql怎么按时间分组 mysql如何实现分组排序( 二 )

以下为代码:
-- by sleest 2019/06/06 临近分组
-- 制造临时表数据用于验证
DROP TEMPORARY TABLE IF EXISTS TMP_DEAL_TIME;
CREATE TEMPORARY TABLE TMP_DEAL_TIME(DEAL_TIME DATETIME) AS
SELECT DATE_ADD(CURDATE(), INTERVAL 600 * RAND() MINUTE) AS DEAL_TIME
FROM (SELECT 1 UNION ALL SELECT 2 UNION SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) A,
(SELECT 1 UNION ALL SELECT 2 UNION SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) B;
-- 查看临时表数据
SELECT DEAL_TIME FROM TMP_DEAL_TIME ORDER BY DEAL_TIME;
-- 理解1: 只要相邻时间为指定分钟数以内则聚合
SELECT DEAL_TIME,
CASE WHEN @lastDealTime IS NULL THEN @minLevel:=1
WHEN DEAL_TIMEDATE_ADD(@lastDealTime, INTERVAL 30 MINUTE)
THEN @minLevel:=@minLevel+1 END AS COMPUT_CONDITION,
@lastDealTime:=DEAL_TIME AS LAST_DEAL_TIME,
@minLevel AS GROUP_LEVEL
FROM (SELECT DEAL_TIME FROM TMP_DEAL_TIME ORDER BY DEAL_TIME) E,
(SELECT @lastDealTime:=NULL) F,
(SELECT @minLevel:=0) G;
-- 理解2: 只和开始聚合的第一条时间比较, 只有当该条数据处在聚合第一条指定分钟数以内才归组
SELECT DEAL_TIME,
CASE WHEN @lastDealTime IS NULL THEN @minLevel:=1
WHEN DEAL_TIMEDATE_ADD(@lastDealTime, INTERVAL 30 MINUTE)
THEN @minLevel:=@minLevel+1 END AS COMPUT_CONDITION,
CASE WHEN @lastDealTime IS NULL THEN @lastDealTime:=DEAL_TIME
WHEN DEAL_TIMEDATE_ADD(@lastDealTime, INTERVAL 30 MINUTE)
THEN @lastDealTime:=DEAL_TIME END AS LAST_DEAL_TIME,
@minLevel AS GROUP_LEVEL
FROM (SELECT DEAL_TIME FROM TMP_DEAL_TIME ORDER BY DEAL_TIME) E,
(SELECT @lastDealTime:=NULL) F,
(SELECT @minLevel:=0) G;
对于理解1的执行结果如下:
对于理解2的执行结果如下:
后续可以根据结果中的GROUP_LEVEL进行分组显示
MySql如何按时间段来分组SELECT DATE_FORMAT(time,'%Y-%m-%d') as day, sum(case when amount0 then amount when amount=0 then 0 end) as amount1
from table where time='2014-11-01' group by day;
我没有测试。time表示日期， amount表示数量。查询11月后每天成交数量
【mysql怎么按时间分组 mysql如何实现分组排序】mysql怎么按时间分组的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql如何实现分组排序、mysql怎么按时间分组的信息别忘了在本站进行查找喔。