如何编辑此 MySQL 查询以包含带有计数和平均值的额外列? [英] How to edit this MySQL query to include an extra column with count and avg?
本文介绍了如何编辑此 MySQL 查询以包含带有计数和平均值的额外列?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这个查询:
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 是否更大.如果是,则显示数字.
What it does: Takes the average duration number of the whole table, and checks if the average duration URL is greater. If yes, it displays the number.
我想做同样的事情,这次添加综合浏览量.每一行都计为一次网页浏览.
I want to do the same thing and this time to add the pageviews. Each row is counted as a pageview.
我如何实现这一目标?
推荐答案
我找到了实现此目的的方法,但不确定查询的性能:
I found a way to achieve this but not sure about the performance of the query:
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
)
;
这篇关于如何编辑此 MySQL 查询以包含带有计数和平均值的额外列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文