播面

WITH temp_order_period AS (
    SELECT 
        order_id,
        user_id,
        gmv,
        -- 提取小时数
        HOUR(order_time) AS order_hour
    FROM t_order
),
temp_period_mapped AS (
    SELECT 
        order_id,
        user_id,
        gmv,
        CASE 
            WHEN order_hour >= 0 AND order_hour < 6   THEN '深夜'
            WHEN order_hour >= 6 AND order_hour < 12  THEN '上午'
            WHEN order_hour >= 12 AND order_hour < 18 THEN '下午'
            WHEN order_hour >= 18 AND order_hour < 24 THEN '晚上'
            ELSE '未知'
        END AS time_period
    FROM temp_order_period
)
SELECT 
    time_period,
    -- 1. 下单用户数（去重）
    COUNT(DISTINCT user_id) AS active_user_count,
    -- 2. 下单订单数
    COUNT(order_id) AS total_order_count,
    -- 3. 各时段 GMV
    SUM(gmv) AS period_gmv,
    -- 4. 占比计算：当前时段GMV / 总GMV（利用窗口函数避免二次关联）
    ROUND(SUM(gmv) / SUM(SUM(gmv)) OVER() * 100, 2) AS gmv_ratio_pct
FROM temp_period_mapped
GROUP BY time_period
ORDER BY 
    CASE time_period
        WHEN '深夜' THEN 1
        WHEN '上午' THEN 2
        WHEN '下午' THEN 3
        WHEN '晚上' THEN 4
        ELSE 5
    END;