MySQL页面访问统计及排名情况

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页面访问统计及排名情况，希望对大家有所帮助。