うとうとしながら。

学んだことのノート

docker-compose + Rails + PostgreSQL で環境構築する

docker-compose を使って、Rails + PostgreSQL の開発環境を構築していきます。

用意するもの

Dockerfile
FROM ruby:3

RUN curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add - \
  && echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list \
  && apt-get update -qq \
  && apt-get install -y nodejs yarn

RUN mkdir /app
WORKDIR /app

COPY Gemfile* /app/
RUN bundle install

Ruby イメージを元に、Yarn をインストールしています。
/app ディレクトリを作成し、ワーキングディレクトリとしています。
それから、Gemfile と Gemfile.lock をワーキングディレクトリにコピーします。
最後に、bundle installを行い、必要なライブラリ等をインストールします。

docker-compose.yml
version: '3'
services:
  db:
    image: postgres:alpine
    environment:
      POSTGRES_HOST_AUTH_METHOD: trust
    volumes:
      - db_data:/var/lib/postgresql/data

  rails:
    build: .
    command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'"
    volumes:
      - .:/app
    ports:
      - '3000:3000'
    depends_on:
      - db

volumes:
  db_data:

DB サーバーと Railsサーバーを立てます。

DB サーバーは PostgreSQL を使うので、postgres:alpine イメージを利用します。
POSTGRES_HOST_AUTH_METHOD: trust は、パスワードなしで、PostgreSQL に接続できるようにする設定です。
データの永続化のために、ボリュームの設定も行っています。

Rails サーバーは、先述の Dockerfile をベースに作成します。
3000番ポートの開放も忘れずに行います。

Gemfile
source 'https://rubygems.org'
gem 'rails', '~>6.x'

Rails を追加しておきます。

Rails のインストール

docker-compose の run コマンドで、bundle install を行うことで、Rails をインストールします。

docker-compose run rails bundle install

Rails のプロジェクト作成

続いて、Rails プロジェクトを作成します。
rails new を使います。

docker-compose run rails rails new . --database=postgresql --force

--database=postgresql の部分でデータベースの種類を指定しています。

database.yml の設定

ファイルの場所は config/database.yml にあります。
database.yml に必要な情報を追加していきます。
以下、一部抜粋です。

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # https://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  host: db
  port: 5432
  username: postgres

下3行を追加しています。
ホスト名、ポート番号、ユーザ名を指定しています。
ここで、ホスト名としては docker-compose.yml で設定したデータベースのサービス名とします。
(ここでは db)

その他、今回はパスワードなしで接続できるので、設定していませんが、パスワードの設定もできます。

Rails サーバー起動

まずは、docker イメージをビルドしなおします。

docker-compose build

それから、Rails が使用する DB を作成します。

docker-compose run rails rails db:create

最後に、起動します。

docker-compose up

これでサーバーが起動しました。

終わり

WHERE句とHAVING句

WHERE句とHAVING句

WHERE句もHAVING句も、条件抽出を行うためのものです。
ただし、WHERE句とHAVING句では、用法が異なります。

WHERE句は、FROM によってテーブルから抽出されたデータに条件を適用します。
(WHERE句はGROUP BYより前に評価されます。)
一方、HAVING句は、GROUP BYによってグルーピングされた各グループに対して条件を適用します。

つまり、テーブルのデータ全体に対して、何か条件を適用したい場合はWHERE句を利用します。
(性別のカラムが'男'のデータだけ抽出する、など)
GROUP BYによってまとめられた、各グループごとに何か条件を適用したい場合はHAVING句を利用します。
都道府県別で平均年齢が40歳以上の都道府県だけを抽出する、など)

SQL の評価順序

SQL は以下の順序で評価します。
(WHERE と HAVING に関連してるもののみ)

  1. FROM (テーブルからのデータ抽出)
  2. WHERE (条件抽出)
  3. GROUP BY (グループにまとめる)
  4. HAVING (グループごとに条件適用)
  5. SELECT (カラム選択)

ということで、WHERE句はGROUP BYより前に条件抽出を、
HAVING句はGROUP BYより後で条件抽出を行います。

参考: SELECT文の評価順序の話 - Qiita

サンプル

以下のテーブルで試してみます。

id name gender birthregion height
1 山田 勝 関東 172
2 佐藤 瞳 関東 160
3 高橋 桃子 関西 144
4 高橋 愛美 関西 152
5 鈴木 博文 関東 180
6 藤田 拓也 関東 168
7 小林 晶子 関東 155
8 橋本 浩之 関西 170
9 和田 博 関西 182
10 中村 幸子 関西 160

