使用CrossTab製作樞紐分析表(一)

最近工作上需要針對財務資料表進行樞紐(Pivot)分析，亦即要作多維度分析，將日期變成欄位，並以會計科目為列，比較多個期別的財務表現，含兩個表 -- 會計科目資料表(account_profile)、總帳資料表(account_data)， 測試資料附於文末。


設計SQL指令分為兩步驟:
1. 將上圖兩個資料表連結(join)，取出相關資料
SELECT i2.datekey::integer As row_name,
            i1.account_code::text As col_name,
            i2.account_amount::double precision As cell_value
        FROM tejdb.account_profile i1
        JOIN tejdb.account_data As i2
        ON (i1.account_code = i2.account_code)
        where  i1.account_code <=6060 and  i1.account_code >=6000
        order by i2.datekey, i1.account_code
       測試是否可正確查詢到資料.
2. 將上述SQL包在crosstab函數內，以產生樞紐分析表
SELECT output_table1.*  FROM crosstab(
SELECT output_table1.*  FROM crosstab(
    'SELECT
        i1.account_code::text As col_name,
        i2.datekey::integer As row_name,
        i2.account_amount::double precision As cell_value
    FROM tejdb.account_profile i1
    JOIN tejdb.account_data As i2
    ON (i1.account_code = i2.account_code)
    where  i1.account_code <=6060 and  i1.account_code >=6000
    order by i1.account_code, i2.datekey'
) As output_table1(account_code text, f_20050601 double precision, f_20050701 double precision);
其中 output_table1 為輸出資料表名稱，可自取，另外須設定輸出資料的格式如上一行。
上述方法已經可以輸出結果，但是有三個問題

1. 如果有些日期的某些科目沒有值，這時顯示會有錯誤，系統會將數值誤值至其他科目。
2. 通常日期會有不定個數，上述方法只顯示兩期(f_20050601, f_20050701)，但資料其實不只兩期，我們需先執行 select distinct datekey' from account_data 才能知道有多少期。
3. 每個個案的日期個數不同，甚至資料表不同，我們每次都要改寫crosstab嗎? 能不能寫成一個通用的function呢?

且看下回分解。
以下為測試資料
會計科目資料表: account_profile
6000;"Salaries"
6020;"Payroll Taxes"
6040;"Employee Benefits"
總帳資料表: account_data
20060501;6020;2945
20080401;6000;76797
20070301;6040;5154
20060301;6000;20460
20051101;6040;1432
20060801;6020;7353
20050801;6000;20020
20060701;6040;8283
20070701;6000;86112
20051001;6000;21840
20080501;6040;8255
20070101;6020;10713
20080601;6000;69069
20061001;6020;7952
20070101;6000;87009
20051101;6020;2046
20080601;6020;8504
20060101;6040;2470
20060501;6000;28704
20061201;6020;7854
20070401;6040;8025
20060601;6040;2648
20070601;6040;7952
20070501;6040;7854
20060201;6020;2178
20080201;6020;7880
20060201;6040;1782
20071001;6040;6626
20061201;6040;6872
20070901;6020;7009
20061001;6000;96876
20080201;6000;76797
20080301;6040;5342
20060801;6000;89580
20070201;6020;9314
20051201;6040;2310
20060101;6020;2964
20060601;6020;2913
20070701;6020;7952
20061101;6020;8639
20060401;6040;2284
20050801;6020;2402
20070801;6040;3894
20051201;6020;2079
20051001;6020;1747
20071101;6040;5778
20080301;6020;7632
20070701;6040;6185
20080401;6020;7880
20050901;6040;2178
20071201;6040;6939
20070901;6040;3504
20061201;6000;95680
20060701;6000;80730
20070201;6000;82524
20080101;6040;7657
20050701;6000;22080
20060201;6000;19800
20050701;6020;2208
20060601;6000;25806
20070501;6000;95680
20071001;6000;71760
20060401;6020;2284
20051201;6000;23100
20050901;6020;2178
20080401;6040;7880
20070301;6020;9449
20070601;6000;86112
20070201;6040;7620
20071001;6020;6626
20061101;6000;105248
20070801;6000;75900
20060901;6020;11663
20070301;6000;83720
20061101;6040;6479
20060101;6000;24700
20071101;6000;80454
20070801;6020;7788
20070601;6020;9719
20080101;6000;74624
20050801;6040;2002
20071101;6020;6604
20060401;6000;27830
20070101;6040;8927
20080501;6000;80454
20050701;6040;1546
20050901;6000;21780
20060901;6000;94723
20071201;6000;75141
20060901;6040;8747
20070401;6000;97773
20060301;6020;2046
20060501;6040;2651
20080201;6040;7880
20080301;6000;74382
20060701;6020;8283
20080601;6040;3543
20070401;6020;12038
20070501;6020;8835
20051001;6040;1092
20061001;6040;6958
20051101;6000;20460
20070901;6000;68310
20060801;6040;5515
20080501;6020;8255
20071201;6020;9252
20060301;6040;1432
20080101;6020;6125