【PostgreSQL初心者向け】サンプルデータベースの作成方法とSQL問題

こんにちは、宮原です。
 
新人の頃にSQLに苦労したことはありますか？ 私はSQLが苦手で、苦手克服のため練習用に手頃なサンプルデータベースを探していました。
 
今回はPostgreSQL Tutorialからサンプルデータベースを作成する方法やSQL問題を紹介します。ぜひ、SQLが苦手だと思っている方に活用していただければと思います。

環境

今回の説明で使用している環境は以下のとおりです。

PostgreSQL

15.2

サンプルデータベースのダウンロード

PostgreSQL Tutorialサイトを開き、Download DVD Rental Sample Databaseからサンプルをダウンロードします。
 
同サイトには、サンプルデータのER図も用意されているので、学習の際にご活用ください。
 

サンプルデータベースの作成方法

まず、PostgreSQLでサンプルのデータを流すためのデータベースを作成してください。私はpgAdminをインストールしていたので、pgAdminを起動してデータベースを作成しました。
 

データベース名にdvdrentalを入力し保存してください。これで、データベースの作成は完了です。
 

コマンドでデータベースの作成をしたい場合は、まずこちらからPostgreSQLをダウンロードしてください。
ダウンロード後、コマンドプロンプトを起動しPostgreSQLのbinフォルダまでcdコマンドで移動してください。

cd binフォルダパス

 
次に下記のコマンドを流してください。このコマンドは、PostgreSQLに接続するコマンドになります。

psql -U postgres

無事接続ができたら、PostgreSQL内で下記のコマンドを流してみてください。

\du

データベースを扱う際には、特定の権限を持つユーザーを新規に作成する必要があります。デフォルトのスーパーユーザーのみが表示されるはずなので、新しいユーザーを追加してください。

\password superuser-name

ユーザーの追加ができたら、データベースを作成するためにコマンドを流します。

CREATE DATABASE dvdrental

これで、データベース作成までは完了しました。次に、dvdrentalのデータベースにリストアしていきます。ダウンロードしたzipファイルを任意の場所に保存してください。
ファイルの解凍は、7-zipを使って解凍しました。コマンドでファイルを解凍するためには、保存したファイルを解凍するためには下記のコマンドを流します。

unzip dvdrental.zip

コマンドを流すとパスワードの入力を求められるのでパスワードの入力をしてください。7-zipで解凍した人は、PostgreSQLのbinフォルダまでcdコマンドで移動してください。
./dvdrental.tarの「.」部分は、ダウンロードしたzipファイルの任意の場所を指定してください。

pg_restore -h localhost -U postgres -d dvdrental ./dvdrental.tar

以上の工程で、サンプルデータベースの作成準備が完了です。

SQL問題

映画出演数ごとにランキング付けした俳優名・出演数の一覧を作成してください。但し、Woodridgeで借りられた映画に限ります。
 
取得イメージは下記のとおりです。

回答

SELECT
    ROW_NUMBER() OVER (ORDER BY COUNT(film.title) DESC) ranking
　　,  actor.first_name || ' ' || actor.last_name AS actor_name
　　, COUNT(film.title) AS film_appearances
FROM
　　film
　　INNER JOIN film_actor ON film_actor.film_id= film.film_id
　　INNER JOIN actor ON film_actor.actor_id= actor.actor_id
　　INNER JOIN inventory ON inventory.film_id= film.film_id
　　INNER JOIN rental ON rental.inventory_id= inventory.inventory_id
　　INNER JOIN payment ON payment.rental_id= rental.rental_id
　　INNER JOIN staff ON staff.staff_id= payment.staff_id
　　INNER JOIN address ON address.address_id= staff.address_id
　　INNER JOIN city ON city.city_id= address.city_id
WHERE
　　city.city = 'Woodridge'
GROUP BY
　　actor.first_name || ' ' || actor.last_name

解説（SELECT句）

取得したい要素を、順位・俳優名・出演数の3つに分けて考えていきます。まずは、各3つの要素をどのように取得する必要があるかを整理していきます。
 

順位

順位については、結果セットの行に一意の連番を付与するため、ROW_NUMBER関数を使用します。
 
ROW_NUMBER()関数の基本的な使い方は、下記のように使用します。

ROW_NUMBER() OVER ()

レコードの作成日時createdが古いものから順に番号をつけたい場合は、OVER()の中に書き出してあげるだけで付番してくれます。

ROW_NUMBER() OVER (ORDER BY created ASC)

今回は出演数の結果によって番号を振りたいので、下記のとおりになります（COUNT関数については出演数で詳しく説明しています）。また、付番した要素に対して別名をつけています。

ROW_NUMBER() OVER (ORDER BY COUNT(film.title) DESC) ranking

俳優名

俳優名については、テーブルからそのまま取得することができないためactorテーブルからfirst_nameとlast_nameを取得します。文字列の結合は、||を使用します。結合する際に見やすいように姓名の間に空白を入れています。

actor.first_name || ' ' || actor.last_name AS actor_name

出演数

出演数に関しても、俳優名と同様テーブルからそのまま要素を取得することができないため、回数をカウントする集約関数COUNT()を使用します。

COUNT(film.title) AS film_appearances

解説（FROM句）

次に何テーブルから取得するかを考えていきます。SELECT句で使用するactorとfilmテーブルだけで一見問題ないように思えますが、actorとfilmには共通のキーとなる列がありません。そのため、actorとfilmテーブルを結合させる条件を考える必要があります。冒頭で紹介しているサンプルデータベースのER図を確認すると、テーブル同士のつながりが分かりやすいです。
 
actorとfilmをつなぐテーブルとして最適なのはfilm_actorになります。ただし、これだけではWoodridgeで借りられた映画に限定することができません。貸出場所を限定するためには、cityテーブルからWoodridgeに条件を絞る必要があります（条件の指定方法についてはWHERE句で後述しています）。
 
今回は、cityとactor・film・film_actorテーブルを結合させるテーブルを考える必要があります。イメージとしては、映画に出ている俳優とWoodridgeで借りられた映画を結びつけてあげます。結合条件は複数のパターンがありますが、今回は下記で考えています。

FROM
　　film
　　INNER JOIN film_actor ON film_actor.film_id= film.film_id
　　INNER JOIN actor ON film_actor.actor_id= actor.actor_id
　　INNER JOIN inventory ON inventory.film_id= film.film_id
　　INNER JOIN rental ON rental.inventory_id= inventory.inventory_id
　　INNER JOIN payment ON payment.rental_id= rental.rental_id
　　INNER JOIN staff ON staff.staff_id= payment.staff_id
　　INNER JOIN address ON address.address_id= staff.address_id
　　INNER JOIN city ON city.city_id= address.city_id

解説（WHERE句）

WHERE句では検索条件を指定していきます。今回はWoodridgeで借りられた映画に限るとしているため、貸出場所をWoodridgeに指定してあげます。

WHERE
　　city.city = 'Woodridge'

解説（GROUP BY句）

映画に出ている俳優単位でグループ化します。グループ化にはGROUP BYを使用します。

GROUP BY
　　actor.first_name || ' ' || actor.last_name

GROUP BYについて詳しい解説をしているブログも出しているので、ぜひご参照ください。
【SQL入門】複数のデータをグループ化するGROUP BYの使い方

まとめ

サンプルデータを使用したSQL問題いかがでしたでしょうか。
 
SQLを苦手だと思う方や学習したいと思っている方にお役に立てれば幸いです。
 
 
 
 
《関連記事》