まずは、WHERE句から試してみます。

SELECT *
FROM students
WHERE gender = '';

結果

 id |   name    | gender | birthregion | height 
----+-----------+--------+-------------+--------
  1 | 山田 勝   | 男     | 関東        |    172
  5 | 鈴木 博文 | 男     | 関東        |    180
  6 | 藤田 拓也 | 男     | 関東        |    168
  8 | 橋本 浩之 | 男     | 関西        |    170
  9 | 和田 博   | 男     | 関西        |    182
(5 rows)

男性だけを抽出することができました。

続いて、HAVING句を試してみます。
HAVING句はGROUP BYでまとめたグループごとに条件を適用しますので、
性別ごとにグループにまとめて、平均身長が160を超えている性別のみを表示します。

SELECT gender, AVG(height)
FROM students
GROUP BY gender
HAVING AVG(height) > 160;

結果

 gender |         avg          
--------+----------------------
 男     | 174.4000000000000000
(1 row)

男性の平均身長は174.4cmなので、男性は表示されます。

最後に、WHERE句とHAVING句を両方つかってみます。

SELECT birthregion, AVG(height)
FROM people
WHERE gender = ''
GROUP BY birthregion
HAVING AVG(height) > 175;

結果

 birthregion |         avg          
-------------+----------------------
 関西        | 176.0000000000000000
(1 row)

終わり

外部キー制約

外部キー制約について

外部キー制約

外部キー制約とは、2つのテーブル間の整合性を保つための制約です。

2つのテーブル A と B を考え、B が A を参照するカラムを持つとします。
そのとき、「B の A を参照するカラムは、対応するレコードが A 内に存在しなければならない」という制約を考えます。
これが、外部キー制約です。

例えば、2つのテーブル A, B を以下のように考えます。

A

column_name type
id integer
name string

B

column_name type
id integer
a_id integer

テーブル B の a_id カラムは、テーブル A の id カラムを参照しています。
結合する際には、この B の a_idと A の idで行います。

SELECT *
FROM A
INNER JOIN B ON A.id = B.a_id

そして、B の a_id に外部キー制約をつけます。
そうすると、B にレコードを挿入する際、a_id に対応するレコードがテーブル A 内に存在しなければエラーになります。

これにより、B は存在しない a_id を持つレコードが挿入できないため、テーブル A とテーブル B の整合性が保たれます。

PostgreSQL で試す

上で載せたテーブル A と B を使っていきます。
まずは、テーブルを作成します。

CREATE TABLE a ( 
  id integer primary key, 
  name varchar(255)
);

CREATE TABLE b ( 
  id integer primary key, 
  a_id integer references a(id)
);

テーブル B の作成時に、a_idreferences a(id) をつけています。
これが PostgreSQL での、外部キー制約の設定方法になります。

続いて、テーブル A にデータを挿入します。

INSERT INTO a (id, name) 
VALUES
  (1, 'xxx'), 
  (2, 'yyy'), 
  (3, 'zzz');

では、テーブル B にデータを挿入していきます。
まずは、A に存在する a_id のデータを挿入します。

INSERT INTO b (id, a_id)
VALUES
  (1, 2);

挿入操作が成功しました。

続いて、A に存在しない a_idのデータを挿入してみます。

INSERT INTO b (id, a_id)
VALUES
  (2, 4);

結果はこのようになりました。

ERROR:  insert or update on table "b" violates foreign key constraint "b_a_id_fkey"
DETAIL:  Key (a_id)=(4) is not present in table "a".

テーブル A に対応する id が存在しないために、エラーになりました。
これが、外部キー制約です。

参考: https://www.postgresql.jp/document/12/html/tutorial-fk.html

終わり

内部結合と外部結合

内部結合と外部結合ってどう違うのか、を調べたのでまとめていきます。
実際に、SQL操作をしながらやっていきます。

DB 準備

DB は PostgreSQL を利用します。

テーブルを二つ用意します。
記事 (articles) と カテゴリー (categories) テーブルです。

CREATE TABLE categories (
  id SERIAL NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL
);

CREATE TABLE articles (
  id SERIAL NOT NULL PRIMARY KEY,
  title varchar(255) NOT NULL,
  category_id integer
);

テーブルにデータを入れます。

