Oracle数据库SQL语句优化

Question

如何优化这个SQL语句：
分析：它在这里查询时花时间比较大【ROW_NUMBER() OVER (PARTITION BY 施工单编码 ORDER BY CASE WHEN INSTR(a.新网格, 区域) > 0 then 2 else 1 end DESC,LENGTH(小区关键字) DESC) AS 去重参考】；我应该如何优化下面的SQL语句呢；求各位支下招；非常感谢。

WITH table_xd AS(
 SELECT * FROM install_form_view WHERE 地市 = '梧' AND 产品 = '宽带' AND 所属WG IS NOT NULL
),
table_a AS (
  SELECT a.*,
  CASE 
       WHEN INSTR(a.区县, '山') > 0 THEN '蒙山'
       WHEN INSTR(a.区县, '藤') > 0 THEN '藤山'
       WHEN INSTR(a.区县, '溪') > 0 THEN '溪山'
       WHEN INSTR(a.区县, '苍') > 0 OR INSTR(a.区县, '龙') > 0 THEN '苍龙'
       WHEN INSTR(a.区县, '长洲') > 0 OR INSTR(a.区县, '万') > 0 THEN '城区'
       ELSE '其它'
    END AS 区域,
  CASE WHEN a.所属WG='网格信息为空' then b.营销网格 else a.所属WG end AS 新网格,
  CASE WHEN LENGTH(b.小区)>0 THEN b.小区 ELSE a.小区 end AS 小区关键字
  FROM table_xd a
  left JOIN (SELECT * FROM wz_basic_info_view GROUP BY 资源标识,小区,CUID,标准地址,营销网格) b ON a.标准地址 LIKE '%' || b.小区 || '%'
),
table_b AS (
  SELECT a.*,
  ROW_NUMBER() OVER (PARTITION BY 施工单编码 ORDER BY CASE WHEN INSTR(a.新网格, 区域) > 0 then 2 else 1 end DESC,LENGTH(小区关键字) DESC) AS 去重参考,
  CASE 
        WHEN a.新网格 = '新圩网格' THEN 2
        WHEN a.新网格 = '陈塘网格' THEN 3
        WHEN a.新网格 = '大隆网格' OR a.新网格 = '水汶网格' OR a.新网格 = '文圩网格' THEN 4
        WHEN a.新网格 = '大黎网格' OR a.新网格 = '琅南网格' OR a.新网格 = '象棋网格' THEN 5
        WHEN a.新网格 = '塘步网格' OR a.新网格 = '糯安网格' OR a.新网格 = '筋诚网格' OR a.新网格 = '岭京网格' THEN 6
        WHEN a.新网格 = '金鸡网格' OR a.新网格 = '三塘网格' THEN 7
        WHEN a.新网格 ='归义网格' THEN 9
        WHEN a.新网格 = '城区富民网格' OR a.新网格 = '城区中山网格' OR a.新网格 = '濛江网格' OR a.新网格 = '南马网格' THEN 10
        WHEN a.新网格 = '旺木网格' OR a.新网格 = '县城网格' THEN 11
        WHEN a.新网格 = '城区三云网格' OR a.新网格 = '大广新网格' THEN 12
        WHEN a.新网格 ='河西网格' THEN 13
        WHEN a.新网格 = '太平网格'OR a.新网格 = '新县城网格' THEN 14
        WHEN a.新网格 = '城区新兴网格'OR a.新网格 = '城区长洲网格' THEN 15
        WHEN a.新网格 = '城区新兴三网格' OR a.新网格 = '城东网格' THEN 17
        WHEN a.新网格 ='河东网格' THEN 18
        WHEN a.新网格 ='城西网格' THEN 19
        WHEN a.新网格 ='县城网格' THEN 22
        ELSE 0
    END AS 日归档目标值,
  CASE 
      WHEN a.新网格 = '新圩网格' then 70 
    WHEN a.新网格 = '陈塘网格' then 89 
    WHEN a.新网格 = '旺木网格' then 126
    WHEN a.新网格 = '文圩网格' then 128
    WHEN a.新网格 = '大黎网格' then 132
    WHEN a.新网格 = '象棋网格' then 159
    WHEN a.新网格 = '琅南网格' then 163
    WHEN a.新网格 = '筋诚网格' then 168
    WHEN a.新网格 = '糯安网格' then 170
    WHEN a.新网格 = '大隆网格' OR a.新网格 = '水汶网格' then 174
    WHEN a.新网格 = '塘步网格' then 179
    WHEN a.新网格 = '三塘网格' then 181
    WHEN a.新网格 = '归义网格' then 182
    WHEN a.新网格 = '城区中山网格' OR a.新网格 = '金鸡网格' then 199
    WHEN a.新网格 = '南马网格' then 202
    WHEN a.新网格 = '岭京网格' then 210
    WHEN a.新网格 = '城区富民网格' then 230
    WHEN a.新网格 = '大广新网格' then 241
    WHEN a.新网格 = '濛江网格' then 279
    WHEN a.新网格 = '县城网格' then 303
    WHEN a.新网格 = '河西网格' then 331
    WHEN a.新网格 = '城区长洲网格' then 332
    WHEN a.新网格 = '城区新兴网格' then 420
    WHEN a.新网格 = '太平网格' then 437
    WHEN a.新网格 = '河东网格' then 443
    WHEN a.新网格 = '新县城网格' then 471
    WHEN a.新网格 = '城区三云网格' then 476
    WHEN a.新网格 = '城区新兴三网格' then 550
    WHEN a.新网格 = '城东网格' then 568
    WHEN a.新网格 = '县城网格' then 584
    WHEN a.新网格 = '城西网格' then 616
       ELSE 0 
  END AS 月归档目标值
  FROM table_a a
),
table_tj AS(
 SELECT 区域,新网格 AS 所属WG,
  SUM(CASE 
        WHEN $[to_char(受理时间, 'YYYY-MM-DD') = '${结束时间}']
              AND 工单类型 = '装' AND 产品 = '宽带' AND 校园='否'
      -- 匹配业务号码
      AND 业务号码 NOT IN(
        SELECT 业务号码 FROM table_b WHERE 去重参考 = 1 AND $[to_char(受理时间, 'YYYY-MM-DD') >= '${开始时间}'] AND $[to_char(受理时间, 'YYYY-MM-DD') < '${结束时间}']           
       )
      THEN 1 ELSE 0 
 END) AS 当日下单,
  SUM(CASE 
        WHEN $[to_char(归档时间, 'YYYY-MM-DD') = '${结束时间}']
              AND 工单类型 = '装' AND 产品 = '宽带' AND 校园='否'AND 状态='已归档' THEN 1 ELSE 0 
 END) AS 当日竣工,
  日归档目标值,
  SUM(CASE WHEN 产品 = '宽带' AND 工单类型 = '装' AND 状态 NOT IN ('已归档','已撤单','已退单') then 1 else 0 end) AS 在途工单,
  SUM(CASE 
    WHEN $[to_char(受理时间, 'YYYY-MM-DD') >= '${开始时间}'] AND $[to_char(受理时间, 'YYYY-MM-DD') < '${结束时间}']
        AND 工单类型 = '装' AND 产品 = '宽带' AND 校园 = '否'
        THEN 1 ELSE 0 
    END) AS 当月下单,
  SUM(CASE 
        WHEN $[to_char(归档时间, 'YYYY-MM-DD') >= '${开始时间}'] AND $[to_char(归档时间, 'YYYY-MM-DD') <= '${结束时间}']
              AND 工单类型 = '装' AND 产品 = '宽带' AND 校园='否' AND 状态='已归档' THEN 1 ELSE 0 
 END) AS 当月竣工,
  月归档目标值
  FROM table_b WHERE 去重参考 = 1
  GROUP BY 区域,新网格,日归档目标值,月归档目标值
)
SELECT 区域,所属WG,当日下单,当日竣工,日归档目标值, 
CASE WHEN main."当日竣工" <> 0 THEN ROUND(main."当日竣工"/NULLIF(main."日归档目标值",0),2) else 0 end AS 日完成率,
在途工单,当月下单,当月竣工,月归档目标值,
CASE WHEN main."当月竣工" <> 0 THEN ROUND(main."当月竣工"/NULLIF(main."月归档目标值",0),2) else 0 end AS 月完成率,
CASE WHEN main."当月竣工" <> 0 THEN ROUND(main."当月竣工"/EXTRACT(DAY FROM SYSDATE),0) else 0 end AS 当月日均竣工
FROM table_tj main WHERE LENGTH(所属WG)>0 
ORDER BY 
  CASE 区域
    WHEN '城区' THEN 1
    WHEN '' THEN 2
    WHEN '' THEN 3
    WHEN '' THEN 4
    WHEN '' THEN 5
    ELSE 6
  END

