Hql中的基本查询语句(含聚合函数)

基本查询SELECT


1.查询表中的某几个字段
select empno,empname from emp;
开发优化查询速度
set hive.fetch.task.conversion=more;
set hive.exec.mode.local.auto=true;


2.distinct 去重
select distinct(depno) from emp;


3.count 统计计数
select count(empno) from emp;
select count(*) from emp;
select count(1) from emp;


count(*) -所有值不全为null时，加1操作
count(1) -不管有没有值，只要有这条记录，值就加1
count(col) -col列里面的值为null，值不会加1，这个列的值不为null，才加1


4.别名
select  count(empno) as empnonum from emp a;


5.where 
** and
** or
** between ... and ..
** + - * /
** < > = >= <=
** is null  is not null


select * from emp where depno=20;
select * from emp where depno=20 and salary > 2000; 
select * from emp where depno=20 or empno=7521;
select * from emp where salary between 2000 and 5000;
select * from emp where salary > 3000;


select empname, salary, bonus,(salary+bonus) as tolsal from emp;
select empno,name,salary,bonus,salary+nvl(bonus,0) as totalsalary from emp;


6.limit 
select * from emp limit 2;


7.聚合函数
** count 
count(*) -所有值不全为null时，加1操作
count(1) -不管有没有值，只要有这条记录，值就加1
count(col) -col列里面的值为null，值不会加1，这个列的值不为null，才加1
select count(bonus) from emp;


** avg  平均值
** sum  求和
** max  最大值
** min 最小值

select avg(salary) salary from emp;
select depno,avg(salary) salary from emp group by depno; 
select depno,max(salary) salary from emp group by depno; 
select depno,min(salary) salary from emp group by depno;


8.group by 
* 求每个部门的薪资总和
select depno,sum(salary) sum_salary from emp group by depno;


* 求每个部门的薪资最高的员工，薪水
【错误】select empname,max(salary) salary from emp group by depno; 


FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'empname'
Error: Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'empname' (state=42000,code=10025)




***注意：group by 后面没有出现的字段，不能直接放到select后面，为了构成语法，可以结合聚合函数使用。
FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'empname'


【语法正确-结果错误,须用子查询方式】select max(empname),max(salary) max_sal from emp group by  deptno;




select e.empno,e.empname,e.salary,e.depno from emp e where e.salary in (select max(salary) salary from emp group by depno); 




elect e.empname from emp e where e.deptno in (select d.deptno from dept d where d.deptname='SALES' or d.deptname='ACCOUNTING');


Hive的子查询
Hive只支持where和from后面的子查询
-语法中的括号
-合理的书写风格
-hive只支持where和from语句中的子查询
-主查询和子查询可以不是同一张表
-子查询中的空值问题


9.having 条件过滤
 select depno, avg(salary) avg_sal from emp group by depno hvaing  avg_sal >3000;


 10.union  union all
 必须保证union all 前后select 查询字段的个数和字段类型相同
 select depno from emp 
 union all
 select depno from dept;


11 join  (map join, reduce join, SMB join)
* on  条件  --等值连接
 查询员工姓名、部门名称及员工薪资  
 select a.empname,b.deptname,a.salary from emp a join dept b on a.depno=b.depno;


//左连
 select a.empname,b.deptname,a.salary from emp a left join dept b on a.depno=b.depno;


//右连
 select a.empname,b.deptname,a.salary from emp a right join dept b on a.depno=b.depno;


两张表中没有共同字段做jion
select * from student join dept on 1=1;