1.函数

SQL的顺序.png
谐音记忆：from加入各处团伙，我们人数多，啥都有，随便选，就是有排队这个限制。
form加入（join）各处（where）团伙（group by），我们人数多（count），啥都有，随便选，就是有排队这个限制。

2.哪些函数是窗口函数

2.1 窗口函数

lead
lag
first_value
last_value

2.2聚合函数

sum
count
avg
max
min

2.3排名分析函数

rank（） 排序相同时会重复，总数不变 ** 1 1 3

dense_rank 排序相同时会重复，总数减少 ** 1 1 2

row_number 会根据顺序计算 **1 2 3 **
**

2.4.窗口函数的定义

窗口+函数

窗口表示限定函数的计算范围

窗口函数是一行一行走的**

3.窗口函数的语法

  1. 窗口函数()+over([partition by 字段...][order by 字段...][窗口子句])
  2. over表示开窗
  3. 窗口子句
  4. (ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
  5. (ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
  6. (ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
  7. 窗口子句是有默认值的
  8. When ORDER BY is specified with missing WINDOW clause, the WINDOW specification defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  9. 当有order by 但是缺少窗口子句的时候 窗口的默认范围是上无边界到当前行
  10. When both ORDER BY and WINDOW clauses are missing, the WINDOW specification defaults to ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
  11. 当既没有窗口子句又没有order by的时候 窗口默认的范围是上无边界到下无边界

但是缺少窗口子句的时候** 窗口的默认范围是上无边界到当前行**

4.重要的函数

4.1空字段赋值

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

4.2 CASE WHEN THEN ELSE END

image.png
查询表格使得能得出
image.png

  1. select 
  2.   dept_id,
  3.   sum(case sex when '男' then 1 else 0 end) male_count,     // sum(if(sex="男",1,0)) male_count
  4.   sum(case sex when '女' then 1 else 0 end) female_count    // sum(if(sex="女",1,0)) female_count
  5. from 
  6.   emp_sex
  7. group by
  8.   dept_id;

4.3 行转列

函数 函数说明
CONCAT(string A/col, string B/col…) 返回输入字符串连接后的结果，支持任意个输入字符串
CONCAT_WS(separator, str1, str2,…) 第一个参数剩余参数间的分隔符
分隔符可以是与剩余参数一样的字符串

| | COLLECT_SET(col) | 将某字段的值进行去重汇总，产生array类型字段。 | | COLLECT_LIST(col) | 将某字段的值进行不去重汇总，产生array类型字段。 | | substring(str, pos[num ，len ]) | 获取字符串从哪位开始，和长度 |

image.png
image.png

  1. SELECT t1.c_b , CONCAT_WS("|",collect_set(t1.name))
  2. FROM (
  3. SELECT NAME ,CONCAT_WS(',',constellation,blood_type) c_b
  4. FROM person_info
  5. )t1 
  6. GROUP BY t1.c_b

image.png

总结：以c_b为group by 来进行分组，那么就会有name一列中就会是多个值分到各个分组后的行中，所以要进行数组化（利用collect_list ()和collect_set()来进行数组化）

4.4 列转行(一列转多行)

Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array。
explode(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW ###侧写
用法：LATERAL VIEW udtf(expression) tableAlias AS c olumnAlias

解释：lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表
image.png
image.png

  1. SELECT movie,category_name 
  2. FROM movie_info 
  3. lateral VIEW
  4. explode(split(category,",")) movie_info_tmp  AS category_name ;

4.5窗口函数例题

相关函数说明
over（）：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
current row：当前行
n preceding：往前n行数据
n following：往后n行数据
unbounded：无边界
unbounded preceding 前无边界，表示从前面的起点，
unbounded following后无边界，表示到后面的终点
lagcol,n,default_val)：往前第n行数据
lead(col,n, default_val)：往后第n行数据
first_value (col,true/false)：当前窗口下的第一个值，第二个参数为true，跳过空值
last_value(col,true/false)：当前窗口下的最后一个值，第二个参数为true，跳过空值
ntile(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。
注意：n必须为int类型。

例题：

1）数据准备：

name，orderdate，cost 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）创建hive表并导入数据

create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;
load data local inpath “/opt/module/hive/datas/business.txt” into table business;

3）按需求查询数据

（1）查询在2017年4月份购买过的顾客及总人数
  1.  **substring(orderdate,1,7)： **
  1. select name,count(*) over ()
  2. from business
  3. where substring(orderdate,1,7) = '2017-04'
  4. group by name;

**总结**：over（）对每一行都进行了 开窗计算
count（*）over（）是指当前的所有行

（2）查询顾客的购买明细及月购买总额
  1. select
  2. name,
  3. orderdate,
  4. cost,
  5. sum(cost) over(partition by name,month(orderdate)) name_month_cost
  6. from business;

（3）将每个顾客的cost按照日期进行累加
  1. select name,orderdate,cost,
  2. sum(cost) over() as sample1,                                    --所有行相加
  3. sum(cost) over(partition by name) as sample2,                   --按name分组，组内数据相加
  4. sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加
  5. sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合
  6. sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合
  7. sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
  8. sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
  9. from business;
  10. rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

（4）查询顾客购买明细以及上次的购买时间和下次购买时间
  1. select
  2. name,orderdate,cost,
  3. lag(orderdate,1,'1970-01-01') over(PARTITION by name  order by orderdate) prev_time,
  4. lead(orderdate,1,'1970-01-01') over(PARTITION by name order by orderdate) next_time
  5. from business;

（5）查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
  1. select
  2. name,
  3. orderdate,
  4. cost,
  5. FIRST_VALUE(orderdate) over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) first_time,
  6. LAST_VALUE(orderdate) over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) last_time
  7. from business;

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

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

4.6 常用函数

4.6.1常用日期函数

unix_timestamp:返回当前或指定时间的时间戳 select unix_timestamp(‘2018/08/08 08-08-08’,’yyyy/MM/dd HH-mm-ss’); -前面是日期后面是指，日期传进来的具体格式
from_unixtime：将时间戳转为日期格式 select from_unixtime(1533715688); 2018/08/08 08-08-08
current_date：当前日期 select current_date; 不带时间
current_timestamp：当前的日期加时间 select current_timestamp; 带时间返回 并且精确的毫秒
to_date：抽取日期部分 select to_date(‘2018-08-08 08:08:08’) 抽取2018-08-08
year：获取年 select year(‘2018-08-08 08:08:08’) 2018
month：获取月 select month(‘2018-08-08 08:08:08’) 8
day：获取日 select day(‘2018-08-08 08:08:08’) 8
hour：获取时 select hour(‘2018-08-08 08:08:08’) 8
minute：获取分 select minute(‘2018-08-08 08:08:08’) 8
second：获取秒 select second(‘2018-08-08 08:08:08’) 8
weekofyear：当前时间是一年中的第几周 select weekofyear(‘2020-07-13’); 7-13号是今年的29周
dayofmonth：当前时间是一个月中的第几天 select dayofmonth(‘2020-07-13’);
months_between： 两个日期间的月份 select months_between(‘2018-08-08’,’2020-10-09’); 前面的-后面的 并且还挺精确
add_months：日期加减月 select add_months(‘2018-08-08’,2); 日期加减月
datediff：两个日期相差的天数 select datediff(‘2018-08-08’,’2020-10-09’); 前面-后面
date_add：日期加天数 select date_add(‘2018-08-08’,2); 2018-08-10
date_sub：日期减天数 select date_sub(‘2018-08-08’,2); 2018-08-06
last_day：日期的当月的最后一天 select last_day (‘2018-08-08’); 2018-08-31

4.6.2常用取整函数

round： 四舍五入 select round(3.3); 3
ceil： 向上取整 select ceil(3.1) ; 4
floor： 向下取整 select floor(4.8); 4
常用字符串操作函数
upper： 转大写 select upper(‘facebook’); FACEBOOK
lower： 转小写 select lower(‘FACEBOOK’); facebook
length： 长度 select length(‘FACEBOOK’); 8
trim： 前后去空格 select trim(‘ aaa bbb ‘);
lpad： 向左补齐，到指定长度 select lpad(‘aaa’,10,’‘); **aaa
rpad： 向右补齐，到指定长度 select rpad(‘aaa’,10,’‘); aaa**
regexp_replace： SELECT regexp_replace(‘100-200’, ‘(\d+)’, ‘num’) ；
使用正则表达式匹配目标字符串，匹配成功后替换！

4.6.3集合操作

size： 集合中元素的个数 用当时创的test表做测试 select size(friends) from test; 2/2 每一行数据中的friends集合里的个数
map_keys： 返回map中的key select map_keys(children) from test;
map_values: 返回map中的value select map_values(children) from test;
array_contains: 判断array中是否包含某个元素 select array_contains(friends,’bingbing’) from test;
sort_array： 将array中的元素排序 select sort_array(array(‘a’,’d’,’c’));

5. hive练习

5.1 手写HQL 第1题

表结构：uid,subject_id,score
求：找出所有科目成绩都大于某一学科平均成绩的学生
数据集如下

uid, subject_id,score 1001 01 90 1001 02 90 1001 03 90 1002 01 85 1002 02 85 1002 03 70 1003 01 70 1003 02 70 1003 03 85

1）建表语句

  1. create table score(
  2. uid string,
  3. subject_id string,
  4. score int)
  5. row format delimited fields terminated by '\t';

2）求出每个学科平均成绩

  1. select
  2. uid,
  3. score,
  4. avg(score) over(partition by subject_id) avg_score
  5. from
  6. score;t1

3）根据是否大于平均成绩记录flag，大于则记为0否则记为1

  1. select
  2. uid,
  3. if(score>avg_score,0,1) flag
  4. from
  5. t1;t2

4）根据学生id进行分组统计flag的和，和为0则是所有学科都大于平均成绩

  1. select
  2. uid
  3. from
  4. t2
  5. group by
  6. uid
  7. having
  8. sum(flag)=0;

5）最终SQL

  1. select
  2. uid
  3. from
  4. (select
  5. uid,
  6. if(score>avg_score,0,1) flag
  7. from
  8. (select
  9. uid,
  10. score,
  11. avg(score) over(partition by subject_id) avg_score
  12. from
  13. score)t1)t2
  14. group by
  15. uid
  16. having
  17. sum(flag)=0;

5.2 手写HQL 第2题

我们有如下的用户访问数据

userId visitDate visitCount u01 2017/1/21 5 u02 2017/1/23 6 u03 2017/1/22 8 u04 2017/1/20 3 u01 2017/1/23 6 u01 2017/2/21 8 U02 2017/1/23 6 U01 2017/2/22 4

要求使用SQL统计出每个用户的累积访问次数，如下表所示：

用户id 月份 小计 累积 u01 2017-01 11 11 u01 2017-02 12 23 u02 2017-01 12 12 u03 2017-01 8 8 u04 2017-01 3 3

数据集

u01 2017/1/21 5 u02 2017/1/23 6 u03 2017/1/22 8 u04 2017/1/20 3 u01 2017/1/23 6 u01 2017/2/21 8 u02 2017/1/23 6 u01 2017/2/22 4

1）创建表

  1. create table action
  2. (userId string,
  3. visitDate string,
  4. visitCount int)
  5. row format delimited fields terminated by "\t";

2）修改数据格式

  1. select
  2. userId,
  3. date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
  4. visitCount
  5. from
  6. action;t1

3）计算每人单月访问量

  1. select
  2. userId,
  3. mn,
  4. sum(visitCount) mn_count
  5. from
  6. t1
  7. group by
  8. userId,mn;t2

4）按月累计访问量

  1. select
  2. userId,
  3. mn,
  4. mn_count,
  5. sum(mn_count) over(partition by userId order by mn)
  6. from t2;

5）最终SQL

  1. select
  2. userId,
  3. mn,
  4. mn_count,
  5. sum(mn_count) over(partition by userId order by mn)
  6. from
  7. (   select
  8. userId,
  9. mn,
  10. sum(visitCount) mn_count
  11. from
  12. (select
  13. userId,
  14. date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,
  15. visitCount
  16. from
  17. action)t1
  18. group by userId,mn)t2;