MySQL – How to Calculate Average Record Hourly

MySQL

I have one table, here is the schema

CREATE TABLE `table_200901` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `carType` bigint(20) DEFAULT NULL,
  `dateTime` datetime DEFAULT NULL,
  `distance` bigint(20) DEFAULT NULL,
  `inNum` bigint(20) DEFAULT NULL,
  `outNum` bigint(20) DEFAULT NULL,
  `totalNum` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1680002 DEFAULT CHARSET=latin1

table

here is how the table looks and record

i have to create like this

e

for example user input the inNum 4

so in 24 hour

because there are 2 records which has inNum 4 and it has a same hour (01) so i have to average totalnum to be( 3+3 )/ 2 = 3 so in column hour1 the data is 3

because there are 3 records which has inNum 4 and it has a same hour (03) so i have to average totalnum to be( 1+2+123 ) / 3 = 42 so in column hour3 the data is 42

i have successed to create a sum query not avg

here's how the query

select 

a.jdateTime
, "土" as 曜日
,a.inNum as 入口番号 , a.outNum as 出口番号, rin.tollgateName  as 入口名 , rout.tollgateName as 出口名 , a.carType as 課金車種
 , a.distance as 営業補正
, sum(a.hour0) as d1
, sum(a.hour1) 
, sum(a.hour2) 
, sum(a.hour3) 
 , sum(a.hour4)  , sum(a.hour5) ,
sum(a.hour6) , sum(a.hour7) , sum(a.hour8) , sum(a.hour9)  , sum(a.hour10)  , sum(a.hour11) ,
sum(a.hour12) , sum(a.hour13) , sum(a.hour14) , sum(a.hour15)  , sum(a.hour16)  , sum(a.hour17) ,
sum(a.hour18) , sum(a.hour19) , sum(a.hour20) , sum(a.hour21)  , sum(a.hour22)  , sum(a.hour23) 
from (select 
    date(dateTime) as realDate
    ,
     concat(year(dateTime) , "年",  month(dateTime),"月" ,day(dateTime) , "日" ) as jdateTime
    ,
    inNum,outNum,carType,distance
    ,
    case when hour(dateTime) = 0
    then totalNum 
            else 
        0
    end  as hour0,
    case when hour(dateTime) = 1 
    then totalNum 
    else 
        0
    end as hour1,

    case when hour(dateTime) = 2
    then totalNum 
            else 
        0
    end  as hour2
    ,

    case when hour(dateTime) = 3
    then totalNum 
            else 
        0
    end  as hour3
    ,case when hour(dateTime) = 4
    then totalNum 
            else 
        0
    end  as hour4
    ,case when hour(dateTime) = 5
    then totalNum 
            else 
        0
    end  as hour5
    ,case when hour(dateTime) = 6
    then totalNum 
            else 
        0
    end  as hour6
    ,case when hour(dateTime) = 7
    then totalNum 
            else 
        0
    end  as hour7
    ,case when hour(dateTime) = 8
    then totalNum 
            else 
        0
    end  as hour8
    ,case when hour(dateTime) = 9
    then totalNum 
            else 
        0
    end  as hour9
    ,case when hour(dateTime) = 10
    then totalNum 
            else 
        0
    end  as hour10
    ,case when hour(dateTime) = 11
    then totalNum 
            else 
        0
    end  as hour11
    ,case when hour(dateTime) = 12
    then totalNum 
            else 
        0
    end  as hour12
    ,case when hour(dateTime) = 13
    then totalNum 
            else 
        0
    end  as hour13
    ,case when hour(dateTime) = 14
    then totalNum 
            else 
        0
    end  as hour14
    ,case when hour(dateTime) = 15
    then totalNum 
            else 
        0
    end  as hour15
    ,case when hour(dateTime) = 16
    then totalNum 
            else 
        0
    end  as hour16
    ,case when hour(dateTime) = 17
    then totalNum 
            else 
        0
    end  as hour17
    ,case when hour(dateTime) = 18
    then totalNum 
            else 
        0
    end  as hour18
    ,case when hour(dateTime) = 19
    then totalNum 
            else 
        0
    end  as hour19
    ,case when hour(dateTime) = 20
    then totalNum 
            else 
        0
    end  as hour20
    ,case when hour(dateTime) = 21
    then totalNum 
            else 
        0
    end  as hour21
    ,case when hour(dateTime) = 22
    then totalNum 
            else 
        0
    end  as hour22
    ,case when hour(dateTime) = 23
    then totalNum 
            else 
        0
    end  as hour23


from table_200901 
    limit 500 ) as a 
     left outer join ramp_block as rin on rin.tollgateCode = a.inNum
     left outer join ramp_block as rout on rout.tollgateCode = a.outNum
     group by a.realDate order by a.realDate;

but i am so confused with avg, how to create average function with counted hourly data?

Best Answer

Instead of using NULLIF, you could have your CASE expressions return NULLs instead of zeros:

CASE
   WHEN HOUR(dateTime) = 0 THEN totalNum 
   ELSE NULL
END  AS hour0

or just omit the ELSE clause altogether (which means the same as specifying ELSE NULL):

CASE
   WHEN HOUR(dateTime) = 0 THEN totalNum
END  AS hour0

That way AVG(hour0) would work as expected.

Note that if a particular hour has no data at all, AVG would return NULL, and so would the corresponding SUM. If, however, you want your SUMs to default to zero, then you will need to combine the method suggested in this answer with adding IFNULL or COALESCE to your SUM expressions, like this:

COALESCE(SUM(a.hour0), 0) AS d1
Related Question