[SQL] 營收圖表研究

Source: http://mopsov.twse.com.tw/t21/sii/t21sc03_101_8.html


Example 1: 累計營業收入: 當月累計營收

select A.activity_date, A.income, sum(B.income) from
(
    select max(report_date), activity_date, income from OperatingIncome
    where stock_code = '1101'
    group by activity_date
) as A,
(
    select max(report_date), activity_date, income from OperatingIncome
    where stock_code = '1101'
    group by activity_date
) as B
where B.activity_date <= A.activity_date
and strftime('%Y', B.activity_date) = strftime('%Y', A.activity_date)
group by A.activity_date



Example 2: 營業收入: 去年同月增減(%)

select date, YoY from
(
    select
        B.activity_date as date,
        B.income / A.income - 1 as YoY,
        max(B.report_date)
    from OperatingIncome as A
    inner join
    OperatingIncome as B
    on A.stock_code = B.stock_code
        and strftime('%Y', B.activity_date) - strftime('%Y', A.activity_date) = 1
        and strftime('%m-%d', B.activity_date) = strftime('%m-%d', A.activity_date)
        and A.stock_code = '1101'
    group by B.activity_date
)



Example 3: 累計營業收入: 前期比較增減(%)

select
    D.date,
    D.accumlated / C.accumlated - 1 as YoY
from
(
    select A.activity_date as date, sum(B.income) as accumlated from
    (
        select max(report_date), activity_date, income from OperatingIncome
        where stock_code = '1101'
        group by activity_date
    ) as A,
    (
        select max(report_date), activity_date, income from OperatingIncome
        where stock_code = '1101'
        group by activity_date
    ) as B
    where B.activity_date <= A.activity_date
    and strftime('%Y', B.activity_date) = strftime('%Y', A.activity_date)
    group by A.activity_date
) as C
inner join
(
    select A.activity_date as date, sum(B.income) as accumlated from
    (
        select max(report_date), activity_date, income from OperatingIncome
        where stock_code = '1101'
        group by activity_date
    ) as A,
    (
        select max(report_date), activity_date, income from OperatingIncome
        where stock_code = '1101'
        group by activity_date
    ) as B
    where B.activity_date <= A.activity_date
    and strftime('%Y', B.activity_date) = strftime('%Y', A.activity_date)
    group by A.activity_date
) as D
on strftime('%Y', D.date) - strftime('%Y', C.date) = 1
and strftime('%m-%d', D.date) = strftime('%m-%d', C.date)