hive简单数据查询、函数

一、简单数据查询

1、where

WHERE + 过滤条件  
        between/  in /  is NULL / IS NOT NULL / > < = ! ...
        如果多个存在多个过滤条件 可以用 AND   OR 进行条件关联  或者是用NOT 进行条件结果取反

2、JOIN

JOIN 内连接  左外连接  右外连接  自连接  满连接

    内连接:
        注意：关联时一定要跟上关联条件
SELECT  
T1.age
,T2.gender
FROM   表1  T1   JOIN   表2  T2    ON    T1.name = T2.name;   //和sql语句不同的是 表 as t1 中的as不用加

    左外连接:
SELECT  
T1.age
,T2.gender
FROM   表1   T1  LEFT JOIN   表2   T2 ON   T1.name = T2.name;

    右外连接:
SELECT  
T1.age
,T2.gender
FROM   表1   T1 RIGHT JOIN   learn2.emp   T2 ON   T1.name = T2.name;

3、GROUP BY 分组聚合

-- 统计每个部门下的员工数

select 
JOB
from learn2.emp t1 group by t1.JOB;

4、distinct 去重操作，结果与分组相同

SELECT
DISTINCT T1.JOB
FROM learn2.emp T1;  

5、排序

（1） ORDER BY 默认只能全局排序，默认正序从小到大
-- 对薪资进行倒排
SELECT
T1.ENAME
,T1.SAL
FROM learn2.emp T1 ORDER BY T1.SAL desc
    通过查看MAPREDUCE日志 可以看到 Reduce: 1  ORDER BY 中默认的reduce数量只能为1
        在实际处理数据过程中，要尽量避免使用全局排序

**** set mapreduce.job.reduces; 表示查看当前reduce数量
**** set mapreduce.job.reduces = 3;表示设置当前reduce数量为3

（2）sort by 可用于分区间排序，区间内有序，整体无序
SELECT
T1.ENAME
,T1.SAL
FROM learn2.emp T1 SORT BY T1.SAL desc

修改reduce数量以达到分区间排序
**** set mapreduce.job.reduces; 表示查看当前reduce数量
**** set mapreduce.job.reduces = 3;表示设置当前reduce数量为3

通过设置reduce数量为3 查询出的结果为整体乱序，但有三个区间有序

多字段排序
-- 以部门做升序，薪资做倒叙排列
SELECT
T1.DEPTNO
,T1.SAL
FROM learn2.emp T1 ORDER BY T1.DEPTNO, T1.SAL desc      加上desc表示倒叙
-- 结果也是分区间无序，分区内有序

（3）distribute by + sort by 搭配使用进行排序
    distribute by：指定按某列数据进行分区操作，例：下面就是以相同的DEPTNO为分区，有几个不同的DEPTNO，就有几个分区，然后进行分区排序

SELECT
T1.DEPTNO
,T1.SAL
FROM learn2.emp T1 distribute by T1.DEPTNO SORT BY T1.SAL desc;
 

二、函数

1、如何查看函数

        1)SHOW FUNCTIONS 查看所有支持的函数 共289个
        2)SHOW FUNCTIONS LIKE  "**"  模糊查询函数名
        3)DESC FUNCTION + 函数名 可以查看函数的具体使用方法

2、if函数

  格式:IF(判断条件,为TRUE则返回该参数中内容,为FLASE则返回当前参数位置的内容)

        select
        T1.ename
        ,if(T1.sal > 1000 ,"高收入","低收入") as T
        from learn2.emp T1;

3、case when函数 适合多个判断

   格式:CASE WHEN 判断条件1 THEN 如果为TURE返回THEN1后的值, 
                      WHEN 判断条件2 THEN 如果为TURE返回THEN2后的值, 
                       ELSE 以上都不满足，则返回ELSE后的值
          END

        select
        T1.ename
        ,case when T1.sal > 3000 then "高收入"
         when T1.sal <=3000 and T1.sal > 1000 then "中收入"
         else "低收入" end as T
        from learn2.emp T1

