なんか飽きてきたから閃乱カグラのデータでやる

f:id:wand_ta:20190209220111j:plain

第5章 SQLによるデータベースの操作応用編

第5章 SQLによるデータベースの操作応用編

Y/M/Dな日付を投入する場合、datestyleの設定が必要
1. show datestyle; で現在のdatestyle設定値を確認
2. set datestyle to 'YMD';してからデータINSERT
3. 終わったらset datestyle to 'MDY';に戻す

SELECT * FROM kagura;

       name        |   team    | grade |  birthday  | age | blood_type | height | boob | waist | hip | cup
-------------------+-----------+-------+------------+-----+------------+--------+------+-------+-----+-----
 雪泉            | 月閃    |     3 | 2000-12-31 |  17 | A          |    167 |   92 |    56 |  84 | G
 叢               | 月閃    |     3 | 2000-10-08 |  17 | B          |    172 |   96 |    58 |  85 | H
 夜桜            | 月閃    |     2 | 2000-04-05 |  16 | O          |    159 |   90 |    53 |  82 | H
 四季            | 月閃    |     1 | 2000-03-25 |  15 | AB         |    161 |   95 |    54 |  83 | I
 美野里         | 月閃    |     1 | 2000-02-14 |  15 | AB         |    144 |   86 |    50 |  75 | G
 飛鳥            | 半蔵    |     2 | 2000-09-08 |  16 | A          |    155 |   90 |    57 |  85 | F
 斑鳩            | 半蔵    |     3 | 2000-07-07 |  18 | A          |    168 |   93 |    59 |  90 | G
 葛城            | 半蔵    |     3 | 2000-11-05 |  17 | B          |    165 |   95 |    57 |  90 | H
 柳生            | 半蔵    |     1 | 2000-12-23 |  15 | O          |    158 |   85 |    60 |  83 | E
 雲雀            | 半蔵    |     1 | 2000-02-18 |  15 | B          |    160 |   80 |    55 |  73 | E
 雅緋            | 蛇女    |     3 | 2000-08-15 |  20 | B          |    169 |   90 |    56 |  87 | G
 紫               | 蛇女    |     2 | 2000-10-26 |  16 | A          |    162 |  105 |    59 |  88 | K
 忌夢            | 蛇女    |     3 | 2000-06-09 |  21 | AB         |    157 |   88 |    60 |  82 | E
 両備            | 蛇女    |     1 | 2000-01-19 |  15 | O          |    160 |   69 |    56 |  90 | AAA
 両備(転身後) | 蛇女    |     1 | 2000-01-19 |  15 | O          |    160 |   95 |    56 |  90 | H
 両奈            | 蛇女    |     1 | 2000-01-19 |  15 | O          |    160 |   98 |    56 |  88 | I
 焔               | 紅蓮    |     2 | 2000-01-03 |  16 | B          |    163 |   87 |    57 |  85 | E
 詠               | 紅蓮    |     2 | 2000-02-10 |  16 | B          |    160 |   95 |    58 |  90 | H
 日影            | 紅蓮    |     3 | 2000-09-09 |  17 | O          |    160 |   85 |    57 |  85 | E
 未来            | 紅蓮    |     1 | 2000-12-28 |  15 | A          |    150 |   62 |    48 |  59 | A
 春花            | 紅蓮    |     3 | 2000-07-20 |  18 | AB         |    169 |   99 |    55 |  88 | I
 大道寺先輩   | 半蔵    |       | 2000-11-11 |  26 | B          |    170 |  100 |    58 |  98 | H
 凛               | 蛇女    |       | 2000-09-30 |  27 | A          |    160 |   97 |    57 |  90 | H
 神楽            | DLC       |       |            |     |            |    173 |   95 |    55 |  87 | I
 奈楽            | DLC       |       | 2000-07-15 |  16 | O          |    151 |   92 |    56 |  82 | G
 両姫            | 千年祭 |       | 2000-07-26 |  17 |            |    171 |   94 |    55 |  89 | H
 蓮華            | 千年祭 |       | 2000-07-20 |  18 | A          |    168 |   93 |    58 |  82 | G
 華毘            | 千年祭 |       | 2000-10-10 |  17 | O          |    163 |   96 |    57 |  84 | H
 華風流         | 千年祭 |       | 2000-05-05 |  16 | B          |    152 |   73 |    52 |  60 | C
(29 rows)

AND/OR演算子

焔紅蓮隊の2年生

