回答編集履歴
3
要望に応えて動作の詳細を追記
answer
CHANGED
|
File without changes
|
2
要望に応えて動作の詳細を追記
answer
CHANGED
|
@@ -55,4 +55,193 @@
|
|
|
55
55
|
### 説明資料
|
|
56
56
|
SQL全体でやりたいのは、Coddの関係演算（代数）の「商」です。まず資料を読んで考えてください。「商」は「積」の反対演算です。hotels_tags ÷ tgasをやりたいのです。not existsを2回使う意味は、~~補集合の補集合~~二重否定をとるためですが、今はわからなくてよいです。
|
|
57
57
|
|
|
58
|
-
[https://ja.wikipedia.org/wiki/%E9%96%A2%E4%BF%82%E4%BB%A3%E6%95%B0_(%E9%96%A2%E4%BF%82%E3%83%A2%E3%83%87%E3%83%AB)](https://ja.wikipedia.org/wiki/%E9%96%A2%E4%BF%82%E4%BB%A3%E6%95%B0_(%E9%96%A2%E4%BF%82%E3%83%A2%E3%83%87%E3%83%AB))
|
|
58
|
+
[https://ja.wikipedia.org/wiki/%E9%96%A2%E4%BF%82%E4%BB%A3%E6%95%B0_(%E9%96%A2%E4%BF%82%E3%83%A2%E3%83%87%E3%83%AB)](https://ja.wikipedia.org/wiki/%E9%96%A2%E4%BF%82%E4%BB%A3%E6%95%B0_(%E9%96%A2%E4%BF%82%E3%83%A2%E3%83%87%E3%83%AB))
|
|
59
|
+
|
|
60
|
+
### 相関副問合せをもつ exists / not exists （ここから、2018-03-02)
|
|
61
|
+
くどいかもしれませんが、動作を知りたいという要望に応えます。このあと、まとめとして、関係演算の積、商の解説、さらに、すべてを検索するのになぜexistsを使用するかを解説する予定ですが、そちらで、これについて別の質問をたてたほうがよいかも。(疲れたので休憩します）
|
|
62
|
+
|
|
63
|
+
**相関副問合せにおける exists/not exist の動作**
|
|
64
|
+
|
|
65
|
+
exists/not existの動作を if 構文に直して考えます。外側の検索で読まれたレコード1件ずつを判定の対象とします。
|
|
66
|
+
|
|
67
|
+
```SQL
|
|
68
|
+
<外側の検索で読まれたレコード> -- <-- 1件が判定の対象
|
|
69
|
+
if exists|not exists(<外側の検索で読まれたレコード>の項目の値を使うselectの結果)
|
|
70
|
+
<外側の検索で読まれたレコード>を採用する -- <-- 上の exists/ not existsがtrueの場合
|
|
71
|
+
else
|
|
72
|
+
<外側の検索で読まれたレコード>を採用しない
|
|
73
|
+
```
|
|
74
|
+
|
|
75
|
+
### SQL動作確認：（関係演算の除算）ホテルとタグの組がすべて存在するホテルを検索
|
|
76
|
+
|
|
77
|
+
explain を使ってSQLを調べます。(JSON 形式の出力を行う exlain format=json <SQL> ... もあります。こちらは詳細が表示されます)
|
|
78
|
+
|
|
79
|
+
```SQL
|
|
80
|
+
> explain select hotel_id from hotels a where not exists(
|
|
81
|
+
-> select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
|
|
82
|
+
-> select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
|
|
83
|
+
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+--------------------------+
|
|
84
|
+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
|
85
|
+
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+--------------------------+
|
|
86
|
+
| 1 | PRIMARY | a | index | NULL | hotel_id | 6 | NULL | 12 | Using where; Using index |
|
|
87
|
+
| 2 | DEPENDENT SUBQUERY | c | range | tag | tag | 302 | NULL | 2 | Using where; Using index |
|
|
88
|
+
| 3 | DEPENDENT SUBQUERY | b | index_subquery | hotel_id | hotel_id | 4 | func | 8 | Using index; Using where |
|
|
89
|
+
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+--------------------------+
|
|
90
|
+
3 rows in set (0.001 sec)
|
|
91
|
+
```
|
|
92
|
+
|
|
93
|
+
explainからわかるのは select の入れ子です。select_type の意味は次のとおり。
|
|
94
|
+
- PRIMARY 主検索
|
|
95
|
+
- DEPENDENCY SUBQUERY 外側の検索結果を使用する検索（外側の検索が先に実行される）
|
|
96
|
+
|
|
97
|
+
上の検索 id.1,id.2,id.3 はすべて「選択」であり「結合」はありません。依存関係は、id.1 <- id.2 <- id.3。以下の疑似コードで動作を示します。
|
|
98
|
+
|
|
99
|
+
``` pseudo sql
|
|
100
|
+
hotels a を全件検索 <-- id.1
|
|
101
|
+
hotelsのレコードごとに以下を実行
|
|
102
|
+
tags c のレコード'朝食付き','キャンセル無料'を検索する。 <-- id.2
|
|
103
|
+
各レコード(2,'朝食付き'),(3,'キャンセル無料')のそれぞれに以下を実行する
|
|
104
|
+
hotels_tags b のレコードを検索する。(<ホテルid>,<タグid=2>)と(<ホテルid>,<タグid=3>)の2回 <-- id.3
|
|
105
|
+
```
|
|
106
|
+
|
|
107
|
+
ここまでで、SQLの動作の概要を理解してください。
|
|
108
|
+
|
|
109
|
+
### SQL: ホテルごとの動作の確認
|
|
110
|
+
|
|
111
|
+
ホテルと検索対象のタグの存在有無の組み合わせを調べます。検索で選択されるホテルは、すべてのタグを持つ「クラウン リージェンシー ホテル & タワーズ」だけです。これらの3つのホテルを確認用データとして使います。
|
|
112
|
+
|
|
113
|
+
|hotel_id|ホテル|「朝食付き」(tags_id:2)|「キャンセル無料」(tags_id:3)|
|
|
114
|
+
|:--:|:--|:--|:--|
|
|
115
|
+
|1|クラウン リージェンシー ホテル & タワーズ|○|○|
|
|
116
|
+
|5|レッド プラネット セブ|ー|○|
|
|
117
|
+
|8|パーム グラス ホテル|ー|ー|
|
|
118
|
+
|
|
119
|
+
まず、うえのSQLの検索条件に hotel_id を追加、1ホテルごとの検索の和に変形します。
|
|
120
|
+
|
|
121
|
+
```SQL
|
|
122
|
+
select hotel_id from hotels a where hotel_id = 1 and not exists( ...
|
|
123
|
+
union
|
|
124
|
+
...
|
|
125
|
+
union
|
|
126
|
+
select hotel_id from hotels a where hotel_id = 5 and not exists( ...
|
|
127
|
+
union
|
|
128
|
+
...
|
|
129
|
+
union
|
|
130
|
+
select hotel_id from hotels a where hotel_id = 8 and not exists( ...
|
|
131
|
+
union
|
|
132
|
+
...
|
|
133
|
+
```
|
|
134
|
+
|
|
135
|
+
**確認してください-1**
|
|
136
|
+
上の個別SQLの hotel_id を、1, 5, 8 のそれぞれで実行して動作を確認してください。
|
|
137
|
+
|
|
138
|
+
### SQL(id.2-1): 必要なタグを検索
|
|
139
|
+
タグの検索は全てのホテルで同じです。次の副問合せ(id.3)で使用する tags_id、2, 3 を取得します。すべてのホテルで使用するタグの定数レコードが二件。
|
|
140
|
+
|
|
141
|
+
```SQL
|
|
142
|
+
select c.tags_id from tags c where c.tag in ('朝食付き','キャンセル無料')
|
|
143
|
+
```
|
|
144
|
+
|
|
145
|
+
### SQL(id.3): ホテル、タグの組の検索
|
|
146
|
+
SQL(id.2-1)の各定数( tags_id: 2, 3 )を使って以下の問い合わせを実行します。not existsが二回実行されます。
|
|
147
|
+
|
|
148
|
+
```SQL
|
|
149
|
+
not exists(select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id)
|
|
150
|
+
```
|
|
151
|
+
|
|
152
|
+
**確認してください-2**
|
|
153
|
+
hotel_idが、1、 5、 8、tags_idが、2、3 の組み合わせで以下の動作を確認してください。MySQLの結果は、trueが 1、falseが 0 になります。
|
|
154
|
+
組み合わせは 6 とおりです。指定する(ホテル、タグ)の組が存在すれば false、存在しなければ trueになります。
|
|
155
|
+
|
|
156
|
+
```SQL
|
|
157
|
+
select not exists(select 1 from hotels_tags b where <タグid(2,3のいずれか)> = b.tags_id and b.hotel_id = <ホテルのid(1,5,8のいずれか)>);
|
|
158
|
+
```
|
|
159
|
+
|
|
160
|
+
### SQL(id.2-2): ホテル、タグ検索(id.3)の結果をタグ検索にまとめる
|
|
161
|
+
|
|
162
|
+
各ホテルでtags_id 2,3 の検索を実行したので、UNIONを使って結果をまとめます。select 1 の部分は、わかりやすくするためつぎのように置き換えておきます。
|
|
163
|
+
- select '朝食付きなし' from ...
|
|
164
|
+
- select 'キャンセル無料なし' from ...
|
|
165
|
+
|
|
166
|
+
```SQL
|
|
167
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = <ホテルのid>)
|
|
168
|
+
union
|
|
169
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = <ホテルのid>);
|
|
170
|
+
```
|
|
171
|
+
|
|
172
|
+
**確認してください-3**
|
|
173
|
+
hotel_idが、1、5、8 のそれぞれで、上のまとめ検索SQL(id.2-2)を実行してください。確認するのは次のことです。
|
|
174
|
+
- (ホテル、タグ)が存在しなければ、まとめ検索でレコードが存在する。
|
|
175
|
+
- (ホテル、タグ)が2つ揃っているなら、まとめ検索のレコードは存在しない。
|
|
176
|
+
|
|
177
|
+
### SQL(id.1-2): ホテル、タグ検索の結果をタグ検索にまとめたものSQL(id.2-2)に not exists を適用する。
|
|
178
|
+
|
|
179
|
+
最後のホテルの判定処理は、ホテル、タグ検索の結果をタグ検索にまとめたものを使って、ホテルごとになされます。
|
|
180
|
+
|
|
181
|
+
```SQL
|
|
182
|
+
select hotel_id from hotels a where a.hotel_id = <ホテルのid> and not exists(
|
|
183
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = <ホテルのid>)
|
|
184
|
+
union
|
|
185
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = <ホテルのid>)
|
|
186
|
+
);
|
|
187
|
+
```
|
|
188
|
+
|
|
189
|
+
**確認してください-4**
|
|
190
|
+
hotel_idが、1、5、8 のそれぞれで、上のまとめ検索を実行してください。確認するのは次のことです。
|
|
191
|
+
- (ホテル、タグ)がひとつでも存在しなければ、ホテルが選択されない。
|
|
192
|
+
- (ホテル、タグ)が2つ揃っているときに限りホテルが選択される。
|
|
193
|
+
|
|
194
|
+
以上が動作の詳細です。プログラマーはこの動作を無意識のうちに（直観的に）考えながらSQLを組み立てています。
|
|
195
|
+
|
|
196
|
+
### 確認してください sql 一覧
|
|
197
|
+
確認してください-1
|
|
198
|
+
```SQL
|
|
199
|
+
select hotel_id from hotels a where hotel_id = 1 and not exists(
|
|
200
|
+
select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
|
|
201
|
+
select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
|
|
202
|
+
select hotel_id from hotels a where hotel_id = 5 and not exists(
|
|
203
|
+
select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
|
|
204
|
+
select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
|
|
205
|
+
select hotel_id from hotels a where hotel_id = 8 and not exists(
|
|
206
|
+
select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
|
|
207
|
+
select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
|
|
208
|
+
```
|
|
209
|
+
確認してください-2
|
|
210
|
+
```SQL
|
|
211
|
+
select not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 1);
|
|
212
|
+
select not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 1);
|
|
213
|
+
select not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 5);
|
|
214
|
+
select not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 5);
|
|
215
|
+
select not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 8);
|
|
216
|
+
select not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 8);
|
|
217
|
+
```
|
|
218
|
+
確認してください-3
|
|
219
|
+
```SQL
|
|
220
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 1)
|
|
221
|
+
union
|
|
222
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 1);
|
|
223
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 5)
|
|
224
|
+
union
|
|
225
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 5);
|
|
226
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 8)
|
|
227
|
+
union
|
|
228
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 8);
|
|
229
|
+
```
|
|
230
|
+
確認してください-4
|
|
231
|
+
```SQL
|
|
232
|
+
select hotel_id from hotels a where a.hotel_id = 1 and not exists(
|
|
233
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 1)
|
|
234
|
+
union
|
|
235
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 1)
|
|
236
|
+
);
|
|
237
|
+
select hotel_id from hotels a where a.hotel_id = 5 and not exists(
|
|
238
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 5)
|
|
239
|
+
union
|
|
240
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 5)
|
|
241
|
+
);
|
|
242
|
+
select hotel_id from hotels a where a.hotel_id = 8 and not exists(
|
|
243
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 8)
|
|
244
|
+
union
|
|
245
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 8)
|
|
246
|
+
);
|
|
247
|
+
```
|
1
補集合の補集合を削除、二重否定に変更する。
answer
CHANGED
|
@@ -53,6 +53,6 @@
|
|
|
53
53
|
|
|
54
54
|
|
|
55
55
|
### 説明資料
|
|
56
|
-
SQL全体でやりたいのは、Coddの関係演算（代数）の「商」です。まず資料を読んで考えてください。「商」は「積」の反対演算です。hotels_tags ÷ tgasをやりたいのです。not existsを2回使う意味は、補集合の補集合をとるためですが、今はわからなくてよいです。
|
|
56
|
+
SQL全体でやりたいのは、Coddの関係演算（代数）の「商」です。まず資料を読んで考えてください。「商」は「積」の反対演算です。hotels_tags ÷ tgasをやりたいのです。not existsを2回使う意味は、~~補集合の補集合~~二重否定をとるためですが、今はわからなくてよいです。
|
|
57
57
|
|
|
58
58
|
[https://ja.wikipedia.org/wiki/%E9%96%A2%E4%BF%82%E4%BB%A3%E6%95%B0_(%E9%96%A2%E4%BF%82%E3%83%A2%E3%83%87%E3%83%AB)](https://ja.wikipedia.org/wiki/%E9%96%A2%E4%BF%82%E4%BB%A3%E6%95%B0_(%E9%96%A2%E4%BF%82%E3%83%A2%E3%83%87%E3%83%AB))
|