MySQL 頁面訪問統計：解析度排名與最近 7/30 天趨勢查詢

針對 wifi_status_page 表，提供三類常用統計查詢：解析度分布、每日訪問量（最近 7/30 天）、小時級趨勢。

1. 解析度訪問排名（Top 30）

SELECT CONCAT(`height`, '*', `width`) AS `resolution`,
       COUNT(*) AS `total`
FROM `wifi_status_page`
GROUP BY `resolution`
ORDER BY `total` DESC
LIMIT 30;

2. 最近 7 天每日訪問量（含無資料日期補 0）

SELECT a.td AS showdate,
       COALESCE(b.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') AS 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;

3. 最近 30 天每日訪問量

邏輯同 7 天，將 INTERVAL -6 DAY 擴展至 -29 DAY，UNION ALL 產生 30 筆日期序列。

4. 昨日 24 小時分時統計

SELECT DATE_ADD(CURDATE(), INTERVAL -seq HOUR) AS showhour
FROM (
    SELECT 1 AS seq UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
) t
ORDER BY showhour;

5. 今日已過小時（含當前小時共 12 筆）

SELECT DATE_ADD(NOW(), INTERVAL -seq HOUR) AS showhour
FROM (
    SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
) t
ORDER BY showhour;

技巧說明：透過衍生表產生連續日期/小時序列，再 LEFT JOIN 實際統計資料，確保無資料的時段仍顯示 0，利於圖表繪製。