MySQL页面访问统计及排名情况
统计访问页面数量,以分辨率进行排名
SELECTCONCAT(`height`,'*',`width`)AS`resolution`,COUNT(CONCAT(`height`,'*',`width`))AS`total`
FROM`wifi_status_page`
GROUPBYCONCAT(`height`,'*',`width`)
ORDERBY`total`DESC
LIMIT0,30
最近7天页面访问量,每日统计数量,以日期为序
SELECTtdASshowdate,COALESCE(totalcount,0)AStotalcount
FROM(
SELECTCURDATE()AStd
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-1DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-2DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-3DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-4DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-5DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-6DAY)
ORDERBYtd
)a
LEFTJOIN(
SELECTFROM_UNIXTIME(add_time,'%Y-%m-%d')add_data,COUNT(id)AStotalcount
FROMwifi_status_page
WHEREFROM_UNIXTIME(add_time,'%Y-%m-%d')BETWEENDATE_ADD(CURDATE(),INTERVAL-6DAY)ANDCURDATE()
GROUPBYadd_data
)bONa.td=b.add_data
最近30天页面访问量,每日统计数量,以日期为序
SELECTtdASshowdate,COALESCE(totalcount,0)AStotalcount
FROM(
SELECTCURDATE()AStd
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-1DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-2DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-3DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-4DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-5DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-6DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-7DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-8DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-9DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-10DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-11DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-12DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-13DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-14DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-15DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-16DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-17DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-18DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-19DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-20DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-21DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-22DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-23DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-24DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-25DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-26DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-27DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-28DAY)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-29DAY)
ORDERBYtd
)a
LEFTJOIN(
SELECTFROM_UNIXTIME(add_time,'%Y-%m-%d')add_data,COUNT(id)AStotalcount
FROMwifi_status_page
WHEREFROM_UNIXTIME(add_time,'%Y-%m-%d')BETWEENDATE_ADD(CURDATE(),INTERVAL-29DAY)ANDCURDATE()
GROUPBYadd_data
)bONa.td=b.add_data
昨天24小时分时
SELECTtdASshowhour
FROM(
SELECTDATE_ADD(CURDATE(),INTERVAL-1HOUR)astd
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-2HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-3HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-4HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-5HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-6HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-7HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-8HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-9HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-10HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-11HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-12HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-13HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-14HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-15HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-16HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-17HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-18HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-19HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-20HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-21HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-22HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-23HOUR)
UNIONALLSELECTDATE_ADD(CURDATE(),INTERVAL-24HOUR)
ORDERBYtd
)a
今天过去的11个小时
SELECTtdASshowhour
FROM(
SELECTNOW()AStd
UNIONALLSELECTDATE_ADD(NOW(),INTERVAL-1HOUR)
UNIONALLSELECTDATE_ADD(NOW(),INTERVAL-2HOUR)
UNIONALLSELECTDATE_ADD(NOW(),INTERVAL-3HOUR)
UNIONALLSELECTDATE_ADD(NOW(),INTERVAL-4HOUR)
UNIONALLSELECTDATE_ADD(NOW(),INTERVAL-5HOUR)
UNIONALLSELECTDATE_ADD(NOW(),INTERVAL-6HOUR)
UNIONALLSELECTDATE_ADD(NOW(),INTERVAL-7HOUR)
UNIONALLSELECTDATE_ADD(NOW(),INTERVAL-8HOUR)
UNIONALLSELECTDATE_ADD(NOW(),INTERVAL-9HOUR)
UNIONALLSELECTDATE_ADD(NOW(),INTERVAL-10HOUR)
UNIONALLSELECTDATE_ADD(NOW(),INTERVAL-11HOUR)
ORDERBYtd
)a
|