大数据中篇02_Hive-数据操作语句

Hive-数据操作语句（陆续修改中）

函数部分

查看系统内置函数

1）查看系统自带的函数

hive> show functions;

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

hive> desc function upper;

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

hive> desc function extended upper;

常用内置函数

空字段赋值（NVL）

函数说明

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

举例

查询：如果员工的comm为NULL，则用-1代替

hive (default)> select comm,nvl(comm, -1) from emp;

条件判断（CASE WHEN）

举例

求出不同部门男女各多少人,结果如下

select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count, --是'男'输出1,否则0,该列名为male_count
  sum(case sex when '女' then 1 else 0 end) female_count
from 
  emp_sex
group by
  dept_id;

行转列

函数说明

CONCAT(string A/col, string B/col…) ：返回输入字符串连接后的结果，支持任意个输入字符串

CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段

举例

查询成如下结果

select
    t1.base,
    concat_ws('|', collect_set(t1.name)) name --对name列的所有行进行整合,按'|'分开
from
    (select
        name,
        concat(constellation, ",", blood_type) base --将constellation和blood_type列的行一一整合,中间用','分隔开
    from
        person_info) t1
group by
    t1.base;

列转行

函数说明

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行

LATERAL VIEW：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合

举例

查询出以下效果

select
    m.movie,
    tbl.cate
from
    movie_info m
lateral view --将下面的cate列和上面的聚合(粘合剂)
    explode(split(category, ",")) tbl as cate; --将category列炸开(以','为标准拆分),并命名为tbl表的cate列(因为该表只有cate这一列)

窗口函数（重点！！！）

函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n,default_val)：往前第n行数据
LEAD(col,n, default_val)：往后第n行数据
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型

举例

（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)) --将cost按照orderdate的月分组，累加(因为这里忘记起别名，窗口函数随机拼了个别名sum_window_0)（partition by分组）
from
 business;

（3）上述的场景, 将每个顾客的cost按照日期进行累加

select name,orderdate,cost, 
sum(cost) over() as sample1,--所有行相加 
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
from business;

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

select name,orderdate,cost, 
 lag(orderdate,1,'1900-01-01') --以orderdate为数据源，返回上一次消费的时间，没有则返回'1900-01-01'
 over(partition by name order by orderdate ) as time1, --按名字分组，按orderdate排序生成表别名为 time1
 lag(orderdate,2) 
 over (partition by name order by orderdate) as time2 
from business;

（5）查询前20%时间的订单信息

select * from (
    select 
      name,orderdate,cost, 
      ntile(5) over(order by orderdate) sorted --用ntil分为5组 按照orderdate排序 别名为sorted
    from business
) t
where sorted = 1;

Rank

函数说明

RANK()： 排序相同时会重复，总数不会变

DENSE_RANK()： 排序相同时会重复，总数会减少

ROW_NUMBER()： 会根据顺序计算

举例

查询出以下结果

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;

日期相关函数

（1）current_date返回当前日期

select current_date();

（2）date_add, date_sub 日期的加减

--今天开始90天以后的日期
select date_add(current_date(), 90);
--今天开始90天以前的日期
select date_sub(current_date(), 90);

（3）两个日期之间的日期差

--今天和1990年6月4日的天数差
SELECT datediff(CURRENT_DATE(), "1990-06-04");

（2）date_add, date_sub 日期的加减

--今天开始90天以后的日期
select date_add(current_date(), 90);
--今天开始90天以前的日期
select date_sub(current_date(), 90);

（3）两个日期之间的日期差

--今天和1990年6月4日的天数差
SELECT datediff(CURRENT_DATE(), "1990-06-04");