Hive基本函数

1、count(*)、count(1) 、count('字段名') 区别

从执行结果来看

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL 最慢的

• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL 最快的

• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计 仅次于count(1)

从执行效率来看

• 如果列为主键，count(列名)效率优于count(1)

• 如果列不为主键，count(1)效率优于count(列名)

• 如果表中存在主键，count(主键列名)效率最优

• 如果表中只有一列，则count(*)效率最优

• 如果表有多列，且不存在主键，则count(1)效率优于count(*)

在工作中如果没有特殊的要求，就使用count(1)来进行计数。

hive语句的执行顺序 1.from

2.join on 或 lateral view explode(需炸裂的列) tbl as 炸裂后的列名

3.where

4.group by

5.聚合函数 如Sum() avg() count(1)等

6.having 在此开始可以使用select中的别名

7.select 若包含over（）开窗函数，此时select中的内容作为窗口函数的输入，窗口中所选的数据范围也是在group by，having之后，并不是针对where后的数据进行开窗，这点要注意。需要注意开窗函数的执行顺序及时间点。

8.distinct

9.order by

10.limit

3、where 条件里不支持不等式子查询，实际上是支持 in、not in、exists、not exists

-- 列出与“SCOTT”从事相同工作的所有员工。
select  t1.EMPNO
        ,t1.ENAME
        ,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and t1.job in(
    select  job
    from emp
    where ENAME = "SCOTT");
    
7900,JAMES,CLERK,7698,1981-12-03,950,null,30
7902,FORD,ANALYST,7566,1981-12-03,3000,null,20
​
select  t1.EMPNO
        ,t1.ENAME
        ,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and exists(
    select  job
    from emp t2
    where ENAME = "SCOTT"
    and t1.job = t2.job
);

4、hive中大小写不敏感

5、在hive中，数据中如果有null字符串，加载到表中的时候会变成 null （不是字符串）

如果需要判断 null，使用 某个字段名 is null 这样的方式来判断

或者使用 nvl() 函数，不能 直接 某个字段名 == null

6、使用explain查看SQL执行计划

explain select  t1.EMPNO
        ,t1.ENAME
        ,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and t1.job in(
    select  job
    from emp
    where ENAME = "SCOTT");
    
# 查看更加详细的执行计划，加上extended
explain extended select  t1.EMPNO
        ,t1.ENAME
        ,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and t1.job in(
    select  job
    from emp
    where ENAME = "SCOTT");

Hive 常用函数

关系运算

// 等值比较 = == <=>
// 不等值比较 != <>
// 区间比较： select * from default.students where id between 1500100001 and 1500100010;
// 空值/非空值判断：is null、is not null、nvl()、isnull()
// like、rlike、regexp用法

数值计算

取整函数(四舍五入)：round
向上取整：ceil
向下取整：floor

条件函数

• if： if(表达式,如果表达式成立的返回值,如果表达式不成立的返回值)

select if(1>0,1,0); 
select if(1>0,if(-1>0,-1,1),0);
select score,if(score>120,'优秀',if(score>100,'良好',if(score>90,'及格','不及格'))) as pingfen from score limit 20;

• COALESCE

select COALESCE(null,'1','2'); // 1 从左往右 依次匹配 直到非空为止
select COALESCE('1',null,'2'); // 1

• case when

select  score
        ,case when score>120 then '优秀'
              when score>100 then '良好'
              when score>90 then '及格'
        else '不及格'
        end as pingfen
from score limit 20;
​
select  name
        ,case name when "施笑槐" then "槐ge"
                  when "吕金鹏" then "鹏ge"
                  when "单乐蕊" then "蕊jie"
        else "算了不叫了"
        end as nickname
from students limit 10;

注意条件的顺序

日期函数重点！！！

select from_unixtime(1610611142,'YYYY/MM/dd HH:mm:ss');
​
select from_unixtime(unix_timestamp(),'YYYY/MM/dd HH:mm:ss');
​
​
// '2021年01月14日' -> '2021-01-14'
select from_unixtime(unix_timestamp('2022年06月06日','yyyy年MM月dd日'),'yyyy-MM-dd');
// "04牛2021数加16逼" -> "2021/04/16"
select from_unixtime(unix_timestamp("06牛2022数加06强","MM牛yyyy数加dd强"),"yyyy/MM/dd");

字符串函数

concat('123','456'); // 123456
concat('123','456',null); // NULL
​
select concat_ws('#','a','b','c'); // a#b#c
select concat_ws('#','a','b','c',NULL); // a#b#c 可以指定分隔符，并且会自动忽略NULL
select concat_ws("|",cast(id as string),name,cast(age as string),gender,clazz) from students limit 10;
​
select substring("abcdefg",1); // abcdefg HQL中涉及到位置的时候 是从1开始计数
// '2021/01/14' -> '2021-01-14'
select concat_ws("-",substring('2021/01/14',1,4),substring('2021/01/14',6,2),substring('2021/01/14',9,2));
// 建议使用日期函数去做日期
select from_unixtime(unix_timestamp('2021/01/14','yyyy/MM/dd'),'yyyy-MM-dd');
​
select split("abcde,fgh",","); // ["abcde","fgh"]
select split("a,b,c,d,e,f",",")[2]; // c 数组的下标依旧是从0开始
​
select explode(split("abcde,fgh",",")); // abcde
                                        //  fgh
​
// 解析json格式的数据
select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[1].score"); // 100

Hive 中的wordCount

create table words(
    words string
)row format delimited fields terminated by '|';
​
// 数据
hello,java,hello,java,scala,python
hbase,hadoop,hadoop,hdfs,hive,hive
hbase,hadoop,hadoop,hdfs,hive,hive
​
select word,count(*) from (select explode(split(words,',')) word from words) a group by a.word;
​
// 结果
hadoop  4
hbase   2
hdfs    2
hello   2
hive    4
java    2
python  1
scala   1