MySQL 頁面訪問統計與排名 SQL 實戰：解析度分布、近 7/30 天趨勢、小時級明細

MySQL 頁面訪問統計與排名 SQL 實戰：解析度分布、近 7/30 天趨勢、小時級明細

針對 wifi_status_page 訪問記錄表，提供生產可用的統計查詢：解析度排名、近 N 天趨勢 (含零數據填充)、昨日 24 小時分時、今日迄今分時。核心技巧：**派生表生成連續日期/時間維度 + LEFT JOIN 保證零值顯示**。

表結構假設

CREATE TABLE wifi_status_page (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    height INT NOT NULL COMMENT '螢幕高',
    width INT NOT NULL COMMENT '螢幕寬',
    add_time INT UNSIGNED NOT NULL COMMENT 'Unix 時間戳',
    INDEX idx_add_time (add_time)
) ENGINE=InnoDB;

一、解析度訪問量排名 (Top 30)

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

優化點：GROUP BY height, width 直接使用欄位而非表達式，利用索引 (若有複合索引)；CONCAT 僅用於輸出顯示。

二、近 7 天每日訪問量 (含零值日期)

SELECT 
    d.td AS showdate,
    COALESCE(t.totalcount, 0) AS totalcount
FROM (
    -- 派生表：生成連續 7 天日期序列 (含今天)
    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)
) d
LEFT JOIN (
    -- 實際統計：近 7 天每日計數
    SELECT 
        DATE(FROM_UNIXTIME(add_time)) AS add_date,
        COUNT(*) AS totalcount
    FROM wifi_status_page
    WHERE add_time >= UNIX_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL -6 DAY))
      AND add_time < UNIX_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 1 DAY))
    GROUP BY add_date
) t ON d.td = t.add_date
ORDER BY d.td;

關鍵技巧**：

• 派生表 d 生成連續日期骨架，LEFT JOIN 確保無數據日期顯示 0。
• WHERE 使用 add_time >= ... AND add_time < ... 半開區間，**利用索引範圍掃描**，避免函數包裹導致全表掃描。
• COALESCE 處理 NULL → 0。

三、近 30 天每日訪問量 (含零值日期)

SELECT 
    d.td AS showdate,
    COALESCE(t.totalcount, 0) AS totalcount
FROM (
    -- 生成連續 30 天日期 (建議用遞歸 CTE 或數字表，此處展開 UNION ALL)
    SELECT CURDATE() AS td UNION ALL
    SELECT DATE_ADD(CURDATE(), INTERVAL -1 DAY) UNION ALL
    -- ... 省略中間 2-27 天 ...
    SELECT DATE_ADD(CURDATE(), INTERVAL -29 DAY)
) d
LEFT JOIN (
    SELECT 
        DATE(FROM_UNIXTIME(add_time)) AS add_date,
        COUNT(*) AS totalcount
    FROM wifi_status_page
    WHERE add_time >= UNIX_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL -29 DAY))
      AND add_time < UNIX_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 1 DAY))
    GROUP BY add_date
) t ON d.td = t.add_date
ORDER BY d.td;

生產建議**：建立 dim_date 維度表或使用 WITH RECURSIVE (MySQL 8.0+) 生成日期序列，避免巨大 UNION ALL。

四、昨日 24 小時分時統計

SELECT 
    d.td AS showhour,
    COALESCE(t.cnt, 0) AS totalcount
FROM (
    -- 生成昨日 0-23 時 (以小時為粒度的 datetime)
    SELECT DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 DAY), INTERVAL 0 HOUR) AS td UNION ALL
    SELECT DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR) UNION ALL
    -- ... 省略 2-22 時 ...
    SELECT DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 DAY), INTERVAL 23 HOUR)
) d
LEFT JOIN (
    SELECT 
        DATE_FORMAT(FROM_UNIXTIME(add_time), '%Y-%m-%d %H:00:00') AS add_hour,
        COUNT(*) AS cnt
    FROM wifi_status_page
    WHERE add_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY))
      AND add_time < UNIX_TIMESTAMP(CURDATE())
    GROUP BY add_hour
) t ON d.td = t.add_hour
ORDER BY d.td;

五、今日迄今 (近 11 小時) 分時統計

SELECT 
    d.td AS showhour,
    COALESCE(t.cnt, 0) AS totalcount
FROM (
    -- 生成當前小時往前 11 小時
    SELECT NOW() AS td UNION ALL
    SELECT DATE_ADD(NOW(), INTERVAL -1 HOUR) UNION ALL
    -- ... 省略 -2 到 -10 小時 ...
    SELECT DATE_ADD(NOW(), INTERVAL -11 HOUR)
) d
LEFT JOIN (
    SELECT 
        DATE_FORMAT(FROM_UNIXTIME(add_time), '%Y-%m-%d %H:00:00') AS add_hour,
        COUNT(*) AS cnt
    FROM wifi_status_page
    WHERE add_time >= UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -11 HOUR))
      AND add_time < UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL 1 HOUR))
    GROUP BY add_hour
) t ON d.td = t.add_hour
ORDER BY d.td;

六、進階優化：維度表 / CTE 重構 (MySQL 8.0+)

-- 建立日期維度表 (一次建立，長期使用)
CREATE TABLE dim_date (
    dt DATE NOT NULL PRIMARY KEY,
    year INT, quarter INT, month INT, day INT, weekday INT,
    is_weekend BOOL, is_holiday BOOL
);
-- 填充 2020-2030 年數據
INSERT INTO dim_date (dt) 
WITH RECURSIVE dates AS (
    SELECT '2020-01-01' AS dt UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates WHERE dt < '2030-12-31'
) SELECT dt FROM dates;

-- 重構近 30 天查詢 (極簡、高效、可擴展)
SELECT 
    d.dt AS showdate,
    COALESCE(t.totalcount, 0) AS totalcount
FROM dim_date d
LEFT JOIN (
    SELECT DATE(FROM_UNIXTIME(add_time)) AS add_date, COUNT(*) AS totalcount
    FROM wifi_status_page
    WHERE add_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 29 DAY))
      AND add_time < UNIX_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 1 DAY))
    GROUP BY add_date
) t ON d.dt = t.add_date
WHERE d.dt BETWEEN DATE_SUB(CURDATE(), INTERVAL 29 DAY) AND CURDATE()
ORDER BY d.dt;

七、效能關鍵點總結

1. 索引覆蓋：INDEX idx_add_time (add_time) 讓時間範圍過濾走索引範圍掃描。
2. 避免函數包裹列：WHERE FROM_UNIXTIME(add_time) > ... 導致全表掃描。**轉換常量**：WHERE add_time > UNIX_TIMESTAMP(...)。
3. 派生表/維度表 LEFT JOIN：保證零值顯示，前端圖表無斷點。
4. 半開區間：>= start AND < end_next 精確邊界，避免邊界重複/遺漏。
5. MySQL 8.0 CTE：WITH RECURSIVE 替代巨大 UNION ALL，碼更清晰、優化器更友好。

總結

訪問統計核心模式：**時間維度骨架 (派生表/維度表) + LEFT JOIN 實際聚合 + 索引友好時間範圍過濾 + 零值填充**。此模式通用於日活、留存、轉化漏斗、錯誤率等所有時間序列統計場景。