mysql avg 分组_MySQl - AVG（AVG IF）＆amp;通过...分组

我使用MySQL。我有以下格式的数据 -

DATE , PAGE , PAGE_LOAD_DURATION , VISIT_TYPE

01-01-15 , A , 6 , AM

01-01-15 , B , 4 , PM

01-01-15 , C , 5 , AM

01-01-15 , D , 4 , AM

02-01-15 , B , 13 , PM

03-01-15 , C , 15 , PM

04-01-15 , A , 17 , PM

05-01-15 , A , 34 , PM

06-01-15 , B , 56 , AM

07-01-15 , C , 12 , AM

08-01-15 , D , 89 , AM

09-01-15 , B , 34 , AM

10-01-15 , R , 12 , PM

11-01-15 , T , 56 , AM

12-01-15 , E , 78 , PM

13-01-15 , B , 90 , AM

14-01-15 , A , 34 , PM

15-01-15 , A , 56 , AM

16-01-15 , B , 23 , AM

17-01-15 , A , 15 , PM

18-01-15 , R , 17 , AM

19-01-15 , Y , 37 , PM

20-01-15 , C , 23 , AM

我尝试为给定日期范围生成每次访问类型的每页平均页面加载持续时间

我希望以下列格式生成输出 -

PAGE, VISIT_TYPE, AVERAGE_PAGE_LOAD_DUR

A, AM, average_page_load_dur

A, PM, average_page_load_dur

B, AM, average_page_load_dur

B, PM, average_page_load_dur

C, AM, average_page_load_dur

C, PM, average_page_load_dur

D, AM, average_page_load_dur

D, PM, average_page_load_dur

so on

要计算SUM而不是平均值，我使用以下查询 -

select

PAGE,

SUM(IF(VISIT_TYPE = 'AM',AVERAGE_PAGE_LOAD_DUR,0)) AS SUM_AM,

SUM(IF(VISIT_TYPE = 'PM',AVERAGE_PAGE_LOAD_DUR,0)) AS SUM_PM

from

PAGE_VISITS

where

DATE >= '01-01-15' and DATE <= '09-01-15'

group by

PAGE

为了计算平均值，我用AVG替换了SUM，但是，看起来这不是正确的方法，因为扫描的每行都没有计算平均值(这就是我的方法)认为它有效。如果不这样，请纠正我)

select

PAGE,

AVG(IF(VISIT_TYPE = 'AM',AVERAGE_PAGE_LOAD_DUR,0)) AS SUM_AM,

AVG(IF(VISIT_TYPE = 'PM',AVERAGE_PAGE_LOAD_DUR,0)) AS SUM_PM

from

PAGE_VISITS

where

DATE >= '01-01-15' and DATE <= '09-01-15'

group by

PAGE

我怎样才能让平均值得工作？