- なんか飽きてきたから閃乱カグラのデータでやる
[f:id:wand_ta:20190209220111j:plain]
第5章 SQLによるデータベースの操作 応用編
-
Y/M/Dな日付を投入する場合、datestyleの設定が必要
show datestyle;
で現在のdatestyle設定値を確認set datestyle to 'YMD';
してからデータINSERT- 終わったら
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にインデックスが張ってあっても効かない
(インデックスの実装は探索木)
- 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句の適用順序
-
順序
- WHERE
- GROUP BY
- 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;
prodid | prodname | price ---------+-----------+------- 1 | みかん | 50 2 | りんご | 70 3 | メロン | 100 4 | みかん | 31 (4 rows)
SELECT * FROM orders;
orderid | orderdate | customerid | prodid | 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`
```sql
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)
- タイムゾーン設定してなかったや
CURRENTDATE/CURRENTTIME/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しない限り保証されないことに留意する