-- 用戶價(jià)值分析表
CREATE TABLE IF NOT EXISTS ads.ads_user_value_analysis
(
    dt                      DATE COMMENT '統(tǒng)計(jì)日期',
    user_id                 BIGINT COMMENT '用戶ID',
    order_count_td          BIGINT COMMENT '累計(jì)下單次數(shù)',
    order_amount_td         DECIMAL(20,2) COMMENT '累計(jì)下單金額',
    order_last_date         DATE COMMENT '最近下單日期',
    order_first_date        DATE COMMENT '首次下單日期',
    login_count_td          BIGINT COMMENT '累計(jì)登錄次數(shù)',
    login_last_date         DATE COMMENT '最近登錄日期',
    average_order_amount    DECIMAL(20,2) COMMENT '平均客單價(jià)',
    purchase_cycle_days     INT COMMENT '平均購(gòu)買(mǎi)周期(天)',
    account_days            INT COMMENT '賬號(hào)存續(xù)天數(shù)',
    life_time_value         DECIMAL(20,2) COMMENT '生命周期價(jià)值(LTV)',
    recency_score           INT COMMENT '最近活躍度評(píng)分(R)',
    frequency_score         INT COMMENT '活動(dòng)頻次評(píng)分(F)',
    monetary_score          INT COMMENT '消費(fèi)金額評(píng)分(M)',
    rfm_score               INT COMMENT 'RFM總分',
    user_value_level        STRING COMMENT '用戶價(jià)值分層',
    active_status           STRING COMMENT '活躍狀態(tài)',
    life_cycle_status       STRING COMMENT '生命周期狀態(tài)',
    shopping_preference     STRING COMMENT '購(gòu)物偏好',
    growth_trend            STRING COMMENT '價(jià)值發(fā)展趨勢(shì)'
)
COMMENT '用戶價(jià)值分層分析報(bào)表，基于RFM模型計(jì)算用戶價(jià)值'
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

