-- ============================================================
|
-- 规下货运量/周转量口径（2026-09-04 业务确认）
|
-- 公式（分市州、逐月同式）：
|
-- 规上货运量(万吨) = SUM(h2032_enterprise_monthly.freight_total) / 10000 -- h2032 单位为吨
|
-- 全口径货运量(万吨) = freight_turnover_import.freight_m{MM} -- 该表已是万吨
|
-- 规下货运量(万吨) = 全口径 - 规上
|
-- 周转量同式：h2032.turnover_total(吨公里)/10000 = 规上(万吨公里)；
|
-- freight_turnover_import.turnover_m{MM} = 全口径(万吨公里)；
|
-- 规下 = 全口径 - 规上
|
-- 全省校验：17 市州求和 = 库内"湖北省"行 = 母版汇总表" 货运"页(2026-07: O5=16285.71, O7=3754.78, O9=12530.93)
|
-- 注意单位：跨表相减前 h2032 必须先 /10000，否则差 4 个数量级。
|
-- ============================================================
|
|
-- Q1 分市州：规上 / 全口径 / 规下（货运量+周转量），2026-07 示例
|
SELECT m.city, ft.freight_m07 AS total_freight_wan,
|
ROUND(gs.gs_freight_wan, 4) AS gs_freight_wan,
|
ROUND(ft.freight_m07 - gs.gs_freight_wan, 4) AS below_freight_wan,
|
ft.turnover_m07 AS total_turnover_wan,
|
ROUND(gs.gs_turnover_wan, 4) AS gs_turnover_wan,
|
ROUND(ft.turnover_m07 - gs.gs_turnover_wan, 4) AS below_turnover_wan
|
FROM (
|
SELECT '武汉' city, '武汉市' full_name UNION ALL SELECT '黄石','黄石市'
|
UNION ALL SELECT '十堰','十堰市' UNION ALL SELECT '宜昌','宜昌市'
|
UNION ALL SELECT '襄阳','襄阳市' UNION ALL SELECT '鄂州','鄂州市'
|
UNION ALL SELECT '荆门','荆门市' UNION ALL SELECT '孝感','孝感市'
|
UNION ALL SELECT '荆州','荆州市' UNION ALL SELECT '黄冈','黄冈市'
|
UNION ALL SELECT '咸宁','咸宁市' UNION ALL SELECT '随州','随州市'
|
UNION ALL SELECT '恩施','恩施州' UNION ALL SELECT '仙桃','仙桃市'
|
UNION ALL SELECT '潜江','潜江市' UNION ALL SELECT '天门','天门市'
|
UNION ALL SELECT '林区','林区'
|
) m
|
LEFT JOIN freight_turnover_import ft
|
ON ft.report_period = '2026-07' AND ft.region_name = m.full_name
|
LEFT JOIN (
|
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 '林区' END AS city,
|
SUM(freight_total) / 10000 AS gs_freight_wan,
|
SUM(turnover_total) / 10000 AS gs_turnover_wan
|
FROM h2032_enterprise_monthly
|
WHERE report_period = '2026-07'
|
GROUP BY city
|
) gs ON gs.city = m.city;
|
|
-- Q2 全省校验：与库内"湖北省"行及母版 O5/O7/O9 对账
|
SELECT
|
(SELECT ROUND(SUM(freight_total)/10000, 4) FROM h2032_enterprise_monthly
|
WHERE report_period='2026-07' AND (region_code LIKE '42%' OR region_code LIKE '4290%')) AS gs_freight_hb_wan,
|
(SELECT ROUND(SUM(turnover_total)/10000, 4) FROM h2032_enterprise_monthly
|
WHERE report_period='2026-07' AND (region_code LIKE '42%' OR region_code LIKE '4290%')) AS gs_turnover_hb_wan,
|
(SELECT freight_m07 FROM freight_turnover_import
|
WHERE report_period='2026-07' AND region_name='湖北省') AS total_freight_hb_wan,
|
(SELECT turnover_m07 FROM freight_turnover_import
|
WHERE report_period='2026-07' AND region_name='湖北省') AS total_turnover_hb_wan;
|
|
-- Q3 可选：把货运量拆分回填到 scale_split_transport（仅当月口径 MONTHLY 行适用；
|
-- 现有 2026-07 数据是 CUMULATIVE 累计行，需要 1-7 月逐月求和后再回填，暂不执行）
|
-- UPDATE scale_split_transport s
|
-- JOIN (Q1 分市州派生结果) d ON d.city = 市州短名
|
-- SET s.above_scale_freight = d.gs_freight_wan,
|
-- s.below_scale_freight = d.below_freight_wan,
|
-- s.total_freight = d.total_freight_wan
|
-- WHERE s.report_period='2026-07' AND s.period_type='MONTHLY';
|
