欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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