MySQL页面访问统计及排名情况
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
以上所述是小编给大家分享的MySQL页面访问统计及排名情况，希望对大家有所帮助。