-- ============================================================
-- 2025 / 2026 年 1-8 月累计 货运量、货物周转量 汇总（同名企业口径）
-- 生成日期：2026-09-19   数据库：traffic_audit（MySQL 8.0.29）
-- ------------------------------------------------------------
-- 数据来源表：h2032_enterprise_monthly
--   《道路货物运输月度生产情况表》= 交企统H203-2表，企业级月报（规上企业）
--   字段：freight_total   货运量_总计（吨）
--         turnover_total  货物周转量_总计（吨公里）
--         report_period   报表期，格式 'YYYY-MM'
--         enterprise_name 企业名称
-- 单位换算：表内存的是「吨 / 吨公里」，报表口径是「万吨 / 万吨公里」，
--          所以下面统一 ÷10000。
-- 期间取法：1-8 月 = report_period BETWEEN '2025-01' AND '2025-08'
--          （report_period 是字符串 'YYYY-MM'，直接比大小即可）
-- 为什么要「同名企业口径」：
--   2025 年 1-8 月有 664 家企业、2026 年 1-8 月只有 636 家，
--   直接拿两年总数相减，差额里混着「企业数量变了」的影响，不是真实增减。
--   把两年都报送过的企业名称取交集（实测 545 家），两年的企业范围才一致。
-- 实测结果（同名企业口径 545 家）：
--   2025 年 1-8 月：货运量 30153.14 万吨，周转量 4635340.46 万吨公里
--   2026 年 1-8 月：货运量 27752.68 万吨，周转量 4389882.05 万吨公里
--   同比：货运量 -7.96%，周转量 -5.30%
-- ============================================================


-- =====================================================================
-- 查询 1：汇总一行 —— 同名企业口径的两年累计数 + 同比增速
-- =====================================================================
SELECT COUNT(DISTINCT e.enterprise_name)                                   AS 企业数,
       ROUND(SUM(CASE WHEN e.report_period <= '2025-08'
                      THEN e.freight_total  ELSE 0 END) / 10000, 2)        AS 货运量2025_万吨,
       ROUND(SUM(CASE WHEN e.report_period >= '2026-01'
                      THEN e.freight_total  ELSE 0 END) / 10000, 2)        AS 货运量2026_万吨,
       ROUND((SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END)
            / NULLIF(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total ELSE 0 END), 0) - 1) * 100, 2)
                                                                           AS 货运量同比_百分比,
       ROUND(SUM(CASE WHEN e.report_period <= '2025-08'
                      THEN e.turnover_total ELSE 0 END) / 10000, 2)        AS 周转量2025_万吨公里,
       ROUND(SUM(CASE WHEN e.report_period >= '2026-01'
                      THEN e.turnover_total ELSE 0 END) / 10000, 2)        AS 周转量2026_万吨公里,
       ROUND((SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END)
            / NULLIF(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END), 0) - 1) * 100, 2)
                                                                           AS 周转量同比_百分比
FROM h2032_enterprise_monthly e
-- 下面两个 JOIN：只要两年 1-8 月都报送过的企业（同名企业口径的核心）
JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
       WHERE report_period BETWEEN '2025-01' AND '2025-08') a
  ON a.enterprise_name = e.enterprise_name
JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
       WHERE report_period BETWEEN '2026-01' AND '2026-08') b
  ON b.enterprise_name = e.enterprise_name
WHERE e.report_period BETWEEN '2025-01' AND '2026-08';


-- =====================================================================
-- 查询 2：逐企业对照明细（同名企业，两年并排），可直接导出 Excel
--   按 2026 年货运量从大到小排；去掉 ORDER BY 就是完整 545 行。
-- =====================================================================
SELECT e.enterprise_name                                                   AS 企业名称,
       MAX(CASE WHEN e.report_period >= '2026-01' THEN e.region_code END)  AS 地区代码,
       ROUND(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total  ELSE 0 END) / 10000, 2) AS 货运量2025_万吨,
       ROUND(SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total  ELSE 0 END) / 10000, 2) AS 货运量2026_万吨,
       ROUND((SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END)
            - SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total ELSE 0 END)) / 10000, 2) AS 货运量增减_万吨,
       ROUND((SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total ELSE 0 END)
            / NULLIF(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total ELSE 0 END), 0) - 1) * 100, 2) AS 货运量同比_百分比,
       ROUND(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END) / 10000, 2) AS 周转量2025_万吨公里,
       ROUND(SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END) / 10000, 2) AS 周转量2026_万吨公里,
       ROUND((SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END)
            - SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END)) / 10000, 2) AS 周转量增减_万吨公里,
       ROUND((SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END)
            / NULLIF(SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END), 0) - 1) * 100, 2) AS 周转量同比_百分比
