Hive(10):hive支持常用HQL语句

1、过滤

(1)where：select,update,delete,insert into 

select * from emp where sal > 3000;

(2)limit  

 select * from emp limit 1;

(3)distinct,count/group by 

    select distinct deptno from emp;
    select  count(distinct empno) as uv  from emp;
    select min(sal),deptno from emp group by deptno;

(4)between and    

select * from emp where sal between 2000 and 3000;

(5)is null & is not null(/n,"")

    select ename from emp where comm is null;
    select ename from emp where comm is not null;

(6)having 
    分组语句之后

    select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
    select deptno,avg from (select deptno,avg(sal) as avg from emp group by deptno) a where avg > 2000;

    注意：where和having可以在一条语句中执行，where先执行 

2、聚合函数

(1)count  

 select count(1) from emp;

(2)sum
(3)avg   

select avg(sal) avg_sal from emp;

(4)max
(5)min

select deptno,avg(sal),empno from emp group by deptno,empno;
select deptno,avg(sal),max(empno) from emp group by deptno;

3、group by分组(去重)

    select deptno,avg(sal) from emp group by deptno;
    select count(userid) as uv from log(日志表) group by userid

4、join

分类：

        等值join   inner  
        左join    left
        右join    right
        全join    full
        semi join 半关联
        map join 优化（大表和小表join的优化）

 

A:emp表
emp.empno       emp.ename       emp.job emp.mgr emp.hiredate    emp.sal emp.comm        emp.deptno
7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20
7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30
7566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    20
7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30
7698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    30
7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10
7788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    20
7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    10
7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30
7876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    20
7900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    30
7902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    20
7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10

B:dept表
dept.deptno     dept.dname      dept.loc
10      ACCOUNTING      NEW YORK
20      RESEARCH        DALLAS
30      SALES   CHICAGO
40      OPERATIONS      BOSTON

(1)等值join语句：

select e.empno,e.ename,d.deptno,e.sal from emp e join dept d on e.deptno=d.deptno;
【结果：】
e.empno e.ename d.deptno        e.sal
7369    SMITH   20      800.0
7499    ALLEN   30      1600.0
7521    WARD    30      1250.0
7566    JONES   20      2975.0
7654    MARTIN  30      1250.0
7698    BLAKE   30      2850.0
7782    CLARK   10      2450.0
7788    SCOTT   20      3000.0
7839    KING    10      5000.0
7844    TURNER  30      1500.0
7876    ADAMS   20      1100.0
7900    JAMES   30      950.0
7902    FORD    20      3000.0
7934    MILLER  10      1300.0

(2)左join语句：

select e.empno,e.ename,d.deptno,e.sal from emp e left join dept d on e.deptno=d.deptno;
【结果：】
e.empno e.ename d.deptno        e.sal
7369    SMITH   20      800.0
7499    ALLEN   30      1600.0
7521    WARD    30      1250.0
7566    JONES   20      2975.0
7654    MARTIN  30      1250.0
7698    BLAKE   30      2850.0
7782    CLARK   10      2450.0
7788    SCOTT   20      3000.0
7839    KING    10      5000.0
7844    TURNER  30      1500.0
7876    ADAMS   20      1100.0
7900    JAMES   30      950.0
7902    FORD    20      3000.0
7934    MILLER  10      1300.0

(3)右join语句：

select e.empno,e.ename,d.deptno,e.sal from emp e right join dept d on e.deptno=d.deptno;
【结果：】
e.empno e.ename d.deptno        e.sal
7782    CLARK   10      2450.0
7839    KING    10      5000.0
7934    MILLER  10      1300.0
7369    SMITH   20      800.0
7566    JONES   20      2975.0
7788    SCOTT   20      3000.0
7876    ADAMS   20      1100.0
7902    FORD    20      3000.0
7499    ALLEN   30      1600.0
7521    WARD    30      1250.0
7654    MARTIN  30      1250.0
7698    BLAKE   30      2850.0
7844    TURNER  30      1500.0
7900    JAMES   30      950.0
NULL    NULL    40      NULL

(4)全join语句：

select e.empno,e.ename,d.deptno,e.sal from emp e full join dept d on e.deptno=d.deptno;
【结果：】

e.empno e.ename d.deptno        e.sal
7934    MILLER  10      1300.0
7839    KING    10      5000.0
7782    CLARK   10      2450.0
7876    ADAMS   20      1100.0
7788    SCOTT   20      3000.0
7369    SMITH   20      800.0
7566    JONES   20      2975.0
7902    FORD    20      3000.0
7844    TURNER  30      1500.0
7499    ALLEN   30      1600.0
7698    BLAKE   30      2850.0
7654    MARTIN  30      1250.0
7521    WARD    30      1250.0
7900    JAMES   30      950.0
NULL    NULL    40      NULL