-- 用戶商品交易行為匯總表（最近N日）
CREATE TABLE dws.dws_trade_user_sku_order_nd (
    user_id         STRING COMMENT '用戶ID',
    sku_id          STRING COMMENT '商品ID',
    k1              DATE COMMENT '數據日期',


    -- 商品維度冗余
    sku_name        STRING COMMENT '商品名稱',
    category1_id    STRING COMMENT '一級品類ID',
    category1_name  STRING COMMENT '一級品類名稱',
    tm_id           STRING COMMENT '品牌ID',
    tm_name         STRING COMMENT '品牌名稱',


    -- 7日匯總指標
    order_count_7d  BIGINT COMMENT '7日下單次數',
    order_num_7d    BIGINT COMMENT '7日購買件數',
    order_amount_7d DECIMAL(16,2) COMMENT '7日下單金額',


    -- 30日匯總指標
    order_count_30d BIGINT COMMENT '30日下單次數',
    order_num_30d   BIGINT COMMENT '30日購買件數',
    order_amount_30d DECIMAL(16,2) COMMENT '30日下單金額'
) COMMENT '交易域用戶商品粒度訂單最近N日匯總表'
PARTITION BY k1;

-- 1. 關聯商品維度信息
SELECT 
    od.user_id, od.sku_id, od.k1,


    -- 2. 冗余維度屬性
    COALESCE(sku.sku_name, '未知商品') as sku_name,
    COALESCE(sku.category1_id, '-1') as category1_id,
    COALESCE(sku.category1_name, '未知品類') as category1_name,
    COALESCE(sku.tm_id, '-1') as tm_id,
    COALESCE(sku.tm_name, '未知品牌') as tm_name,


    -- 3. 統計指標
    od.order_count_1d,
    od.order_num_1d,
    od.order_amount_1d
FROM order_detail od
LEFT JOIN dim.dim_sku_full sku
    ON od.sku_id = sku.id
    AND sku.k1 = (
        SELECT MAX(k1) 
        FROM dim.dim_sku_full 
        WHERE k1 <= DATE('${pdate}')
    );

-- 1. 計算匯總指標
SELECT
    user_id, sku_id, k1,
    sku_name, category1_id, category1_name,
    tm_id, tm_name,


    -- 2. 計算7日累計
    SUM(IF(k1 >= DATE_ADD(DATE('${pdate}'), -6),
           order_count_1d, 0)) AS order_count_7d,
    SUM(IF(k1 >= DATE_ADD(DATE('${pdate}'), -6),
           order_num_1d, 0)) AS order_num_7d,
    SUM(IF(k1 >= DATE_ADD(DATE('${pdate}'), -6),
           order_amount_1d, 0)) AS order_amount_7d,


    -- 3. 計算30日累計
    SUM(order_count_1d) AS order_count_30d,
    SUM(order_num_1d) AS order_num_30d,
    SUM(order_amount_1d) AS order_amount_30d
FROM dws.dws_trade_user_sku_order_1d
WHERE k1 >= DATE_ADD(DATE('${pdate}'), -29)
GROUP BY 
    user_id, sku_id, k1,
    sku_name, category1_id, category1_name,
    tm_id, tm_name;