SELECT name, team, grade
  FROM kagura
 WHERE team = '蛇女'
   AND grade = 2;

明日(2/10)は詠ちゃんの誕生日です

 name |  team  | grade
------+--------+-------
 焔  | 紅蓮 |     2
 詠  | 紅蓮 |     2
(2 rows)

(元)悪忍のみ

SELECT name, team
  FROM kagura
 WHERE team = '紅蓮'
    OR team = '蛇女';

       name        |  team
-------------------+--------
 雅緋            | 蛇女
 紫               | 蛇女
 忌夢            | 蛇女
 両備            | 蛇女
 両備(転身後) | 蛇女
 両奈            | 蛇女
 焔               | 紅蓮
 詠               | 紅蓮
 日影            | 紅蓮
 未来            | 紅蓮
 春花            | 紅蓮
 凛               | 蛇女
(12 rows)

LIKE演算子

両なんとかちゃんを探す
- _は任意の1文字

SELECT name
  FROM kagura
 WHERE name LIKE '両_';

  name
--------
 両備
 両奈
 両姫
(3 rows)

転身後の両備ちゃんも拾う
- _は任意の0文字以上

SELECT name
  FROM kagura
 WHERE name LIKE '両%';

       name
-------------------
 両備
 両備(転身後)
 両奈
 両姫
(4 rows)

3文字の名前の子
- 1文字目がワイルドカードなので、この検索はnameにインデックスが張ってあっても効かない
  (インデックスの実装は探索木)

SELECT name
  FROM kagura
 WHERE name LIKE '___';

   name
-----------
 美野里
 華風流
(2 rows)

BETWEEN演算子

閉区間
バストが90以上100以下の子を探す

SELECT *
  FROM kagura
 WHERE boob BETWEEN 90 AND 100;

       name        |   team    | grade |  birthday  | age | blood_type | height | boob | waist | hip | cup
-------------------+-----------+-------+------------+-----+------------+--------+------+-------+-----+-----
 雪泉            | 月閃    |     3 | 2000-12-31 |  17 | A          |    167 |   92 |    56 |  84 | G
 叢               | 月閃    |     3 | 2000-10-08 |  17 | B          |    172 |   96 |    58 |  85 | H
 夜桜            | 月閃    |     2 | 2000-04-05 |  16 | O          |    159 |   90 |    53 |  82 | H
 四季            | 月閃    |     1 | 2000-03-25 |  15 | AB         |    161 |   95 |    54 |  83 | I
 飛鳥            | 半蔵    |     2 | 2000-09-08 |  16 | A          |    155 |   90 |    57 |  85 | F
 斑鳩            | 半蔵    |     3 | 2000-07-07 |  18 | A          |    168 |   93 |    59 |  90 | G
 葛城            | 半蔵    |     3 | 2000-11-05 |  17 | B          |    165 |   95 |    57 |  90 | H
 雅緋            | 蛇女    |     3 | 2000-08-15 |  20 | B          |    169 |   90 |    56 |  87 | G
 両備(転身後) | 蛇女    |     1 | 2000-01-19 |  15 | O          |    160 |   95 |    56 |  90 | H
 両奈            | 蛇女    |     1 | 2000-01-19 |  15 | O          |    160 |   98 |    56 |  88 | I
 詠               | 紅蓮    |     2 | 2000-02-10 |  16 | B          |    160 |   95 |    58 |  90 | H
 春花            | 紅蓮    |     3 | 2000-07-20 |  18 | AB         |    169 |   99 |    55 |  88 | I
 大道寺先輩   | 半蔵    |       | 2000-11-11 |  26 | B          |    170 |  100 |    58 |  98 | H
 凛               | 蛇女    |       | 2000-09-30 |  27 | A          |    160 |   97 |    57 |  90 | H
 神楽            | DLC       |       |            |     |            |    173 |   95 |    55 |  87 | I
 奈楽            | DLC       |       | 2000-07-15 |  16 | O          |    151 |   92 |    56 |  82 | G
 両姫            | 千年祭 |       | 2000-07-26 |  17 |            |    171 |   94 |    55 |  89 | H
 蓮華            | 千年祭 |       | 2000-07-20 |  18 | A          |    168 |   93 |    58 |  82 | G
 華毘            | 千年祭 |       | 2000-10-10 |  17 | O          |    163 |   96 |    57 |  84 | H
(19 rows)

集約関数

count関数

月閃の女学生は何人ですか

SELECT count(*)
  FROM kagura
 WHERE team = '月閃';

 count
