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

在第一回我們留下三個問題

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

在第二回我們解決了第一個問題，這次我們一口氣解決其他的問題，首先我們要把會變動的部分抽出來，讓即將撰寫的function具有通用性
，變動的部分有三個:

1. crosstab的第一個參數，即主要的SQL指令
   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
2. crosstab的第二個參數，為第二回使用的VALUES子句
   $$ values ('20050701'::text), ('20050801')$$
3. 最後就是輸出的表格結構
   As output_table1(account_code text, f_20050701 double precision, f_20050801 double precision)

再次修改，將第二回的SQL改為Function如下:
CREATE OR REPLACE FUNCTION tejdb.acc_crosstab(transaction_sql text, profile_sql text)
  RETURNS SETOF record AS
$BODY$
DECLARE
    stmt text ;
    r record;
    r2 text;
    code_list text;
    code_value text;
BEGIN
    -- get all categories
    code_list := $$ As output_table1(acc_code1 text$$;
    code_value := '';
    FOR r2 IN EXECUTE  profile_sql -- $$select acc_code from $2 $$
    LOOP
        code_list := code_list  || ', "' || r2 || '" integer';
        if char_length(code_value)=0 then
            code_value := '$$' || $$ VALUES ('$$ || r2 || $$'::text)$$;
        else
            code_value := code_value  || $$, ('$$ || r2 || $$')$$;
        end if;
    END LOOP;
    code_list := code_list  || ')';
    code_value := code_value  || '$$)';
    RAISE NOTICE '%',code_list;
    RAISE NOTICE '%',code_value;
   
    stmt := $$SELECT output_table1.*$$ ||
    $$ FROM crosstab('$$ || transaction_sql || $$', $$ ||
        code_value || code_list;
--     $$ As output_table1(acc_code1 text, project_rank_1 integer, project_rank_2 integer, project_rank_3 integer) $$;
    return QUERY EXECUTE stmt;
END
$BODY$
  LANGUAGE plpgsql
使用方式如下:
select * from tejdb.acc_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',
    'select distinct datekey from tejdb.account_data order by datekey')
    As output_table1(acc_code1 text, "20050701" integer, "20050801" integer, "20050901" integer, "20051001" integer, "20051101" integer, "20051201" integer, "20060101" integer, "20060201" integer, "20060301" integer, "20060401" integer, "20060501" integer, "20060601" integer, "20060701" integer, "20060801" integer, "20060901" integer, "20061001" integer, "20061101" integer, "20061201" integer, "20070101" integer, "20070201" integer, "20070301" integer, "20070401" integer, "20070501" integer, "20070601" integer, "20070701" integer, "20070801" integer, "20070901" integer, "20071001" integer, "20071101" integer, "20071201" integer, "20080101" integer, "20080201" integer, "20080301" integer, "20080401" integer, "20080501" integer, "20080601" integer)
其中 As output_table1(acc_code1 text, … 是從執行結果的messages pane 第一行複製而來的，所以，第一次撰寫時，先任意指定幾個欄位，執行後會出現錯誤，再將第一行複製，並貼至原來的SQL即可，或者自己寫個程式產生也行。