teratail
回答率
85.48%
質問するログイン新規登録
トップElasticsearchに関する質問一覧検索と分類ごとの件数取得を効率的に行いたい

編集履歴

回答編集履歴

4

chousei

2018/12/06 06:50

投稿

yambejp
yambejp

スコア114839

test CHANGED
@@ -204,6 +204,8 @@
204
204
 
205
205
  - 国が'日本'または承認が'未承認'
206
206
 
207
+ ```SQL
208
+
207
209
  select '国' as カテゴリ,国 as 要素,sum(1 and (0 or 承認 in('未承認'))) as 件数 from tbl group by 国
208
210
 
209
211
  union all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by 承認
@@ -216,6 +218,8 @@
216
218
 
217
219
  これを更に集計するとこう
218
220
 
221
+ ```SQL
222
+
219
223
  select カテゴリ,group_concat(concat(要素,":",件数)) as 要素件数 from(
220
224
 
221
225
  select '国' as カテゴリ,国 as 要素,sum(1 and (0 or 承認 in('未承認'))) as 件数 from tbl group by 国

3

調整

2018/12/06 06:50

投稿

yambejp
yambejp

スコア114839

test CHANGED
@@ -249,3 +249,13 @@
249
249
 
250
250
 
251
251
  これを受けてjsonを返してやればよいでしょう
252
+
253
+
254
+
255
+ ※注意
256
+
257
+ where句で絞り込みをすると0件のデータが表示されなくなります。
258
+
259
+ 0と表示したいなら今回のようなsumで集計、
260
+
261
+ 表示しなくてもいいなら(つまりjs側で例外処理が必要）where句で絞り込みをしてください

2

DB

2018/12/06 05:51

投稿

yambejp
yambejp

スコア114839

test CHANGED
@@ -117,3 +117,135 @@
117
117
  /* 実際には$sqlをprepareで処理してカテゴリごとの件数をjsonで返すのがベター */
118
118
 
119
119
  ```
120
+
121
+
122
+
123
+ # DB設計について
124
+
125
+ 集計のロジックを変えます
126
+
127
+ テーブルはこんな感じになります
128
+
129
+ ```SQL
130
+
131
+ create table tbl(id int primary key,国 varchar(10),承認 varchar(10),その他 varchar(10));
132
+
133
+ insert into tbl values
134
+
135
+ ( 1,'日本','承認済み','その他1'),
136
+
137
+ ( 2,'日本','承認済み','その他1'),
138
+
139
+ ( 3,'日本','承認済み','その他2'),
140
+
141
+ ( 4,'日本','承認済み','その他2'),
142
+
143
+ ( 5,'日本','承認済み','その他2'),
144
+
145
+ ( 6,'日本','承認済み','その他2'),
146
+
147
+ ( 7,'日本','承認済み','その他3'),
148
+
149
+ ( 8,'日本','未承認','その他1'),
150
+
151
+ ( 9,'日本','未承認','その他2'),
152
+
153
+ (10,'日本','未承認','その他2'),
154
+
155
+ (11,'米国','承認済み','その他1'),
156
+
157
+ (12,'米国','承認済み','その他1'),
158
+
159
+ (13,'米国','承認済み','その他1'),
160
+
161
+ (14,'米国','未承認','その他3'),
162
+
163
+ (15,'中国','承認済み','その他1'),
164
+
165
+ (16,'中国','承認済み','その他2'),
166
+
167
+ (17,'中国','承認済み','その他3'),
168
+
169
+ (18,'中国','承認済み','その他2');
170
+
171
+ ```
172
+
173
+ - 絞り込みなし
174
+
175
+ ```SQL
176
+
177
+ select '国' as カテゴリ,国 as 要素,sum(1) as 件数 from tbl group by 国
178
+
179
+ union all select '承認' as カテゴリ,承認 as 要素,sum(1) as 件数 from tbl group by 承認
180
+
181
+ union all select 'その他' as カテゴリ,その他 as 要素,sum(1) as 件数 from tbl group by その他
182
+
183
+ ```
184
+
185
+
186
+
187
+ - 国が'日本'
188
+
189
+ 国の集計以外の件数集計にor条件を追加します
190
+
191
+ ```SQL
192
+
193
+ select '国' as カテゴリ,国 as 要素,sum(1) as 件数 from tbl group by 国
194
+
195
+ union all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by 承認
196
+
197
+ union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by その他
198
+
199
+
200
+
201
+ ```
202
+
203
+
204
+
205
+ - 国が'日本'または承認が'未承認'
206
+
207
+ select '国' as カテゴリ,国 as 要素,sum(1 and (0 or 承認 in('未承認'))) as 件数 from tbl group by 国
208
+
209
+ union all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by 承認
210
+
211
+ union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 or 国 in('日本') or 承認 in('未承認'))) as 件数 from tbl group by その他
212
+
213
+ ```
214
+
215
+
216
+
217
+ これを更に集計するとこう
218
+
219
+ select カテゴリ,group_concat(concat(要素,":",件数)) as 要素件数 from(
220
+
221
+ select '国' as カテゴリ,国 as 要素,sum(1 and (0 or 承認 in('未承認'))) as 件数 from tbl group by 国
222
+
223
+ union all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by 承認
224
+
225
+ union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 or 国 in('日本') or 承認 in('未承認'))) as 件数 from tbl group by その他
226
+
227
+ ) as sub
228
+
229
+ group by カテゴリ
230
+
231
+ ```
232
+
233
+
234
+
235
+ - 結果：
236
+
237
+
238
+
239
+ |カテゴリ|要素件数|
240
+
241
+ |:--|:--|
242
+
243
+ |その他 |その他2:6,その他3:2,その他1:3|
244
+
245
+ |国 |中国:0,日本:3,米国:1|
246
+
247
+ |承認 |承認済み:7,未承認:3|
248
+
249
+
250
+
251
+ これを受けてjsonを返してやればよいでしょう

1

処理

2018/12/06 05:48

投稿

yambejp
yambejp

スコア114839

test CHANGED
@@ -25,3 +25,95 @@
25
25
  ```
