SQL修行vol.11
『達人に学ぶSQL徹底指南書』ー10.SQLで数列を扱う
有休消化最終日となりました。この3週間、やりたいこと全部やりきったので楽しかったし、充実してたなって感じ。この本のアウトプットもこの章で終わりかな。自分的には、数列より集合の方が理解・イメージしやすいって思った。視覚的にわかりやすいからかな。
活用ケース1:連番を作る
例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;
活用ケース2:欠番を全部求める
例: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;
活用ケース3:3人なんですけど、座れますか?
例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;
活用ケース4:単調増加と単調減少
例:株価の動向テーブルから株価が単調増加している期間を求める
--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;
この記事が気に入ったらサポートをしてみませんか?