MySQL页面访问统计及排名情况
程序员文章站
2024-02-21 17:08:34
mysql页面访问统计及排名情况
统计访问页面数量,以分辨率进行排名
select concat(`height` , '*', `width`) as `re...
mysql页面访问统计及排名情况
统计访问页面数量,以分辨率进行排名
select concat(`height` , '*', `width`) as `resolution` , count(concat(`height`, '*', `width`)) as `total` from `wifi_status_page` group by concat(`height`, '*', `width`) order by `total` desc limit 0 , 30
最近7天页面访问量,每日统计数量,以日期为序
select td as showdate, coalesce( totalcount, 0 ) as totalcount from ( select curdate( ) as td union all select date_add( curdate( ) , interval -1 day ) union all select date_add( curdate( ) , interval -2 day ) union all select date_add( curdate( ) , interval -3 day ) union all select date_add( curdate( ) , interval -4 day ) union all select date_add( curdate( ) , interval -5 day ) union all select date_add( curdate( ) , interval -6 day ) order by td )a left join ( select from_unixtime(add_time, '%y-%m-%d') add_data, count( id ) as totalcount from wifi_status_page where from_unixtime(add_time, '%y-%m-%d') between date_add(curdate(), interval -6 day) and curdate() group by add_data )b on a.td = b.add_data
最近30天页面访问量,每日统计数量,以日期为序
select td as showdate, coalesce( totalcount, 0 ) as totalcount from ( select curdate( ) as td union all select date_add(curdate(), interval -1 day) union all select date_add(curdate(), interval -2 day) union all select date_add(curdate(), interval -3 day) union all select date_add(curdate(), interval -4 day) union all select date_add(curdate(), interval -5 day) union all select date_add(curdate(), interval -6 day) union all select date_add(curdate(), interval -7 day) union all select date_add(curdate(), interval -8 day) union all select date_add(curdate(), interval -9 day) union all select date_add(curdate(), interval -10 day) union all select date_add(curdate(), interval -11 day) union all select date_add(curdate(), interval -12 day) union all select date_add(curdate(), interval -13 day) union all select date_add(curdate(), interval -14 day) union all select date_add(curdate(), interval -15 day) union all select date_add(curdate(), interval -16 day) union all select date_add(curdate(), interval -17 day) union all select date_add(curdate(), interval -18 day) union all select date_add(curdate(), interval -19 day) union all select date_add(curdate(), interval -20 day) union all select date_add(curdate(), interval -21 day) union all select date_add(curdate(), interval -22 day) union all select date_add(curdate(), interval -23 day) union all select date_add(curdate(), interval -24 day) union all select date_add(curdate(), interval -25 day) union all select date_add(curdate(), interval -26 day) union all select date_add(curdate(), interval -27 day) union all select date_add(curdate(), interval -28 day) union all select date_add(curdate(), interval -29 day) order by td )a left join ( select from_unixtime(add_time, '%y-%m-%d') add_data, count( id ) as totalcount from wifi_status_page where from_unixtime(add_time, '%y-%m-%d') between date_add(curdate(), interval -29 day) and curdate() group by add_data )b on a.td = b.add_data
昨天24小时分时
select td as showhour from ( select date_add(curdate(), interval -1 hour) as td union all select date_add(curdate(), interval -2 hour) union all select date_add(curdate(), interval -3 hour) union all select date_add(curdate(), interval -4 hour) union all select date_add(curdate(), interval -5 hour) union all select date_add(curdate(), interval -6 hour) union all select date_add(curdate(), interval -7 hour) union all select date_add(curdate(), interval -8 hour) union all select date_add(curdate(), interval -9 hour) union all select date_add(curdate(), interval -10 hour) union all select date_add(curdate(), interval -11 hour) union all select date_add(curdate(), interval -12 hour) union all select date_add(curdate(), interval -13 hour) union all select date_add(curdate(), interval -14 hour) union all select date_add(curdate(), interval -15 hour) union all select date_add(curdate(), interval -16 hour) union all select date_add(curdate(), interval -17 hour) union all select date_add(curdate(), interval -18 hour) union all select date_add(curdate(), interval -19 hour) union all select date_add(curdate(), interval -20 hour) union all select date_add(curdate(), interval -21 hour) union all select date_add(curdate(), interval -22 hour) union all select date_add(curdate(), interval -23 hour) union all select date_add(curdate(), interval -24 hour) order by td )a
今天过去的11个小时
select td as showhour from ( select now() as td union all select date_add(now(), interval -1 hour) union all select date_add(now(), interval -2 hour) union all select date_add(now(), interval -3 hour) union all select date_add(now(), interval -4 hour) union all select date_add(now(), interval -5 hour) union all select date_add(now(), interval -6 hour) union all select date_add(now(), interval -7 hour) union all select date_add(now(), interval -8 hour) union all select date_add(now(), interval -9 hour) union all select date_add(now(), interval -10 hour) union all select date_add(now(), interval -11 hour) order by td )a
以上所述是小编给大家分享的mysql页面访问统计及排名情况,希望对大家有所帮助。