一：系统内置函数

        1：查看系统自带的函数

hive> show functions;

        2：显示自带的函数的用法

hive> desc function upper;

        3：详细显示自带的函数的用法

hive> desc function extended upper;

二：常用内置函数

        1：NVL

NVL 给值为NULL的数据赋值，它的格式是NVL(value default_value)。它的功能是如果，value为 NULL 则 NVL函数返回 default_value的值，否则返回 value的值。如果两个参数都为NULL，则返回 NULL。

        2：CASE WHEN THEN ELSE END

数据准备

 需求

求出不同部门男女各多少人

创建本地emp_sex.txt，并导入数据

[atguigu@hadoop102 datas]$ vi emp_sex .txt
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女

创建hive表并导入数据


create table emp_sex(
name string,
dept_id s tring,
sex string)
row format delimited fields terminated by "\t";

load data local inpath '/opt/module/hive/data/emp_sex.txt' into table emp_sex;

按需求查询数据

select
dept_id,
sum(case sex when ' 男 ' then 1 else 0 end) male_count,
sum(case sex when ' 女 ' then 1 else 0 end) female_count
from emp_sex
group by dept_id;

select 
dept_id,
sum(if(sex = '男'，1，0)) maleCount,
sum(if(sex = '女'，1，0)) femaleCount,
from emp_sex
group by dept_id;

        3：CONCAT和CONCAT_WS

CONCAT和CONCAT_WS都是用来拼接字符串的，CONCAT_WS只能是string或者array<string>

CONCAT大概就是将多个列拼成一个列。

 COLLECT_SET（col）：函数只接收基本数据类型，作用是将某字段的值进行去重汇总，产生array类型的字段。

数据准备

name        constellation        blood_type

孙悟空        白羊座        A

大海            射手座        A

宋宋            白羊座        B

猪八戒        白羊座        A

凤姐            射手座        A

苍老师         白羊座        B

需求：将星座和血型一样的人归类到一起

射手座，A        大海|凤姐

白羊座，A        孙悟空|猪八戒

白羊座，B        宋宋|苍老师

创建本地constellation.txt，导入数据

[atguigu@hadoop102 datas]$ vim person_info .txt
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
苍老师 白羊座 B

导入hive表并导入数据

create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
load data local inpath "/opt/hive/data/person_info .txt" into table person_info;

按需求查询数据

SELECT
    t1.c_b,
    CONCAT_WS("|",collect_set(t1.name))
FROM (
    SELECT
        NAME,
        CONCAT_WS(',',constellation,blood_type) c_b
    FROM person_info
    )t1
GROUP BY t1.c_b

        4：EXPLODE(COL)：将hive一列中复杂的Array或者Map结构拆分成多行

EXPLODE大概就是将一行展开为多行。

数据准备 vim movie_info.txt

 需求：将电影分类中的数组数据展开

创建hive表并导入数据

create table movie_info(
movie string,
category string)
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/data/movie.txt" into table movie_info;

按需求查询数据

SELECT
    movie,
    category_name
FROM
    movie_info
lateral VIEW
    explode(split(category,",")) movie_info_tmp AS category_name;

三：窗口函数

        1：准备数据

vim business.txt

jack,2017 01 01,10
tony,2017 01 02,15
jack,2017 02 03,23
tony,2017 01 04,29
jack,2017 01 05,46
jack,2017 04 06,42
tony,2017 01 07,50
jack,2017 01 08,55
mart,2017 04 08,62
mart,2017 04 09,68
neil,2017 05 10,12
mart,2017 04 11,75
neil,2017 06 12,80
mart,2017 04 13,94

        2：需求

1 ：查询在 2017 年 4 月份购买过的顾客及总人数
2 ：查询顾客的购买明细及月购买总额
3 ：上述的场景 , 将每个顾客的 cost 按照日期进行累加
4 ：查询每个顾客上次的购买时间
5： 查询前 20% 时间的订单信息        

        3：创建表并导入数据

create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/module/data/business.txt" into table business;

        4：案例测试

1：查询在2017年4月份购买过的顾客及总人数

select name,count(*) over ()
from business
where substring(orderdate,1,7) = '2017-04'
group by name

2：查询顾客的购买明细及月购买总额

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;

3：将每个顾客的cost按照日期进行累加

select name,orderdate,cost,sum(cost) over(partition by name order by orderdate) from busioness;

4：查看顾客上次的购买时间

select name,orderdate,lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate) as time1from business

lag是往前多少行，第一个参数指的是对应的哪一列，第二个参数是指往前多少行，第三个参数是默认值

5：查询前百分之二十时间的订单信息

select * from (
    select name,orderdate,cost,ntile(5) over(order by orderdate) sorted from business
) t
where sorted = 1;

NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。n必须为int。

        5：RANK函数

数据准备

需求：计算每门学科成绩排名

创建本地score.txt，创建hive表，并导入数据

create table score(
name string,
subject string,
score int)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/data/score.txt' into table score;

按需求查询数据

select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

通过这三种结果，可以查看到rank、dense_rank、row_number的不同之处

name subject score rp drp rmp
孙悟空 数学 95 1 1 1
宋宋 数学 86 2 2 2
婷婷 数学 85 3 3 3
大海 数学 56 4 4 4

宋宋 英语 84 1 1 1
大海 英语 84 1 1 2
婷婷 英语 78 3 2 3
孙悟空 英语 68 4 3 4

大海 语文 94 1 1 1
孙悟空 语文 87 2 2 2
婷婷 语文 65 3 3 3
宋宋 语文 64 4 4 4