开窗函数

一、数据类型

1、数值型

1. TINYINT — 微整型，只占用1个字节，只能存储0-255的整数。
2. SMALLINT– 小整型，占用2个字节，存储范围–32768 到 32767。
3. INT– 整型，占用4个字节，存储范围-2147483648到2147483647。
4. BIGINT– 长整型，占用8个字节，
5. 布尔型BOOLEAN — TRUE/FALSE
6. 浮点型FLOAT– 单精度浮点数。
7. DOUBLE– 双精度浮点数。
8. 字符串型STRING– 不设定长度。

数值类型转换

select cast(123 as string);//整数型转换为string类型

2、日期类型

1，Timestamp 格式“YYYY-MM-DD HH：MM：SS.fffffffff”（9位小数位精度）
2，Date DATE值描述特定的年/月/日，格式为YYYY-MM-DD。

3、复杂数据类型

Arrays

create table testArray(
    name string,
    weight array<string>
)row format delimited 
fields terminated by '\t'
COLLECTION ITEMS terminated by ',';

select name,weight[0] from testArray;

Maps

create table scoreMap(
    name string,
    score map<string,int> 
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';

select name,score['语文'] from scoreMap;

Structs结构体

create table scoreStruct(
    name string,
    score struct<course:string,score:int,course_id:int,tearcher:String> 
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';

select name,score.course,score.score from scoreStruct;
小明	语文,91,000001,余老师
小红	数学,100,000002,体育老师

json字符串获取

select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[0].score"); 

二、HQL语法

1、DDL

库表操作

创建数据库

create database xxxxx;

查看数据库

show databases；

删除数据库

drop database tmp;

强制删除数据库：

drop database tmp cascade;

查看表：

SHOW TABLES；

查看表的元信息：

desc test_table;
    describe extended test_table;
    describe formatted test_table;

查看建表语句：

show create table table_XXX

重命名表：

alter table test_table rename to new_table;

修改列数据类型：

alter table lv_test change column colxx string;

增加、删除分区：

alter table test_table add partition (pt=xxxx) 
alter table test_table drop if exists partition(...);

2、DML

三、hive函数使用

1、常用函数

1. if函数 if（，，）
2. case when 函数：case when 。。。end

3. 日期函数：to_date........

时间戳与时间字符串转换

from_unixtime 传入一个时间戳以及pattern（yyyy-MM-dd） 可以将时间戳转换成对应格式的字符串

select from_unixtime(1630915221,'yyyy年MM月dd日 HH时mm分ss秒')

unix_timestamp 传入一个时间字符串以及pattern，可以将字符串按照pattern转换成时间戳

select unix_timestamp('2021年09月06日 16时00分21秒','yyyy年MM月dd日 HH时mm分ss秒');
select unix_timestamp('2021-01-14 14:24:57.200')

4. 字符串函数：concat，concat_ws， split

select concat('123','456');
select concat(123,'456');
select concat(123,456);
select concat(123,456,null);
select concat_ws('123','456',null);
select concat(123,456,789);
select concat_ws(123,456,789);
select concat_ws(cast(123 as string),cast(456 as string),cast(789 as string));

5. 聚合函数：sum，count，avg，min，max
6. null值判断：is null ，is not null
7. 其他：round，floor，……

2、高级函数

lateral view---行转列

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

格式：
select id,新列别名 from 表名 lateral view explode(array) 视图别名 as 新列别名;
//数组转换
select name,col1  from testarray lateral view explode(weight) t1 as col1;
//map转换（视图起两个字段名即可）
select name,col1,col2  from scoremap lateral view explode(score) t1 as col1,col2;

列转行

collect_list(age) 自动转换为list，list允许重复
collect_set(age)  自动转换为set，set不允许重复

3、自定义函数