clickhouse-client --host=aggregator7v.dev.itim.vn --user=stats_webui --password=DsVcN9prGqZTA8s5 --query="select count(*) as C, count(distinct user_id) as U, region, age, gender, query from (select user_id, splitByChar('&', replaceAll(replaceOne(lowerUTF8(decodeURLComponent(request)), 'http://coccoc.com/search/#query=', ''), '+', ' '))[1] as query from browser.clickdata where event_date = '2018-08-10' and request like 'http://coccoc.com/search/#query=%') any inner join (select user_id, region, gender, age from (select user_id, case when region_id in (198 ,307 ,308 ,309 ,310 ,311 ,312 ,314 ,316 ,317 ,318) then 'angiang' when region_id in (15 ,16 ,17 ,18 ,19 ,20 ,21 ,22) then 'vungtau' when region_id in (80 ,81 ,82 ,83 ,84 ,85 ,86 ,87 ,88 ,89) then 'bacgiang' when region_id in (319 ,447 ,448 ,449 ,450 ,451 ,452 ,453) then 'backan' when region_id in (437 ,438 ,439 ,441 ,442 ,444 ,445) then 'baclieu' when region_id in (94 ,95 ,97 ,98 ,99 ,100 ,101 ,102) then 'bacninh' when region_id in (196 ,205 ,206 ,207 ,208 ,209 ,210 ,211 ,212) then 'bentre' when region_id in (44 ,45 ,46 ,47 ,48 ,49 ,50 ,51 ,52) then 'binhdinh' when region_id in (71 ,73 ,74 ,76 ,77 ,78 ,79) then 'binhduong' when region_id in (440 ,519 ,520 ,521 ,522 ,523 ,524 ,525 ,526) then 'binhphuoc' when region_id in (151 ,152 ,153 ,154 ,155 ,156 ,157 ,158 ,159 ,160) then 'binhthuan' when region_id in (315 ,347 ,348 ,349 ,350 ,351 ,352 ,353 ,354) then 'camau' when region_id in (103) then 'cantho' when region_id in (401 ,402 ,403 ,404 ,405 ,406 ,407 ,408 ,409 ,410 ,411 ,412) then 'caobang' when region_id in (4) then 'danang' when region_id in (421 ,423 ,424 ,425 ,426 ,427 ,428 ,429 ,430 ,431 ,432 ,433 ,434 ,435 ,436) then 'daklak' when region_id in (529 ,530 ,532 ,533 ,534 ,535) then 'daknong' when region_id in (469 ,470 ,471 ,472 ,473) then 'dienbien' when region_id in (3 ,32 ,34 ,36 ,37 ,38 ,39 ,40 ,41 ,42 ,43) then 'dongnai' when region_id in (197 ,258 ,259 ,260 ,261 ,262 ,263 ,264 ,266 ,267) then 'dongthap' when region_id in (114 ,115 ,116 ,117 ,118 ,119 ,120 ,121 ,122 ,123 ,124) then 'gialai' when region_id in (377 ,378 ,379 ,380 ,381 ,382 ,383 ,384) then 'hagiang' when region_id in (272 ,273 ,274 ,275 ,276 ,277) then 'hanam' when region_id in (1) then 'hanoi' when region_id in (213 ,214 ,215 ,216 ,217 ,218 ,219 ,220 ,221 ,222) then 'hatinh' when region_id in (53 ,54 ,55 ,56 ,57 ,58 ,59 ,60 ,61 ,62 ,63) then 'haiduong' when region_id in (5) then 'haiphong' when region_id in (474 ,475 ,476 ,477 ,478 ,479) then 'haugiang' when region_id in (2) then 'hochiminh' when region_id in (172 ,443 ,494 ,495 ,496 ,497 ,498 ,499 ,500 ,501) then 'hoabinh' when region_id in (287 ,288 ,289 ,290 ,291 ,292 ,293 ,294 ,295 ,296) then 'hungyen' when region_id in (64 ,65 ,66 ,67 ,68 ,69 ,70) then 'khanhhoa' when region_id in (199 ,337 ,338 ,339 ,340 ,341 ,342 ,343 ,344 ,345 ,346) then 'kiengiang' when region_id in (527 ,528) then 'kontum' when region_id in (464 ,465 ,466 ,467 ,468) then 'laichau' when region_id in (297 ,298 ,299 ,300 ,301 ,302 ,303 ,304 ,305 ,306) then 'lamdong' when region_id in (454 ,455 ,456 ,457 ,458 ,459 ,460 ,461 ,462 ,463) then 'langson' when region_id in (362 ,363 ,364 ,365 ,366 ,368 ,369) then 'laocai' when region_id in (245 ,246 ,247 ,248 ,249 ,250 ,252 ,253 ,254 ,255 ,256 ,257) then 'longan' when region_id in (104 ,105 ,106 ,107 ,108 ,109 ,110 ,111 ,112 ,113) then 'namdinh' when region_id in (161 ,162 ,163 ,164 ,165 ,166 ,167 ,168 ,169 ,170 ,171 ,173 ,174 ,175 ,176 ,177 ,178 ,179 ,180 ,181) then 'nghean' when region_id in (182 ,183 ,184 ,185 ,186 ,187 ,188 ,189) then 'ninhbinh' when region_id in (480 ,481 ,482 ,483 ,484) then 'ninhthuan' when region_id in (265 ,385 ,386 ,387 ,388 ,389 ,390 ,391 ,392 ,393 ,90 ,91 ,92 ,93) then 'phutho' when region_id in (230 ,268 ,269 ,270 ,271) then 'quangbinh' when region_id in (6 ,8 ,9 ,10 ,11 ,12 ,13 ,14) then 'quangnam' when region_id in (485 ,486 ,487 ,488 ,489 ,490 ,491 ,492 ,493) then 'quangngai' when region_id in (231 ,232 ,233 ,234 ,235 ,236 ,237 ,238 ,239 ,240 ,242 ,243 ,244) then 'quangninh' when region_id in (278 ,279 ,280 ,281 ,282 ,283 ,284 ,285 ,286) then 'quangtri' when region_id in (202 ,413 ,414 ,415 ,416 ,417 ,418 ,419 ,420) then 'soctrang' when region_id in (511 ,512 ,513 ,514 ,515 ,516 ,517) then 'sonla' when region_id in (201 ,313 ,394 ,395 ,396 ,397 ,398 ,399 ,400) then 'tayninh' when region_id in (144 ,145 ,146 ,147 ,148 ,149 ,150) then 'thaibinh' when region_id in (502 ,503 ,504 ,505 ,506 ,507 ,508 ,509 ,510) then 'thainguyen' when region_id in (125 ,126 ,127 ,128 ,129 ,130 ,131 ,132 ,133 ,134 ,135 ,136 ,137 ,138 ,139 ,140 ,141 ,142 ,143) then 'thanhhoa' when region_id in (23 ,25 ,26 ,27 ,28 ,29 ,30 ,31) then 'thuathienhue' when region_id in (190 ,191 ,192 ,193 ,194 ,195 ,203 ,204) then 'tiengiang' when region_id in (200 ,370 ,371 ,372 ,373 ,374 ,375 ,376) then 'travinh' when region_id in (355 ,356 ,357 ,358 ,359 ,360 ,361) then 'tuyenquang' when region_id in (223 ,224 ,225 ,226 ,227 ,228 ,229) then 'vinhlong' when region_id in (320 ,322 ,323 ,324 ,325 ,326 ,327 ,328 ,329 ,330) then 'vinhphuc' when region_id in (331 ,332 ,333 ,334 ,335 ,336) then 'yenbai' else 'other' end as region from targeting.geo where event_date = '2018-08-10') any inner join (select user_id, gender, age from targeting.demography where event_date = '2018-08-10') using user_id) using user_id group by region, age, gender, query order by C desc, U desc, region, age, gender" >test4.csv 2>&1 &