hive 查询

hive outline

链接

hive SQL查询执行顺序

from>on>join>where>group by>聚合函数（例如分组后统计数量）>having>select>distinct>order>limit

聚合语句(sum,min,max,avg,count)

hive 聚合函数处理null的注意事项

假设有以下一张表：test_null

c1	c2
aaa	3
bbb	3
ccc	null
ddd	6

select count(*), -- ans:4 统计所有行
       count(1), -- ans:4 count(*)==count(1) 统计所有行
       count(c2) -- ans:3 统计所有行，但不包含该字段下为null的行
from test_null


select sum(c2), -- ans:12 运算时不包括null所在行
       max(c2), -- ans:6 运算时不包括null所在行
       min(c2), -- ans:3 运算时不包括null所在行
       avg(c2)  -- ans:12/3 运算时不包括null所在行
from test_null

总结：

1. count(*) 包含 null 值，统计所有行数
2. count(id) 不包含 null 所在行
3. min&max&avg&sum 运算时不包括null所在行

hive count(*)&count(1)&count(字段名称)的区别

简单来说：

count(*)==count(1)

count(1)：统计所有行，包含null
count(字段名称)：统计所有行，但不包含该字段下为null的行

hive union&union all 联合查询

union

--对两表并行查询，删除重复行，同时进行排序
select num,name from student_local
union
select num,name from student_hdfs;

union all

--对两表并行查询，保留重复行，进行排序
select num,name from student_local
union all
select num,name from student_hdfs;

如果要将 order by 或 limt 应用于整个 union 后结果，请将其放在最后

select sno,sname from student_local
union
select sno,sname from student_hdfs
order by sno desc;

hive CTE

create table s1 like student;

with q1 as (
           select s_id,
                  s_name,
                  s_sex
           from student
           where s_id = '07'
           )
insert overwrite table s1
select *
from q1;

hive like 关键字