PostgreSQLのBETWEENを試す

PostgreSQL

今日はSQLZOOでSQLを学んだ。

sqlzoo.net

普段使ってるDBで試し打ち。

開発環境

github.com

$ docker exec -it faaaar-db bash

root@{CONTAINER_ID}:/# psql -U postgres

postgres=# \dt
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | idol      | table | postgres
 public | idol_unit | table | postgres
 public | unit      | table | postgres

BETWEEN: 基本形

ageが20から25までのレコードを抽出。

SELECT
    *
FROM
    idol
WHERE
    age BETWEEN 20 AND 25
;

↓

 id |    name     | age | height | birth_place | birth_day | blood_type 
----+-------------+-----+--------+-------------+-----------+------------
  9 | 桑山 千雪   |  23 |    163 | 山口県      | 4/18      | A
 16 | 有栖川 夏葉 |  20 |    168 | 愛知県      | 8/16      | B
 25 | 緋田 美琴   |  24 |    170 | 北海道      | 9/8       | B
 26 | 斑鳩 ルカ   |  20 |    161 | 神奈川県    | 1/31      | A

BETWEENは範囲がある概念に対してならある程度は使えそう？

BETWEEN: 応用形

所属ユニット数が2から3までのアイドルを列挙したい。

ステップ1

まずアイドルと所属ユニットを紐付ける。
アイドルとユニットの情報は別のテーブルに存在するため結合させる。

SELECT
    i.id as id,
    i.name as name,
    u.name as unit
FROM
    idol i
    INNER JOIN idol_unit iu
    ON  i.id = iu.idol
    INNER JOIN unit u
    ON  iu.unit = u.id
;

↓

 id |    name     |              unit              
----+-------------+--------------------------------
  1 | 櫻木 真乃   | Stella
  1 | 櫻木 真乃   | イルミネーションスターズ
  2 | 八宮 めぐる | Sol
  2 | 八宮 めぐる | イルミネーションスターズ
  3 | 風野 灯織   | Luna
(省略)

ステップ2

所属ユニットが2から3までのアイドルを列挙するには、アイドルの所属ユニット数を知る必要がある。
idの重複数をカウントすれば目的が叶えられそう。 （nameでも現状はいけそうだが、同姓同名を考えるとユニークなIDで行うのが安牌）

SELECT
    idl.id AS id,
    idl.name AS name,
    COUNT(idl.id)
FROM
    idol idl
    INNER JOIN idol_unit idlunt
    ON  idl.id = idlunt.idol
    INNER JOIN unit unt
    ON  idlunt.unit = unt.id
GROUP BY
    idl.id
;

↓

 id |    name     | count 
----+-------------+-------
 12 | 小宮 果穂   |     2
 24 | 七草 にちか |     1
 19 | 和泉 愛依   |     2
 25 | 緋田 美琴   |     1
 21 | 樋口 円香   |     2
(省略)

ステップ3

ステップ2に対してBETWEENを指定すれば良さそう。

SELECT
    idl.id AS id,
    idl.name AS name,
    COUNT(idl.id) AS count
FROM
    idol idl
    INNER JOIN idol_unit idlunt
    ON  idl.id = idlunt.idol
    INNER JOIN unit unt
    ON  idlunt.unit = unt.id
GROUP BY
    idl.id
HAVING
    idl.count BETWEEN 2 AND 3
;

↓

 id |    name     | count 
----+-------------+-------
  4 | 月岡 恋鐘   |     2
 10 | 大崎 甜花   |     2
  9 | 桑山 千雪   |     2
  7 | 白瀬 咲耶   |     2
 15 | 西城 樹里   |     2
(省略)

手元の実行結果では2から3までで絞れているのだが、ブログに載せるには量が多いので整形する。

ステップ4

countカラムを降順で表示。

SELECT
    idl.id AS id,
    idl.name AS name,
    COUNT(idl.id) AS count
FROM
    idol idl
    INNER JOIN idol_unit idlunt
    ON  idl.id = idlunt.idol
    INNER JOIN unit unt
    ON  idlunt.unit = unt.id
GROUP BY
    idl.id
HAVING
    idl.count BETWEEN 2 AND 3
ORDER BY
    idl.count DESC
;

↓

 id |    name     | count 
----+-------------+-------
 20 | 浅倉 透     |     3
 17 | 黛 冬優子   |     3
 16 | 有栖川 夏葉 |     3
  7 | 白瀬 咲耶   |     2
 15 | 西城 樹里   |     2
(省略)

結構思い通りに書けた。
試行錯誤しながらやっているので一度体系的に学んだ方が良さそう。

参考