播面

WITH base_data AS (
    -- 1. 提取小时并划分时段
    SELECT 
        user_id,
        order_id,
        gmv,
        HOUR(order_time) AS order_hour,
        CASE 
            WHEN HOUR(order_time) >= 0 AND HOUR(order_time) < 6 THEN '深夜(0-6)'
            WHEN HOUR(order_time) >= 6 AND HOUR(order_time) < 12 THEN '上午(6-12)'
            WHEN HOUR(order_time) >= 12 AND HOUR(order_time) < 18 THEN '下午(12-18)'
            WHEN HOUR(order_time) >= 18 AND HOUR(order_time) < 24 THEN '晚上(18-24)'
            ELSE '未知'
        END AS time_interval
    FROM t_order_detail
),
interval_summary AS (
    -- 2. 计算各时段的聚合指标
    SELECT 
        time_interval,
        COUNT(DISTINCT user_id) AS interval_user_cnt,
        COUNT(DISTINCT order_id) AS interval_order_cnt,
        SUM(gmv) AS interval_gmv
    FROM base_data
    GROUP BY time_interval
),
global_summary AS (
    -- 3. 计算全天的全局指标（注意：用户数需全局去重）
    SELECT 
        COUNT(DISTINCT user_id) AS global_user_cnt,
        COUNT(DISTINCT order_id) AS global_order_cnt,
        SUM(gmv) AS global_gmv
    FROM base_data
)
-- 4. 关联计算占比并格式化输出
SELECT 
    i.time_interval,
    i.interval_user_cnt AS user_cnt,
    i.interval_order_cnt AS order_cnt,
    i.interval_gmv AS total_gmv,
    -- 占比计算，转换为百分比格式
    CONCAT(ROUND(i.interval_user_cnt * 100.0 / g.global_user_cnt, 2), '%') AS user_ratio,
    CONCAT(ROUND(i.interval_order_cnt * 100.0 / g.global_order_cnt, 2), '%') AS order_ratio,
    CONCAT(ROUND(i.interval_gmv * 100.0 / g.global_gmv, 2), '%') AS gmv_ratio
FROM interval_summary i
CROSS JOIN global_summary g
ORDER BY 
    CASE i.time_interval
        WHEN '深夜(0-6)' THEN 1
        WHEN '上午(6-12)' THEN 2
        WHEN '下午(12-18)' THEN 3
        WHEN '晚上(18-24)' THEN 4
        ELSE 5
    END;