Answer 1

参考通义千问和郭老师的小迷弟雅思莫了-编写提供，主要是为了帮助您快速解决问题，如下回答，您可以参考下，如有问题，评论区留言。

针对您提供的SQL语句，主要优化点可能在于减少计算量和提高查询效率。以下是部分优化建议：

1. 简化CASE WHEN：对于table_a中的区域判断和table_b中的新网格判断，可以考虑在数据库层面进行预处理或者建立辅助表，将区县与对应的区域、新网格与目标值预先关联好，避免每次执行SQL时进行大量字符串函数操作。

2. 索引优化：确保install_form_view、wz_basic_info_view等表的相关字段（如地市、产品、区县、标准地址、营销网格等）有合适的索引，特别是JOIN和ORDER BY涉及的字段。

3. ROW_NUMBER() OVER PARTITION BY：这个窗口函数可能导致性能瓶颈，因为它需要对每个施工单编码进行排序。如果数据量较大，可能会很耗时。尝试找出是否真的需要去重参考，并尽量提前过滤不必要的记录以减少参与排序的行数。

4. 减少子查询和临时表：尽可能合并或减少WITH语句中的临时表，比如可以把一些不需要多次引用的计算结果直接放入主查询中。

5. 日期范围条件：使用区间条件而非逐个比较，例如：

   AND to_char(受理时间, 'YYYY-MM-DD') BETWEEN '${开始时间}' AND '${结束时间}'
   

6. 适当增加硬件资源或调整数据库参数：如果上述优化无法满足需求，考虑提升数据库服务器性能或调整相关数据库参数以适应这种复杂查询。

7. 业务逻辑优化：分析业务需求，看是否有更高效的实现方式，例如是否可以通过后台定时任务预处理一部分数据，减轻实时查询的压力。

由于没有实际的数据和环境进行测试，以上建议仅供参考，具体优化方案还需根据实际情况进行调整和验证。