INSERT INTO ads.ads_user_value_analysis
(dt, user_id, order_count_td, order_amount_td, order_last_date, order_first_date, 
login_count_td, login_last_date, average_order_amount, purchase_cycle_days, account_days, 
life_time_value, recency_score, frequency_score, monetary_score, rfm_score, 
user_value_level, active_status, life_cycle_status, shopping_preference, growth_trend)
SELECT
    -- 基礎(chǔ)日期統(tǒng)計(jì)
    date('${pdate}') AS dt,       -- 統(tǒng)計(jì)日期，使用調(diào)度日期參數(shù)
    t1.user_id AS user_id,        -- 用戶ID 
    t1.order_count_td,            -- 累計(jì)下單次數(shù)，來(lái)自交易寬表
    t1.total_amount_td,           -- 累計(jì)下單金額，來(lái)自交易寬表
    -- 格式化日期為yyyy-MM-dd格式
    date_format(t1.order_last_date, '%Y-%m-%d') AS order_last_date,  -- 最近下單日期
    date_format(t1.order_first_date, '%Y-%m-%d') AS order_first_date, -- 首次下單日期
    t2.login_count_td,            -- 累計(jì)登錄次數(shù)，來(lái)自用戶登錄寬表
    date_format(t2.login_last_date, '%Y-%m-%d') AS login_last_date,  -- 最近登錄日期

    -- 計(jì)算衍生指標(biāo)
    -- 計(jì)算平均客單價(jià) = 總金額/訂單數(shù)
    CASE WHEN t1.order_count_td > 0 THEN t1.total_amount_td/t1.order_count_td ELSE 0 END AS average_order_amount,
    -- 計(jì)算平均購(gòu)買(mǎi)周期(天) = (最后訂單日期-首次訂單日期)/(訂單數(shù)-1)
    CASE WHEN t1.order_count_td > 1 
         THEN datediff(t1.order_last_date, t1.order_first_date)/(t1.order_count_td-1) 
         ELSE NULL END AS purchase_cycle_days,
    -- 計(jì)算賬號(hào)存續(xù)天數(shù) = 當(dāng)前日期-注冊(cè)日期
    datediff(current_date(), t2.register_date) AS account_days,

    -- 計(jì)算生命周期價(jià)值(LTV) = 平均客單價(jià) * 年購(gòu)買(mǎi)頻率 * 預(yù)期客戶生命周期（年）
    -- 年購(gòu)買(mǎi)頻率計(jì)算方式: 總購(gòu)買(mǎi)次數(shù)*365/賬號(hào)存續(xù)天數(shù)，即年化購(gòu)買(mǎi)頻率
    -- 預(yù)期客戶生命周期取3年作為默認(rèn)預(yù)估
    CASE WHEN t1.order_count_td > 0 AND datediff(current_date(), t2.register_date) > 0 
         THEN (t1.total_amount_td/t1.order_count_td) * (t1.order_count_td*365/datediff(current_date(), t2.register_date)) * 3 
         ELSE 0 END AS life_time_value,

    -- RFM模型計(jì)算 - 為每個(gè)維度打分(1-5分)
    -- Recency(最近購(gòu)買(mǎi)時(shí)間)評(píng)分: 越近分?jǐn)?shù)越高
    CASE 
        WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5  -- 30天內(nèi)
        WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4  -- 31-60天
        WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3  -- 61-90天
        WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2 -- 91-180天
        ELSE 1                                                          -- 180天以上
    END AS recency_score,

    -- Frequency(購(gòu)買(mǎi)頻率)評(píng)分: 購(gòu)買(mǎi)次數(shù)越多分?jǐn)?shù)越高
    CASE
        WHEN t1.order_count_td >= 20 THEN 5  -- 20次及以上
        WHEN t1.order_count_td >= 10 THEN 4  -- 10-19次
        WHEN t1.order_count_td >= 5 THEN 3   -- 5-9次
        WHEN t1.order_count_td >= 2 THEN 2   -- 2-4次
        ELSE 1                               -- 1次
    END AS frequency_score,

    -- Monetary(消費(fèi)金額)評(píng)分: 總消費(fèi)金額越高分?jǐn)?shù)越高
    CASE
        WHEN t1.total_amount_td >= 10000 THEN 5  -- 1萬(wàn)元及以上
        WHEN t1.total_amount_td >= 5000 THEN 4   -- 5千-1萬(wàn)元
        WHEN t1.total_amount_td >= 2000 THEN 3   -- 2千-5千元
        WHEN t1.total_amount_td >= 500 THEN 2    -- 500-2千元
        ELSE 1                                   -- 500元以下
    END AS monetary_score,

    -- 計(jì)算RFM總分 = R分 + F分 + M分
    (CASE 
        WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
        WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
        WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
        WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
        ELSE 1
    END) + 
    (CASE
        WHEN t1.order_count_td >= 20 THEN 5
        WHEN t1.order_count_td >= 10 THEN 4
        WHEN t1.order_count_td >= 5 THEN 3
        WHEN t1.order_count_td >= 2 THEN 2
        ELSE 1
    END) + 
    (CASE
        WHEN t1.total_amount_td >= 10000 THEN 5
        WHEN t1.total_amount_td >= 5000 THEN 4
        WHEN t1.total_amount_td >= 2000 THEN 3
        WHEN t1.total_amount_td >= 500 THEN 2
        ELSE 1
    END) AS rfm_score,

    -- 用戶價(jià)值分層: 根據(jù)RFM總分(3-15分)進(jìn)行分層
    CASE
        WHEN (CASE 
            WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
            WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
            WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
            WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.order_count_td >= 20 THEN 5
            WHEN t1.order_count_td >= 10 THEN 4
            WHEN t1.order_count_td >= 5 THEN 3
            WHEN t1.order_count_td >= 2 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.total_amount_td >= 10000 THEN 5
            WHEN t1.total_amount_td >= 5000 THEN 4
            WHEN t1.total_amount_td >= 2000 THEN 3
            WHEN t1.total_amount_td >= 500 THEN 2
            ELSE 1
        END) >= 13 THEN '高價(jià)值'      -- 13-15分
        WHEN (CASE 
            WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
            WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
            WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
            WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.order_count_td >= 20 THEN 5
            WHEN t1.order_count_td >= 10 THEN 4
            WHEN t1.order_count_td >= 5 THEN 3
            WHEN t1.order_count_td >= 2 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.total_amount_td >= 10000 THEN 5
            WHEN t1.total_amount_td >= 5000 THEN 4
            WHEN t1.total_amount_td >= 2000 THEN 3
            WHEN t1.total_amount_td >= 500 THEN 2
            ELSE 1
        END) >= 10 THEN '中高價(jià)值'    -- 10-12分
        WHEN (CASE 
            WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
            WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
            WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
            WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.order_count_td >= 20 THEN 5
            WHEN t1.order_count_td >= 10 THEN 4
            WHEN t1.order_count_td >= 5 THEN 3
            WHEN t1.order_count_td >= 2 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.total_amount_td >= 10000 THEN 5
            WHEN t1.total_amount_td >= 5000 THEN 4
            WHEN t1.total_amount_td >= 2000 THEN 3
            WHEN t1.total_amount_td >= 500 THEN 2
            ELSE 1
        END) >= 7 THEN '中價(jià)值'       -- 7-9分
        WHEN (CASE 
            WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
            WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
            WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
            WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.order_count_td >= 20 THEN 5
            WHEN t1.order_count_td >= 10 THEN 4
            WHEN t1.order_count_td >= 5 THEN 3
            WHEN t1.order_count_td >= 2 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.total_amount_td >= 10000 THEN 5
            WHEN t1.total_amount_td >= 5000 THEN 4
            WHEN t1.total_amount_td >= 2000 THEN 3
            WHEN t1.total_amount_td >= 500 THEN 2
            ELSE 1
        END) >= 4 THEN '低價(jià)值'       -- 4-6分
        ELSE '流失風(fēng)險(xiǎn)'                                                                 -- 3分
    END AS user_value_level,

    -- 活躍狀態(tài): 基于最近交易和登錄時(shí)間
    CASE
        WHEN datediff(current_date(), t1.order_last_date) <= 30 OR datediff(current_date(), t2.login_last_date) <= 7 THEN '活躍'   -- 30天內(nèi)有交易或7天內(nèi)有登錄
        WHEN datediff(current_date(), t1.order_last_date) <= 90 OR datediff(current_date(), t2.login_last_date) <= 30 THEN '沉默'  -- 90天內(nèi)有交易或30天內(nèi)有登錄
        ELSE '流失'                                                                                                               -- 超過(guò)90天未交易且超過(guò)30天未登錄
    END AS active_status,

    -- 生命周期狀態(tài): 基于交易行為和訂單歷史
    CASE
        WHEN datediff(current_date(), t1.order_first_date) <= 30 AND t1.order_count_td <= 2 THEN '新用戶'     -- 30天內(nèi)首次購(gòu)買(mǎi)且購(gòu)買(mǎi)次數(shù)<=2次
        WHEN t1.order_count_td >= 3 AND (CASE 
            WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
            WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
            WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
            WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
            ELSE 1
        END) >= 4 THEN '成長(zhǎng)期'                                      -- 購(gòu)買(mǎi)>=3次且近期活躍(60天內(nèi))
        WHEN t1.order_count_td >= 5 AND (CASE 
            WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
            WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
            WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
            WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
            ELSE 1
        END) >= 3 THEN '成熟期'                                      -- 購(gòu)買(mǎi)>=5次且90天內(nèi)有購(gòu)買(mǎi)
        WHEN (CASE 
            WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
            WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
            WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
            WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
            ELSE 1
        END) <= 2 AND (CASE
            WHEN t1.order_count_td >= 20 THEN 5
            WHEN t1.order_count_td >= 10 THEN 4
            WHEN t1.order_count_td >= 5 THEN 3
            WHEN t1.order_count_td >= 2 THEN 2
            ELSE 1
        END) >= 3 THEN '衰退期'                                        -- 購(gòu)買(mǎi)次數(shù)>=5但超過(guò)90天未購(gòu)買(mǎi)
        WHEN (CASE 
            WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
            WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
            WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
            WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
            ELSE 1
        END) >= 3 AND datediff(t1.order_last_date, t0.lag_order_date) > 90 THEN '回流'          -- 最近90天內(nèi)有購(gòu)買(mǎi)但之前超過(guò)90天未購(gòu)買(mǎi)
        ELSE '新用戶'                                                                                         -- 默認(rèn)為新用戶
    END AS life_cycle_status,

    -- 購(gòu)物偏好: 基于購(gòu)買(mǎi)頻率和金額
    CASE
        WHEN (CASE
            WHEN t1.order_count_td >= 20 THEN 5
            WHEN t1.order_count_td >= 10 THEN 4
            WHEN t1.order_count_td >= 5 THEN 3
            WHEN t1.order_count_td >= 2 THEN 2
            ELSE 1
        END) >= 4 AND (CASE
            WHEN t1.total_amount_td >= 10000 THEN 5
            WHEN t1.total_amount_td >= 5000 THEN 4
            WHEN t1.total_amount_td >= 2000 THEN 3
            WHEN t1.total_amount_td >= 500 THEN 2
            ELSE 1
        END) <= 3 THEN '高頻低額'    -- 高頻率低金額: 購(gòu)買(mǎi)頻繁但單價(jià)較低
        WHEN (CASE
            WHEN t1.order_count_td >= 20 THEN 5
            WHEN t1.order_count_td >= 10 THEN 4
            WHEN t1.order_count_td >= 5 THEN 3
            WHEN t1.order_count_td >= 2 THEN 2
            ELSE 1
        END) <= 3 AND (CASE
            WHEN t1.total_amount_td >= 10000 THEN 5
            WHEN t1.total_amount_td >= 5000 THEN 4
            WHEN t1.total_amount_td >= 2000 THEN 3
            WHEN t1.total_amount_td >= 500 THEN 2
            ELSE 1
        END) >= 4 THEN '低頻高額'    -- 低頻率高金額: 購(gòu)買(mǎi)較少但大額消費(fèi) 
        WHEN (CASE
            WHEN t1.order_count_td >= 20 THEN 5
            WHEN t1.order_count_td >= 10 THEN 4
            WHEN t1.order_count_td >= 5 THEN 3
            WHEN t1.order_count_td >= 2 THEN 2
            ELSE 1
        END) >= 4 AND (CASE
            WHEN t1.total_amount_td >= 10000 THEN 5
            WHEN t1.total_amount_td >= 5000 THEN 4
            WHEN t1.total_amount_td >= 2000 THEN 3
            WHEN t1.total_amount_td >= 500 THEN 2
            ELSE 1
        END) >= 4 THEN '高頻高額'    -- 高頻率高金額: 高價(jià)值客戶，頻繁且大額
        ELSE '低頻低額'                                                     -- 低頻率低金額: 低價(jià)值客戶
    END AS shopping_preference,

    -- 價(jià)值發(fā)展趨勢(shì): 比較當(dāng)前RFM評(píng)分與30天前的評(píng)分
    CASE
        WHEN (CASE 
            WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
            WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
            WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
            WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.order_count_td >= 20 THEN 5
            WHEN t1.order_count_td >= 10 THEN 4
            WHEN t1.order_count_td >= 5 THEN 3
            WHEN t1.order_count_td >= 2 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.total_amount_td >= 10000 THEN 5
            WHEN t1.total_amount_td >= 5000 THEN 4
            WHEN t1.total_amount_td >= 2000 THEN 3
            WHEN t1.total_amount_td >= 500 THEN 2
            ELSE 1
        END) > COALESCE(t3.previous_rfm_score, 0) THEN '上升'    -- 當(dāng)前分?jǐn)?shù)高于30天前，趨勢(shì)上升
        WHEN (CASE 
            WHEN datediff(current_date(), t1.order_last_date) <= 30 THEN 5
            WHEN datediff(current_date(), t1.order_last_date) <= 60 THEN 4
            WHEN datediff(current_date(), t1.order_last_date) <= 90 THEN 3
            WHEN datediff(current_date(), t1.order_last_date) <= 180 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.order_count_td >= 20 THEN 5
            WHEN t1.order_count_td >= 10 THEN 4
            WHEN t1.order_count_td >= 5 THEN 3
            WHEN t1.order_count_td >= 2 THEN 2
            ELSE 1
        END + 
        CASE
            WHEN t1.total_amount_td >= 10000 THEN 5
            WHEN t1.total_amount_td >= 5000 THEN 4
            WHEN t1.total_amount_td >= 2000 THEN 3
            WHEN t1.total_amount_td >= 500 THEN 2
            ELSE 1
        END) < COALESCE(t3.previous_rfm_score, 0) THEN '下降'    -- 當(dāng)前分?jǐn)?shù)低于30天前，趨勢(shì)下降
        ELSE '穩(wěn)定'                                                      -- 分?jǐn)?shù)相等，趨勢(shì)穩(wěn)定
    END AS growth_trend
