进阶查询与多表操作

-- stations(station_id, name, city)
-- chargers(charger_id, station_id, power_kw, status)
-- charge_sessions(session_id, charger_id, started_at, kwh, amount)
-- 数据与上篇一致，此处不再重复建表语句

-- 每个站：收入、单量、参与充电的桩清单（一行内拼接）
SELECT s.name,
       SUM(cs.amount)                       AS revenue,
       COUNT(DISTINCT cs.charger_id)        AS used_chargers,
       GROUP_CONCAT(DISTINCT cs.charger_id ORDER BY cs.charger_id) AS charger_list
FROM stations s
JOIN chargers c  ON c.station_id = s.station_id
JOIN charge_sessions cs ON cs.charger_id = c.charger_id
GROUP BY s.station_id, s.name;

SELECT s.city, c.power_kw >= 100 AS is_fast, SUM(cs.amount) AS revenue
FROM charge_sessions cs
JOIN chargers c ON cs.charger_id = c.charger_id
JOIN stations s ON c.station_id = s.station_id
GROUP BY s.city, is_fast WITH ROLLUP;

-- 高于全网平均单价的订单（标量子查询，算一次）
SELECT session_id, amount
FROM charge_sessions
WHERE amount > (SELECT AVG(amount) FROM charge_sessions);

-- 高于本桩历史均价的订单（关联子查询，外层每行比一次）
SELECT cs.session_id, cs.charger_id, cs.amount
FROM charge_sessions cs
WHERE cs.amount > (
    SELECT AVG(cs2.amount)
    FROM charge_sessions cs2
    WHERE cs2.charger_id = cs.charger_id
);

-- 每站收入排名，先在子查询里把每站收入算好，再接站点名
SELECT s.name, t.revenue
FROM stations s
JOIN (
    SELECT c.station_id, SUM(cs.amount) AS revenue
    FROM charge_sessions cs
    JOIN chargers c ON cs.charger_id = c.charger_id
    GROUP BY c.station_id
) t ON t.station_id = s.station_id
ORDER BY t.revenue DESC;

-- 有过故障桩的站点
SELECT s.name
FROM stations s
WHERE EXISTS (
    SELECT 1 FROM chargers c
    WHERE c.station_id = s.station_id AND c.status = 'fault'
);

-- 没有任何订单的桩（NOT EXISTS，不踩 NOT IN 的 NULL 陷阱）
SELECT c.charger_id
FROM chargers c
WHERE NOT EXISTS (
    SELECT 1 FROM charge_sessions cs WHERE cs.charger_id = c.charger_id
);

SELECT station, charger_id, revenue,
       ROW_NUMBER() OVER (PARTITION BY station ORDER BY revenue DESC) AS rn
FROM (
    SELECT s.name AS station, cs.charger_id, SUM(cs.amount) AS revenue
    FROM charge_sessions cs
    JOIN chargers c  ON cs.charger_id = c.charger_id
    JOIN stations s  ON c.station_id = s.station_id
    GROUP BY s.name, cs.charger_id
) t;
-- 外面再套一层 WHERE rn = 1 即得"每站冠军"

-- 环比：和前一天比
LAG(revenue)     OVER (ORDER BY d) AS prev_day,
revenue - LAG(revenue) OVER (ORDER BY d) AS day_diff,
-- 累计与移动平均
SUM(revenue) OVER (ORDER BY d) AS running_total,
AVG(revenue) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7,
-- 分桶与组内占比
NTILE(4) OVER (ORDER BY revenue DESC) AS quartile,
revenue / SUM(revenue) OVER (PARTITION BY station) AS share
-- 命名窗口复用：WINDOW w AS (PARTITION BY ... ORDER BY ...) 后各函数 OVER w

WITH daily AS (
    SELECT DATE(cs.started_at) AS d, SUM(cs.amount) AS revenue
    FROM charge_sessions cs
    GROUP BY DATE(cs.started_at)
)
SELECT d, revenue,
       LAG(revenue) OVER (ORDER BY d) AS prev_day
FROM daily;

WITH RECURSIVE calendar AS (
    SELECT DATE('2026-09-01') AS d          -- 锚点：起点
    UNION ALL
    SELECT d + INTERVAL 1 DAY               -- 递归：在上一天基础上 +1 天
    FROM calendar
    WHERE d < '2026-09-30'                  -- 递归终止条件
)
SELECT cal.d, COALESCE(t.revenue, 0) AS revenue
FROM calendar cal
LEFT JOIN (
    SELECT DATE(started_at) AS d, SUM(amount) AS revenue
    FROM charge_sessions GROUP BY DATE(started_at)
) t ON t.d = cal.d
ORDER BY cal.d;

-- 报错：MySQL 不允许 WHERE 里出现窗口函数
SELECT charger_id, revenue
FROM t
WHERE ROW_NUMBER() OVER (ORDER BY revenue DESC) = 1;