Zenn
Open3

【達人に学ぶSQL徹底指南書メモ】7. ウィンドウ関数で行間比較を行う

SQL
DB
churuchuruchuruchuru

7.1 成長・後退・現状維持

Sales

year sale
1990 50
1991 51
1992 52
1993 52
1994 50
1995 50
1996 49
1997 55

結果

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

7.1.1 相関サブクエリの利用

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 '↓'
    ELSE '-'
  END AS var
FROM (
  SELECT
    year,
    sale AS current_sale,
    (
      SELECT sale
      FROM Sales S2
      WHERE S2.year = S1.year - 1
    ) AS pre_sale
  FROM Sales S1
) AS TMP
ORDER BY year;

7.1.2 ウィンドウ関数の利用

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 '↓'
    ELSE '-'
  END AS var
FROM (
  SELECT
    year,
    sale AS current_sale,
    SUM(sale) OVER (
      ORDER BY year
      RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING
    ) AS pre_sale
  FROM Sales
) AS TMP
ORDER BY year;

歯抜けがあったとき、直近の行を比較対象とするには、RANGE を ROWS に置き換えればよい。

churuchuruchuruchuru

7.2 ウィンドウ関数 vs. 相関サブクエリ

  • ウィンドウ関数は、サブクエリだが、相関サブクエリではない
    • サブクエリ単体で実行することができる
      • 可読性が高く、動作も理解しやすい
      • デバッグが容易に行える
  • テーブルに対するスキャンが一度だけで済む
    • パフォーマンスが良い

Shohin

shohin_id shohin_mei shohin_bunrui hanbai_tanka
0001 T シャツ 衣服 1000
0002 穴あけパンチ 事務用品 500
0003 カッターシャツ 衣服 4000
0004 包丁 キッチン用品 3000
0005 圧力鍋 キッチン用品 6800
0006 フォーク キッチン用品 500
0007 おろしがね キッチン用品 880
0008 ボールペン 事務用品 100

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

7.2.1 相関サブクエリ

SELECT
  shohin_bunrui,
  shohin_mei,
  hanbai_tanka
FROM
  Shohin S1
WHERE
  hanbai_tanka > (
    SELECT AVG(shohin_tanka)
    FROM Shohin S2
    WHERE S1.shohin_bunrui = S2.shohin_bunrui
    GROUP BY shohin_bunrui
  );

結果

shohin_bunrui shohin_mei hanbai_tanka
事務用品 穴あけパンチ 500
衣服 カッターシャツ 4000
キッチン用品 包丁 3000
キッチン用品 圧力鍋 6800

7.2.2 ウィンドウ関数

SELECT
  shohin_mei,
  shohin_bunrui,
  hanbai_tanka,
  avg_tanka
FROM
  (
    SELECT
      shohin_mei,
      shohin_bunrui,
      hanbai_tanka,
      AVG(hanbai_tanka) OVER(PARTITION BY shohin_bunrui) AS avg_tanka
    FROM
      Shohin
  ) TMP
WHERE
  hanbai_tanka > avg_tanka;

結果

shohin_mei shohin_bunrui hanbai_tanka avg_tanka
穴あけパンチ 事務用品 500 300
カッターシャツ 衣服 4000 2500
包丁 キッチン用品 3000 2795
圧力鍋 キッチン用品 6800 2795
  • レコードを集約せずにそのまま元のテーブルに列として結果を追加するだけ
    • 平均単価という集合と単価という集合を同じレベルで保持できる
  • 各行で単純に条件を記述するだけで比較ができる
churuchuruchuruchuru

7.3 オーバーラップする期間を調べる

Reservations

reserver start_date end_date
木村 2018-10-26 2018-10-27
荒木 2018-10-28 2018-10-31
堀 2018-10-31 2018-11-01
山本 2018-11-03 2018-11-04
内田 2018-11-03 2018-11-05
水谷 2018-11-06 2018-11-06

7.3.1 相関サブクエリ

SELECT
  reserver,
  start_date,
  end_date
FROM
  Reservations R1
WHERE
  EXISTS(
    SELECT *
    FROM Reservations R2
    WHERE R1.reserver <> R2.reserver
      AND (
        R1.start_date BETWEEN R2.start_date AND R2.end_date
        OR R1.end_date BETWEEN R2.start_date AND R2.end_date
      )
  );

結果

reserver start_date end_date
荒木 2018-10-28 2018-10-31
堀 2018-10-31 2018-11-01
山本 2018-11-03 2018-11-04
内田 2018-11-03 2018-11-05

7.3.2 ウィンドウ関数

SELECT
  reserver,
  next_reserver
FROM (
  SELECT
    reserver,
    start_date,
    end_date,
    MAX(start_date) OVER (
      ORDER BY start_date
      ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
    ) AS next_start_date,
    MAX(reserver) OVER (
      ORDER BY start_date
      ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
    ) AS next_reserver
  FROM
    Reservations
) TMP
WHERE
  next_start_date BETWEEN start_date AND end_date;

結果

reserver next_reserver
荒木 堀
山本 内田