FROM
(
    -- 訂單數(shù)據(jù): 獲取用戶交易相關(guān)信息
    SELECT 
        user_id,
        k1,
        order_date_last,
        LAG(order_date_last, 1, NULL) OVER(PARTITION BY user_id ORDER BY k1) AS lag_order_date  -- 獲取上一次最近下單日期，用于計(jì)算回流狀態(tài)
    FROM dws.dws_trade_user_order_td
    WHERE k1 = date('${pdate}')  -- 取當(dāng)天分區(qū)數(shù)據(jù)
) t0
JOIN
(
    -- 訂單數(shù)據(jù): 獲取用戶交易相關(guān)信息
    SELECT 
        user_id,
        SUM(order_count_td) AS order_count_td,           -- 累計(jì)下單次數(shù)
        SUM(total_amount_td) AS total_amount_td,         -- 累計(jì)下單金額
        MAX(order_date_last) AS order_last_date,         -- 最近下單日期
        MIN(order_date_first) AS order_first_date        -- 首次下單日期
    FROM dws.dws_trade_user_order_td
    WHERE k1 = date('${pdate}')  -- 取當(dāng)天分區(qū)數(shù)據(jù)
    GROUP BY user_id
) t1
ON t0.user_id = t1.user_id
JOIN
(
    -- 登錄數(shù)據(jù): 獲取用戶登錄相關(guān)信息
    SELECT 
        user_id,
        SUM(login_count_td) AS login_count_td,         -- 累計(jì)登錄次數(shù)
        MAX(login_date_last) AS login_last_date,       -- 最近登錄日期
        date('2020-01-01') AS register_date            -- 注冊(cè)日期，使用默認(rèn)值
    FROM dws.dws_user_user_login_td
    WHERE k1 = date('${pdate}')  -- 取當(dāng)天分區(qū)數(shù)據(jù)
    GROUP BY user_id
) t2
ON t1.user_id = t2.user_id
LEFT JOIN
(
    -- 上月RFM評(píng)分?jǐn)?shù)據(jù): 用于計(jì)算價(jià)值發(fā)展趨勢(shì)
    SELECT 
        user_id,
        recency_score + frequency_score + monetary_score AS previous_rfm_score  -- 30天前的RFM總分
    FROM ads.ads_user_value_analysis
    WHERE dt = date_sub(date('${pdate}'), 30)  -- 取30天前的數(shù)據(jù)
) t3
ON t1.user_id = t3.user_id;