SQL修行vol.11

『達人に学ぶSQL徹底指南書』ー10.SQLで数列を扱う

有休消化最終日となりました。この3週間、やりたいこと全部やりきったので楽しかったし、充実してたなって感じ。この本のアウトプットもこの章で終わりかな。自分的には、数列より集合の方が理解・イメージしやすいって思った。視覚的にわかりやすいからかな。

活用ケース１：連番を作る

例1：0~99の連番

SELECT
   D1.digit + (D2.digit*10) AS seq
FROM
   Digits D1 CROSS JOIN Digits D2
ORDER BY
   seq;

例2：1~542の連番

SELECT
   D1.digit + (D2.digit * 10) + (D3.digit * 100 ) AS seq
FROM
   Digits D1 CROSS JOIN Digits D2
   CROSS JOIN Digit D3
WHERE
   D1.digit + (D2.digit * 10) + (D3.digit * 100 ) BETWEEN 1 AND 542
ORDER BY
   seq;

活用ケース２：欠番を全部求める

例：3,9,10が抜けた1~12までのテーブルの欠番を求める

--EXCEPT
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 12
EXCEPT SELECT seq FROM SeqTbl;

--NOT IN
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN 1 AND 12
       AND
       NOT IN (SELECT seq FROM SeqTbl);

--EXCEPTパターンを一般化
SELECT seq
  FROM Sequence
 WHERE seq BETWEEN (SELECT MIN(seq) FROM SeqTbl)
               AND (SELECT MAX(seq) FROM SeqTbl)
EXCEPT SELECT seq FROM SeqTbl;

活用ケース３：３人なんですけど、座れますか？

例1：折り返しのない座席の空席情報から3人横並びで座れる所を探す

--NOT EXISTS
SELECT
   S1.seat AS star_seat,
   '~',
   S2.seat AS end_seat
FROM
   Seats S1, Seats S2
WHERE
   S2.seat = S1.seat + (3-1)
   AND
   NOT EXISTS (SELECT *
               FROM Seat S3
               WHERE S3.seat BETWEEN S1.seat AND S2.seat
                     AND
                     S3.seat <> '空'
              );
--HAVING
SELECT
   S1.seat AS star_seat,
   '~',
   S2.seat AS end_seat
FROM
   Seat S1, Seat S2, Seat S3
WHERE
   S2.seat = S1.seat + (3-1)
   AND
   S3.seat BETWEEN S1.seat AND S2.seat
GROUP BY
   S1.seat, S2.seat
HAVING
   COUNT(*) = SUM (CASE WHEN S3.seat = '空' THEN 1 ELSE 0 END);
   
--ウィンドウ関数
SELECT
   seat,
   '~',
   seat + (3-1)
FROM
   SELECT
      seat,
      MAX(seat)
         OVER(ORDER BY seat
                 ROWS BETWEEN (3-1) FOLLOWING
                              AND
                              (3-1) FOLLOWING ) AS end_seat
   FROM
      Seats
   WHERE
      status = '空') TMP
WHERE
   end_seat - seat = 3-1;

例2：折り返しのある座席の空席情報から3人横並びで座れる所を探す

--NOT EXISTS
SELECT
   S1.seat AS star_seat,
   '~',
   S2.seat AS end_seat
FROM
   Seats S1, Seats S2
WHERE
   S2.seat = S1.seat + (3-1)
   AND
   NOT EXISTS (SELECT *
               FROM Seat S3
               WHERE S3.seat BETWEEN S1.seat AND S2.seat
                     AND
                     (S3.seat <> '空' OR S3.line_id <> S1.line_id)
              );

--HAVING
SELECT
   S1.seat AS star_seat,
   '~',
   S2.seat AS end_seat
FROM
   Seat S1, Seat S2, Seat S3
WHERE
   S2.seat = S1.seat + (3-1)
   AND
   S3.seat BETWEEN S1.seat AND S2.seat
GROUP BY
   S1.seat, S2.seat
HAVING
   COUNT(*) = SUM (CASE WHEN S3.seat = '空' AND S3.line_id = S1.line_id
                        THEN 1
                        ELSE 0 END);
   
--ウィンドウ関数
SELECT
   seat,
   '~',
   seat + (3-1)
FROM
   SELECT
      seat,
      MAX(seat)
         OVER(PARTITION BY line_id
                 ODER BY seat
                 ROWS BETWEEN (3-1) FOLLOWING
                              AND
                              (3-1) FOLLOWING ) AS end_seat
   FROM
      Seats
   WHERE
      status = '空') TMP
WHERE
   end_seat - seat = 3-1;

活用ケース４：単調増加と単調減少

例：株価の動向テーブルから株価が単調増加している期間を求める

--STEP1:前回取引から上昇したかを判断する
SELECT
   deal_date,
   price,
   CASE SIGN(price - MAX(price)
                        OVER(ORDER BY deal_date
                             ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
                             )
             )
      WHEN 1 THEN 'up'
      WHEN 0 THEN 'stay'
      WHEN -1 THEN 'down'
      ELSE NULL
   END AS diff
FROM
   Mystock;

--STEP2:diff = 'up'の行のみのビューを作成する
CREATE VIEW MyStockUpSeq(deal_date, price, row_num)
AS
SELECT
   deal_date,
   price,
   row_num
FROM
   (SELECT
       deal_date,
       price,
       CASE SIGN(price - MAX(price)
                            OVER(ORDER BY deal_date
                                 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
                                )
                )
       WHEN 1 THEN 'up'
       WHEN 0 THEN 'stay'
       WHEN -1 THEN 'down'
       ELSE NULL
       END AS diff,
       ROW_NUMBER() OVER(ORDER BY deal_date) AS row_num
     FROM
       MyStock
   ) TMP
WHERE diff = 'up';

--STEP3:自己結合でシーケンスをグループ化
SELECT
   MIN(deal_date) AS start_date,
   '~',
   MAX(deal_date) AS end_date
FROM
   (SELECT
       M1.deal_date,
       COUNT(M2.row_num) - MIN(M1.row_num) gap
   FROM
      MyStockUpSeq M1
      INNER JOIN
      MyStockUpSeq M2
      ON M2.row_num <= M1.row_num
   GROUP BY
      M1.deal_date
   ) TMP
GROUP BY
   gap;