本文转载自微信公众号「巡山猫说数据」,作者巡山猫说数据。转载本文请联系巡山猫说数据公众号。
你好,我是巡山猫!
今天我们来讲讲Hive中最常用的 where 语句知识要点。
01-基础语法
命令如下:
- SELECT 列名
- FROM 表名
- WHERE 列名 运算符 值 [AND 列名 运算符 值] [OR 列名 运算符 值];
备注:WHERE后的列名必须用真实列名,不能使用自定义的别名,否则会报错。
02-谓词操作符详解
下面表中描述了谓词操作符,这些操作符同样可以用于JOIN…ON和HAVING语句中,后续我们会逐一介绍这两个语句
备注:NULL在Hive中代表空值的意思。
03-AND & OR
用AND连接两个及以上的谓词操作符代表且的意思,即必须所有条件同时满足;用OR连接两个及以上的谓词操作符代表或的意思,即至少有一个条件满足即可。
下面举几个例子:
1、取出表t_od_use_cnt中满足以下条件的5条记录:
·日期为20190101到20190102之间;
·平台取ios平台(1为andriod,2为ios);
·当日使用次数大于等于30。
语句如下:
- SELECT *
- FROM app.t_od_use_cnt
- WHERE date_8 BETWEEN 20190101 AND 20190102
- AND platform = '2'
- AND use_cnt >= 30 limit 5;
备注:数值型的值可以直接写,但字符格式及string格式的值必须加引号
运行结果如下:
- hive (app)> SELECT *
- > FROM app.t_od_use_cnt
- > WHERE date_8 BETWEEN 20190101 AND 20190102
- > AND platform = '2'
- > AND use_cnt >= 30 limit 5;
- OK
- t_od_use_cnt.platform t_od_use_cnt.app_version t_od_use_cnt.user_id t_od_use_cnt.use_cnt t_od_use_cnt.is_active t_od_use_cnt.date_8
- 2 1.3 10001 49 1 20190101
- 2 1.1 10016 37 1 20190101
- 2 1.4 10025 44 1 20190101
- 2 1.1 10034 49 1 20190101
- 2 1.2 10039 37 1 20190101
- Time taken: 0.28 seconds, Fetched: 5 row(s)
2、取出表t_od_use_cnt中满足以下条件的5条记录:
·日期大于20190102;
·1,3和1.4版本且当日使用次数大于等于30 或 1,1和1.2版本且当日使用次数大于等于20。
语句如下:
- SELECT *
- FROM app.t_od_use_cnt
- WHERE date_8 > 20190102
- AND (
- (
- app_version IN ('1.3', '1.4')
- AND use_cnt >= 30
- )
- OR (
- app_version IN ('1.1', '1.2')
- AND use_cnt >= 20
- )
- ) limit 5;
备注:当and、or同时出现时,要使用()将条件区分开,Hive会优先执行括号内的条件语句。这样一来避免逻辑错误,二来使逻辑更清晰。
运行结果如下:
- hive (app)> SELECT *
- > FROM app.t_od_use_cnt
- > WHERE date_8 > 20190102
- > AND (
- > (
- > app_version IN ('1.3', '1.4')
- > AND use_cnt >= 30
- > )
- > OR (
- > app_version IN ('1.1', '1.2')
- > AND use_cnt >= 20
- > )
- > ) limit 5;
- OK
- t_od_use_cnt.platform t_od_use_cnt.app_version t_od_use_cnt.user_id t_od_use_cnt.use_cnt t_od_use_cnt.is_active t_od_use_cnt.date_8
- 2 1.2 10400 40 1 20190103
- 1 1.2 10402 32 1 20190103
- 1 1.2 10403 41 1 20190103
- 2 1.4 10405 50 1 20190103
- 2 1.1 10406 24 1 20190103
- Time taken: 0.298 seconds, Fetched: 5 row(s)
04-Like & Rlike & Regexp
这三种语句的含义和用法上面已经有所介绍,rlike、regexp功能和like功能一致,只是like是后面只支持简单表达式匹配(_%),而rlike、regexp则支持标准正则表达式语法。所以如果正则表达式使用熟练的话,建议使用rlike,功能更加强大。所有的like匹配都可以被替换成rlike。反之,则不行。注意事项:like是从头逐一字符匹配的,但是rlike则不是。这里我们举几个例子。
1、取出表t_od_use_cnt中满足以下条件的5条记录:
·日期大于20190102;
·user_id以5结尾
语句如下:
- SELECT *
- FROM app.t_od_use_cnt
- WHERE date_8 > 20190102
- AND user_id LIKE '%5' limit 5;
运行结果如下:
- hive (app)> SELECT *
- > FROM app.t_od_use_cnt
- > WHERE date_8 > 20190102
- > AND user_id LIKE '%5' limit 5;
- OK
- t_od_use_cnt.platform t_od_use_cnt.app_version t_od_use_cnt.user_id t_od_use_cnt.use_cnt t_od_use_cnt.is_active t_od_use_cnt.date_8
- 2 1.4 10405 50 1 20190103
- 1 1.1 10415 30 1 20190103
- 2 1.3 10425 15 1 20190103
- 1 1.3 10435 3 0 20190103
- 1 1.4 10445 43 1 20190103
- Time taken: 0.977 seconds, Fetched: 5 row(s)
2、取出表t_od_use_cnt中满足以下条件的5条记录:
·日期大于等于20190102;
·user_id中含有45。
语句如下:
- SELECT *
- FROM app.t_od_use_cnt
- WHERE date_8 > 20190102
- AND user_id RLIKE '45' limit 5;
还可以把RLIKE替换为REGEXP:
- SELECT *
- FROM app.t_od_use_cnt
- WHERE date_8 > 20190102
- AND user_id REGEXP '45' limit 5;
运行结果如下:
- hive (app)> SELECT *
- > FROM app.t_od_use_cnt
- > WHERE date_8 > 20190102
- > AND user_id RLIKE '45' limit 5;
- OK
- t_od_use_cnt.platform t_od_use_cnt.app_version t_od_use_cnt.user_id t_od_use_cnt.use_cnt t_od_use_cnt.is_active t_od_use_cnt.date_8
- 1 1.4 10445 43 1 20190103
- 2 1.5 10450 6 1 20190103
- 2 1.5 10451 38 1 20190103
- 1 1.4 10452 44 1 20190103
- 1 1.5 10453 37 1 20190103
- Time taken: 0.13 seconds, Fetched: 5 row(s)
- hive (app)> SELECT *
- > FROM app.t_od_use_cnt
- > WHERE date_8 > 20190102
- > AND user_id REGEXP '45' limit 5;
- OK
- t_od_use_cnt.platform t_od_use_cnt.app_version t_od_use_cnt.user_id t_od_use_cnt.use_cnt t_od_use_cnt.is_active t_od_use_cnt.date_8
- 1 1.4 10445 43 1 20190103
- 2 1.5 10450 6 1 20190103
- 2 1.5 10451 38 1 20190103
- 1 1.4 10452 44 1 20190103
- 1 1.5 10453 37 1 20190103
- Time taken: 0.352 seconds, Fetched: 5 row(s)
RLIKE、REGEXP主要用于正则匹配,具体的正则表达式匹配规则我们后面的文章来具体说明。