结合SQL(MySQL)中的count和avg函数 [英] Combining count and avg functions in SQL (MySQL)
问题描述
所以我正在尝试自己的网站分析功能(我再也无法忍受Google Analytics ...),并且有一个记录用户活动的数据库表。
ID - 自动增量
SessionID(Int)
dDateTime(页面点击的日期/时间)
加上一些其他无关紧要的事情。
现在,我可以使用这个获得基本的会话列表:
选择计数(ID),timediff(max(ddateTime),min(dDateTime))
来自 tableName
其中 date (dDateTIme)> = ' 某些开始日期'
和 date (dDateTIme)< = ' 某个结束日期'
group by SessionID order by SessionID
好的...但我想要的是平均值 - 即一行告诉我每次会话的平均页面点击次数,以及用户在网站上停留的平均时间长度。类似于:
选择 avg(count(ID)), avg(timediff(max(ddateTime),min(dDateTime)))
来自 tableName
其中 date (dDateTIme)> = ' 一些开始日期'
和 date (dDateTIme)< = ' 某个结束日期'
group by SessionID
但是(显然)是对GROUP的无效使用BY ....
我知道可以做到....?
I会这样做:
SELECT SUM(CountOfID) AS 总计,AVG(TimeDifference)平均值
FROM (
SELECT count(ID) AS CountOfID,timediff(SECOND,max(ddateTime),min(dDateTime)) AS TimeDifference
FROM tableName
WHERE < span class =code-keyword> date (dDateTIme)> = ' 某个开始日期' 和 date (dDateTIme)< = ' 某个结束日期'
GROUP BY SessionID 订单 按 SessionID) AS T
如需了解更多信息,请参阅:
MySQL多行的AVG TimeDiff [ ^ ]
MySQL中的平均时差 [ ^ ]
SO I'm experimenting with my own site analytics feature (I can't stand Google Analytics any longer...), and have a databse table that recordsr user activity.
ID - autoincrement
SessionID (Int)
dDateTime (Date/time of page hit)
plus a few other things that don't matter here.
Now, I can get a basic listing of sessions using this:
select count(ID), timediff(max(ddateTime),min(dDateTime))
from tableName
where date(dDateTIme) >= 'some start date'
and date(dDateTIme) <= 'some end date'
group by SessionID order by SessionID
OK... but what I want is the averages - i.e. a single row that tells me the average number of page hits per session, and the average length of time the user spent on the site. Something like:
select avg(count(ID)), avg(timediff(max(ddateTime),min(dDateTime)))
from tableName
where date(dDateTIme) >= 'some start date'
and date(dDateTIme) <= 'some end date'
group by SessionID
But that (apparently) is an invalid use of GROUP BY ....
I know it can be done.... ?
I would do that this way:
SELECT SUM(CountOfID) AS Total, AVG(TimeDifference) AvgTime FROM ( SELECT count(ID) AS CountOfID, timediff(SECOND, max(ddateTime),min(dDateTime)) AS TimeDifference FROM tableName WHERE date(dDateTIme) >= 'some start date' and date(dDateTIme) <= 'some end date' GROUP BY SessionID order by SessionID )AS T
For further information, please see:
MySQL AVG TimeDiff of Multiple rows[^]
Avg Time difference in mysql[^]
这篇关于结合SQL(MySQL)中的count和avg函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!