SQL 計算每月會員流失數量：基於 6 個月無消費判定的輔助表與窗口函數方案

業務定義

• 數據表：`PDetail` (年月 `DateValue`、會員 ID `UserId`、消費日期 `CostValue`)，所有字段 `VARCHAR(8)` 格式 `YYYYMM`。
• 規則：會員在某月份最後一次消費後 **連續 6 個月無任何消費記錄**，則在 **第 6 個月結束時 (即第 7 個月首) 判定為該月流失**。
• 輸出：每月 `YYYYMM` 及該月流失會員數。

核心邏輯：輔助日期表 + 消費截止日期計算 + 聚合統計

1. 構建輔助年月表 `YearMonth`

包含所有需統計的月份 (如 201001~201109)，保證無數據月份也能輸出 0。

2. 計算每筆消費的「應再次消費截止日期」

-- 每筆消費記錄對應的流失判定月份
-- 消費日期 CostValue (如 201001) + 6個月 = NeedCostDate (201007)
-- 會員必須在 NeedCostDate 所在月份之前 (含) 有後續消費，否則在 NeedCostDate 月份流失
SELECT 
    UserId,
    CostValue,
    DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(CostValue,'01'),'%Y%m%d'), INTERVAL 6 MONTH), '%Y%m') AS NeedCostDate
FROM PDetail

3. 判定流失邏輯 (MySQL 8.0+ CTE 寫法)

WITH Consumption AS (
    -- 每筆消費及其流失判定月
    SELECT 
        UserId,
        CostValue,
        DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(CostValue,'01'),'%Y%m%d'), INTERVAL 6 MONTH), '%Y%m') AS NeedCostDate
    FROM PDetail
),
NextConsumption AS (
    -- 關鍵：每筆消費後，同會員是否在 NeedCostDate 之前有下一筆消費
    SELECT 
        c.*,
        EXISTS (
            SELECT 1 FROM PDetail p2
            WHERE p2.UserId = c.UserId
              AND p2.CostValue > c.CostValue
              AND p2.CostValue <= c.NeedCostDate
        ) AS has_next_consumption
    FROM Consumption c
),
LostMembers AS (
    -- 無後續消費的記錄，標記為在 NeedCostDate 月份流失
    SELECT NeedCostDate AS LostMonth, UserId
    FROM NextConsumption
    WHERE has_next_consumption = FALSE
)
-- 最終彙總：左連接輔助表，保證無流失月份顯示 0
SELECT 
    ym.DateValue AS 年月,
    COUNT(lm.UserId) AS 該月會員流失數量
FROM YearMonth ym
LEFT JOIN LostMembers lm ON ym.DateValue = lm.LostMonth
GROUP BY ym.DateValue
ORDER BY 年月;

4. MySQL 5.7 兼容寫法 (子查詢/臨時表)

原答案提供的長 SQL 即為此邏輯的 5.7 兼容實現，核心步驟一致：

1. 建輔助表 `YearMonth`。
2. 子查詢計算 `NeedCostDate`。
3. 關聯子查詢 `EXISTS` 判斷 6 個月內是否有消費。
4. 篩選無後續消費記錄，按 `NeedCostDate` 分組計數。
5. 左連接輔助表補全月份。

關鍵細節與優化

總結

會員流失統計核心是 **「以每次消費為基準，向前看 6 月無消費則流失」**。使用輔助日期表保證月份完整，通過日期運算得出流失判定月，再用 `EXISTS` 子查詢檢查後續消費存在性，最後聚合統計。MySQL 8.0+ 推薦 CTE 寫法清晰可維護；5.7 用嵌套子查詢/臨時表實現同樣邏輯。