業務場景

統計網站關鍵字本週 Google 流量與上週差值 (增長量)。日誌分析程式已寫入週彙總表 mobile_keywords_weeklog，需在資料庫層完成差值計算，避免應用層循環查詢造成資料庫壓力。

表結構

CREATE TABLE `mobile_keywords_weeklog` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `topdate` DATE DEFAULT NULL,           -- 週結束日期
  `keywords_id` INT(11) DEFAULT '0',     -- 關鍵字 ID
  `s_pv` INT(11) DEFAULT '0',            -- 頁面瀏覽
  `s_gv` INT(11) DEFAULT '0',            -- Google 流量 (目標欄位)
  `s_bv` INT(11) DEFAULT '0',            -- 百度流量
  `d_value` INT(11) DEFAULT '0' COMMENT '與上週差值',
  PRIMARY KEY (`id`),
  KEY `topdate` (`topdate`),
  KEY `keywords_id` (`keywords_id`),
  KEY `s_gv` (`s_gv`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

雙游標預存程序完整代碼

DELIMITER $$
USE `brother_mobile`$$
DROP PROCEDURE IF EXISTS `pro_week_stat`$$
CREATE DEFINER=`root`@`%` PROCEDURE `pro_week_stat`()
top: BEGIN
    -- 變數宣告
    DECLARE done INT DEFAULT 0;
    DECLARE curr_week DATE;
    DECLARE last_week DATE;
    DECLARE a1, b1, c1 INT;  -- 當週: id, keywords_id, s_gv
    DECLARE a2, b2, c2 INT;  -- 上週: id, keywords_id, s_gv
    DECLARE d INT;           -- 差值
    DECLARE is_update INT DEFAULT 0;

    -- 取得最近兩週日期 (按 topdate 降序 LIMIT 2)
    DECLARE all_week CURSOR FOR 
        SELECT topdate FROM brother_mobile.mobile_keywords_weeklog 
        GROUP BY topdate ORDER BY topdate DESC LIMIT 0,2;

    -- 當週明細游標
    DECLARE cur1 CURSOR FOR 
        SELECT id, keywords_id, s_gv FROM brother_mobile.mobile_keywords_weeklog WHERE topdate = curr_week;

    -- 上週明細游標
    DECLARE cur2 CURSOR FOR 
        SELECT id, keywords_id, s_gv FROM brother_mobile.mobile_keywords_weeklog WHERE topdate = last_week;

    -- 游標結束處理器
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    -- 1. 取得兩週日期
    OPEN all_week;
    FETCH all_week INTO curr_week;
    FETCH all_week INTO last_week;
    CLOSE all_week;

    -- 邊界檢查：資料不足兩週直接退出
    IF IFNULL(curr_week,'') = '' OR IFNULL(last_week,'') = '' THEN
        LEAVE top;
    END IF;

    -- 2. 外層循環：遍歷當週每筆關鍵字記錄
    OPEN cur1;
    out_repeat: REPEAT
        FETCH cur1 INTO a1, b1, c1;

        -- 內層處理塊：尋找上週同關鍵字
        BEGIN
            DECLARE done1 INT DEFAULT 0;
            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1 = 1;

            OPEN cur2;
            inner_repeat: REPEAT
                FETCH cur2 INTO a2, b2, c2;

                IF NOT done1 THEN
                    SET is_update = 0;
                    IF b1 = b2 THEN          -- 關鍵字 ID 匹配
                        SET d = c1 - c2;     -- 本週 - 上週
                        SET is_update = 1;
                        UPDATE brother_mobile.mobile_keywords_weeklog 
                        SET d_value = d WHERE id = a1;
                        LEAVE inner_repeat;  -- 找到即跳出內層
                    END IF;
                END IF;
            UNTIL done1 END REPEAT inner_repeat;
            CLOSE cur2;
        END;

        -- 內層未找到匹配 (上週無此關鍵字) → 差值 = 本週值
        IF is_update <> 1 THEN
            UPDATE brother_mobile.mobile_keywords_weeklog SET d_value = c1 WHERE id = a1;
        END IF;

    UNTIL done END REPEAT out_repeat;
    CLOSE cur1;
END$$
DELIMITER ;

關鍵語法解析

語法	作用
DECLARE ... CURSOR FOR SELECT ...	定義游標，綁定 SELECT 結果集
OPEN cursor	開啟游標，執行查詢
FETCH cursor INTO var1, var2...	取下一行到變數
CLOSE cursor	釋放游標資源
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1	捕獲「無資料」狀態 (NOT FOUND)，設定旗標控制循環結束
REPEAT ... UNTIL done END REPEAT	至少執行一次的循環，適配 FETCH 邏輯
LEAVE label	跳出指定標籤的循環/區塊 (類 break)
BEGIN ... END	複合語句區塊，可包含局部變數/處理器

執行與驗證

CALL pro_week_stat();
-- 驗證
SELECT topdate, keywords_id, s_gv, d_value FROM mobile_keywords_weeklog ORDER BY topdate DESC, keywords_id LIMIT 20;

效能與優化建議

• 索引支援：KEY topdate (topdate)、KEY keywords_id (keywords_id) 確保游標查詢走索引
• 避免全表掃描：游標 WHERE 條件精確命中索引
• 大資料量改用 JOIN：若週記錄 > 10 萬，雙游標循環慢，改用單條 SQL：

  UPDATE mobile_keywords_weeklog curr
  JOIN mobile_keywords_weeklog prev 
    ON curr.keywords_id = prev.keywords_id
   AND prev.topdate = (SELECT MAX(topdate) FROM mobile_keywords_weeklog WHERE topdate < curr.topdate)
  SET curr.d_value = curr.s_gv - COALESCE(prev.s_gv, 0)
  WHERE curr.topdate = (SELECT MAX(topdate) FROM mobile_keywords_weeklog);

• 事務包裹：整個計算過程建議在事務中執行，確保一致性

適用場景擴展

• 任何「當期 vs 上期」同維度指標對比 (日/週/月)
• 多租戶/多業務線隔離計算 (加上 tenant_id/biz_id 過濾)
• 定時任務 (Event Scheduler / cron) 定期觸發