Oracle行列转换应用

在Oracle中，关于行列转换的一个应用

表结构略，SQL如下：

select z.* from (select row_number() over(order by bs_cnt desc) rn, inn2.* 
from (select distinct sum(bv_sum) over(partition by department_code)+sum(sv_sum) over(partition by department_code) bsv_sum, 
sum(bv_sum) over(partition by department_code)-sum(sv_sum) over(partition by department_code) net_amount, 
sum(bv_sum) over(partition by department_code) bv_sum, sum(sv_sum) over(partition by department_code) sv_sum, 
sum(bs_cnt) over(partition by department_code) bs_cnt, sum(b_cnt) over(partition by department_code) b_cnt, 
sum(s_cnt) over(partition by department_code) s_cnt, department_code,department_name 
from (select nvl(sum(buy_value),0) bv_sum, nvl(sum(sale_value),0) sv_sum, 
count(trade_type) bs_cnt, decode(trade_type,'B',count(trade_type),0) b_cnt,  decode(trade_type,'S',count(trade_type),0) s_cnt, department_code,department_name 
from (select distinct t.department_code,t.department_name,t.buy_value,t.sale_value,t.tradedate,t.trade_type  
from mv_stk_trans_info t where to_char(t.tradedate,'yyyy-mm-dd')<=to_char(sysdate,'yyyy-mm-dd') 
and to_char(t.tradedate,'yyyy-mm-dd')>=to_char(sysdate-30,'yyyy-mm-dd') and t.sec_code='300231') inn 
group by department_code,department_name,trade_type) inn1 
order by bs_cnt desc) inn2 ) z where z.rn<=10 

图如下，1-4图为转换过程，主要功能是按trade_type在某个时段内针对某支股票出现次数最多的各证券部的成交总额，买卖总额，买卖次数，重点在buy_value，sale_value，trade_type统计聚合汇总，生成新列bs_cnt，b_cnt，s_cnt，bv_sum，sv_sum，bsv_sum，对应SQL语句是从内到外逐渐生成。

最后一张图是按倒序后取的数据。

1.

2.

3.

4.