INSERT INTO categories (name)
VALUES
  ('日常'),
  ('技術'),
  ('ポエム'),
  ('政治');

INSERT INTO articles (title, category_id)
VALUES
  ('一月一日', 1),
  ('Ruby on Rails 入門', 2),
  ('北海道行った話', 1),
  ('じんせい', 3),
  ('DNSを理解した', 2);

中身は以下の通りです。

categories

id name
1 日常
2 技術
3 ポエム
4 政治

articles

id title category_id
1 一月一日 1
2 Ruby on Rails 入門 2
3 北海道行った話 1
4 じんせい 3
5 DNSを理解した 2
6 これ何 5

内部結合

まず、結合とは、複数のテーブルを組み合わせることです。
そして、結合するとき、多くの場合は条件を与えます。

そして、内部結合とは組み合わせる両方のテーブルに対象のレコードが存在する場合に限り、それらを組み合わせて、出力するということになります。

PostgreSQL では、内部結合は INNER JOIN ~ ON ・・・ となります。
~の部分が組み合わせるテーブル名、ONの後ろの・・・部分が条件となります。

実際に、準備でつくった categories テーブルと articles テーブルを使って試してみます。

SELECT *
FROM articles
INNER JOIN categories
  ON category_id = categories.id;

結果は以下の通りになります。

 id |       title        | category_id | id |  name
----+--------------------+-------------+----+--------
  1 | 一月一日           |           1 |  1 | 日常
  2 | Ruby on Rails 入門 |           2 |  2 | 技術
  3 | 北海道行った話     |           1 |  1 | 日常
  4 | じんせい           |           3 |  3 | ポエム
  5 | DNSを理解した      |           2 |  2 | 技術
(5 rows)

これを見てわかると思いますが、片方のテーブルにしかないレコードは表示されていません。

例えば、categories テーブルのレコード (id, name) = (4, '政治') は、この idcategory_id にもつレコードが articles テーブル内にないため、結合結果に表れません。
articles テーブルのレコード (id, title, category_id) = ( 6, 'これ何', 5) も同様です。

外部結合

内部結合の次は外部結合です。
内部結合・外部結合の名前や、内部結合の説明から予想できているかもしれませんが、外部結合とはどちらか片方にレコードがあると、結合結果に出力されます。

ただし、どちらか一方というと、

  1. categoreis テーブルのみに表れるレコードはすべて
  2. articles テーブルに表れるレコードはすべて
  3. categories テーブル、または articles テーブルに表れるレコードすべて

と、3パターンがあります。

ということで、外部結合は3パターンあります。

  1. LEFT OUTER JOIN
  2. RIGHT OUTER JOIN
  3. FULL OUTER JOIN

ここからは具体例を見ていきます。 LEFT JOINRIGHT JOINFULL JOINの順に、SQL文とその結果を続けて載せます。

LEFT JOIN

SQL

SELECT *
FROM articles
LEFT JOIN categories
  ON category_id = categories.id;

結果

 id |       title        | category_id | id |  name
----+--------------------+-------------+----+--------
  1 | 一月一日           |           1 |  1 | 日常
  2 | Ruby on Rails 入門 |           2 |  2 | 技術
  3 | 北海道行った話     |           1 |  1 | 日常
  4 | じんせい           |           3 |  3 | ポエム
  5 | DNSを理解した      |           2 |  2 | 技術
  6 | これ何             |           5 |    |
(6 rows)

RIGHT JOIN

SQL

SELECT *
FROM articles
RIGHT JOIN categories
  ON category_id = categories.id;

結果

 id |       title        | category_id | id |  name
----+--------------------+-------------+----+--------
  1 | 一月一日           |           1 |  1 | 日常
  2 | Ruby on Rails 入門 |           2 |  2 | 技術
  3 | 北海道行った話     |           1 |  1 | 日常
  4 | じんせい           |           3 |  3 | ポエム
  5 | DNSを理解した      |           2 |  2 | 技術
    |                    |             |  4 | 政治
(6 rows)

RIGHT JOIN

SQL

SELECT *
FROM articles
FULL JOIN categories
  ON category_id = categories.id;

結果

 id |       title        | category_id | id |  name
----+--------------------+-------------+----+--------
  1 | 一月一日           |           1 |  1 | 日常
  2 | Ruby on Rails 入門 |           2 |  2 | 技術
  3 | 北海道行った話     |           1 |  1 | 日常
  4 | じんせい           |           3 |  3 | ポエム
  5 | DNSを理解した      |           2 |  2 | 技術
  6 | これ何             |           5 |    |
    |                    |             |  4 | 政治
