From bc1f1fe60d4ce20de619275806483a5d0b07f939 Mon Sep 17 00:00:00 2001 From: zhizhijie <zhizhijie@users.noreply.gitee.com> Date: 星期五, 04 九月 2026 20:33:53 +0800 Subject: [PATCH] \ feat: 汇总大表生成支持toMonth裁剪（1-6月生成+1-7月扩列）及验证产物与测试报告\ --- docs/生成汇总大表/规下货运量口径_2026-09-04.sql | 94 +++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 94 insertions(+), 0 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" new file mode 100644 index 0000000..626ad61 --- /dev/null +++ "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" @@ -0,0 +1,94 @@ +-- ============================================================ +-- 瑙勪笅璐ц繍閲� / 瑙勪笅鍛ㄨ浆閲� 璁＄畻鍙ｅ緞锛�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 涓囧惃锛� +-- ============================================================ + +-- ===================================================================== +-- 鏌ヨ 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 '姝︽眽' AS 甯傚窞, '姝︽眽甯�' AS 琛ㄥ唴鍩庡競鍚� 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.琛ㄥ唴鍩庡競鍚� +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 甯傚窞, + 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 甯傚窞 +) gs ON gs.甯傚窞 = m.甯傚窞; + +-- ===================================================================== +-- 鏌ヨ 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 鍏ㄧ渷瑙勪笂璐ц繍閲廮涓囧惃, + (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 鍏ㄧ渷鍏ㄥ彛寰勫懆杞噺_涓囧惃鍏噷; + +-- ===================================================================== +-- 鏌ヨ 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