这语句如何优化

Question

select r2.area_id, r2.org_name,r1.org_id,  sum( r1.高血压) 高血压,sum( r1.恶性肿瘤) 恶性肿瘤, sum(r1.糖尿病) 糖尿病,sum( r1.重性精神病) 重性精神病
  from (select case
               
                 when s1.org_id is null then
                  (case
                 when s2.org_id is null then
                  (case
                 when s3.org_id is null then
                  s4.org_id
                 else
                  s3.org_id
               end) else s2.org_id end) else s1.org_id
               
               end org_id,
               case
                 when 高血压 is null then
                  0
                 else
                  高血压
               end 高血压,
               case
                 when 恶性肿瘤 is null then
                  0
                 else
                  恶性肿瘤
               end 恶性肿瘤,
               case
                 when 糖尿病 is null then
                  0
                 else
                  糖尿病
               end 糖尿病,
               case
                 when 重性精神病 is null then
                  0
                 else
                  重性精神病
               end 重性精神病
             
          from (select  t3.org_id,  count(t3.org_id) 高血压
                  from chronic_ehr t1, chronic_event t2, chronic_org t3
                 where t1.ssid = t2.ssid
                   and t1.event = t2.event
                   and t3.org_id = t2.org_id and (t1.catalog_code = '0510040001' or t1.catalog_code = '0510030001')
                     and t1.COMMIT_TIME between to_date('2011/1/1','yyyy-mm-dd') and to_date('2012/1/1','yyyy-mm-dd') 
                 group by t3.org_id
                
                ) s1
          full join (select t3.org_id, count(t3.org_id) 恶性肿瘤
                      from chronic_ehr t1, chronic_event t2, chronic_org t3
                     where t1.ssid = t2.ssid
                       and t1.event = t2.event
                       and t3.org_id = t2.org_id
                       and (t1.catalog_code = '0510040005' or  t1.catalog_code = '0510030005') and t1.COMMIT_TIME between to_date('2011/1/1','yyyy-mm-dd') and to_date('2012/1/1','yyyy-mm-dd') 
                     group by t3.org_id
                    
                    ) s2 on s1.org_id = s2.org_id
          full join (select t3.org_id, count(t3.org_id) 糖尿病
                      from chronic_ehr t1, chronic_event t2, chronic_org t3
                     where t1.ssid = t2.ssid
                       and t1.event = t2.event
                       and t3.org_id = t2.org_id
                       and (t1.catalog_code = '0510040002' or t1.catalog_code = '0510030002') and t1.COMMIT_TIME between to_date('2011/1/1','yyyy-mm-dd') and to_date('2012/1/1','yyyy-mm-dd') 
                     group by t3.org_id
                    
                    ) s3 on s2.org_id = s3.org_id
          full join (select t3.org_id, count(t3.org_id) 重性精神病
                      from chronic_ehr t1, chronic_event t2, chronic_org t3
                     where t1.ssid = t2.ssid
                       and t1.event = t2.event
                       and t3.org_id = t2.org_id
                       and (t1.catalog_code = '0510030007' or t1.catalog_code = '0510040007') and t1.COMMIT_TIME between to_date('2011/1/1','yyyy-mm-dd') and to_date('2012/1/1','yyyy-mm-dd') 
                     group by t3.org_id
                    
                    ) s4 on s3.org_id = s4.org_id) r1 left join chronic_org r2 on r2.org_id = r1.org_id where 1=1  AND  r2.AREA_ID in (select t8.area_id from chronic_area_dict t8 start with t8.area_id = '2989bc37-8a31-41aa-9f88-8b168125481f'  connect by prior t8.area_id = t8.parent_code )  group by r2.area_id, r2.org_name,r1.org_id ORDER BY r2.area_id, r1.org_id 

Answer 1

