2020-05-13

1.mysql 以一小时为间隔 统计24小时内 表的每一字段一小时的平均值

 SELECT `date`,MAX( times ) AS times FROM
            (SELECT DATE_FORMAT(@cdate := DATE_ADD( @cdate, INTERVAL - 1 hour ), '%Y-%m-%d %H:00:00') `date`,0 AS times FROM
               ( SELECT DATE_FORMAT(@cdate := DATE_ADD( NOW( ), INTERVAL 1 hour ), '%Y-%m-%d %H:00:00') FROM pay_order ) t1
               WHERE @cdate > (NOW() - interval 23 hour)
            UNION ALL
            SELECT DATE_FORMAT(create_time, '%Y-%m-%d %H:00:00') AS `date`,round(AVG(pull_up_time),1) as times from pay_order
               where create_time > (NOW() - interval 23 hour) and pull_up_time >= 0
               group by `date`
               ORDER BY `date` DESC
            ) _tmpAllTable GROUP BY  `date` ORDER BY date DESC

加上 UNION ALL 前面部分 是因为可能出现一小时内没有数据 造成 不连续

2.以某一字段的不同值 分段统计(以10为间隔)

       set @ctime = 0 ;
        SELECT times, max(orderc) as orderCount  , max(success) as successCount , 
       round(max(success)/if(max(orderc)=0,1,max(orderc))*100,2) as rate FROM
        (SELECT times,0 success ,0 as orderc FROM (SELECT @ctime := @ctime+10 as times FROM 
       pay_order) t WHERE  times < 300
        UNION all
        SELECT  floor(pull_up_time /10 ) * 10   as times , COALESCE(sum(order_status = 2),0) as success  , 
       COALESCE(sum(order_status = 2 or order_status = 4),0) as orderc
        from pay_order
        WHERE pull_up_time >= 0
        GROUP BY times ) tem GROUP BY times  ORDER BY times