-------
     5
(1 row)

sum関数

SELECT sum(CASE WHEN team = '月閃' THEN 1
                ELSE NULL
                END) AS 月閃の人数
  FROM kagura;

 月閃の人数
-----------------
               5
(1 row)

【補】特性関数を噛ませてsumするのはよくやること
【補】NULLは集計対象外

avg関数

平均バストサイズが知りたい
- こういうのは中央値のほうがいい定期

SELECT avg(boob)
  FROM kagura;

         avg
---------------------
 90.1724137931034483
(1 row)

max,min関数

SELECT max(boob), min(boob)
  FROM kagura;

 max | min
-----+-----
 105 |  62

GROUP BY句と集約関数の組み合わせ

勢力別にバストサイズを集計する

SELECT team
      ,min(boob) AS 最小バスト
      ,avg(boob) AS 平均バスト
      ,max(boob) AS 最大バスト
  FROM kagura
 GROUP BY team;

   team    | 最小バスト |   平均バスト   | 最大バスト
-----------+-----------------+---------------------+-----------------
 半蔵    |              80 | 90.5000000000000000 |             100
 月閃    |              86 | 91.8000000000000000 |              96
 蛇女    |              69 | 91.7142857142857143 |             105
 DLC       |              92 | 93.5000000000000000 |              95
 紅蓮    |              62 | 85.6000000000000000 |              99
 千年祭 |              73 | 89.0000000000000000 |              96

HAVING句

平均を下げているチームをなかったことにする

SELECT team
      ,min(boob) AS 最小バスト
      ,avg(boob) AS 平均バスト
      ,max(boob) AS 最大バスト
  FROM kagura
 GROUP BY team
HAVING avg(boob) > 90;

HAVINGは集合の性質を絞り込むもの
- 条件には集約関数か、GROUP BYに指定した類別カラムを記述する

WHERE句、GROUP BY句、HAVING句の適用順序

順序
1. WHERE
2. GROUP BY
3. HAVING
【補】先に件数を絞ったほうがパフォーマンス上良いので、
WHEREでできることをHAVINGでやらない

-- これらは同じ結果を返すクエリ

-- WHEREで7人に絞ってから集約関数を適用している
-- 速い(はず)
SELECT 'A'       AS 血液型
      ,count(*)  AS 人数
      ,min(boob) AS 最小バスト
      ,avg(boob) AS 平均バスト
      ,max(boob) AS 最大バスト
  FROM kagura
 WHERE blood_type = 'A';



-- 29人全員についてGROUP BYで類別し集約関数をそれぞれに適用している
-- 遅い(はず)
SELECT blood_type
      ,count(*)  AS 人数
      ,min(boob) AS 最小バスト
      ,avg(boob) AS 平均バスト
      ,max(boob) AS 最大バスト
  FROM kagura
 GROUP BY blood_type
HAVING blood_type = 'A';

     blood_type | 人数 | 最小バスト |   平均バスト   | 最大バスト
------------+--------+-----------------+---------------------+-----------------
 A          |      7 |              62 | 90.2857142857142857 |             105
(1 row)

副問い合わせ

SELECT文の中でさらにSELECT文
副問い合わせの結果に基づいて主問い合わせを実行することができる
- 動的な条件

EXISTS演算子

【補】存在量化
- 全称量化は二重否定で表現する
  ∀xPx = ￢∃x￢Px
みかん(prod_id = 4)は、ordersテーブルにはない

SELECT * FROM prod;

 prod_id | prod_name | price
---------+-----------+-------
       1 | みかん |    50
       2 | りんご |    70
       3 | メロン |   100
       4 | みかん |    31
(4 rows)


SELECT * FROM orders;

 order_id |         order_date         | customer_id | prod_id | qty
----------+----------------------------+-------------+---------+-----
        1 | 2019-01-28 23:04:57.664912 |           1 |       1 |  10
        2 | 2019-01-28 23:04:57.689655 |           2 |       2 |   5
        3 | 2019-01-28 23:04:57.714919 |           3 |       3 |   8
        4 | 2019-01-28 23:04:57.742378 |           2 |       1 |   3
        5 | 2019-01-28 23:04:57.764938 |           3 |       2 |   4

あるprod_idについて、ordersテーブルに下記条件を満たす行が存在する場合のみ
prod.prod_id, prod.prod_nameを表示
- orders.prod_id = prod.prod_id
- orders.qty > 5

