MySQL 儲存函數實戰：統計日留存率完整實現

業務場景與數據模型

計算「昨日登入用戶中，有多少在前日也登入過」的留存率。核心表：

• visitor_user：用戶登入記錄（shop_id, device, a_token, off_time）
• report_user_login_day_detail：每日登入明細
• report_user_remain_day_detail：留存明細（含狀態欄位）
• report_user_remain_day：留存率匯總

三步驟計算邏輯

1. 將前日登入用戶寫入留存明細表（初始狀態 0）
2. 掃描昨日登入用戶，匹配留存明細表，命中者狀態設為 1
3. 統計狀態=1 的用戶數 / 前日總用戶數 = 留存率，寫入匯總表

核心函數實現

主調度函數

CREATE FUNCTION statics_user_remain_day() RETURNS TINYINT
BEGIN
    DECLARE _yesterday DATE;
    DECLARE _before_yesterday DATE;
    DECLARE _is_success TINYINT;

    SELECT DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d'), INTERVAL 1 DAY) INTO _yesterday;
    SELECT DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d'), INTERVAL 2 DAY) INTO _before_yesterday;

    -- 步驟1：前日用戶寫入留存明細
    SET _is_success = get_user_remain_day(_before_yesterday);
    -- 步驟2：昨日用戶匹配更新狀態
    SET _is_success = update_user_status_remain(_yesterday);
    -- 步驟3：統計留存率寫入匯總
    SET _is_success = insert_user_remain_day(_yesterday);

    RETURN _is_success;
END

步驟1：寫入前日用戶

CREATE FUNCTION get_user_remain_day(_day_time DATE) RETURNS INT
BEGIN
    DECLARE stopFlag INT DEFAULT 0;
    DECLARE _shop_id INT DEFAULT 0;
    DECLARE _device VARCHAR(50);
    DECLARE _a_token CHAR(64) DEFAULT '-1';

    DECLARE cur1 CURSOR FOR 
        SELECT shop_id, device, a_token 
        FROM report_user_login_day_detail 
        WHERE day_time = _day_time;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;

    OPEN cur1;
    FETCH cur1 INTO _shop_id, _device, _a_token;
    WHILE stopFlag = 0 DO
        INSERT INTO report_user_remain_day_detail (id, shop_id, day_time, device, a_token)
        VALUES (UUID(), _shop_id, _day_time, _device, _a_token);
        FETCH cur1 INTO _shop_id, _device, _a_token;
    END WHILE;
    CLOSE cur1;
    RETURN 1;
END

步驟2：更新留存狀態

CREATE FUNCTION update_user_status_remain(_day_time DATE) RETURNS TINYINT
BEGIN
    DECLARE stopFlag INT DEFAULT 0;
    DECLARE _device VARCHAR(50);
    DECLARE _a_token CHAR(64) DEFAULT '-1';

    DECLARE cur1 CURSOR FOR 
        SELECT device, a_token 
        FROM report_user_login_day_detail 
        WHERE day_time = _day_time;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;

    OPEN cur1;
    FETCH cur1 INTO _device, _a_token;
    WHILE stopFlag = 0 DO
        UPDATE report_user_remain_day_detail 
        SET status_remain = 1, day_time = _day_time 
        WHERE device = _device;
        FETCH cur1 INTO _device, _a_token;
    END WHILE;
    CLOSE cur1;
    RETURN 1;
END

步驟3：統計留存率

CREATE FUNCTION insert_user_remain_day(_day_time DATE) RETURNS TINYINT
BEGIN
    DECLARE stopFlag INT DEFAULT 0;
    DECLARE _status_remain TINYINT;
    DECLARE _remain_count INT DEFAULT 0;
    DECLARE _all_count INT DEFAULT 0;
    DECLARE _temp_count INT DEFAULT 0;

    DECLARE cur2 CURSOR FOR 
        SELECT status_remain, COUNT(device) 
        FROM report_user_remain_day_detail 
        WHERE day_time = _day_time 
        GROUP BY status_remain;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;

    OPEN cur2;
    FETCH cur2 INTO _status_remain, _remain_count;
    WHILE stopFlag = 0 DO
        IF _status_remain = 1 THEN
            SET _temp_count = _remain_count;
            SET _all_count = _all_count + _remain_count;
        ELSE
            SET _all_count = _all_count + _remain_count;
        END IF;
        FETCH cur2 INTO _status_remain, _remain_count;
    END WHILE;
    CLOSE cur2;

    INSERT INTO report_user_remain_day (id, day_time, remain_count, remain_percent_day, type_client)
    VALUES (UUID(), _day_time, _temp_count, _temp_count / _all_count, 0);

    RETURN 1;
END

Event 定時觸發

CREATE EVENT ev_daily_remain_stat
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 04:00:00'
DO CALL statics_user_remain_day();

效能優化關鍵點

• 避免游標逐行處理：改用 INSERT ... SELECT、UPDATE ... JOIN 批次操作
• 關鍵欄位建立索引：report_user_login_day_detail(day_time, device)、report_user_remain_day_detail(device, day_time)
• 大表分區：按日分區，歷史分區歸檔
• 報表庫隔離：主庫同步至報表專用實例跑統計

優化版核心邏輯（三條 SQL 替代所有函數）

-- 步驟1
INSERT INTO report_user_remain_day_detail (id, shop_id, day_time, device, a_token, status_remain)
SELECT UUID(), shop_id, _before_yesterday, device, a_token, 0
FROM report_user_login_day_detail
WHERE day_time = _before_yesterday;

-- 步驟2
UPDATE report_user_remain_day_detail r
JOIN report_user_login_day_detail l ON r.device = l.device
SET r.status_remain = 1, r.day_time = _yesterday
WHERE l.day_time = _yesterday;

-- 步驟3
INSERT INTO report_user_remain_day (id, day_time, remain_count, remain_percent_day, type_client)
SELECT UUID(), _yesterday, 
    SUM(CASE WHEN status_remain = 1 THEN 1 ELSE 0 END),
    SUM(CASE WHEN status_remain = 1 THEN 1 ELSE 0 END) / COUNT(*),
    0
FROM report_user_remain_day_detail
WHERE day_time IN (_before_yesterday, _yesterday);