(7 rows)

INNER JOIN との違いは、INNERの部分がそれぞれ、LEFTRIGHTFULL にかわったことです。

LEFT、RIGHT、FULLの違いはそれぞれどちらのテーブルに存在する場合に、結合するかということです。
そして、FROM の後ろが左のテーブル、JOINの後ろが右のテーブルとして考えます。
LEFTなら左、RIGHTなら右のテーブルのレコードは、もう片方のテーブルに存在しない場合でも、結合します。
FULLの場合だと、左か右のどちらかに存在すれば、結合します。

終わり

PostgreSQL 練習 (3)

↓の続きです。

utouto97.hatenablog.com

SQL の練習問題を解いていきます。
問題は↓になります。 qiita.com

準備

リンク先の Schema SQL を参考にテーブルの作成・データの挿入を行います。

CREATE TABLE users (
  id SERIAL NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL,
  email varchar(255) NOT NULL,
  age integer NOT NULL
);

INSERT INTO users
VALUES
  (1, 'もっくん', 'mokkun@example.com', 19),
  (2, 'みみこ', 'mimiko@example.net', 20),
  (3, 'さくら', 'sakura@example.com', 31),
  (4, 'ひよこ', 'hiyoko@example1.jp', 23),
  (5, 'すずき', 'suzuki@example.jp', 28);

CREATE TABLE follows (
  follower_id integer NOT NULL,
  followee_id integer NOT NULL,
  PRIMARY KEY(follower_id, followee_id)
);

INSERT INTO follows
VALUES
  (1, 2),
  (1, 3),
  (1, 4),
  (1, 5),
  (3, 1),
  (3, 2),
  (4, 5),
  (5, 1),
  (5, 2),
  (5, 3),
  (5, 4);

問1

さくらがフォローしているユーザーの名前を一覧で表示せよ。

SQL文はこんな感じ。

SELECT u2.name 
FROM users
INNER JOIN follows
  ON id = follows.follower_id
INNER JOIN users u2
  ON follows.followee_id = u2.id
WHERE follower_id = (
  SELECT id
  FROM users
  WHERE name = 'さくら'
);

INNER JON を2回使います。

結果は以下のとおり。

   name   
----------
 もっくん
 みみこ
(2 rows)

問2

誰もフォローしていないユーザーの名前を表示せよ。

SQL文はこんな感じ。

SELECT DISTINCT name
FROM users
LEFT JOIN follows
  ON id = follows.follower_id
WHERE follower_id IS NULL;

結果は以下のとおり。

  name  
--------
 みみこ
(1 row)

参考: https://www.postgresql.jp/document/12/html/queries-table-expressions.html

問3

10代、20代、30代といった年代別にフォロー数の平均を表示せよ。

SQL文はこんな感じ。

SELECT 
  CONCAT((age / 10) * 10, '') AS age_group,
  AVG(num_follow) AS avg_count
FROM (
  SELECT age, COUNT(follower_id) AS num_follow
  FROM users
  LEFT JOIN follows
    ON id = follows.follower_id
  GROUP BY id
) AS temp
GROUP BY age_group
ORDER BY age_group;

これは難しかったので、1ステップ挟んで考えてみました。
まずは、FROMの中

SELECT age, COUNT(follower_id) AS num_follow
FROM users
LEFT JOIN follows
  ON id = follows.follower_id
GROUP BY id;

これを考えました。
ageとフォロー数の組を得る文になります。
あとは平均を求めます。年代別にまとめる方法は以下を参考にしました。
参考: https://qiita.com/sai-san/items/8901468713cfe28adfe2

結果は以下のとおり。

  age_group |     avg_count      
-----------+--------------------
 10代      | 4.0000000000000000
 20代      | 1.6666666666666667
 30代      | 2.0000000000000000
(3 rows)

問4

相互フォローしているユーザーのIDを表示せよ。 なお、重複は許さないものとする。

SQL文はこんな感じ。

SELECT f1.follower_id, f1.followee_id
FROM follows f1
INNER JOIN follows f2
  ON f1.follower_id = f2.followee_id AND f1.followee_id = f2.follower_id
WHERE f1.follower_id < f1.followee_id;

WHERE 句で重複を排除してます。