FROM h2032_enterprise_monthly e
JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
       WHERE report_period BETWEEN '2025-01' AND '2025-08') a
  ON a.enterprise_name = e.enterprise_name
JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
       WHERE report_period BETWEEN '2026-01' AND '2026-08') b
  ON b.enterprise_name = e.enterprise_name
WHERE e.report_period BETWEEN '2025-01' AND '2026-08'
GROUP BY e.enterprise_name
ORDER BY 货运量2026_万吨 DESC;


-- =====================================================================
-- 查询 3：三种口径对照 —— 看清楚「全量」和「同名」差在哪
--   ALL_2025 / ALL_2026   : 各自年份的全部企业
--   SAME_2025 / SAME_2026 : 同名企业口径（545 家）
-- =====================================================================
SELECT t.scope_type                            AS 口径,
       COUNT(DISTINCT t.enterprise_name)       AS 企业数,
       ROUND(SUM(t.freight_total)  / 10000, 2) AS 货运量_万吨,
       ROUND(SUM(t.turnover_total) / 10000, 2) AS 周转量_万吨公里
FROM (
  SELECT 'ALL_2025' AS scope_type, enterprise_name, freight_total, turnover_total
    FROM h2032_enterprise_monthly WHERE report_period BETWEEN '2025-01' AND '2025-08'
  UNION ALL
  SELECT 'ALL_2026', enterprise_name, freight_total, turnover_total
    FROM h2032_enterprise_monthly WHERE report_period BETWEEN '2026-01' AND '2026-08'
  UNION ALL
  SELECT 'SAME_2025', e.enterprise_name, e.freight_total, e.turnover_total
    FROM h2032_enterprise_monthly e
    JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
           WHERE report_period BETWEEN '2025-01' AND '2025-08') a ON a.enterprise_name = e.enterprise_name
    JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
           WHERE report_period BETWEEN '2026-01' AND '2026-08') b ON b.enterprise_name = e.enterprise_name
   WHERE e.report_period BETWEEN '2025-01' AND '2025-08'
  UNION ALL
  SELECT 'SAME_2026', e.enterprise_name, e.freight_total, e.turnover_total
    FROM h2032_enterprise_monthly e
    JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
           WHERE report_period BETWEEN '2025-01' AND '2025-08') a ON a.enterprise_name = e.enterprise_name
    JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
           WHERE report_period BETWEEN '2026-01' AND '2026-08') b ON b.enterprise_name = e.enterprise_name
   WHERE e.report_period BETWEEN '2026-01' AND '2026-08'
) t
GROUP BY t.scope_type
ORDER BY t.scope_type;


-- =====================================================================
-- 查询 4：差异企业名单（核对用）—— 只在一年报送的企业及其累计量
--   only_in = 2025 / 2026
--   实测：2025 年独有 119 家、2026 年独有 91 家（1-8 月口径）
-- =====================================================================
SELECT '2025' AS only_in, e.enterprise_name AS 企业名称,
       ROUND(SUM(e.freight_total) / 10000, 2) AS 货运量_万吨,
       ROUND(SUM(e.turnover_total) / 10000, 2) AS 周转量_万吨公里
FROM h2032_enterprise_monthly e
LEFT JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
            WHERE report_period BETWEEN '2026-01' AND '2026-08') b
       ON b.enterprise_name = e.enterprise_name
WHERE e.report_period BETWEEN '2025-01' AND '2025-08' AND b.enterprise_name IS NULL
GROUP BY e.enterprise_name
UNION ALL
SELECT '2026', e.enterprise_name,
       ROUND(SUM(e.freight_total) / 10000, 2),
       ROUND(SUM(e.turnover_total) / 10000, 2)
FROM h2032_enterprise_monthly e
LEFT JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
            WHERE report_period BETWEEN '2025-01' AND '2025-08') a
       ON a.enterprise_name = e.enterprise_name
WHERE e.report_period BETWEEN '2026-01' AND '2026-08' AND a.enterprise_name IS NULL
GROUP BY e.enterprise_name
ORDER BY only_in, 货运量_万吨 DESC;