4、字符串函数

字符串拼接：拼接的间隔字符位置不同
    SELECT CONCAT(T1.EMPNO,"/",T1.ENAME) FROM learn2.emp T1
    SELECT CONCAT_WS("/",CAST(T1.EMPNO AS STRING),T1.ENAME) FROM learn2.emp T1
            注：CONCAT_WS需要提供STRING类型数据，如果不满足可以通过CAST将数据类型进行转换

字符串截取函数：
            SELECT SUBSTRING(CURRENT_DATE,1,7);

切分函数：
            select split('abtcdtef','t')   返回的数据类型为复杂数据类型Array

5、with  as函数

将表存入缓存中并标记。

格式：

        WITH table1  AS  T1(
         SELECT 查询语句1
        )
        , table2 AS  T2(
         SELECT 查询语句2
        )
        SELECT   FROM   

例:

SELECT T.col,sum(T.num)
FROM (
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
)T GROUP BY T.col

6、集合函数

COLLECT_LIST() 
        需要跟GROUP BY column1 配合使用，将column1中相同的组column2数据放至一个集合中
    
    COLLECT_SET()
        需要跟GROUP BY column1 配合使用，将column1中相同的组column2数据放至一个集合中,并对集合中的数据进行去重操作

--需求：
    将一列中相同的内容放至一组数据中
        将 word列中所有相同的单词对应的num 放至一个数组中

SELECT 
word
,COLLECT_LIST(num)
FROM learn3.wordcount2
GROUP BY word
+--------+------------+
|  word  |    _c1     |
+--------+------------+
| hello  | [1,2,1,2]  |
| hive   | [3,3]      |

SELECT   //结果中去重了
word
,COLLECT_SET(num)
FROM learn3.wordcount2
GROUP BY word

| hello  | [1,2]  |
| hive   | [3]    |

7、EXPLODE() 函数，将一行数据装换成多行

INSERT INTO TABLE learn3.wordcount (word) VALUES ("hello,word,hello,java,hello,spark");

插入一行数据

将集合中的一行数据以逗号隔开并转换为多行

SELECT
EXPLODE(split(word,",")) as word   
FROM learn3.wordcount

但如果要输出多列就不行了，要和LATERAL VIEW搭配使用

SELECT
movie_name,type
FROM learn3.movie LATERAL VIEW EXPLODE(split(types,"/")) view as type

解析：  
    ① 通过split方法将types中的字符串切分为数组
    ② 通过EXPLODE方法将数组由一行数据转换为多行
    ③ 通过LATERAL VIEW 将EXPLODE转换的结果包装成一个名为view的一个侧写表，并且列名为type
    ④ 通过全连接将侧写表中的数据与原表 learn3.movie 中的一行数据进行全连接 

8、窗口函数

分组排序

row_number：无并列排名 (1,2,3,4,5)

* 用法： select xxxx, row_number() over(partition by 分组字段 order by 排序字段 desc) as rn from tb group by xxxx  

 dense_rank：有并列排名，并且依次递增(1,2,2,3,3)

* 用法： select xxxx, dense_rank() over(partition by 分组字段 order by 排序字段 desc) as dr from tb group by xxxx

 rank：有并列排名，不依次递增(1,2,2,4,4)

* 用法： select xxxx, rank() over(partition by 分区字段 order by 排序字段 desc) as r from tb group by xxxx

percent_rank：[(rank的结果-1)/(分区内数据的个数-1)] 

* 用法： select xxxx, percent_rank() over(partition by 分组字段 order by 排序字段 desc) as pr from tb group by xxxx

8、max、min、avg、count、sum函数

max
    用法：
            ① max(T1.total_score) OVER(PARTITION BY T1.clazz ORDER BY T1.total_score DESC) 
                基于每个partition分区内数据取最大值
            ② max(T1.total_score) OVER() 
                基于整个数据集取最大值
min、avg(平均数)、count(计数)、sum(和)：与max方法使用一致