分組統計SQL - leohahah－IT工程師數位筆記本

文章出處

Itpub上遇到一個求助寫SQL的帖子，感覺很有意思，于是寫出來看看，要求如下：

有個計劃表1， 記錄物料的年度計劃量
有個實際使用情況表2，記錄實際使用情況。
最后要出個統計表，把計劃和實際的數據結合到一起進行統計，
有特殊的情況就是可能部分物料有計劃但是沒有實際使用，有的物料無計劃但是有實際使用情況，
要把這些數據合并到一起按照季度進行統計并算差異量，能實現嗎？？

于是建表插數據，表結構如下：

create table plan(OBJ varchar2(10),YEAR number(4),PLAN_COUNT number(10));
create table actual(OBJ varchar2(10),YEAR number(4),SEASON number(10),USED number(10));

語句如下：

with t1 as (
select distinct a.obj,p.plan_count,a.year,
case when a.season=1 then sum(a.used) over (partition by a.obj,a.season) else null end as s1,
case when a.season=2 then sum(a.used) over (partition by a.obj,a.season) else null end as s2,
case when a.season=3 then sum(a.used) over (partition by a.obj,a.season) else null end as s3,
case when a.season=4 then sum(a.used) over (partition by a.obj,a.season) else null end as s4,
sum(a.used) over (partition by a.obj) as sum_count 
from actual a left join plan p on a.obj=p.obj order by 1,4
)
select temp.obj,nvl(temp.plan_count,0),temp.year,temp.q1,temp.q2,temp.q3,temp.q4,nvl(temp.sum_count,0),(nvl(temp.plan_count,0)-nvl(temp.sum_count,0)) as diff 
from(
select distinct t1.obj,t1.plan_count,t1.year,
sum(s1) over (partition by t1.obj) as Q1,
sum(s2) over (partition by t1.obj) as Q2,
sum(s3) over (partition by t1.obj) as Q3,
sum(s4) over (partition by t1.obj) as Q4,t1.sum_count from t1
union all
select pl.obj,pl.plan_count,pl.year,null,null,null,null,null from plan pl where pl.obj not in (select distinct obj from actual)
) temp 
order by 1

用到了nvl,case when,not in,over partition,sum,union,left join,with as等寫法，常年寫SQL的應該有更加效率的寫法，有待發現之后補充到此進行完善。

發現一個更簡明的寫法：

select 
distinct case when a.obj is not null then a.obj else p.obj end as obj,  
nvl(p.plan_count,0) as plan_count,
sum(case when a.season=1 then a.used end) as Q1,
sum(case when a.season=2 then a.used end) as Q2,
sum(case when a.season=3 then a.used end) as Q3,
sum(case when a.season=4 then a.used end) as Q4,
sum(nvl(a.used,0)) as sum_season,
nvl(p.plan_count,0)-sum(nvl(a.used,0)) as diff 
from plan p full join actual a on p.obj=a.obj
group by case when a.obj is not null then a.obj else p.obj end,nvl(p.plan_count,0)
order by 1

文章列表