-- =====================================================================
-- 查询 5：分市州「同名企业口径」两年累计对照
--   地区代码取 2026 年那条记录的 region_code
-- =====================================================================
SELECT CASE
         WHEN region_code LIKE '4201%' THEN '武汉' WHEN region_code LIKE '4202%' THEN '黄石'
         WHEN region_code LIKE '4203%' THEN '十堰' WHEN region_code LIKE '4205%' THEN '宜昌'
         WHEN region_code LIKE '4206%' THEN '襄阳' WHEN region_code LIKE '4207%' THEN '鄂州'
         WHEN region_code LIKE '4208%' THEN '荆门' WHEN region_code LIKE '4209%' THEN '孝感'
         WHEN region_code LIKE '4210%' THEN '荆州' WHEN region_code LIKE '4211%' THEN '黄冈'
         WHEN region_code LIKE '4212%' THEN '咸宁' WHEN region_code LIKE '4213%' THEN '随州'
         WHEN region_code LIKE '4228%' THEN '恩施' WHEN region_code LIKE '429004%' THEN '仙桃'
         WHEN region_code LIKE '429005%' THEN '潜江' WHEN region_code LIKE '429006%' THEN '天门'
         WHEN region_code LIKE '429021%' THEN '林区' ELSE '其他' END           AS 市州,
       COUNT(DISTINCT d.enterprise_name)                                       AS 同名企业数,
       ROUND(SUM(d.freight_2025) / 10000, 2)                                   AS 货运量2025_万吨,
       ROUND(SUM(d.freight_2026) / 10000, 2)                                   AS 货运量2026_万吨,
       ROUND(SUM(d.turnover_2025) / 10000, 2)                                  AS 周转量2025_万吨公里,
       ROUND(SUM(d.turnover_2026) / 10000, 2)                                  AS 周转量2026_万吨公里
FROM (
  SELECT e.enterprise_name,
         MAX(CASE WHEN e.report_period >= '2026-01' THEN e.region_code END)    AS region_code,
         SUM(CASE WHEN e.report_period <= '2025-08' THEN e.freight_total  ELSE 0 END) AS freight_2025,
         SUM(CASE WHEN e.report_period >= '2026-01' THEN e.freight_total  ELSE 0 END) AS freight_2026,
         SUM(CASE WHEN e.report_period <= '2025-08' THEN e.turnover_total ELSE 0 END) AS turnover_2025,
         SUM(CASE WHEN e.report_period >= '2026-01' THEN e.turnover_total ELSE 0 END) AS turnover_2026
  FROM h2032_enterprise_monthly e
  JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
         WHERE report_period BETWEEN '2025-01' AND '2025-08') a ON a.enterprise_name = e.enterprise_name
  JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
         WHERE report_period BETWEEN '2026-01' AND '2026-08') b ON b.enterprise_name = e.enterprise_name
  WHERE e.report_period BETWEEN '2025-01' AND '2026-08'
  GROUP BY e.enterprise_name
) d
GROUP BY 市州
ORDER BY 货运量2026_万吨 DESC;


-- =====================================================================
-- 查询 6：数据体检 —— 同名企业里月份不足 8 个月的（累计数会偏小，比对时注意）
--   正常：2025 年 1-8 月 8 条 + 2026 年 1-8 月 8 条
--   实测：无异常企业，全部 545 家两年各 8 个月齐全
-- =====================================================================
SELECT e.enterprise_name AS 企业名称,
       SUM(CASE WHEN e.report_period BETWEEN '2025-01' AND '2025-08' THEN 1 ELSE 0 END) AS 记录数2025,
       SUM(CASE WHEN e.report_period BETWEEN '2026-01' AND '2026-08' THEN 1 ELSE 0 END) AS 记录数2026
FROM h2032_enterprise_monthly e
JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
       WHERE report_period BETWEEN '2025-01' AND '2025-08') a ON a.enterprise_name = e.enterprise_name
JOIN (SELECT DISTINCT enterprise_name FROM h2032_enterprise_monthly
       WHERE report_period BETWEEN '2026-01' AND '2026-08') b ON b.enterprise_name = e.enterprise_name
WHERE e.report_period BETWEEN '2025-01' AND '2026-08'
GROUP BY e.enterprise_name
HAVING 记录数2025 <> 8 OR 记录数2026 <> 8;