SELECT prod_id
      ,prod_name
  FROM prod
 WHERE EXISTS (SELECT *
                 FROM orders
                WHERE orders.prod_id = prod.prod_id
                  AND orders.qty > 5);

 prod_id | prod_name
---------+-----------
       1 | みかん
       3 | メロン
(2 rows)

【補】主問い合わせ側のprod.prod_idにより副問い合わせの結果が変わるので「相関副問い合わせ」と呼ぶ

IN演算子

さっきのはこうも書ける

SELECT prod_id
      ,prod_name
  FROM prod
 WHERE prod_id IN (SELECT orders.prod_id
                     FROM orders
                    WHERE orders.qty > 5);

こちらの副問い合わせは主問い合わせ側に依存しないため相関副問い合わせではない
- この場合、副問い合わせは1回しか実行されないはず
【補】パフォーマンス上、INをEXISTSで書き換えるのはよく行われることらしい
- EXISTSの利点
  - orders.prod_idでの検索でインデックスが効く
  - 「存在量化」なので、1つでも見つけた時点で検索を打ち切る

日付・時刻型データの取り扱い

日付形式を確認・設定する

SHOW DATESTYLE;

 DateStyle
-----------
 ISO, MDY
(1 row)

now()関数

SELECT now();

              now
-------------------------------
 2019-02-09 12:07:37.832881+00
(1 row)

タイムゾーン設定してなかったや

CURRENT_DATE/CURRENT_TIME/CURRENT_TIMESTAMP関数

SELECT CURRENT_DATE;

current_date
--------------
 2019-02-09
(1 row)

SELECT CURRENT_TIME;

    current_time
--------------------
 12:09:23.528644+00
(1 row)

SELECT CURRENT_TIMESTAMP;

       current_timestamp
-------------------------------
 2019-02-09 12:09:44.720597+00
(1 row)

【補】今月(2月)生まれの忍学生を探す

SELECT name
     , 誕生月
  FROM (SELECT name
               ,DATE_PART('month', birthday) AS 誕生月
          FROM kagura) TMP
 WHERE 誕生月 = DATE_PART('month', CURRENT_DATE);

   name    | 誕生月
-----------+-----------
 美野里 |         2
 雲雀    |         2
 詠       |         2
(3 rows)

複雑な結合

外部結合

バストサイズ表を作りたい
こうすると、例えば104-101が欠番になってしまう

SELECT boob, name
  FROM kagura
 ORDER BY boob DESC;

boob |       name
------+-------------------
  105 | 紫
  100 | 大道寺先輩
   99 | 春花
   98 | 両奈
   97 | 凛
   96 | 華毘
   96 | 叢
   95 | 両備(転身後)
   95 | 四季
   95 | 葛城
   95 | 神楽
   95 | 詠
   94 | 両姫
   93 | 蓮華
   93 | 斑鳩
   92 | 雪泉
   92 | 奈楽
   90 | 飛鳥
   90 | 雅緋
   90 | 夜桜
   88 | 忌夢
   87 | 焔
   86 | 美野里
   85 | 柳生
   85 | 日影
   80 | 雲雀
   73 | 華風流
   69 | 両備
   62 | 未来
(29 rows)

欠番も表示したい場合: 左外部結合
連番のビューをつくる

CREATE TABLE digits (
digit integer
);

INSERT INTO digits(digit) VALUES (0);
INSERT INTO digits(digit) VALUES (1);
INSERT INTO digits(digit) VALUES (2);
INSERT INTO digits(digit) VALUES (3);
INSERT INTO digits(digit) VALUES (4);
INSERT INTO digits(digit) VALUES (5);
INSERT INTO digits(digit) VALUES (6);
INSERT INTO digits(digit) VALUES (7);
INSERT INTO digits(digit) VALUES (8);
INSERT INTO digits(digit) VALUES (9);


-- 自己結合！
CREATE VIEW seq AS (
SELECT D1.digit * 100
     + D2.digit * 10
     + D3.digit * 1 AS seq
  FROM digits D1
 CROSS JOIN digits D2
 CROSS JOIN digits D3
);

SELECT seq
  FROM seq
 WHERE seq BETWEEN (SELECT MIN(boob) FROM kagura)
               AND (SELECT MAX(boob) FROM kagura)
 ORDER BY seq DESC;

seq
-----
 105
 104
 103
 102
 101
 100
...
  65
  64
  63
  62
(44 rows)

これにkaguraテーブルをLEFT OUTER JOINする

