SQL修行vol.8

『達人に学ぶSQL徹底指南書』ー7.ウィンドウ関数で行間比較を行う

世間よりだいぶ遅れてゼルダの伝説BotWを昨年始めたわけだが、コログを320匹ほど集めたところで、絶対900匹全部集めきりたいという気持ちになってきた今日この頃。コログ収集に励みたい欲を抑えつつ、今日の学びのアウトプットを。（これ終わったらコログ集めよう）

活用ケース１：成長・後退・現状維持

例：前年に比べて年商が増えたのか、減ったのか、変わらなかったのかを調べる
STEP1_前年と年商が同じ年度を求める

--相関サブクエリの利用
SELECT
   year,
   sale,
FROM
   Sales S1
WHERE sale = (SELECT
                 sale
              FROM
                 Sales S2
              WHERE
                 S2.year = S1.year - 1)
ORDER BY
   year;

--ウィンドウ関数の利用
SELECT
   year,
   current_sale
FROM
   SELECT
      year,
      sale AS current_sale
      SUM(sale) OVER (ORDER BY year
                         ROWS BETWEEN 1 PRECEDING AND PRECEDING) AS pre_sale
    FROM
       Sales
WHERE
   current_sale = pre_sale
ORDER BY
   year;

STEP2_前年に比べて年商が増えたのか、減ったのか、変わらなかったのかを一度に求める

--相関サブクエリの利用
SELECT
   year,
   current_sale AS sale,
   CASE WHEN current_sale < pre_sale THEN '↓'
        WHEN current_sale = pre_sale THEN '→'
        WHEN current_sale > pre_sale THEN '↑'
        THEN '-'
   END
FROM
   (SELECT
       year,
       sale AS current_sale,
       (SELECT
           sale AS pre_sale
        FROM
           Sales S2
        WHERE
           S2.year = S1.year - 1)
    FROM
       Sales S10
ORDER BY
   year;

--ウィンドウ関数の利用
SELECT
   year,
   current_sale AS sale
   CASE WHEN current_sale < pre_sale THEN '↓'
        WHEN current_sale = pre_sale THEN '→'
        WHEN current_sale > pre_sale THEN '↑'
        THEN '-'
   END
FROM
   SELECT(year,
          sale AS current_sale,
          SUM(sale) OVER(ORDER BY year
                            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS pre_sale
   FROM
      Sales
ORDER BY
    year;

活用ケース２：時系列に歯抜けがある場合ー直近と比較

例：活用ケース１と同じことをしたいが、各年のデータが揃っていないときに「直近」の行を比較対象にして求める
※「直近」＝1.自分より前の年であること、2.条件1を満たす中で最大であること
STEP1_直近の年と年商が同じ年度を求める

--相関サブクエリの利用
SELECT
   year,
   sale
FROM
   Sales S1
WHERE
   sale = (SELECT
              sale
           FROM
              Sales S2
           WHERE 
             S2.year = (SELECT
                           MAX(year)
                        FROM
                           Sales S3
                        WHERE
                           S2.year > S3.year
                        )
            )
ORDER BY
   year;

--ウィンドウ関数の利用
SELECT
   year,
   current_sale
FROM
   SELECT
      year,
      sale AS current_sale
      SUM(sale) OVER(ORDER BY year
                     ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS pre_sale
   FROM
      Sales
WHERE
   current_sale = pre_sale
ORDER BY
   year;
            

活用ケース３：ウィンドウ関数 vs. 相関サブクエリ

〜おさらい〜
ウィンドウ関数：「行の順序」に基づいて操作を行う
相関サブクエリ：複数のテーブルを結合して操作を行う

例：各商品分類について平均単価より高い商品を選択する

--相関サブクエリの利用
SELECT
   shohin_bunrui,
   shohin_mei,
   hanbai_tanka
FROM
   Shohin S1
WHERE
   hanbai_tanka > (SELECT 
                      AVG(hanbai_tanka)
                   FROM
                      Shohin S2
                   WHERE
                      S2.shohin_bunrui = S1.shohin_bunrui
                   GROUP BY
                      shohin_bunrui
                   );

--ウィンドウ関数の利用
SELECT
   shohin_bunrui,
   shohin_mei,
   hanbai_tanka
FROM
   (SELECT
       shohin_bunrui,
       shohin_mei,
       hanbai_tanka,
       AVG(hanbai_tanka) OVER(PATITION BY shohin_bunri) AS avg_tanka
    FROM
       Shohin)
WHERE
   hanbai_tanka > avg_tabka;

活用ケース４：オーバーラップする期間を調べる

例：部屋の予約状況から予約の重複を調べる

--相関サブクエリの利用
SELECT
   reserver,
   start_date,
   end_date
FROM
   Reservation R1
WHERE EXISTS (SELECT
                 *
              FROM
                 Reservation R2
              WHERE
                 R1.reserver <> R2.reserver
                 AND
                 (R1.start_date BETWEEN R2.star_date AND R2.edate
                  OR
                  R1.end_date BETWEEN R2.star_date AND R2.edate)
              );

--ウィンドウ関数の利用
SELECT
   reserver,
   next_reserver
FROM
   SELECT
      reserver,
      start_date,
      end_date,
      MAX(start_Date)
         OVER(ORDER BY start_date
                 ROW BETWEEN 1 PRECEDING AND 1 PRECEDING) AS next_sdat,
      MAX(reserver)
         OVER(ORDER BY start_date
                 ROW BETWEEN 1 PRECEDING AND 1 PRECEDING) AS next_reserver
   FROM
      Reservations
WHERE
   next_sdate BETWEEN start_date AND end_date;