各位大神幫忙看下如何優化下面陳述句
select a.區域 ,
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-30,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-394,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "6/22",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-29,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-393,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "6/23",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-28,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-392,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "6/24",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-27,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-391,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "6/25",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-26,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-390,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "6/26",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-25,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-389,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "6/27",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-24,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-388,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "6/28",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-23,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-387,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "6/29",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-22,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-386,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "6/30",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-21,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-385,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/1",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-20,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-384,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/2",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-19,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-383,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/3",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-18,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-382,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/4",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-17,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-381,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/5",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-16,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-380,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/6",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-15,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-379,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/7",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-14,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-378,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/8",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-13,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-377,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/9",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-12,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-376,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/10",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-11,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-375,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/11",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-10,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-374,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/12",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-9,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-373,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/13",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-8,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-372,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/14",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-7,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-371,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/15",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-6,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-370,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/16",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-5,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-369,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/17",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-4,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-368,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/18",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-3,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-367,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/19",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-2,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-366,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/20",
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-1,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)/
sum(case when to_char(a.日期,'yyyymmdd') = to_char(sysdate-365,'yyyymmdd') then a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop end)-1 as "7/21"
from daily_sales_temp a
where a.區域 in ('A-華北','B-華東','C-華南')
group by a.區域
order by a.區域
uj5u.com熱心網友回復：
每天資料很大？可以考慮設計調整一下，比如采用磁區表
還可以考慮物化視圖
另外要是允許的話，可以利用晚上的時間每天統計當天的資料到一個匯總表
uj5u.com熱心網友回復：
daily_sales_temp 的資料量有多大，是否還有其他的條件控制，比如年份，是否存在合理的索引，是否可用物化視圖定期（如每天）重繪uj5u.com熱心網友回復：
是不是可以簡化一下：with t1 as (select a.區域,trunc(sysdate-a.日期) xh,sum(a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop) sum_v from daily_sales_temp a
where a.區域 in ('A-華北','B-華東','C-華南') and a.日期 between sysdate-30 and sysdate-1 group by a.區域,trunc(sysdate-a.日期)),
t2 as (select a.區域,trunc(sysdate-a.日期) xh,sum(a.tddv大家電自營fbp+a.tddv中小件自營fbp+a.tddvpop) sum_v from daily_sales_temp a
where a.區域 in ('A-華北','B-華東','C-華南') and a.日期 between sysdate-394 and sysdate-365 group by a.區域,trunc(sysdate-a.日期))
select t1.區域,t1.sum_v/t2.sum_v-1 from t1,t2 where t2.區域=t1.區域 and t2.xh=t1.xh+364 order by t1.區域,t1.xh;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/115042.html
標籤：開發
下一篇：sde服務啟動后停止