結果は以下のとおり。

 follower_id | followee_id 
-------------+-------------
           1 |           3
           1 |           5
           4 |           5
(3 rows)

終わり

PostgreSQL 練習 (2)

↓の続きです。 utouto97.hatenablog.com

SQL の練習問題を解いていきます。
問題は↓になります。

qiita.com

準備

リンク先の Schema SQL を参考にテーブルの作成・データの挿入を行います。

CREATE TABLE employees (
  id SERIAL NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL,
  department varchar(255) NOT NULL,
  hobby1 varchar(255),
  hobby2 varchar(255),
  hobby3 varchar(255)
);

INSERT INTO employees
VALUES
  (1, '杉山 圭佑', '営業部', 'サッカー', 'ドライブ', '映画鑑賞'),
  (2, '佐藤 結菜', '人事部', '映画鑑賞', '旅行', 'インスタ'),
  (3, '高橋 絵里', '経理部', 'ゲーム', NULL, NULL),
  (4, '早川 良太', '人事部', 'ドライブ', '料理', NULL),
  (5, '佐藤 一弥', '経理部', NULL, NULL, NULL),
  (6, '佐藤 優穂', '営業部', 'インスタ', 'TikTok', NULL);

一つ注意すべき点は、PostgreSQL では、AUTO_INCREMENTではなく、SERIAL型を利用します。

問1

趣味に映画鑑賞が含まれる社員の名前を一覧で表示せよ。

SQL文はこんな感じ。

SELECT name
FROM employees
WHERE hobby1 = '映画鑑賞'
  OR hobby2 = '映画鑑賞'
  OR hobby3 = '映画鑑賞';

結果は以下のとおり。

   name    
-----------
 杉山 圭佑
 佐藤 結菜
(2 rows)

問2

趣味1~3を縦に表示せよ。

SQL文はこんな感じ。

SELECT name, hobby1 as hobby
FROM employees
UNION ALL
SELECT name, hobby2 as hobby
FROM employees
UNION ALL
SELECT name, hobby3 as hobby
FROM employees;

UNION ALL を使って、それぞれの和をとります。

結果は以下のとおり。

   name    |  hobby   
-----------+----------
 杉山 圭佑 | サッカー
 佐藤 結菜 | 映画鑑賞
 高橋 絵里 | ゲーム
 早川 良太 | ドライブ
 佐藤 一弥 | 
 佐藤 優穂 | インスタ
 杉山 圭佑 | ドライブ
 佐藤 結菜 | 旅行
 高橋 絵里 | 
 早川 良太 | 料理
 佐藤 一弥 | 
 佐藤 優穂 | TikTok
 杉山 圭佑 | 映画鑑賞
 佐藤 結菜 | インスタ
 高橋 絵里 | 
 早川 良太 | 
 佐藤 一弥 | 
 佐藤 優穂 | 
(18 rows)

参考: https://www.postgresql.jp/document/12/html/queries-union.html

問3

名字が佐藤である社員の、趣味の数を表示せよ。

SQL文はこんな感じ。

SELECT name, COUNT(hobby1) + COUNT(hobby2) + COUNT(hobby3) AS hobby_count
FROM employees
WHERE name LIKE '佐藤%'
GROUP BY name;

COUNTでは、NULLは数えられません。
参考: https://lightgauge.net/database/sqlserver/1675/

結果は以下のとおり。

   name    | hobby_count 
-----------+-------------
 佐藤 一弥 |           0
 佐藤 優穂 |           2
 佐藤 結菜 |           3
(3 rows)

問4

同じ趣味を持つ社員の一覧を表示せよ。 なお、氏名リストの並び順は社員番号の昇順で、区切り文字は「, 」とする。

SQL文はこんな感じ。

CREATE VIEW hobby_name AS
  SELECT name, hobby1 as hobby
  FROM employees
  UNION ALL
  SELECT name, hobby2 as hobby
  FROM employees
  UNION ALL
  SELECT name, hobby3 as hobby
  FROM employees;

SELECT hobby, ARRAY_TO_STRING(
  ARRAY_AGG(
    name
  ), ','
) AS name_list
FROM hobby_name
WHERE hobby IS NOT NULL
GROUP BY hobby
HAVING COUNT(*) > 1;

これはかなり難しかったです。
まず、hobby1,hobby2,hobby3と分かれていると扱いづらいので、問2の結果をそのままビューとします。
これで、人の名前と趣味の組み合わせのテーブルができました。