看你的语句，没有执行计划看不出来，看你写的，提出2点建议
1.把这个in（select t8.area_id from chronic_area_dict t8 start with t8.area_id = '2989bc37-8a31-41aa-9f88-8b168125481f' connect by prior t8.area_id = t8.parent_code）的查询(如果返回数据少的话)改为
in(select t8.area_id /*+no_unnest*/ from chronic_area_dict t8 start with t8.area_id = '2989bc37-8a31-41aa-9f88-8b168125481f' connect by prior t8.area_id = t8.parent_code)。
2.你的那个查高血压，恶性肿瘤，糖尿病，重性精神病数量的可以合为一个语句，
 select t3.org_id, 
              sum(case  when  t1.catalog_code = '0510040001' or t1.catalog_code = '0510030001' then 1 else 0 ) end 高血压,
              sum(case  when  t1.catalog_code = '0510040005' or t1.catalog_code = '0510030005' then 1 else 0 ) end 恶性肿瘤,
              sum(case  when  t1.catalog_code = '0510040002' or t1.catalog_code = '0510030002' then 1 else 0 ) end 糖尿病,
              sum(case  when  t1.catalog_code = '0510030007' or t1.catalog_code = '0510040007' then 1 else 0 ) end 重性精神病
              from chronic_ehr t1, chronic_event t2, chronic_org t3
              where t1.ssid = t2.ssid
              and t1.event = t2.event
              and t3.org_id = t2.org_id and (t1.catalog_code = '0510040001' or t1.catalog_code = '0510030001')
              and t1.COMMIT_TIME between to_date('2011/1/1','yyyy-mm-dd') and to_date('2012/1/1','yyyy-mm-dd')  
              group by t3.org_id

Answer 2

上面写错，你把这个结果数量，再连接你的chronic_org不就可以了么，
select t3.org_id,  
  sum(case when t1.catalog_code = '0510040001' or t1.catalog_code = '0510030001' then 1 else 0 ) end 高血压,
  sum(case when t1.catalog_code = '0510040005' or t1.catalog_code = '0510030005' then 1 else 0 ) end 恶性肿瘤,
  sum(case when t1.catalog_code = '0510040002' or t1.catalog_code = '0510030002' then 1 else 0 ) end 糖尿病,
  sum(case when t1.catalog_code = '0510030007' or t1.catalog_code = '0510040007' then 1 else 0 ) end 重性精神病
  from chronic_ehr t1, chronic_event t2, chronic_org t3
  where t1.ssid = t2.ssid
  and t1.event = t2.event
  and t3.org_id = t2.org_id 
  and t1.COMMIT_TIME between to_date('2011/1/1','yyyy-mm-dd') and to_date('2012/1/1','yyyy-mm-dd')   
  group by t3.org_id

Answer 3

我们做的公卫没有包括恶性肿瘤分类……
你的语法太长了，实际根本不可行。
改进建议：
1、s1,s2,s3,s4四类子句可以写为一个查询，也可以做成一个视图V_R1
select t3.org_id,
       sum(decode(t1.catalog_code, '0510040001', 1, '0510030001', 1, 0)) as 高血压,
       sum(decode(t1.catalog_code, '0510040005', 1, '0510030005', 1, 0)) as 恶性肿瘤, --后面的两类同理
  from chronic_ehr t1, chronic_event t2, chronic_org t3
 where t1.ssid = t2.ssid
   and t1.event = t2.event
   and t3.org_id = t2.org_id
   and t1.COMMIT_TIME between to_date('2011/1/1', 'yyyy-mm-dd') and
       to_date('2012/1/1', 'yyyy-mm-dd')
2、where 1=1 去掉
3、子句中的order by 没有作用，即使进行了排序，到外面还是需要进行排序，无端增加工作量
4、把这个也做成视图V_areaIdList
select t8.area_id
          from chronic_area_dict t8
         start with t8.area_id = '2989bc37-8a31-41aa-9f88-8b168125481f'
        connect by prior t8.area_id = t8.parent_code
5、最终你的语句就是：
select r2.area_id,
       r2.org_name,
       r1.org_id,
       sum(nvl(r1.高血压,0)) 高血压,
       sum(nvl(r1.恶性肿瘤,0)) 恶性肿瘤,
       sum(nvl(r1.糖尿病,0)) 糖尿病,
       sum(nvl(r1.重性精神病,0)) 重性精神病
  from V_R1 r1
  left join chronic_org r2 on r2.org_id = r1.org_id
 where r2.AREA_ID in (select area_id from v_areaIdList)
 group by r2.area_id, r2.org_name, r1.org_id
 ORDER BY r2.area_id, r1.org_id