From d36f11239d8e8407c0b224f7f19792efc84080bd Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期五, 04 九月 2026 11:12:43 +0800
Subject: [PATCH] docs: 汇总表/SQL 的库表名改写为业务中文表名（保留库表名仅作程序对照）
---
docs/生成汇总大表/规下货运量口径_2026-09-04.sql | 110 +++++++++++++++++++++++++++++++++---------------------
1 files changed, 67 insertions(+), 43 deletions(-)
diff --git "a/docs/\347\224\237\346\210\220\346\261\207\346\200\273\345\244\247\350\241\250/\350\247\204\344\270\213\350\264\247\350\277\220\351\207\217\345\217\243\345\276\204_2026-09-04.sql" "b/docs/\347\224\237\346\210\220\346\261\207\346\200\273\345\244\247\350\241\250/\350\247\204\344\270\213\350\264\247\350\277\220\351\207\217\345\217\243\345\276\204_2026-09-04.sql"
index 1ae6468..626ad61 100644
--- "a/docs/\347\224\237\346\210\220\346\261\207\346\200\273\345\244\247\350\241\250/\350\247\204\344\270\213\350\264\247\350\277\220\351\207\217\345\217\243\345\276\204_2026-09-04.sql"
+++ "b/docs/\347\224\237\346\210\220\346\261\207\346\200\273\345\244\247\350\241\250/\350\247\204\344\270\213\350\264\247\350\277\220\351\207\217\345\217\243\345\276\204_2026-09-04.sql"
@@ -1,25 +1,41 @@
-- ============================================================
--- 瑙勪笅璐ц繍閲�/鍛ㄨ浆閲忓彛寰勶紙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 涓暟閲忕骇銆�
+-- 瑙勪笅璐ц繍閲� / 瑙勪笅鍛ㄨ浆閲� 璁＄畻鍙ｅ緞锛�2026-09-04 涓氬姟纭锛�
+-- ------------------------------------------------------------
+-- 鐢ㄥ埌鍝簺琛紙涓枃鍚� = 鐢ㄦ埛骞虫椂鍙殑鍚嶅瓧锛屾嫭鍙峰唴鏄▼搴忛噷瀛樼殑琛ㄥ悕锛屼竴鑸笉鐢ㄧ锛夛細
+-- 鈶犮�婇亾璺揣鐗╄繍杈撴湀搴︾敓浜ф儏鍐佃〃銆�= 浜や紒缁烪203-2琛紝浼佷笟绾ф湀鎶�
+-- 锛堝簱琛ㄥ悕 h2032_enterprise_monthly锛夆�斺�旈噷闈㈢殑浼佷笟閮芥槸鈥滆涓婁紒涓氣�濓紝
+-- 鎶婂悇甯傚窞鎵�鏈変紒涓氱殑鈥滆揣杩愰噺銆佽揣鐗╁懆杞噺鈥濆姞璧锋潵锛屽氨鏄�滆涓娾�濆悎璁°��
+-- 娉ㄦ剰锛氳琛ㄧ殑璐ц繍閲忓崟浣嶆槸鈥滃惃鈥濄�佸懆杞噺鍗曚綅鏄�滃惃鍏噷鈥濓紝
+-- 鑰屾姤琛ㄥ崟浣嶆槸鈥滀竾鍚ㄣ�佷竾鍚ㄥ叕閲屸�濓紝鎵�浠ヨ闄や互 10000銆�
+-- 鈶°�婅揣杩愰噺鍛ㄨ浆閲忚〃銆嬶紙搴撹〃鍚� freight_turnover_import锛夆�斺�斿叏鐪佸拰鍚勫競宸炵殑
+-- 鈥滃叏鍙ｅ緞鈥濊揣杩愰噺銆佸懆杞噺锛堣涓�+瑙勪笅閮界畻鍦ㄥ唴锛夛紝鍗曚綅宸茬粡鏄�滀竾鍚�/涓囧惃鍏噷鈥濓紝
+-- 閲岄潰杩樻湁涓�琛屸�滄箹鍖楃渷鈥濈敤鏉ユ牳瀵瑰叏鐪佸悎璁★紝浠ュ強鍘诲勾鍚屾湀鍒楋紙last_*锛夈��
+-- 鈶€�婅涓婅涓嬫媶鍒嗚〃銆嬶紙搴撹〃鍚� scale_split_transport锛夆�斺�旀妸鎷嗗垎缁撴灉瀛樹笅鏉ョ殑琛�
+-- 锛堣涓�/瑙勪笅/鍚堣锛屽惈鎺掑悕銆佸悓姣旓級銆傜洰鍓嶅鍏ョ殑鎷嗗垎妯℃澘鍙湁鈥滃懆杞噺鈥濓紝
+-- 鎵�浠ヨ琛ㄥ懆杞噺涓夊垪鏈夋暟銆佽揣杩愰噺涓夊垪涓虹┖锛涜揣杩愰噺鎷嗗垎濡傞渶钀借〃锛�
+-- 鐢ㄦ煡璇� 3锛堥粯璁や笉鎵ц锛夊洖濉��
+-- 鏍稿績鍏紡锛堝垎甯傚窞銆佹瘡涓湀閮戒竴鏍凤級锛�
+-- 瑙勪笂璐ц繍閲�(涓囧惃) = 鈶犺〃璐ц繍閲忓悎璁� 梅 10000
+-- 鍏ㄥ彛寰勮揣杩愰噺(涓囧惃) = 鈶¤〃褰撴湀璐ц繍閲�
+-- 瑙勪笅璐ц繍閲�(涓囧惃) = 鍏ㄥ彛寰� 鈭� 瑙勪笂
+-- 鍛ㄨ浆閲忓悓鐞嗭紙瑙勪笅鍛ㄨ浆閲� = 鈶¤〃褰撴湀鍛ㄨ浆閲� 鈭� 鈶犺〃鍛ㄨ浆閲忓悎璁� 梅 10000锛�
+-- 鍏ㄧ渷鏍￠獙锛�17 涓競宸炵浉鍔� = 鈥滄箹鍖楃渷鈥濊 = 姹囨�昏〃銆娾�� 璐ц繍鈥濋〉銆嬪叏鐪佹暟
+-- 锛�2026-07锛氳揣杩愰噺 16285.71 涓囧惃銆佽涓� 3754.78 涓囧惃銆佽涓� 12530.93 涓囧惃锛�
-- ============================================================
--- 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
+-- =====================================================================
+-- 鏌ヨ 1锛氬垎甯傚窞 鈥滆涓� / 鍏ㄥ彛寰� / 瑙勪笅鈥� 璐ц繍閲忋�佸懆杞噺锛�2026-07 绀轰緥锛�
+-- 鎶� '2026-07' 鎹㈡垚鍒殑鏈堜唤锛屾妸 m07 鎹㈡垚瀵瑰簲鏈堝彿锛坢01~m12锛夊嵆鍙煡鍏朵粬鏈堛��
+-- =====================================================================
+SELECT m.甯傚窞,
+ ft.freight_m07 AS 鍏ㄥ彛寰勮揣杩愰噺_涓囧惃,
+ ROUND(gs.瑙勪笂璐ц繍閲廮涓囧惃, 4) AS 瑙勪笂璐ц繍閲廮涓囧惃,
+ ROUND(ft.freight_m07 - gs.瑙勪笂璐ц繍閲廮涓囧惃, 4) AS 瑙勪笅璐ц繍閲廮涓囧惃,
+ ft.turnover_m07 AS 鍏ㄥ彛寰勫懆杞噺_涓囧惃鍏噷,
+ ROUND(gs.瑙勪笂鍛ㄨ浆閲廮涓囧惃鍏噷, 4) AS 瑙勪笂鍛ㄨ浆閲廮涓囧惃鍏噷,
+ ROUND(ft.turnover_m07 - gs.瑙勪笂鍛ㄨ浆閲廮涓囧惃鍏噷, 4) AS 瑙勪笅鍛ㄨ浆閲廮涓囧惃鍏噷
FROM (
- SELECT '姝︽眽' city, '姝︽眽甯�' full_name UNION ALL SELECT '榛勭煶','榛勭煶甯�'
+ SELECT '姝︽眽' AS 甯傚窞, '姝︽眽甯�' AS 琛ㄥ唴鍩庡競鍚� UNION ALL SELECT '榛勭煶','榛勭煶甯�'
UNION ALL SELECT '鍗佸牥','鍗佸牥甯�' UNION ALL SELECT '瀹滄槍','瀹滄槍甯�'
UNION ALL SELECT '瑗勯槼','瑗勯槼甯�' UNION ALL SELECT '閯傚窞','閯傚窞甯�'
UNION ALL SELECT '鑽嗛棬','鑽嗛棬甯�' UNION ALL SELECT '瀛濇劅','瀛濇劅甯�'
@@ -29,8 +45,8 @@
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 freight_turnover_import ft -- 鈶°�婅揣杩愰噺鍛ㄨ浆閲忚〃銆嬶細鍏ㄥ彛寰勬�婚噺鏈堟姤
+ ON ft.report_period = '2026-07' AND ft.region_name = m.琛ㄥ唴鍩庡競鍚�
LEFT JOIN (
SELECT CASE
WHEN region_code LIKE '4201%' THEN '姝︽眽' WHEN region_code LIKE '4202%' THEN '榛勭煶'
@@ -41,30 +57,38 @@
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
+ WHEN region_code LIKE '429021%' THEN '鏋楀尯' END AS 甯傚窞,
+ SUM(freight_total) / 10000 AS 瑙勪笂璐ц繍閲廮涓囧惃, -- 鈶犺〃鈥滆揣杩愰噺鈥濆悎璁★紝鍚�10000鈫掍竾鍚�
+ SUM(turnover_total) / 10000 AS 瑙勪笂鍛ㄨ浆閲廮涓囧惃鍏噷 -- 鈶犺〃鈥滃懆杞噺鈥濆悎璁★紝鍚ㄥ叕閲屆�10000鈫掍竾鍚ㄥ叕閲�
+ FROM h2032_enterprise_monthly -- 鈶犮�婇亾璺揣鐗╄繍杈撴湀搴︾敓浜ф儏鍐佃〃銆嬶紙H203-2锛岃涓婁紒涓氾級
WHERE report_period = '2026-07'
- GROUP BY city
-) gs ON gs.city = m.city;
+ GROUP BY 甯傚窞
+) gs ON gs.甯傚窞 = m.甯傚窞;
--- Q2 鍏ㄧ渷鏍￠獙锛氫笌搴撳唴"婀栧寳鐪�"琛屽強姣嶇増 O5/O7/O9 瀵硅处
+-- =====================================================================
+-- 鏌ヨ 2锛氬叏鐪佹牎楠岋紙鐪嬬▼搴忕畻鐨勫叏鐪佹暟瀵逛笉瀵癸級
+-- 鏈熸湜鍊� = 2026-07锛氳涓婅揣杩愰噺 3754.78锛涜涓婂懆杞噺 597278.64锛�
+-- 鍏ㄥ彛寰勮揣杩愰噺 16285.71锛堟瘝鐗� O5锛夛紱鍏ㄥ彛寰勫懆杞噺 2181925.71銆�
+-- =====================================================================
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;
+ (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 鍏ㄧ渷瑙勪笂璐ц繍閲廮涓囧惃,
+ (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 鍏ㄧ渷瑙勪笂鍛ㄨ浆閲廮涓囧惃鍏噷,
+ (SELECT freight_m07 FROM freight_turnover_import -- 鈶¤〃鈥滄箹鍖楃渷鈥濊
+ WHERE report_period='2026-07' AND region_name='婀栧寳鐪�') AS 鍏ㄧ渷鍏ㄥ彛寰勮揣杩愰噺_涓囧惃,
+ (SELECT turnover_m07 FROM freight_turnover_import -- 鈶¤〃鈥滄箹鍖楃渷鈥濊
+ WHERE report_period='2026-07' AND region_name='婀栧寳鐪�') AS 鍏ㄧ渷鍏ㄥ彛寰勫懆杞噺_涓囧惃鍏噷;
--- 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';
\ No newline at end of file
+-- =====================================================================
+-- 鏌ヨ 3锛堝彲閫夛紝榛樿涓嶆墽琛岋級锛氭妸鈥滆揣杩愰噺鎷嗗垎鈥濆洖濉埌銆婅涓婅涓嬫媶鍒嗚〃銆�
+-- 娉ㄦ剰锛氳琛ㄧ幇鍦ㄥ彧鏈夆�滅疮璁★紙CUMULATIVE锛夆�濆彛寰勭殑鍛ㄨ浆閲忚锛�
+-- 鍥炲～璐ц繍閲忛渶瑕佸厛鎯虫竻妤氭槸鈥滃綋鏈堚�濊繕鏄��1-鏈堢疮璁♀�濆彛寰勶紝
+-- 绱鍙ｅ緞瑕佸厛鎶� 1~7 鏈堥�愭湀绠楀嚭鏉ュ啀鍔犳�汇�傜‘璁ゅ彛寰勫悗鍐嶆斁寮�娉ㄩ噴鎵ц銆�
+-- =====================================================================
+-- UPDATE scale_split_transport -- 鈶€�婅涓婅涓嬫媶鍒嗚〃銆�
+-- JOIN (鏌ヨ 1 鐨勭粨鏋�) d ON d.甯傚窞 = 甯傚窞鍚�
+-- SET above_scale_freight = d.瑙勪笂璐ц繍閲廮涓囧惃,
+-- below_scale_freight = d.瑙勪笅璐ц繍閲廮涓囧惃,
+-- total_freight = d.鍏ㄥ彛寰勮揣杩愰噺_涓囧惃
+-- WHERE report_period='2026-07' AND period_type='MONTHLY';
\ No newline at end of file
--
Gitblit v1.9.1