もうひとつポイントなのは、複数の結果をカンマでつなぐ必要があるということです。
これはARRAY_TO_STRINGを使いました。

結果は以下のとおり。

  hobby   |      name_list      
----------+---------------------
 ドライブ | 早川 良太,杉山 圭佑
 映画鑑賞 | 佐藤 結菜,杉山 圭佑
 インスタ | 佐藤 優穂,佐藤 結菜
(3 rows)

解答例を見てみると、一文で表現されていました。
また、ARRAY_TO_STRINGではなく、GROUP_CONCATというものを利用していました。

utouto97.hatenablog.com

終わり

PostgreSQL 練習 (1)

↓の続きです。今回は問題を解いていきます。

utouto97.hatenablog.com

問1

性別が男である生徒の名前を一覧で表示せよ。

こんな感じの SQL 文を書きました。

SELECT name 
FROM students 
WHERE gender = '';

得られた出力は以下の通りです。

   name    
-----------
 長岡 一馬
 松本 義文
(2 rows)

参考: https://www.postgresql.jp/document/12/html/tutorial-select.html

問2

1教科でも30点以下の点数を取った生徒の名前を一覧で表示せよ。 ただし、重複は許さないものとする。

こんな感じの SQL 文を書きました。

SELECT DISTINCT students.name
FROM students, exam_results
WHERE students.id = exam_results.student_id 
  AND exam_results.score <= 30;

得られた出力は以下の通りです。

   name    
-----------
 長岡 一馬
 佐竹 友香
(2 rows)

別解として、INNER JOINを使った書き方も試してみました。

SELECT DISTINCT students.name
FROM students
INNER JOIN exam_results
  ON exam_results.student_id = students.id
WHERE exam_results.score <= 30;

参考: https://www.postgresql.jp/document/12/html/tutorial-join.html

問3

性別ごとに、最も高かった試験の点数を表示せよ。

こんな感じの SQL 文を書きました。

SELECT students.gender, MAX(exam_results.score) AS max_score
FROM students, exam_results
WHERE students.id = exam_results.student_id
GROUP BY students.gender;

得られた出力は以下の通りです。

 gender | max_score 
--------+-----------
 男     |        92
 女     |        90
(2 rows)

この問題も別解としてINNER JOINを使った書き方も試してみました。

SELECT students.gender, MAX(exam_results.score) AS max_score
FROM students
INNER JOIN exam_results
  ON exam_results.student_id = students.id
GROUP BY students.gender;

参考: https://www.postgresql.jp/document/12/html/tutorial-agg.html

問4

教科ごとの試験の平均点が50点以下である教科を表示せよ。

こんな感じの SQL 文を書きました。

SELECT subject, AVG(score) AS avg_score
FROM exam_results
GROUP BY subject
HAVING AVG(score) <= 50;

得られた出力は以下の通りです。

 subject |      avg_score      
---------+---------------------
 英語    | 26.0000000000000000
 国語    | 50.0000000000000000
(2 rows)

問5

試験結果テーブルの点数の右に、その教科の平均点を表示せよ。

こんな感じの SQL 文を書きました。

SELECT student_id, subject, score, AVG(score) OVER (PARTITION BY subject) AS subject_avg_score
FROM exam_results;

得られた出力は以下の通りです。

 student_id | subject | score |  subject_avg_score  
------------+---------+-------+---------------------
          1 | 国語    |    30 | 50.0000000000000000
          2 | 国語    |    70 | 50.0000000000000000
          2 | 数学    |    80 | 80.0000000000000000
          3 | 理科    |    92 | 63.5000000000000000
          4 | 理科    |    35 | 63.5000000000000000
          4 | 社会    |    90 | 90.0000000000000000
          4 | 英語    |    22 | 26.0000000000000000
          1 | 英語    |    30 | 26.0000000000000000
(8 rows)

参考: https://www.postgresql.jp/document/12/html/tutorial-window.html

問6

試験結果に理科が含まれない生徒の名前を一覧で表示せよ。

こんな感じの SQL 文を書きました。

SELECT name
FROM students
WHERE id NOT IN (
  SELECT student_id
  FROM exam_results
  WHERE subject = '理科'
);

得られた出力は以下の通りです。

   name    
-----------
 長岡 一馬
 中本 知佳
(2 rows)

utouto97.hatenablog.com

終わり