php - 如何编辑此 MySQL 查询以包含带有计数和平均值的额外列？

Question

此查询:

select p.visitor_id , p.url, avg(p.duration), count(*) as sum_pages
from pages p
where visitor_id = 1
group by p.visitor_id , p.url
having avg(p.duration) > (select avg(p2.duration)
                          from pages p2
                          where p2.visitor_id = p.visitor_id
                         );

生成这个假设的表格:

visitor_id ---- url ---- duration --- sum_pages
1 ------------ home ------- 5 ------- 20
1 ------------ about ------ 8 ------- 12
1 ------------ contact ---- 2 ------- 13
1 ------------ services --- 2 ------- 18

它的作用:获取整个表的平均持续时间数，并检查平均持续时间 URL 是否更大。如果是，则显示号码。

我想做同样的事情，这次是添加综合浏览量。每行都算作一次综合浏览量。

如何实现这一目标？

Answer 1

我找到了一种方法来实现此目的，但不确定查询的性能:

select
visitor_id, 
url,
sum(duration), 
avg(duration),
count(*) as sum_pages
from pages p 
where visitor_id='1' 
group by visitor_id,url
having sum(duration) > (select avg(duration)
                         from pages
                         where visitor_id=p.visitor_id
                       )
 and sum_pages > (select count(*)/(select count(distinct url) from pages where visitor_id=p1.visitor_id)
                   from pages p1
                  where p1.visitor_id=p.visitor_id
                  )
 ;    

DEMO