SELECT seq AS boob
      ,kagura.name
  FROM seq
  LEFT OUTER JOIN kagura
    ON seq.seq = kagura.boob
 WHERE seq BETWEEN (SELECT MIN(boob) FROM kagura)
               AND (SELECT MAX(boob) FROM kagura)
 ORDER BY seq DESC;

 boob |       name
------+-------------------
  105 | 紫
  104 |
  103 |
  102 |
  101 |
  100 | 大道寺先輩
   99 | 春花
   98 | 両奈
   97 | 凛
   96 | 華毘
   96 | 叢
   95 | 両備(転身後)
   95 | 四季
   95 | 葛城
   95 | 神楽
   95 | 詠
   94 | 両姫
   93 | 蓮華
   93 | 斑鳩
   92 | 雪泉
   92 | 奈楽
   91 |
   90 | 飛鳥
   90 | 雅緋
   90 | 夜桜
   89 |
   88 | 忌夢
   87 | 焔
   86 | 美野里
   85 | 柳生
   85 | 日影
   84 |
   83 |
   82 |
   81 |
   80 | 雲雀
   79 |
   78 |
   77 |
   76 |
   75 |
   74 |
   73 | 華風流
   72 |
   71 |
   70 |
   69 | 両備
   68 |
   67 |
   66 |
   65 |
   64 |
   63 |
   62 | 未来
(54 rows)

自己結合

組み合わせを得るのに使うやつ
外部結合にて、連番ビューを作るときに使っている
- 0-9を3つ組み合わせることで0-999の連番を作っている

LIMIT句による検索行数制限、OFFSET句

バスト表を全部表示するとすごく長くなってしまう
10件に絞る

SELECT seq AS boob
      ,kagura.name
  FROM seq
  LEFT OUTER JOIN kagura
    ON seq.seq = kagura.boob
 WHERE seq BETWEEN (SELECT MIN(boob) FROM kagura)
               AND (SELECT MAX(boob) FROM kagura)
 ORDER BY seq DESC
 LIMIT 10;

 boob |      name
------+-----------------
  105 | 紫
  104 |
  103 |
  102 |
  101 |
  100 | 大道寺先輩
   99 | 春花
   98 | 両奈
   97 | 凛
   96 | 華毘
(10 rows)

次の10件

SELECT seq AS boob
      ,kagura.name
  FROM seq
  LEFT OUTER JOIN kagura
    ON seq.seq = kagura.boob
 WHERE seq BETWEEN (SELECT MIN(boob) FROM kagura)
               AND (SELECT MAX(boob) FROM kagura)
 ORDER BY seq DESC
 LIMIT 10
OFFSET 10;

 boob |       name
------+-------------------
   96 | 叢
   95 | 両備(転身後)
   95 | 詠
   95 | 神楽
   95 | 四季
   95 | 葛城
   94 | 両姫
   93 | 蓮華
   93 | 斑鳩
   92 | 奈楽
(10 rows)

問い合わせ結果の順番はORDER BYしない限り保証されないことに留意する

勉強日記

チラ裏

閃乱カグラのデータで学ぶOSSDB標準教科書5章 SQLによるデータベースの操作応用編

第5章 SQLによるデータベースの操作応用編

AND/OR演算子

LIKE演算子

BETWEEN演算子

集約関数

count関数

sum関数

avg関数

max,min関数

GROUP BY句と集約関数の組み合わせ

HAVING句

WHERE句、GROUP BY句、HAVING句の適用順序

副問い合わせ

EXISTS演算子

IN演算子

日付・時刻型データの取り扱い

日付形式を確認・設定する

now()関数

CURRENT_DATE/CURRENT_TIME/CURRENT_TIMESTAMP関数

複雑な結合

外部結合

自己結合

LIMIT句による検索行数制限、OFFSET句

第5章 SQLによるデータベースの操作 応用編

AND/OR演算子

LIKE演算子

BETWEEN演算子

集約関数

count関数

sum関数

avg関数

max,min関数

GROUP BY句と集約関数の組み合わせ

HAVING句

WHERE句、GROUP BY句、HAVING句の適用順序

副問い合わせ

EXISTS演算子

IN演算子

日付・時刻型データの取り扱い

日付形式を確認・設定する

now()関数

CURRENT_DATE/CURRENT_TIME/CURRENT_TIMESTAMP関数

複雑な結合

外部結合

自己結合

LIMIT句による検索行数制限、OFFSET句

第5章 SQLによるデータベースの操作応用編