[PostgreSQL] 重寫 ROE query

PostgreSQL 語法比 SQLite 龜毛的多，也就是嚴謹的多。會這樣寫，主要是為了接上 iReport，使用 Timeseries Chart 畫圖，Dataset 的 query 如下，這樣我們就能拉出四個 Time series，這樣就很直覺了。



select
    U.activity_date,
    U.annual_adjusted_net_income/U.shareholder_equity as roe,
    U.annual_adjusted_net_income/U.annual_adjusted_operating_income as net_profit_margin,
    U.annual_adjusted_operating_income/U.total_assets as total_assets_turnover,
    U.total_assets/U.shareholder_equity as equity_multiplier
from
(
    select
        T.activity_date,
        T.shareholder_equity,
        case
            when date_part('month', T.activity_date) = 3 then T.net_income * 4/1
            when date_part('month', T.activity_date) = 6 then T.net_income * 4/2
            when date_part('month', T.activity_date) = 9 then T.net_income * 4/3
            else T.net_income
        end as annual_adjusted_net_income,
        case
            when date_part('month', T.activity_date) = 3 then T.operating_income * 4/1
            when date_part('month', T.activity_date) = 6 then T.operating_income * 4/2
            when date_part('month', T.activity_date) = 9 then T.operating_income * 4/3
            else T.operating_income
        end as annual_adjusted_operating_income,
        T.total_assets,
        max(T.report_date)
    from
    (
        select
            A.activity_date,
            A.report_date,
            A.number as shareholder_equity,
            B.number as net_income,
            C.number as operating_income,
            D.number as total_assets
        from
            BalanceSheet as A,
            IncomeStmt as B,
            IncomeStmt as C,
            BalanceSheet as D
        where
        A.stock_code = B.stock_code
        and B.stock_code = C.stock_code
        and C.stock_code = D.stock_code
        and A.activity_date = B.activity_date
        and B.activity_date = C.activity_date
        and C.activity_date = D.activity_date
        and A.item = '股東權益總計'
        and B.item = '合併總損益'
        and C.item = '營業收入合計'
        and D.item = '資產總計'
        and A.report_type = 'C'
        and B.report_type = 'C'
        and C.report_type = 'C'
        and D.report_type = 'C'
        and A.stock_code = '2498'
    ) as T
    where
        T.shareholder_equity != 0
        and T.net_income != 0
        and T.operating_income != 0
        and T.total_assets != 0
    group by
        T.activity_date,
        T.shareholder_equity,
        T.net_income,
        T.operating_income,
        T.total_assets
    order by T.activity_date
) as U


但接下的問題是四個 time series 通通共用一個 axis，如果要拆開成 primary axis 與 secondary axis ((Excel 用語))，我們得用 Multi Axis Chart 當湯底，兩個 Timeseries Charts 當材料，Axis Position 取「Right or Bottom」或是「Left or Top」。



然後繼續做圖表，又用一些好招式：用 Parameters 變化各種圖表。

((這個是資本結構的 query，當做另一個 dataset))

select
    U.activity_date,
    U.equity/U.assets as equity_ratio,
    U.liabilities/U.assets as liabilities_ratio,
    U.assets/U.equity as equity_multiplier
from
(
    select
        T.activity_date,
        T.assets,
        T.liabilities,
        T.equity,
        max(T.report_date)
    from
    (
select
            A.activity_date,
            A.report_date,
            A.number as assets,
            B.number as liabilities,
            C.number as equity
        from
            BalanceSheet as A,
            BalanceSheet as B,
            BalanceSheet as C
        where
        A.stock_code = B.stock_code
        and B.stock_code = C.stock_code
        and A.activity_date = B.activity_date
        and B.activity_date = C.activity_date
        and A.item = '資產總計'
        and B.item = '負債總計'
        and C.item = '股東權益總計'
        and A.report_type = 'C'
        and B.report_type = 'C'
        and C.report_type = 'C'
        and A.stock_code = $P{STOCK_CODE}
    ) as T
    group by
        T.activity_date,
        T.assets,
        T.liabilities,
        T.equity
    order by T.activity_date
) as U
where
    U.assets != 0