From af8cab142a6b15c06e131a8474574dd5b00df982 Mon Sep 17 00:00:00 2001
From: wlzboy <66905212@qq.com>
Date: 星期四, 04 十二月 2025 22:09:58 +0800
Subject: [PATCH] feat: 改造微信accesstoken存放在系统配置表中

---
 sql/test_mileage_stats_optimization.sql |  110 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 files changed, 110 insertions(+), 0 deletions(-)

diff --git a/sql/test_mileage_stats_optimization.sql b/sql/test_mileage_stats_optimization.sql
new file mode 100644
index 0000000..d169520
--- /dev/null
+++ b/sql/test_mileage_stats_optimization.sql
@@ -0,0 +1,110 @@
+-- ========================================
+-- 娴嬭瘯閲岀▼缁熻浼樺寲鏁堟灉
+-- 鐢ㄩ�旓細瀵规瘮浼樺寲鍓嶅悗鐨勬�ц兘鍜屽噯纭��
+-- ========================================
+
+-- 1. 鏌ョ湅鏌愪釜杞﹁締鏌愬ぉ鐨勫垎娈垫暟鎹畉ask_id瑕嗙洊鐜�
+-- 鏇挎崲鍙傛暟锛欯vehicleId, @statDate
+SET @vehicleId = 1;  -- 鏇挎崲涓哄疄闄呰溅杈咺D
+SET @statDate = '2025-12-04';  -- 鏇挎崲涓哄疄闄呮棩鏈�
+
+SELECT 
+    '鍒嗘缁熻' as '绫诲瀷',
+    COUNT(*) as '鎬诲垎娈垫暟',
+    SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) as '鏈変换鍔D鐨勫垎娈垫暟',
+    CONCAT(ROUND(SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%') as 'task_id瑕嗙洊鐜�'
+FROM tb_vehicle_gps_segment_mileage
+WHERE vehicle_id = @vehicleId
+  AND DATE(segment_start_time) = @statDate;
+
+-- 2. 瀵规瘮涓ょ璁＄畻鏂瑰紡鐨勭粨鏋�
+-- 鏂瑰紡A锛氫紭鍖栨柟妗堬紙鐩存帴鎸塼ask_id鑱氬悎锛�
+SELECT 
+    '浼樺寲鏂规' as '璁＄畻鏂瑰紡',
+    SUM(segment_distance) as '鎬婚噷绋�',
+    SUM(CASE WHEN task_id IS NOT NULL THEN segment_distance ELSE 0 END) as '浠诲姟閲岀▼',
+    SUM(CASE WHEN task_id IS NULL THEN segment_distance ELSE 0 END) as '闈炰换鍔￠噷绋�',
+    CONCAT(ROUND(SUM(CASE WHEN task_id IS NOT NULL THEN segment_distance ELSE 0 END) * 100.0 / 
+           NULLIF(SUM(segment_distance), 0), 2), '%') as '浠诲姟鍗犳瘮'
+FROM tb_vehicle_gps_segment_mileage
+WHERE vehicle_id = @vehicleId
+  AND DATE(segment_start_time) = @statDate;
+
+-- 鏂瑰紡B锛氬綋鍓嶇粺璁¤〃涓殑鏁版嵁锛堝彲鑳戒娇鐢ㄤ簡鏃堕棿閲嶅彔璁＄畻锛�
+SELECT 
+    '缁熻琛ㄦ暟鎹�' as '璁＄畻鏂瑰紡',
+    total_mileage as '鎬婚噷绋�',
+    task_mileage as '浠诲姟閲岀▼',
+    non_task_mileage as '闈炰换鍔￠噷绋�',
+    CONCAT(ROUND(task_ratio * 100, 2), '%') as '浠诲姟鍗犳瘮'
+FROM tb_vehicle_mileage_stats
+WHERE vehicle_id = @vehicleId
+  AND DATE(stat_date) = @statDate;
+
+-- 3. 鏌ョ湅浣跨敤浼樺寲鏂规鐨勮溅杈嗘暟閲忥紙task_id瑕嗙洊鐜� > 80%锛�
+SELECT 
+    DATE(segment_start_time) as '鏃ユ湡',
+    vehicle_id,
+    vehicle_no,
+    COUNT(*) as '鎬诲垎娈垫暟',
+    SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) as '鏈塼ask_id鍒嗘鏁�',
+    CONCAT(ROUND(SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%') as '瑕嗙洊鐜�',
+    CASE 
+        WHEN SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) > COUNT(*) * 0.8 
+        THEN '鉁� 浣跨敤浼樺寲鏂规' 
+        ELSE '脳 浣跨敤闄嶇骇鏂规' 
+    END as '鏂规閫夋嫨'
+FROM tb_vehicle_gps_segment_mileage
+WHERE segment_start_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
+GROUP BY DATE(segment_start_time), vehicle_id, vehicle_no
+HAVING COUNT(*) > 10  -- 鍙粺璁″垎娈垫暟澶т簬10鐨�
+ORDER BY DATE(segment_start_time) DESC, vehicle_id
+LIMIT 20;
+
+-- 4. 缁熻鏁翠綋浼樺寲鏁堟灉
+SELECT 
+    '鏁翠綋缁熻' as '缁熻绫诲瀷',
+    COUNT(DISTINCT CONCAT(vehicle_id, '_', DATE(segment_start_time))) as '杞﹁締-鏃ユ湡缁勫悎鎬绘暟',
+    SUM(CASE WHEN task_coverage > 0.8 THEN 1 ELSE 0 END) as '鍙娇鐢ㄤ紭鍖栨柟妗堢殑鏁伴噺',
+    CONCAT(ROUND(SUM(CASE WHEN task_coverage > 0.8 THEN 1 ELSE 0 END) * 100.0 / 
+           COUNT(DISTINCT CONCAT(vehicle_id, '_', DATE(segment_start_time))), 2), '%') as '浼樺寲瑕嗙洊鐜�'
+FROM (
+    SELECT 
+        vehicle_id,
+        DATE(segment_start_time) as stat_date,
+        SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) * 1.0 / COUNT(*) as task_coverage
+    FROM tb_vehicle_gps_segment_mileage
+    WHERE segment_start_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
+    GROUP BY vehicle_id, DATE(segment_start_time)
+    HAVING COUNT(*) > 10
+) coverage_stats;
+
+-- 5. 鎬ц兘娴嬭瘯锛氭墽琛屾椂闂村姣�
+-- 璇存槑锛氫娇鐢‥XPLAIN ANALYZE鏌ョ湅鎵ц璁″垝锛圡ySQL 8.0+锛夋垨浣跨敤BENCHMARK鍑芥暟
+
+-- 浼樺寲鏂规鏌ヨ锛堢洿鎺ヨ仛鍚堬級
+EXPLAIN 
+SELECT 
+    vehicle_id,
+    SUM(CASE WHEN task_id IS NOT NULL THEN segment_distance ELSE 0 END) as task_mileage,
+    SUM(CASE WHEN task_id IS NULL THEN segment_distance ELSE 0 END) as non_task_mileage
+FROM tb_vehicle_gps_segment_mileage
+WHERE vehicle_id = @vehicleId
+  AND DATE(segment_start_time) = @statDate
+GROUP BY vehicle_id;
+
+-- 6. 寤鸿鐨勪紭鍖栨帾鏂�
+SELECT 
+    '浼樺寲寤鸿' as '绫诲瀷',
+    '琛ュ厖鍘嗗彶鏁版嵁鐨則ask_id' as '鎺柦1',
+    '纭繚瀹氭椂浠诲姟姝ｅ父杩愯' as '鎺柦2',
+    '纭繚浠诲姟鐘舵�佹纭洿鏂�' as '鎺柦3',
+    '瀹氭湡妫�鏌ask_id瑕嗙洊鐜�' as '鎺柦4';
+
+-- ========================================
+-- 浣跨敤璇存槑锛�
+-- 1. 璁剧疆鍙傛暟锛氫慨鏀圭7-8琛岀殑@vehicleId鍜孈statDate
+-- 2. 鎵ц鏌ヨ1-4鏌ョ湅浼樺寲鏁堟灉
+-- 3. 濡傛灉瑕嗙洊鐜囦綆浜�80%锛屾墽琛宖ix_segment_mileage_task_association.sql淇
+-- 4. 閲嶆柊缁熻锛岃瀵熸�ц兘鎻愬崌
+-- ========================================

--
Gitblit v1.9.1