1 CASE WHEN
示例 咱们有这样一组数据
- data1.txt
id name dept project1001 zhangsan sale A1002 lisi sale A1003 wangwu IT B1004 zhaoliu IT A1004 zhaosi sale B 求出 每个部门加入a、b我的项目各有多少人？
创立表
create table emp_pro(id int,name string,dept string,project string)row format delimited fields terminated by "\t";导入数据
load data local inpath into '/home/v2admin/demo/demo1.txt' into table emp_pro;查看下咱们的表的内容
+-------------+---------------+---------------+------------------+| emp_pro.id | emp_pro.name | emp_pro.dept | emp_pro.project |+-------------+---------------+---------------+------------------+| 1001 | zhangsan | sale | A || 1002 | lisi | sale | A || 1003 | wangwu | IT | B || 1004 | zhaoliu | IT | A || 1004 | zhaosi | sale | B |+-------------+---------------+---------------+------------------+创立表和导入数据没有问题，接下来开始实现咱们的需要
select dept,sum(case project when "A" then 1 else 0 end) A_Count,sum(case project when "B" then 1 else 0 end) B_Countfrom emp_progroup by dept;后果
+-------+----------+----------+| dept | a_count | b_count |+-------+----------+----------+| IT | 1 | 1 || sale | 2 | 1 |+-------+----------+----------+2.CONCAT、CONCAT_WS、COLLECT_SET(col)
| 函数 | 阐明 |
|---|---|
| CONCAT(string A/col, string B/col…) | 返回输出字符串连贯后的后果，反对任意个输出字符串; |
| CONCAT_WS(separator, str1, str2,...) | 它是一个非凡模式的 CONCAT()。第一个参数残余参数间的分隔符。分隔符能够是与残余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连贯的字符串之间; |
| COLLECT_SET(col) | 函数只承受根本数据类型，它的次要作用是将某字段的值进行去重汇总，产生array类型字段。 |
示例如下
还是这个数据
- data1.txt
id name dept project1001 zhangsan sale A1002 lisi sale A1003 wangwu IT B1004 zhaoliu IT A1004 zhaosi sale B 咱们要失去如下后果
sale,A zhangsan,lisisale,B zhaosiIT,A zhaoliuIT,B wangwu查问语句
select t1.bs,concat_ws("|",collect_set(t1.name)) namefrom(select name,concat(dept,",",project) bs from emp_pro) t1group by t1.bs;后果
+---------+----------------+| t1.bs | name |+---------+----------------+| IT,A | zhaoliu || IT,B | wangwu || sale,A | zhangsan|lisi || sale,B | zhaosi |+---------+----------------+3 EXPOLODE(col)和LATERAL VIEW
| 函数 | 阐明 |
|---|---|
| EXPLODE(col) | 将hive一列中简单的array或者map构造拆分成多行。 |
| LATERAL VIEW | 用于和split, explode等UDTF一起应用，它可能将一列数据拆成多行数据，在此基础上能够对拆分后的数据进行聚合，例如 LATERAL VIEW udtf(expression) tableAlias AS columnAlias |
示例
有这样一组数据
书名 标签Python外围编程 开发、后盾、编程、pythonJava编程与进阶 开发、编程、JavaMYSQL实战 数据库、SQL咱们想得到开展的数据
Python外围编程 开发Python外围编程 后盾Python外围编程 编程Python外围编程 pythonJava编程与进阶 开发Java编程与进阶 编程Java编程与进阶 JavaMYSQL实战 数据库MYSQL实战 SQL首先筹备数据data2.txt
Python外围编程 开发、后盾、编程、pythonJava编程与进阶 开发、编程、JavaMYSQL实战 数据库、SQL创立表并导入数据
-- 创立表create table book_info(book_name string,category string)row format delimited fields terminated by "\t";-- 导入数据load data local inpath "/home/v2admin/demo/data2.txt" into table book_info;查看下表的数据
+----------------------+---------------------+| book_info.book_name | book_info.category |+----------------------+---------------------+| Python外围编程 | 开发、后盾、编程、python || Java编程与进阶 | 开发、编程、Java || MYSQL实战 | 数据库、SQL |+----------------------+---------------------+Ok，接下来实现咱们的需要
selectbook_info.book_name,tb.catefrom book_infolateral viewexplode(split(category, "、")) tb as cate;后果
+----------------------+-----------+| book_info.book_name | tbl.cate |+----------------------+-----------+| Python外围编程 | 开发 || Python外围编程 | 后盾 || Python外围编程 | 编程 || Python外围编程 | python || Java编程与进阶 | 开发 || Java编程与进阶 | 编程 || Java编程与进阶 | Java || MYSQL实战 | 数据库 || MYSQL实战 | SQL |+----------------------+-----------+4.开窗函数(也叫窗口函数)
4.1 函数阐明
| 函数 | 阐明 |
|---|---|
| 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类型。 |
过一下即可，等看示例的时候，再对照着看
4.2 示例
1) 老套路，先上数据
姓名 日期 生产
# 姓名 日期 生产zhangsan 2017-01-01 10zhangsan 2017-01-02 15wangwu 2017-02-03 23wangwu 2017-01-04 29zhaoliu 2017-01-05 46zhaoliu 2017-04-06 42wangwu 2017-01-07 50zhangsan 2017-01-08 55zhangsan 2017-04-08 62wangwu 2017-04-09 68zaholiu 2017-05-10 12zhouzhou 2017-04-11 75zhouzhou 2017-04-13 94保留文件data3.txt
2)创立表并导入数据
-- 创立表create table log_info(name string,log_date string,cost int)row format delimited fields terminated by "\t";-- 导入数据load data local inpath "/home/v2admin/demo/data3.txt" into table log_info;3)需要一: 查问2017年4月份生产过的用户和用户总数
-- over() 不加参数，示意整张表开个窗口selectname,count(*) over() as countfrom log_infowhere substring(log_date,1,7) = '2017-04'group by name;后果
+-----------+--------+| name | count |+-----------+--------+| wangwu | 4 || zhangsan | 4 || zhaoliu | 4 || zhouzhou | 4 |+-----------+--------+4）需要2 查问用户的生产明细和每月生产总额
selectname,log_date,cost,sum(cost) over(partition by month(log_date))from log_info;后果
+-----------+-------------+-------+---------------+| name | log_date | cost | sum_window_0 |+-----------+-------------+-------+---------------+| zhangsan | 2017-01-01 | 10 | 205 || zhangsan | 2017-01-02 | 15 | 205 || wangwu | 2017-01-04 | 29 | 205 || zhaoliu | 2017-01-05 | 46 | 205 || wangwu | 2017-01-07 | 50 | 205 || zhangsan | 2017-01-08 | 55 | 205 || wangwu | 2017-02-03 | 23 | 23 || zhouzhou | 2017-04-11 | 75 | 341 || zhouzhou | 2017-04-13 | 94 | 341 || zhangsan | 2017-04-08 | 62 | 341 || wangwu | 2017-04-09 | 68 | 341 || zhaoliu | 2017-04-06 | 42 | 341 || zaholiu | 2017-05-10 | 12 | 12 |+-----------+-------------+-------+---------------+5）需要3 查问用户上次的生产日期
selectname,log_date,cost,lag(log_date,2) over (partition by name order by log_date) as last_timefrom log_info;后果
+-----------+-------------+-------+-------------+| name | log_date | cost | last_time |+-----------+-------------+-------+-------------+| wangwu | 2017-01-04 | 29 | NULL || wangwu | 2017-01-07 | 50 | NULL || wangwu | 2017-02-03 | 23 | 2017-01-04 || wangwu | 2017-04-09 | 68 | 2017-01-07 || zaholiu | 2017-05-10 | 12 | NULL || zhangsan | 2017-01-01 | 10 | NULL || zhangsan | 2017-01-02 | 15 | NULL || zhangsan | 2017-01-08 | 55 | 2017-01-01 || zhangsan | 2017-04-08 | 62 | 2017-01-02 || zhaoliu | 2017-01-05 | 46 | NULL || zhaoliu | 2017-04-06 | 42 | NULL || zhouzhou | 2017-04-11 | 75 | NULL || zhouzhou | 2017-04-13 | 94 | NULL |+-----------+-------------+-------+-------------+4.日期函数
1)current_date返回以后日期
0: jdbc:hive2://hadoop10:10000> select current_date();+-------------+| _c0 |+-------------+| 2020-01-15 |+-------------+2）date_add, date_sub,datediff()日期的加减
-- 从明天开始，100天当前的日期select date_add(current_date(), 100);-- 从明天开始，100天以前的日期；select date_sub(current_date(), 100);-- 两个日期之间的差select datediff(current_date(), "2019-01-03");