今回も、令和6年度秋期応用情報技術者試験の午後問6「データベース」 について解説します。
前回の続きとなっておりますので、まだご覧になられていない場合はまずこちらからどうぞ。
【応用情報】令和６年度秋期（午後：問６ データベース）第１回 - ER図
この記事は、下記の方々を対象としております。
- 応用情報技術者試験を受験する、且つ午後にデータベースを選択する。
- データベーススペシャリスト試験を受験するが、応用情報レベルから復習したい。
- 開発エンジニアでデータモデリングやDB関連の設計を担当する、もしくは目指している。
試験問題の構成や難易度、SQLやインデックス設計のポイントを全３回に分けてご紹介しています。
- 第１回：設問１（ER図 - 自己結合、連関エンティティ）
- 第２回（今回）：設問２（SQL - 再帰的クエリ）
- 第３回：設問３（インデックス設計）
設問2：SQLの再帰的クエリと部分一致検索
※出典：令和6年度 秋期 応用情報技術者試験 午後 問題冊子
WITH句と再帰的クエリの構成（e,f）
今回のSQLでは、WITH RECURSIVE句を使用して指定カテゴリーを中間テーブルとして作成し、その階層を再帰的にたどる構造になっています。
再帰的クエリは、親カテゴリーから子カテゴリーへと繰り返しデータを取得するために必要な構文です。
そして、繰り返し取得したデータをまとめるために集合演算が必要になります。
過去問の傾向から、SELECT文が同じインデントで複数行に並び、間に空欄がある場合は集合演算の可能性が非常に高いです。
※過去問の傾向をまとめたこちらの記事も参考にどうぞ。
【過去31回分】応用情報技術者試験（午後 問６：データベース ）過去問と模範解答を見て傾向と対策を考えてみた。
このような構造はカテゴリーの階層構造を扱う際に有効で、指定したカテゴリーIDから上位・下位カテゴリーを辿るイメージです。
カテゴリーと出品テーブルの結合（g）
指定カテゴリーテーブルと出品テーブルはカテゴリーIDをキーに内部結合（INNER JOIN） されます。
上位カテゴリーIDはあくまでも階層を掘り下げるための材料であり、実際の結合条件はカテゴリーID同士の一致となります。
部分一致検索におけるLIKE句のワイルドカード（h）
商品名や商品説明の部分一致検索では、LIKE句とワイルドカード（%） を用います。
任意の文字列を用いて部分一致させるには、文字列連結をする必要があります。
RDBにより連結方法の種類は異なる部分もありますが、標準SQLで定義されている ||（パイプ2本） を使います。
※模範解答も、この方法になっております。
文字列連結には ||（パイプ2本） を使います。
例えば、 LIKE '%' || 任意の文字列 || '%' のように記述し、任意の文字列の前後にワイルドカードを付与して部分一致検索を実現します。
今回は、任意の文字列はパラメータ（引数） で、名称が『キーワード』なので問題文の説明に沿ってコロンを付与し :キーワード と書きます。
SQLを動かしてみよう
解説はしたものの、簡単に試せるのなら試した方が良いです！
過去に紹介したSQLを発展させてみました。
PostgreSQLで書きますので、よろしければ試してみてください。
※簡単に試す方法はこちらの記事を参考にして下さい。
データサンプル
問題文には具体的なデータが無いので、私が考えたオリジナルをご紹介します。
トレーディングカードということで、武器っぽいものを。
-- カテゴリテーブルを作る
CREATE TABLE カテゴリ (
カテゴリID INTEGER PRIMARY KEY -- 主キー
, カテゴリ名 VARCHAR
, 上位カテゴリID INTEGER REFERENCES カテゴリ(カテゴリID) -- 外部キー
);
-- カテゴリテーブルにデータを登録
INSERT INTO カテゴリ VALUES
(1,'武器',NULL)
,(10,'剣',1)
,(20,'銃',1)
,(101,'エペ',10)
,(102,'刀',10)
,(201,'マシンガン',20)
,(202,'ライフル',20);
-- 出品テーブルを作る
CREATE TABLE 出品 (
出品ID CHAR(6) PRIMARY KEY -- 主キー
, 出品者ID CHAR(4) -- ※問題文では外部キーだが割愛
, カテゴリID INTEGER REFERENCES カテゴリ(カテゴリID) -- 外部キー
, 商品名 VARCHAR
, 商品説明 VARCHAR
, 出品価格 NUMERIC
, 商品状態 VARCHAR
, 出品状況 VARCHAR
);
-- 出品テーブルにデータを登録
INSERT INTO 出品 VALUES
('SHU001',1111,201,'マシンガン','連続して打てる銃',300,'傷あり','出品中')
,('SHU002',1111,20,'火縄銃','昔の人の知恵',1000,'ほぼ新品','出品中')
,('SHU003',1111,10,'ビームサーベル','剣だけど何か違う',5000,'ほぼ新品','出品中')
,('SHU004',1111,102,'青龍刀','オリエンタル系',1200,'傷あり','出品中')
,('SHU005',1111,101,'フルーレ','剣なの？エペなの？',2300,'傷あり','出品中')
,('SHU006',1111,202,'スマートライフル','銃の中でも人気',1300,'傷あり','出品中')
;
再帰的クエリ
空欄(e,f)の結果だけを確認できます。
下記の「-- A.カテゴリIDを1か10か20にしてみよう」の手前がパラメータ（引数）だと思ってください。
パラメータを変えながら実行し、結果がどう変わるのかを比べてみてください。
※ここではあえて結果はご紹介しません。試したもののみぞ知る。
-- サブクエリ（再帰クエリ）
WITH RECURSIVE 指定カテゴリ(カテゴリID ,カテゴリ名 ,上位カテゴリID) AS (
SELECT A.カテゴリID ,A.カテゴリ名 ,A.上位カテゴリID
FROM カテゴリ A WHERE A.カテゴリID = 1 -- A.カテゴリIDを1か10か20にしてみよう
UNION ALL
SELECT B.カテゴリID ,B.カテゴリ名 ,B.上位カテゴリID
FROM カテゴリ B ,指定カテゴリ C WHERE B.上位カテゴリID = C.カテゴリID
)
-- 再帰クエリの結果をみてみよう
SELECT * FROM 指定カテゴリ ORDER BY カテゴリID ;
LIKE句のワイルドカード
全ての空欄の結果を確認できます。
こちらも、パラメータに該当するWHERE句以降の条件を変えながら、試してみてください。
-- サブクエリ（再帰クエリ）
WITH RECURSIVE 指定カテゴリ(カテゴリID ,カテゴリ名 ,上位カテゴリID) AS (
SELECT A.カテゴリID ,A.カテゴリ名 ,A.上位カテゴリID
FROM カテゴリ A WHERE A.カテゴリID = 1 -- A.カテゴリIDを1か10か20にしてみよう
UNION ALL
SELECT B.カテゴリID ,B.カテゴリ名 ,B.上位カテゴリID
FROM カテゴリ B ,指定カテゴリ C WHERE B.上位カテゴリID = C.カテゴリID
)
-- 再帰クエリとワールドカードの結果をみてみよう
SELECT * FROM 出品
INNER JOIN 指定カテゴリ
ON 出品.カテゴリID = 指定カテゴリ.カテゴリID
WHERE 出品.出品価格 BETWEEN 0 AND 99999 -- 出品価格の範囲してみよう
AND 出品.商品状態 = '傷あり' -- 商品状態を指定してみよう
AND 出品.出品状況 = '出品中' -- 出品状況を指定してみよう
AND (出品.商品名 LIKE '%' || '銃' || '%' OR 出品.商品説明 LIKE '%' || '銃' || '%' ) -- 商品名と商品説明のキーワードを変えてみよう
余談ですが、標準SQLではない？（後から追加された？）ものに CONCAT があります。
今や多くのRDBで利用可能ですので、併せて例をご紹介します。
最後の『AND (出品.商品名 LIKE～～～』以降を、下記に差し替えてみてください。
AND (出品.商品名 LIKE CONCAT('%','銃','%') OR 出品.商品説明 LIKE CONCAT('%','銃','%'))
さいごに
これで、設問２は終わりです。
前回と同じことですが、問題文の全体ではなくポイントだけ紹介しているので、分かり辛いところもあるかもしれません。
下記のコンテンツを合わせてご利用いただくことで、更に理解が深まると思います。
- IPAの過去問を見ながら、本記事を読む。
- 私の個人YouTubeの過去問解説をご視聴いただく（※毎回プレビュー載せるのも何なので、今回はリンクのみ）
- 本記事でご紹介したSQLを動かしてみる。
では、次回は設問３のインデックスについてです。
ここまでお付き合い頂き、ありがとうございましたm(_ _)m
Discussion