PostgreSQL 練習 (3)

DB

↓の続きです。

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)

終わり