[SQL] 三大獲利能力研究

毛利率 (Gross Profit Margin) = 營業毛利(毛損) / 營業收入合計

select activity_date, gross_profit_margin from

(

    select activity_date, gross_profit_margin, max(report_date) from

    (

        select

        A.activity_date,

        A.number / B.number as gross_profit_margin,

        A.report_date

        from IncomeStmt as A

        inner join

        IncomeStmt as B

        on A.stock_code = B.stock_code

        and A.activity_date = B.activity_date

        and A.item = '營業毛利(毛損)'

        and B.item = '營業收入合計'

        and B.report_type = 'C'

        and B.report_type = 'C'

        and B.stock_code = '2498'

    )

    where gross_profit_margin is not null

    group by activity_date

    order by activity_date

)

營益率 (Operating Profit Margin) = 營業淨利(淨損) / 營業收入合計

select activity_date, operating_profit_margin from
(
    select activity_date, operating_profit_margin, max(report_date) from
    (
        select
        A.activity_date,
        A.number / B.number as operating_profit_margin,
        A.report_date
        from IncomeStmt as A
        inner join
        IncomeStmt as B
        on A.stock_code = B.stock_code
        and A.activity_date = B.activity_date
        and A.item = '營業淨利(淨損)'
        and B.item = '營業收入合計'
        and B.report_type = 'C'
        and B.report_type = 'C'
        and B.stock_code = '2498'
    )
    where operating_profit_margin is not null
    group by activity_date
    order by activity_date
)



稅後盈利率 (Net Profit Margin) = 本期淨利(淨損) / 營業收入合計

select activity_date, net_profit_margin from
(
    select activity_date, net_profit_margin, max(report_date) from
    (
        select
        A.activity_date,
        A.number / B.number as net_profit_margin,
        A.report_date
        from IncomeStmt as A
        inner join
        IncomeStmt as B
        on A.stock_code = B.stock_code
        and A.activity_date = B.activity_date
        and A.item = '本期淨利(淨損)'
        and B.item = '營業收入合計'
        and B.report_type = 'C'
        and B.report_type = 'C'
        and B.stock_code = '2498'
    )
    where net_profit_margin is not null
    group by activity_date
    order by activity_date
)



PS. hTC 獲利能力掉蠻快的