day04 函数和运算符
1、运算符
建表和数据
create external table ext_table(
id int,
name string,
age int,
s_id int,
score double
)row format delimited fields terminated by ','
location '/tmp/hive/external/ext_table';
1,'tom',18,1,70
1,'tom',18,2,50
1,'tom',18,3,80
2,'jack',20,1,60
2,'jack',20,2,50
2,'jack',20,3,70
3,'tom',21,1,50
3,'tom',21,2,80
3,'tom',21,3,30
1、关系运算
# 不等于
!= , <>
# 空值判断
is null
select "adc" is null;
# 非空判断
is not null
# like比较
select 'a' like 'a%';
判断的是查的值还是本身的输入？？？？？？
2、数学运算
# + - * /
# 按位与&、按位或|、按位异或^
# 按位取反~
3、逻辑运算
# 逻辑与 and
# 逻辑或 or
# 逻辑非 not
select not 1=1;
2、函数
查看函数详情
describe function extended 函数名；
1、条件函数***
1、if(expr1,expr2,expr3)
当表达式expr1正确，返回expr2；expr1错误，返回expr3,相当于三目运算符。
select if(1=1,'true','false');// true
select if(1<>1,'true','false');// false
2、非空查找：coalesce(a1,a2,...)
返回第一个非空参数/非空列
select coalesce(null,'tom',null); //tom
select coalesce(id,name) from t_tab;//表格
表格，返回第一个非空列
create table t_tab(
id int,
name string
)
row format delimited fields terminated by ',';
insert into table t_tab(name) values(null),('tom');
select coalesce(id,name) from t_tab;
// 结果:
NULL
tom
3、条件判断：CASE
1、匹配值
当id=100时返回a,id=200时返回b,否则返回c
# 第一种写法
select case id when 100 then a when 200 then b else c end from case_table;
2、条件判断
# 当工资大于5000，返回high，当工资大于3000小于5000时，返回medium，否则返回low
# 建表、数据
create table sl_tab(
id int,
salary int
)
row format delimited fields terminated by ',';
insert into table sl_tab values(1,8000),(2,3000),(3,1000),(4,5000),(5,4000);
# 语句
select
case
when salary>5000 then 'high'
when salary>3000 then 'medium'
else 'low'
end as salary_level
from sl_tab s;
2、日期函数
# 1、时间戳转日期 from_unixtime
select from_unixtime(1704439397);
# 2、日期转UNIX时间戳 unix_timestamp
select unix_timestamp(current_date);//获取当前时间戳
select unix_timestamp();//获取当前时间戳
select unix_timestamp('2020-02-02 14:20:20');//指定格式日期转UNIX时间戳
# 3、日期时间提取日期 to_date
select to_date('2020-02-02 14:20:20');
# 4、日期提取年year、月month、日day、小时hour、分钟minute、秒second、周weekofyear
select year('2020-02-02 14:20:20');//提取年
select weekofyear('2020-02-02 14:20:20');//日期提取周
# 5、日期比较 datediff(date1,date2)
--结果为相差几天
select datediff('2024-01-02','2024-01-01');// 结果为1
select datediff('2024-01-01','2024-01-02');// 结果为-1
# 6、日期增加date_add(date,int)、日期减少date_sub(date,int)
select date_add('2023-08-15',100);//从8.15日往后加100天的日期是多少
select date_sub('2023-11-23',100);
3、字符串函数
# 1、字符串长度length
select length('tom'); // 3
# 2、字符串反转reverse
select reverse('abc'); //cba
# 3、字符串转大写 upper,ucase 转小写lower,lcase
# 4、去空格函数：trim
左边去空格函数：ltrim
右边去空格函数：rtrim
正则表达式替换函数：regexp_replace
正则表达式解析函数：regexp_extract
URL解析函数：parse_url
空格字符串函数：space
重复字符串函数：repeat
首字符ascii函数：ascii
左补足函数：lpad
右补足函数：rpad
size:求取元素个数
# 3、字符串拼接concat****
select concat('abc','cd'); //abccd
# 4、字符串concat_ws**
使用指定分隔符连接字符串
concat_ws(separator,string1,string2... | array[string,...])
select concat_ws('.','www','baidu','com');//www.baidu.com
# 5、字符串截取 substr(str,pos [,len]),substring****
pos=index+1;如果pos为负数，则从后往前数
select substr('facebook',3); // cebook
select substr('facebook',-3); // ook
select substr('facebook',3,2); // ce
# 6、分隔字符串split(str,regex)
select split('a,b,c,dd',',');//["a","b","c","dd"]
select size(split('a,b,c,dd',','));//4
# 集合查找find_in_set
查找字符串在另一个被逗号隔开的字符串中存在的位置
select find_in_set('a','b,d,a,c');//3
1\还可以使用判断语句
select case when (find_in_set('a','b,d,a,c')) > 0 then 'exist' else 'not found' end as result; // exist
2\区分大小写，可以先都转为小写，再查找
select find_in_set(lower('A'),lower('a,b,c,D'));
# get_json_object
从json格式的字符串数据解析出所需字段的值，包含一个名为json_col的列，它存储了一些JSON数据。我们想要提取其中的某个字段。
create external table json_tab(
js string
)
location '/tmp/hive/json';
{"name":"lizhang","age":10}
select get_json_object(js,'$.name') from json_tab;
4、自定义函数
1、UDF：(User-Defined-Function)一进一出
1、引入架包hive-exec 3.1.3
2、建类继承GenericUDF
实现三个方法：initialize初始化、evaluate实现功能、帮助文档
initialize方法只处理类型，就是在select 函数的时候，反序列化读取数据时，需要确认数据以什么类型返回的；
evaluate方法只处理真实数据。
package org.example;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorUtils;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector;
/**
* @program: hadoop2
* @description:
* @author: Brooke
* @create: 2024-01-06 14:40
**/
public class ContainFunc extends GenericUDF {
// 定义出读取出的文件类型
private StringObjectInspector strIO;
/**
* 初始化只用来定义select使用函数后反序列化二进制文件为数据的数据类型
*
* @param objectInspectors
* @return
* @throws UDFArgumentException
*/
@Override
public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
// 限制了输入的值的类型只能是string类型，否则就会报错
this.strIO = (StringObjectInspector) objectInspectors[0];
// 确定返回的字段的类型为java类型的int，在evaluate方法里的返回值也要是java类型的int
return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
}
@Override
public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
// 传进去的deferrndObjects是String的类型，所以不能直接返回，
// Object str = deferredObjects[0].get();
// 需要下面的工具类来拿出String的值
String value = PrimitiveObjectInspectorUtils.getString(deferredObjects[0].get(), strIO);
// 再转为int
return Integer.parseInt(value);
}
/**
* 这是帮助文档，在hive中使用desc function extended fuc_name时，会显示
*
* @param strings
* @return
*/
@Override
public String getDisplayString(String[] strings) {
return null;
}
}
3、生成jar
lifecycle里先clean，在install
日志里有jar模块下的installing后有文件地址
D:\ComputerData\InstallBox\mavenRepository\org\example\customerFunction\1.0-SNAPSHOT\customerFunction-1.0-SNAPSHOT.jar
4、把jar导入到hive安装目录下的lib下(永久的函数加在lib下，临时的可以随便放)
5、添加jar到类路径
add jar /opt/demo/customerFunction-1.0-SNAPSHOT.jar;
6、创建临时函数
create temporary function ac(函数方法名) as '包名加类名';
creaet temporary function ac as 'org.example.ContainFunc';
7、使用方法
# 输入的类型必须为String，返回的是int类型数值
select ac('1');
8、案例
判断集合中是否包含数值的函数
package org.example;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ListObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils;
import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.BooleanObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.io.BooleanWritable;
/**
* @program: hadoop2
* @description:
* @author: Brooke
* @create: 2024-01-06 16:07
**/
public class ArrayContain extends GenericUDF {
// 先设置出两个字段的数据类型
private ListObjectInspector arr;
private PrimitiveObjectInspector arg;
// 设置数组的元素的类型
private PrimitiveObjectInspector ele;
// private BooleanObjectInspector result;//不存数据，
private BooleanWritable result;
/**
* 两个参数，第一个字段是数组，第二个字段是值，判断数值是否在数组中存在，返回boolean类型
* 类型详情：
* 1、第一个字段类型List
* 2、第一个字段内的元素为基本数据类型Primitive
* 3、第二个字段为基本数据类型Primitive
* 4、返回值为布尔类型Boolean
* ****只初始化一次
*
* @param objectInspectors
* @return
* @throws UDFArgumentException
*/
@Override
public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
// 1、显示字段类型
this.arr = (ListObjectInspector) objectInspectors[0];
this.ele = (PrimitiveObjectInspector) arr.getListElementObjectInspector();
this.arg = (PrimitiveObjectInspector) objectInspectors[1];
// 保证第一字段的元素和第二字段类型一致,如果不一致，提出异常
if (!ObjectInspectorUtils.compareTypes(ele, arg)) {
throw new UDFArgumentTypeException(000, "元素类型不一致");
}
// 保证集合里的元素是支持相互比较的，否则抛异常
// 什么元素不能比较？？？？？
if (!ObjectInspectorUtils.compareSupported(ele)) {
throw new UDFArgumentException("集合中的元素不能比较");
}
// 设定result初始值为false，在evalute方法里，满足条件再改为true
result = new BooleanWritable(false);
// 返回布尔类型
return PrimitiveObjectInspectorFactory.writableBooleanObjectInspector;
}
/**
* 判断数值是否包含在集合中，方法多次执行
*
* @param deferredObjects
* @return
* @throws HiveException
*/
@Override
public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
// 多行数据数据会重复执行，所以让每次执行result都先设为false；
result.set(false);
// arr是PrimitiveObjectInspector类型，提供了方法getListLength()，能得到集合长度
// deferredObjects[0].get()表示拿到了集合
int listLength = this.arr.getListLength(deferredObjects[0].get());
for (int i = 0; i < listLength; i++) {
// arr还有getListElement()方法获得集合元素，第一参数是集合，第二参数是下标
Object ele1 = arr.getListElement(deferredObjects[0].get(), i);
if (ObjectInspectorUtils.compare(ele1, ele, deferredObjects[1].get(), arg) == 0) {
result.set(true);
}
// 两个方法都可以
// if (ele1.equals(deferredObjects[1].get())){
// result.set(true);
// }
}
return result;
}
/**
* 帮助文档
*
* @param strings
* @return
*/
@Override
public String getDisplayString(String[] strings) {
return null;
}
}
安装scala，idea里面下载插件
2、UDAF：(User-Defined Aggregation Function)
多进一出，比如count，sum，多行转一行
package com.hylk;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator;
import org.apache.hadoop.hive.serde2.objectinspector.*;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorUtils;
import org.apache.hadoop.io.Text;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.*;
/**
* eg:
* select customer_avg(course_id,completed_status,completed_date)
* from record
* group by user_id
* ++++++++++++++++++++++++++++++++++++++++++++++
* result:
* map<course_id,struct {completed_status:1,completed_date:2023-09-01}>
**/
public class GeneratorUDAFAvg extends GenericUDAFEvaluator {
private static final Logger LOG = LoggerFactory.getLogger(GeneratorUDAFAvg.class.getName());
private PrimitiveObjectInspector courseIdOI;
private PrimitiveObjectInspector completeStatusOI;
private PrimitiveObjectInspector completeDateOI;
private MapObjectInspector aggOI;
@Override
public ObjectInspector init(Mode m, ObjectInspector[] parameters) throws HiveException {
super.init(m,parameters);
if(m == Mode.PARTIAL1 || m == Mode.COMPLETE){
courseIdOI = (PrimitiveObjectInspector)parameters[0];
completeStatusOI = (PrimitiveObjectInspector) parameters[1];
completeDateOI = (PrimitiveObjectInspector) parameters[2];
}else{
aggOI = (MapObjectInspector) parameters[0];
}
ObjectInspector mapKeyOI = ObjectInspectorFactory.getReflectionObjectInspector(String.class, ObjectInspectorFactory.ObjectInspectorOptions.JAVA);
List<String> structFieldName = new ArrayList<>();
structFieldName.add("completed_status");
structFieldName.add("completed_date");
List<ObjectInspector> structFieldObjectInspectors = new ArrayList<>();
structFieldObjectInspectors.add(ObjectInspectorFactory.getReflectionObjectInspector(Integer.class , ObjectInspectorFactory.ObjectInspectorOptions.JAVA));
structFieldObjectInspectors.add(ObjectInspectorFactory.getReflectionObjectInspector(String.class , ObjectInspectorFactory.ObjectInspectorOptions.JAVA));
StandardStructObjectInspector mapValueOI = ObjectInspectorFactory.getStandardStructObjectInspector(structFieldName, structFieldObjectInspectors);
return ObjectInspectorFactory.getStandardMapObjectInspector(mapKeyOI, mapValueOI);
}
@Override
public AggregationBuffer getNewAggregationBuffer() throws HiveException {
return new AvgBuffer();
}
@Override
public void reset(AggregationBuffer aggregationBuffer) throws HiveException {
AvgBuffer agg = (AvgBuffer) aggregationBuffer;
agg.cache.clear();
}
@Override
public void iterate(AggregationBuffer aggregationBuffer, Object[] objects) throws HiveException {
AvgBuffer agg = (AvgBuffer) aggregationBuffer;
agg.put(
PrimitiveObjectInspectorUtils.getString(objects[0] , courseIdOI),
PrimitiveObjectInspectorUtils.getInt(objects[1] , completeStatusOI),
PrimitiveObjectInspectorUtils.getString(objects[2] , completeDateOI)
);
}
@Override
public Object terminatePartial(AggregationBuffer aggregationBuffer) throws HiveException {
AvgBuffer agg = (AvgBuffer) aggregationBuffer;
return agg.cache;
}
@Override
public void merge(AggregationBuffer aggregationBuffer, Object par) throws HiveException {
if(par != null){
AvgBuffer agg = (AvgBuffer) aggregationBuffer;
Map<String, List<Object>> map = (Map<String, List<Object>>)ObjectInspectorUtils.copyToStandardJavaObject(par, aggOI);
for(Map.Entry<String, List<Object>> entry: map.entrySet()){
String courseId = entry.getKey().toString();
List<Object> values = entry.getValue();
agg.put(courseId, (Integer) values.get(0), values.get(1).toString());
}
}
}
@Override
public Object terminate(AggregationBuffer aggregationBuffer) throws HiveException {
AvgBuffer agg = (AvgBuffer) aggregationBuffer;
return agg.cache;
}
static class AvgBuffer implements AggregationBuffer{
Map<String,List<Object>> cache = new HashMap<>();
void put(String courseId,Integer completeStatus,String completeDate){
if(!cache.containsKey(courseId)){
List<Object> values = new ArrayList<>();
values.add(completeStatus);
values.add(completeDate);
cache.put(courseId , values);
}else{
List<Object> oldValue = cache.get(courseId);
int oldStatus = (Integer) oldValue.get(0);
String oldDate = (String) oldValue.get(1);
int newStatus = completeStatus > oldStatus ? completeStatus: oldStatus;
String newDate = completeDate.compareTo(oldDate)>0? completeDate:oldDate;
oldValue.set(0, newStatus);
oldValue.set(1, newDate);
cache.put(courseId , oldValue);
}
}
}
}
3、UDTF：(User-Defined Function)
一进多出，比如explode，一行转行
5、explode分解函数
1、定义
参数为map、array类型，将输入的数据中每个参数分为一行，有几个参数分为几行，列转行。
select explode(array(1,2,3)) as result;
2、注意
1、explode生成的表为虚拟表，不能直接select 源表字段和虚拟表字段一起查询显示，只能借助lateral view侧视图，lateral View侧视图专门用于搭配UDTF函数，实现源表和虚拟表一起查询。
数据
create external table test_tab(
id int,
file array<string>
)
row format delimited fields terminated by ','
collection items terminated by '-'
location '/tmp/hive/external/test';
hdfs dfs -mkdir /tmp/hive/external/test
1,a-b-c
2,c-d-f
hdfs dfs -put a.txt /tmp/hive/external/test
命令
# 单命令
select explode(file) from test_tab;
// 结果
a
b
c
c
d
f
# 连接alteral view
select x.id,y.result from test_tab x lateral view explode(file) y as result;
// 结果
1 a
1 b
1 c
2 c
2 d
2 f
6、Lateral View侧视图
Lateral View是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制的问题。
一般只要使用UDTF，就会固定搭配lateral view使用。
将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。
使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by 、limit等语句中，不需要再单独嵌套一层子查询。
6、Aggregation聚合函数UDAF
max、min、avg、count
聚合函数的功能是对一组值执行计算并返回单一的值。
聚合函数是典型的输入多行输出一行，使用Hive的分类标准，属于UDAF类型函数。
通常搭配Group By语法一起使用，分组后进行聚合操作。
HQL提供了几种内置的UDAF聚合函数，例如max（…），min（…）和avg（…），称之为聚合函数。
通常情况下聚合函数会与GROUP BY子句一起使用。分完组之后聚合，如果未指定GROUP BY子句，默认情况下，它会汇总所有行数据。
1、没有group by子句的聚合操作
2、带有group by子句的聚合操作
3、select时多个聚合函数一起使用
4、聚合函数和case when条件转换函数、coalesce函数、if函数使用
5、聚合参数不支持嵌套聚合函数
6、配合distinct关键字去重聚合-count(distinct(id))-NULL不参与运算
7、count对null不参与运算
案例
create table sl_tab(
id int,
salary int
)
row format delimited fields terminated by ',';
#数据
1 8000
2 3000
3 1000
4 5000
5 4000
2 5000
2 3000
3 2000
4 6000
5 7000
#命令
# 先根据id分组，再组内选出最大值；
select id,max(salary) from sl_tab group by id;
# 根据id分组，得出每组的工资和；
select id,sum(salary) from sl_tab group by id;
// 结果
1 8000
2 11000
3 3000
4 11000
5 11000
7、grouping_sets、cube、rollup增强聚合分组
主要用于OLAP多维数据分析模式中（多维：多角度都可以）
增强聚合包括grouping_sets、cube、rollup这几个函数；主要适用于OLAP多维数据分析模式中，多维分析中的维指的分析问题时看待问题的维度、角度
1、grouping sets是一种将多个group by逻辑写在一个sql语句中的便利写法。等价于将不同维度的GROUP BY结果集进行UNION ALL。GROUPING__ID表示结果属于哪一个分组集合
2、cube表示根据GROUP BY的维度的所有组合进行聚合。
对于cube来说,如果有n个维度,则所有组合的总个数是：2的n次方
比如cube有a,b,c 3个维度，则所有组合情况是： (a,b,c),(a,b),(b,c),(a,c),(a),(b),©,()
3、cube的语法功能指的是根据GROUP BY的维度的所有组合进行聚合。
rollup是cube的子集，以最左侧的维度为主，从该维度进行层级聚合。
比如ROLLUP有a,b,c3个维度，则所有组合情况是：(a,b,c),(a,b),(a),()
案例，数据
create table table1(
id int,
dept int,
salary int
)
row format delimited fields terminated by ',';
insert into table table1 values
(1,1,4000),
(1,1,7000),
(2,1,5000),
(2,1,3000),
(3,2,2000),
(3,2,4000),
(4,2,6000),
(5,1,7000);
1、grouping sets 多维度分组
根据后面的参数来决定都从几个维度来分组再union
模板
grouping sets（a,b）==>group by(a) union group by(b)
grouping sets（(a,b)）==>group by a,b
grouping sets（(a,b),a）==>group by a,b union group by b
命令
1、
# 分组中的字段没有加括号，就和group by id,dept一样了
select id,dept,sum(salary) from table1 group by id,dept grouping sets(id,dept);
2、
# 根据id，dept分组，求salary和，再根据id分组，求和salary，两个结果union all
select id,dept,sum(salary) from table1 group by id,dept grouping sets((id,dept),id);
3、
# 根据id分组求和，再根据dept分组求和，再不分组求和，三个结果union all
select id,dept,sum(salary) from table1 group by id,dept grouping sets((id),(dept),());
2、cube 结合所有组的可能
表有a,b两个维度可以分组，cube就是结合(null,null),(a,null),(null,b)(a,b)查完union一起
# (),(id),(dept),(id,dept)所有分组类型
select id,dept,sum(salary) from table1 group by id,dept with cube;
3、rollup 以左侧的为基准分组
表有两个维度a,b可以分组，rollup的结果就是(null,null),(a,null),(a,b)查完后union一起
# (),(id),(id,dept)以左侧为基准
select id,dept,sum(salary) from table1 group by id,dept with rollup;
# (),(dept),(id,dept)以左侧的dept为基准
select id,dept,sum(salary) from table1 group by dept,id with rollup;
8、over开窗函数
1、定义
输入值是从select语句的结果集中的一行或多行的”窗口“获得的。得出结果后，显示时，每行从各自的组里拿出结果，显示在自己行的后面。
2、作用：
1、数据分析过滤数据；
2、ETL
3、注意：开窗函数和group by区别：
group by 计算后结果是一行；
over(partition by ...)显示的还是表的所有行；
4、格式
窗口函数（Window functions）也叫做开窗函数、OLAP函数，其最大特点是输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。如果函数具有OVER子句，则它是窗口函数。
窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。
1、窗口聚合函数指的是sum、max、min、avg这样的聚合函数在窗口中的使用
Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])
--其中Function(arg1,..., argn) 可以是下面分类中的任意一个
--聚合函数：比如sum max avg等
--排序函数：比如rank row_number等
--分析函数：比如lead lag first_value等
--OVER [PARTITION BY <...>] 类似于group by 用于指定分组每个分组你可以把它叫做窗口
--如果没有PARTITION BY 那么整张表的所有行就是分区
--[ORDER BY <....>] 用于指定每个分区内的数据排序规则 支持ASC、DESC
--[<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行
窗口表达式window_expression
在sum(…) over( partition by… order by … )语法完整的情况下，进行累积聚合操作，默认累积聚合行为是：从第一行聚合到当前行。Window expression窗口表达式给我们提供了一种控制行范围的能力，比如向前2行，向后3行。
关键字rows between,包括下面这几个选项
- preceding：往前
- following：往后
- current row：当前行
- unbounded：边界
- unbounded preceding：表示窗口的起点位置
- unbounded following：表示窗口的终点
2、窗口排序函数–row_number家族
用于给每个分组内的数据打上排序的标号，注意窗口排序函数不支持窗口表达式
row_number：在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复；
rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置；
dense_rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置；
3、窗口排序函数–ntile
将每个分组内的数据分为指定的若干个桶里（分为若干个部分），并且为每一个桶分配一个桶编号。
如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
4、窗口分析函数
LAG(col,n,DEFAULT) ，用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不
指定，则为NULL;
LEAD(col,n,DEFAULT) ，用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不
指定，则为NULL;
FIRST_VALUE，取分组内排序后，截止到当前行，第一个值
LAST_VALUE，取分组内排序后，截止到当前行，最后一个
5、案例
案例，数据
create table table1(
id int,
dept int,
salary int
)
row format delimited fields terminated by ',';
insert into table table1 values
(1,1,4000),
(1,1,7000),
(2,1,5000),
(2,1,3000),
(3,2,2000),
(3,2,4000),
(4,2,6000),
(5,1,7000);
命令
1、聚合
# 聚合
# 1、根据id分组，每组求和，显示在每条记录后
select id,dept,sum(salary) over(partition by id) as salarys from table1;
# 2、over()里面没有分组，表示全部数据为一组，求和
select id,dept,sum(salary) over() from table1;
# 3、根据id，dept分组，并先得出每个组的最大值，再每行显示出来
select id,dept,max(salary) over(partition by id,dept) from table1;
2、排序
# 排序
# 1、rank()。使用id，dept分组，分组后组内按照salary排序，得到组内的工资排行榜
select id,dept,salary,rank() over(partition by id,dept order by salary [desc]) as rank from table1;
# 2、row_number()
select id,dept,salary,row_number() over(partition by id,dept order by salary desc) as rank from table1;
rank和row_number区别：
rank遇到相同的值，可以并列排名，下一个轮空；
row_number遇到相同的值，还是会排先后
# 3、拿出每一个窗口，工资排名第一的人
select x.id,x.dept,x.salary from
(select id,dept,salary,rank()
over(partition by id,dept order by salary desc) as salarys from table1) x
where x.salarys = 1;
3、分析
# 分析函数
# 1、使用id，dept分组出多个窗口，每个窗口按照salary排序，并查看自己的下一行的salary是否为空，有则返回下一行的工资，没有则返回null
select id,dept,salary,lead(salary,1,'no') over(partition by id,dept order by salary) from table1;
# 2、根据id，dept分组出多个窗口，每个窗口按照salary排序，显示出每个窗口的salary列的第一条内容
select id,dept,salary,first_value(salary) over(partition by id,dept order by salary desc) as firstv from table1;
4、窗口表达式
window_expression 限制函数执行的行范围，几行到几行
格式：rows between ... and ...
例如：rows between 2 preceding and 2 following//前两行到当前行的后两行
rows between 2 preceding and current row//前两行到当前行
关键字rows between,包括下面这几个选项
- preceding：往前
- following：往后
- current row：当前行
- unbounded：边界
- unbounded preceding：表示窗口的起点位置，即向上无边界
- unbounded following：表示窗口的终点，即向下无边界
案例
# 全表排序后的数据
select id,dept,salary,rank() over(partition by id,dept order by salary) from table1;
# 1、使用id,dept分多个窗口，每个窗口按salary升序排序，求和(但是限制了求和的边界，是当前行、前一行、后一行的总和)
select id,dept,sum(salary)
over(partition by id,dept order by salary rows between 1 preceding and 1 following) as rn
from table1;
# 2、unbounded preceding向上无边界,表示每一行后面新加的求和列的值是，窗口内当前行、后一行、前面所有行salary相加的结果。
select id,dept,sum(salary)
over(partition by id,dept order by salary rows between unbounded preceding and 1 following) as rn
from table1;
# 3、current row前面起点到当前行的salary求和
select id,dept,sum(salary)
over(partition by id,dept order by salary rows between 1 preceding and current row) as rn
from table1;
5、老师案例
create external table window_tab(
user_id string,
course_id string,
duration double,
study_date string
)
row format delimited
fields terminated by ','
location '/tmp/hive/window_tab';
---创建数据
u_001,c_001,30.0,2023-10-02
u_001,c_002,10.0,2023-10-03
u_001,c_002,30.0,2023-10-04
u_001,c_002,40.0,2023-10-05
u_001,c_003,10.0,2023-10-06
u_001,c_003,15.0,2023-10-07
u_001,c_004,20.0,2023-10-07
u_001,c_005,20.0,2023-10-08
u_001,c_006,20.0,2023-10-09
u_001,c_007,20.0,2023-10-10
u_002,c_001,20.0,2023-10-10
u_002,c_001,30.0,2023-10-11
u_001 c_002 10.0 2023-10-03 2023-10-04
u_001 c_002 30.0 2023-10-04 2023-10-05
u_001 c_003 10.0 2023-10-06 2023-10-07
u_002 c_001 20.0 2023-10-10 2023-10-11
--聚合函数
select user_id,course_id,duration,study_date,sum(duration) over(partition by user_id) as sum_duration
from window_tab;
select user_id,course_id,duration,study_date,sum(duration) over() as sum_duration
from window_tab;
--排序函数
select x.user_id,x.course_id,x.duration from(
select
user_id,
course_id,
duration,
study_date, row_number() over(partition by user_id,course_id order by study_date) rn
from window_tab
)x where x.rn=1;
----分析函数
select
user_id,
course_id,
duration,
date_add(study_date,1) as next_day,
lead(study_date,1,null) over(partition by user_id,course_id order by study_date) window_next_day
from window_tab
where next_day=window_next_day;
select * from(
select
user_id,
course_id,
duration,
study_date,
lead(study_date,1,null) over(partition by user_id,course_id order by study_date) window_next_day
from window_tab
)x
where date_add(study_date,1)=window_next_day
9、行列转换的常用函数
case when 函数-多行转多列
concat函数-多列转单列
concat_ws函数
collect_list函数-列中的多行合并为一行，不进行去重
collect_set函数-将一列中的多行合并为一行，并进行去重
union函数-多列转多行-将多个select语句结果合并为一个，且结果去重且排序
union all函数-将多个select语句结果合并为一个，且结果不去重不排序
explode函数-单列转多行
本文转载自: https://blog.csdn.net/2301_79020421/article/details/135445505
版权归原作者 余淮~ 所有， 如有侵权，请联系我们删除。
版权归原作者 余淮~ 所有， 如有侵权，请联系我们删除。