前年度と比較を行う

年と売り上げを持つテーブルがある。

````sql
select * from sales;

+------+------+
| year | sale |
+------+------+
| 1990 |   50 |
| 1991 |   51 |
| 1992 |   52 |
| 1993 |   52 |
| 1994 |   50 |
| 1995 |   50 |
| 1996 |   49 |
| 1997 |   55 |
+------+------+
````

前年度の売り上げと比較し、結果に応じた出力を行う。

````sql
select
s1.year
,s1.sale
,case 
when sale = 
(
    select
    sale
    from
    sales s2
    where
    s2.year = s1.year -1
) then '→'
when sale >
(
    select
    sale
    from
    sales s2
    where
    s2.year = s1.year -1
) then '↑'
when sale <
(
    select
    sale
    from
    sales s2
    where
    s2.year = s1.year -1
) then '↓'
else '-' end as var
from
sales s1
order by year;

+------+------+-----+
| year | sale | var |
+------+------+-----+
| 1990 |   50 | -   |
| 1991 |   51 | ↑   |
| 1992 |   52 | ↑   |
| 1993 |   52 | →   |
| 1994 |   50 | ↓   |
| 1995 |   50 | →   |
| 1996 |   49 | ↓   |
| 1997 |   55 | ↑   |
+------+------+-----+
````

相関サブクエリの結合条件で-1とすることで
前年度のレコードを比較対象としている。


次は時系列に歯抜けがある場合

````sql
select * from sales2;

+------+------+
| year | sale |
+------+------+
| 1990 |   50 |
| 1992 |   50 |
| 1993 |   52 |
| 1994 |   55 |
| 1997 |   55 |
+------+------+
````

````sql
select
s1.year
,s1.sale
,case
    when sale = (
        select
        sale
        from
        sales2 s2
        where
        year = (
            select
            max(year)
            from
            sales s3
            where
            s1.year > s3.year
        )
    ) then '→'
    when sale > (
        select
        sale
        from
        sales2 s2
        where
        year = (
            select
            max(year)
            from
            sales s3
            where
            s1.year > s3.year
        )
    ) then '↑'
    when sale < (
        select
        sale
        from
        sales2 s2
        where
        year = (
            select
            max(year)
            from
            sales s3
            where
            s1.year > s3.year
        )
    ) then '↓'
    else '-' end as var
from
sales2 s1

+------+------+-----+
| year | sale | var |
+------+------+-----+
| 1990 |   50 | -   |
| 1992 |   50 | -   |
| 1993 |   52 | ↑   |
| 1994 |   55 | ↑   |
| 1997 |   55 | -   |
+------+------+-----+

````

上記2つのSQLでは3回サブクエリを実行しているが、
1つにまとめられるようだ。

````sql
SELECT 
S1.year
,S1.sale
,CASE SIGN(sale - (
    SELECT sale
    FROM Sales S2
    WHERE S2.year = S1.year - 1)
)
WHEN 0  THEN '→'  /* 横ばい */
WHEN 1  THEN '↑'  /* 成長   */
WHEN -1 THEN '↓'  /* 後退   */
ELSE '-' END AS var
FROM Sales S1
ORDER BY year;
````

こちらを参考にさせていただきました。
<a href="https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6SQL%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E7%AC%AC2%E7%89%88-%E5%88%9D%E7%B4%9A%E8%80%85%E3%81%A7%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%9F%E3%81%8F%E3%81%AA%E3%81%84%E3%81%82%E3%81%AA%E3%81%9F%E3%81%B8-%E3%83%9F%E3%83%83%E3%82%AF-ebook/dp/B07GB4CNKP/ref=sr_1_1?adgrpid=48836428930&dchild=1&gclid=Cj0KCQiA48j9BRC-ARIsAMQu3WSrZJJd4-psCT9_qj0w4f0_fuN9b3Rh3DD8mwgP9UkTOLLapCJzwJwaAsG_EALw_wcB&hvadid=338547693256&hvdev=c&hvlocphy=1009306&hvnetw=g&hvqmt=e&hvrand=10462958271481352436&hvtargid=kwd-335112034228&hydadcr=4076_10899478&jp-ad-ap=0&keywords=%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6sql%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8&qid=1605532234&sr=8-1&tag=googhydr-22">達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ
</a>