#MySQL 8.0 新特性

-- 传统聚合：减少行数
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;
-- 结果：每个用户一行

-- 窗口函数：不减少行数
SELECT
    order_no,
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id) as user_total
FROM orders;
-- 结果：每行都有，同时返回该用户的总金额

-- 场景 1：按用户查订单，同时计算累计金额
SELECT
    order_no,
    created_at,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_amount
FROM orders;

-- 场景 2：查每个用户的订单排名
SELECT
    order_no,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank
FROM orders;

-- 场景 3：查环比增长
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) as growth
FROM monthly_sales;

-- 递归查询：查询组织架构
WITH RECURSIVE org_tree AS (
    -- 基础查询：CEO
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归查询：下属
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;

-- 替代方案：多层嵌套子查询（难读、难维护）

-- 普通 CTE：替代嵌套子查询
WITH active_users AS (
    SELECT id, name FROM users WHERE status = 1
),
recent_orders AS (
    SELECT * FROM orders WHERE created_at >= '2024-01-01'
)
SELECT u.name, COUNT(o.id) as order_count
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- MySQL 8.0 支持 Hash Join
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;

-- 执行计划显示：
-- "Nested loop hash join"

-- JSON_TABLE：把 JSON 转为表
SELECT *
FROM JSON_TABLE(
    '[{"name":"Tom","age":25}, {"name":"Jerry","age":30}]',
    '$[*]' COLUMNS (
        name VARCHAR(50) PATH '$.name',
        age INT PATH '$.age'
    )
) as t;

-- JSON_VALUE：提取 JSON 字段值
SELECT JSON_VALUE(extra, '$.phone') as phone FROM users;

-- 查每个月的销售额，以及环比增长率
WITH monthly AS (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') as month,
        SUM(amount) as revenue
    FROM orders
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) as prev_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) /
        LAG(revenue) OVER (ORDER BY month) * 100, 2
    ) as growth_rate
FROM monthly
ORDER BY month;

-- MySQL 8.0 支持 Spatial Reference ID (SRID)
-- 支持更精确的地理计算
SELECT ST_Distance_Sphere(
    ST_GeomFromText('POINT(116.4074 39.9042)', 4326),  -- 北京
    ST_GeomFromText('POINT(121.4737 31.2304)', 4326)   -- 上海
) as distance_meters;