26
26
 
27
27
  のような処理になります
28
+
29
+
30
+
31
+ # sample
32
+
33
+ - 送り側
34
+
35
+ ※今回は面倒なのでjQueryで
36
+
37
+ ```javascript
38
+
39
+ <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
40
+
41
+ <script>
42
+
43
+ $(function(){
44
+
45
+ $('[name="country[]"]').on('change',function(){
46
+
47
+ $.ajax({
48
+
49
+ url:"recv.php",
50
+
51
+ data:$('[name="country[]"]:checked').map(function(){
52
+
53
+ return "country[]="+encodeURIComponent($(this).val());
54
+
55
+ }).get().join("&"),
56
+
57
+ }).done(function(data){
58
+
59
+ console.log(data);
60
+
61
+ /* 実際にはここで承認済み、非承認の件数を書き換える */
62
+
63
+ });
64
+
65
+ });
66
+
67
+ });
68
+
69
+ </script>
70
+
71
+
72
+
73
+ <div><label><input type="checkbox" name="country[]" value="日本">日本</label>：10件</div>
74
+
75
+ <div><label><input type="checkbox" name="country[]" value="米国">米国</label>：4件</div>
76
+
77
+ <div><label><input type="checkbox" name="country[]" value="中国">中国</label>：4件</div>
78
+
79
+
80
+
81
+ <ul>
82
+
83
+ <li>承認済み:<span id="shouninzumi">0</span>件
84
+
85
+ <li>未承認:<span id="mishounin">0</span>件
86
+
87
+ </ul>
88
+
89
+ ```
90
+
91
+ - 受け側(recv.php)
92
+
93
+ ```PHP
94
+
95
+ <?PHP
96
+
97
+ $country=filter_input(INPUT_GET,"country", FILTER_DEFAULT , FILTER_REQUIRE_ARRAY);
98
+
99
+ $sql ="select カテゴリ,count(*) as 件数from tbl where 1 ";
100
+
101
+ $data=[];
102
+
103
+ if(count((array) $country)>0){
104
+
105
+ $sql.="and 国 in (".implode(",",array_fill(0,count($country),"?")).")";
106
+
107
+ $data=$country;
108
+
109
+ }
110
+
111
+ $sql.=" group by カテゴリ";
112
+
113
+ print $sql.";\n";
114
+
115
+ print_r($data);
116
+
117
+ /* 実際には$sqlをprepareで処理してカテゴリごとの件数をjsonで返すのがベター */
118
+
119
+ ```