标签：count 第八天 -- Hive t1 score emp where select

Hive函数学习

SQL练习

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)来进行计数。

2、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（建议：今后在大数据环境中，一张表的数据量肯定十分庞大的，养成加limit的习惯）

limit功能：
- 选取几条数据
- 数据分页

3、where 条件里不支持不等式子查询，实际上是支持 in、not in、exists、not exists( hive3.x版本是支持的 )

假设是旧版本的hive，不支持where 条件里的不等式子查询，进行一个实例如下

-- 查询薪资大于SCOTT的薪资员工信息
-- 首先第一步是查询出SCOTT的薪资
select sal from emp where ename='SCOTT';

-- 第二步：思考如何将开始的员工表emp和上面查询到的SCOTT薪资表结合？从而使用不是子查询的where语句
-- **制造列****
-- 分别在emp表的最后一列创造一个tmp_id的临时列，在SCOTT薪资表的最后一列新建一个相同的tmp_id的列，用于关联
select *,1 as tmp_id from emp;
select t1.*,1 as tmp_id from (select sal from emp where ename='SCOTT') t1;

-- 第三步，进行两个表的关联
select 	
	t1.empno
	,t1.ename
	,t1.job
	,t1.mgr
	,t1.hiredate
	,t1.sal
	,t1.comm
	,t1.deptno
	,tt1.sal 
from(select *,1 as tmp_id from emp) t1 
join
 	(select t2.*,1 as tmp_id from (select sal from emp where ename='SCOTT') t2) tt1 on t1.tmp_id=tt1.tmp_id;

-- 最后，进行where语句的筛选
select * from 
    (select
        t1.empno
        ,t1.ename
        ,t1.job
        ,t1.mgr
        ,t1.hiredate
        ,t1.sal
        ,t1.comm
        ,t1.deptno
        ,tt1.sal as sals 
    from(select *,1 as tmp_id from emp) t1 
    join (select t2.*,1 as tmp_id 
          from (select sal from emp where ename='SCOTT') t2) tt1 
    on t1.tmp_id=tt1.tmp_id) tt2 
where tt2.sal>tt2.sals;

-- 列出与“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执行计划

hive的主要作用就是将一条交互式的sql语句通过hive中的解析器、编译器、优化器、执行器，最终会产生一个mapreduce作业提交到yarn上运行。

面试题：hive中一条sql语句如何解析成MapReduce作业执行的？（hive的版本）

新版本中生成的是stage，旧版本中生成的是tok

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;
// 空值/非空值判断：isnull、isnotnull、nvl()、isnull()
// like、rlike、regexp用法

数值计算

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

条件函数(主要使用场景是数据清洗的过程中使用，有些构建表的过程也是需要的)

if： if(表达式,如果表达式成立的返回值,如果表达式不成立的返回值) （重点）
条件表达式?表达式1:表达式2;

create table sc(
sno string,
cno string,
score bigint
)row format delimited fields terminated by '\n';
select sal,if(sal<2000,'低薪',if(sal>=2000 and sal<3000,'中等','高薪')) as level from emp;

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 sc;

COALESCE

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

case when（重点）


select sal,case when sal<2000 then '低薪' 
                when sal>=2000 and sal<3000 then '中等薪资'
                else '高薪' end as level
from emp;

select  score
        ,case when score>90 then '优秀'
              when score>80 then '良好'
              when score>=60 then '及格'
        else '不及格'
        end as pingfen
from sc;

select  name
        ,case name when "施笑槐" then "槐ge"
                  when "吕金鹏" then "鹏ge"
                  when "单乐蕊" then "蕊jie"
        else "算了不叫了"
        end as nickname
from students limit 10;


------------------------
select 
	a
	,max(case b when "A" then c end) as col_A
    ,max(case b when "B" then c end) as col_B
from group_score
group by a;

注意条件的顺序

日期函数重点！！！

-- 将时间戳转换成年月日时分秒
select from_unixtime(1717666208,'YYYY年MM月dd日 hh时mm分ss秒');

select from_unixtime(unix_timestamp(),'YYYY/MM/dd HH:mm:ss');


// '2021年01月14日' -> '2021-01-14'
-- unix_timestamp 将当前的时间格式解析出来，from_unxitime 把时间转换成指定的格式
select from_unixtime(unix_timestamp('2024年06月06日','yyyy年MM月dd日'),'yyyy-MM-dd');

// "04牛2021数加16强" -> "2021/04/16"
select from_unixtime(unix_timestamp("04牛2024数加11强","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 concat_ws("-",split('2021/01/14','/'));

// 建议使用日期函数去做日期
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[0].score"); // 100
// $.表示去json全文数据中去找对应的值 

{
	"name": "zhangsan",
	"age": 18,
	"score": [{
		"course_name": "math",
		"score": 100
	}, {
		"course_name": "english",
		"score": 60
	}]
}
$.score[0].score

检查是否是json格式，使用json在线解析器

json格式就是指用大括号括起来，里面是键值对的格式的数据，其中注意键只能是String字符串，但是值可以是多样的。

Hive 中的wordCount

create table words(
    words string
)row format delimited fields terminated by '\n';

// 数据
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

标签：count,第八天,--,Hive,t1,score,emp,where,select
From： https://www.cnblogs.com/shmil/p/18329064

Hive学习第八天--函数的用法

Hive函数学习

SQL练习

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

2、hive语句的执行顺序

3、where 条件里不支持不等式子查询，实际上是支持 in、not in、exists、not exists( hive3.x版本是支持的 )

4、hive中大小写不敏感（列名无所谓大小写）

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

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

Hive 常用函数

关系运算

数值计算

条件函数(主要使用场景是数据清洗的过程中使用，有些构建表的过程也是需要的)

日期函数重点！！！

字符串函数

Hive 中的wordCount

相关文章

赞助商

阅读排行