Mysql page access statistics and ranking situation _mysql

MySQL page access statistics and ranking situation

Count the number of pages visited to rank by resolution

SELECT CONCAT (' height ', ' * ', ' width ') as ' resolution ', COUNT (CONCAT (' height ', ' * ', ' width ')) as ' total ' from 
' wifi_s Tatus_page ' 
GROUP by CONCAT (' height ', ' * ', ' width ') Order by 
' Total ' DESC 

Number of page visits in the last 7 days, daily statistics, in date order

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 Sele CT Date_add (Curdate (), INTERVAL-3 Day) 
UNION ALL SELECT Date_add (Curdate (), INTERVAL-4 Day) 
UNION ALL SE Lect Date_add (Curdate (), INTERVAL-5 Day) 
UNION all SELECT date_add (Curdate (), INTERVAL-6 Day) Order by 
T D 
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 

Number of page visits in the last 30 days, daily statistics, in date order

Select TD as Showdate, COALESCE (totalcount, 0) as TotalCount from (select Curdate () as TD UNION all SELECT DATE_AD  D (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 (C Urdate (), 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- 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 DAT E_add (Curdate (), INTERVAL-24 Day) union ALL Select Date_add (Curdate (), INTERVAL-25 Day) union ALL select Date_add (curd ATE (), INTERVAL-26 Day) union ALL Select Date_add (Curdate (), INTERVAL-27 Day) union ALL Select Date_add (Curdate (), INT ERVAL-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

Time-Sharing 24 hours yesterday

Select TD as Showhour from (select Date_add (Curdate (), INTERVAL-1 HOUR) as TD UNION all SELECT Date_add (Curdate (), I  NTERVAL-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) UN ION all Select Date_add (Curdate (), INTERVAL-7 HOUR) union ALL Select Date_add (Curdate (), INTERVAL-8 HOUR) UNION ALL SE Lect 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 (CUR DATE (), 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 DAT E_add (Curdate (), INTERVAL-20 HOUR) union ALL Select Date_add (Curdate (), INTERVAL-21 HOUR) union ALL Select Date_add (CU  Rdate (), 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

The past 11 hours today

Select TD as Showhour 
from ( 
Select Now () as TD 
UNION ALL SELECT Date_add (now (), INTERVAL-1 HOUR) 
Union A LL Select Date_add (now (), INTERVAL-2 HOUR) 
UNION ALL Select Date_add (now (), INTERVAL-3 HOUR) 
UNION ALL Select DA Te_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) C13/>union all SELECT Date_add (now (), INTERVAL-11 HOUR) Order by 
TD 

The above is a small set for everyone to share the MySQL page access statistics and ranking situation, I hope to help.