MySQL 統計頁面訪問量與排名：實戰 SQL 大全

MySQL 統計頁面訪問量與排名：實戰 SQL 大全

數據分析場景中，頁面訪問統計（PV/UV、分辨率分布、趨勢圖）是高頻需求。本文提供開箱即用的 MySQL 查詢語句，覆蓋分辨率排名、最近 7/30 天趨勢、昨日 24 時分時、今日小時級統計等核心指標。

一、 分辨率訪問排名 TOP 30

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

優化點：CONCAT 直接在 GROUP BY 中復用別名（MySQL 8.0+ 支持），避免重複計算。

二、 最近 7 天每日訪問趨勢（含零值填充）

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;

核心技巧：

• 派生表 a 生成連續 7 天日期序列（日曆表模式）
• LEFT JOIN 確保無數據日期顯示 0（COALESCE）
• FROM_UNIXTIME 統一時間戳轉日期，利用索引範圍查詢

三、 最近 30 天每日訪問趨勢（通用模板）

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
    -- ... 重複至 INTERVAL -29 DAY
    SELECT DATE_ADD(CURDATE(), INTERVAL -29 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 -29 DAY) AND CURDATE()
    GROUP BY add_data
) b ON a.td = b.add_data;

💡 生產建議：創建永久日曆表 dim_date，替代 UNION ALL 生成序列，提升可讀性與性能。

四、 昨日 24 小時分時統計

SELECT 
    DATE_ADD(CURDATE(), INTERVAL -1 HOUR) AS td UNION ALL
    SELECT DATE_ADD(CURDATE(), INTERVAL -2 HOUR) UNION ALL
    -- ... 重複至 INTERVAL -24 HOUR
    SELECT DATE_ADD(CURDATE(), INTERVAL -24 HOUR)
    ORDER BY td;

此查詢僅生成時間序列，需 LEFT JOIN 實際訪問表獲取計數，邏輯同每日趨勢。

五、 今日過往 11 小時統計

SELECT NOW() AS td UNION ALL
SELECT DATE_ADD(NOW(), INTERVAL -1 HOUR) UNION ALL
-- ... 重複至 INTERVAL -11 HOUR
SELECT DATE_ADD(NOW(), INTERVAL -11 HOUR)
ORDER BY td;

使用 NOW() 而非 CURDATE()，精確到小時級時間窗口。

六、 性能優化與最佳實踐

1. 索引策略：add_time 建立單列索引；高頻查詢可建立聯合索引 (add_time, height, width) 覆蓋分辨率統計。
2. 避免函數索引失效：WHERE FROM_UNIXTIME(add_time, ...) = '2024-01-15' 會導致全表掃描。
   ✅ 改為範圍查詢：WHERE add_time BETWEEN UNIX_TIMESTAMP('2024-01-15') AND UNIX_TIMESTAMP('2024-01-16')
3. 預聚合表：億級數據下，建立按天/小時預聚合表 wifi_status_page_daily，定時任務刷新，查詢毫秒級返回。
4. 時區一致性：確保 MySQL time_zone、應用層、數據寫入層時區統一（建議均用 UTC）。

七、 通用模板化建議

將上述邏輯封裝為存儲過程或應用層工具類：

-- 存儲過程示例：獲取任意天數趨勢
CREATE PROCEDURE `sp_page_trend`(IN days INT)
BEGIN
    SET @sql = CONCAT('
        SELECT a.td AS showdate, COALESCE(b.totalcount, 0) AS totalcount
        FROM (
            SELECT CURDATE() AS td ',
            REPEAT('UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL -', days, ' DAY) '),
            'ORDER BY td
        ) a
        LEFT JOIN (
            SELECT FROM_UNIXTIME(add_time, ''%Y-%m-%d'') add_data, COUNT(id) totalcount
            FROM wifi_status_page
            WHERE add_time BETWEEN UNIX_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL -', days, ' DAY)) 
                             AND UNIX_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 1 DAY))
            GROUP BY add_data
        ) b ON a.td = b.add_data
    ');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

總結

「日曆表 LEFT JOIN 業務表」是時序統計的標準范式，解決「無數據日期/小時顯示 0」的核心痛點。結合範圍查詢利用索引、預聚合表應對大數據量，可構建高性能、易維護的訪問統計體系。