citu_vanna/output/sql_validation_20250623_221222_summary.txt

SQL验证报告
==================================================

输入文件: output\qs_highway_db_20250623_192120_pair.json
验证时间: 2025-06-23T22:12:22.207844
验证耗时: 213.11秒

验证结果摘要:
  总SQL数量: 50
  有效SQL: 46
  无效SQL: 4
  成功率: 92.00%
  平均耗时: 0.075秒
  重试次数: 0

SQL修复统计:
  尝试修复: 5
  修复成功: 1
  修复失败: 4
  修复成功率: 20.00%

原始文件修改统计:
  修改的SQL: 1
  删除的无效项: 4
  修改失败: 0

错误详情（共4个）:
==================================================

1. 问题: 车辆数量与订单量相关性分析（关联车流数据）
   错误: 函数 round(double precision, integer) 不存在
HINT:  没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
   LLM修复尝试: 失败
   修复失败原因: LLM修复失败或返回空结果
   完整SQL:
   SELECT a.oper_date, a.order_sum AS 订单数量, b.customer_count AS 车辆数量, ROUND(CORR(a.order_sum, b.customer_count),2) AS 相关系数 FROM (SELECT oper_date, SUM(order_sum) AS order_sum FROM bss_business_day_data WHERE delete_ts IS NULL GROUP BY oper_date) a JOIN (SELECT count_date, SUM(customer_count) AS customer_count FROM bss_car_day_count GROUP BY count_date) b ON a.oper_date = b.count_date GROUP BY a.oper_date;
   ----------------------------------------

2. 问题: 预测未来一周各服务区基于历史车流与消费数据的消费趋势（使用线性回归）
   错误: 函数 pg_catalog.extract(unknown, integer) 不存在
HINT:  没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
   LLM修复尝试: 失败
   修复失败原因: LLM修复失败或返回空结果
   完整SQL:
   SELECT service_area_id AS 服务区ID, date, REGR_INTERCEPT(pay_sum, day_num) + REGR_SLOPE(pay_sum, day_num) * EXTRACT(EPOCH FROM date)/86400 AS 预测消费 FROM (SELECT c.service_area_id, b.oper_date AS date, EXTRACT(EPOCH FROM b.oper_date - CURRENT_DATE + 7) AS day_num, b.pay_sum FROM bss_car_day_count c JOIN bss_service_area_mapper m ON c.service_area_id = m.service_area_id AND m.delete_ts IS NULL JOIN bss_business_day_data b ON m.service_name = b.service_name AND c.count_date = b.oper_date WHERE b.oper_date BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE AND c.delete_ts IS NULL AND b.delete_ts IS NULL) sub GROUP BY service_area_id, date;
   ----------------------------------------

3. 问题: 分析不同档口类型（餐饮/零售/其他）的消费转化率与车流密度（车流量/营业面积）的关系
   错误: 字段 b.branch_type 不存在
HINT:  也许您想要引用列"t.branch_type"。
   LLM修复尝试: 失败
   修复失败原因: 字段 "c.customer_count" 必须出现在 GROUP BY 子句中或者在聚合函数中使用
   完整SQL:
   SELECT b.branch_type AS 档口类型, c.customer_count / NULLIF(s.area,0) AS 车流密度, SUM(b.order_sum)/SUM(c.customer_count) AS 转化率 FROM bss_business_day_data b JOIN (SELECT branch_name, CASE WHEN branch_name LIKE '%餐饮%' THEN '餐饮' WHEN branch_name LIKE '%零售%' THEN '零售' ELSE '其他' END AS branch_type FROM bss_business_day_data GROUP BY branch_name) t ON b.branch_name = t.branch_name JOIN bss_service_area_mapper m ON b.service_name = m.service_name AND m.delete_ts IS NULL JOIN bss_car_day_count c ON m.service_area_id = c.service_area_id AND b.oper_date = c.count_date JOIN (SELECT id, (RANDOM()*1000+500)::INT AS area FROM bss_service_area) s ON m.service_area_id = s.id WHERE b.delete_ts IS NULL AND c.delete_ts IS NULL GROUP BY ROLLUP(branch_type);
   ----------------------------------------

4. 问题: 各区域管理公司单位能耗产出对比（需结合能耗表）
   错误: 对于表"e",丢失FROM子句项
   LLM修复尝试: 失败
   修复失败原因: 关系 "energy_consumption_table" 不存在
   完整SQL:
   SELECT c.company_name AS 公司名称,
       ROUND(SUM(b.pay_sum)/SUM(e.energy_consumption), 2) AS 单位能耗产出
FROM bss_company c
JOIN bss_service_area sa ON c.id = sa.company_id
JOIN bss_business_day_data b ON sa.service_area_no = b.service_no
-- 假设存在能耗表 energy_consumption_table e
-- JOIN energy_consumption_table e ON sa.id = e.service_area_id
WHERE b.oper_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY c.company_name
ORDER BY 单位能耗产出 DESC;
   ----------------------------------------

成功修复的SQL（共1个）:
==================================================

1. 问题: 各服务区不同数据源支付总额差异分析
   原始错误: 字段关联 "service_name" 是不明确的
   修复后SQL:
   SELECT sa.service_name AS 服务区名称, bdd.source_type AS 数据源类型, SUM(bdd.pay_sum) AS 支付总额 FROM bss_business_day_data bdd JOIN bss_service_area_mapper sa ON bdd.service_no = sa.service_no WHERE bdd.delete_ts IS NULL GROUP BY sa.service_name, bdd.source_type HAVING SUM(bdd.pay_sum) > 10000 ORDER BY 支付总额 DESC LIMIT 20;
   ----------------------------------------