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.