HiveSQL常用操作

1.concat_ws()、concat()、collect_set()
concat：连接多个字段
collect_set：collect_set(col)函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
concat_ws：表示concat with separator,即有分隔符的字符串连接，concat_ws(”,collect_set())表示用空的字符”来连接collect_set返回的array中的每个元素。
参考：https://blog.csdn.net/waiwai3/article/details/79071544

2.日期函数
2.1 unix_timestamp() 将指定时间格式转换成long类型时间
select unix_timestamp(); --获取当前时间，并返回long类型时间
select unix_timestamp(‘20170208 11:00:00’,‘yyyyMMdd HH:mm:ss’); --指定输入时间格式，并返回long类型时间
select unix_timestamp(‘2020-09-19 11:00:00’,‘yyyy-MM-dd HH:mm:ss’); --指定输入时间格式，并返回long类型时间

2.2 from_unixtime() 将long类型时间转换成指定时间格式
select from_unixtime(1591551670); --输入long类型，返回格式默认为yyyy-MM-dd HH:mm:ss
select from_unixtime(1591551670,‘yyyyMMdd’); --指定输出的日期格式
//from_unixtime(long类型时间,‘yyyy-MM-dd’)
select from_unixtime(unix_timestamp(‘20170208 11:00:00’,‘yyyyMMdd’),‘yyyy-MM-dd’)

2.3 date_format()、todate()
date_format(‘2020-06-06 11:11:11’,‘yyyy-MM’) --输入格式yyyy-MM-dd HH:mm:ss
to_date(‘2020-06-07 11:11:11’,‘yyyy-MM-dd HH:mm:ss’)–输出yyyy-MM-dd

2.4 next_day()
获取下一周的日期：
next_day(‘指定日期yyyy-MM-dd’,‘Mo’) 周一
next_day(‘指定日期’,‘Tu’) 周二
next_day(‘指定日期’,‘We’) 周三
next_day(‘指定日期’,‘Th’) 周四
next_day(‘指定日期’,‘Fr’) 周五
next_day(‘指定日期’,‘Sa’) 周六
next_day(‘指定日期’,‘Su’) 周日
next_day(to_date(CURRENT_TIMESTAMP),‘Mo’) 当前日期的下一周的周一
next_day(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’),‘Mo’) 当前日期的下一周的周一

2.5 date_add()
获取指定日期的周一、周日:
date_add(next_day(‘2020-06-06’,‘Mo’),-7) 周一
date_add(next_day(‘2020-06-06’,‘Mo’),-1) 周日

获取本周的周一、周日：
date_add(next_day(to_date(CURRENT_TIMESTAMP),‘Mo’),-7) 周一
date_add(next_day(to_date(CURRENT_TIMESTAMP),‘Mo’),-1) 周日
date_add(next_day(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’),‘Mo’),-7) 周一
date_add(next_day(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’),‘Mo’),-1) 周日

2.6 last_day()
获取本月最后一天：
last_day(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’))

2.7 months_between(date1,date2)
计算年龄：months_between(‘2020-06-05’,‘出生日期’)/12

2.8 datediff()
计算两个日期相差天数–前面减去后面，格式统一为yyyy-MM-dd
datediff(‘2020-09-19’,from_unixtime(unix_timestamp(‘20200915’,‘yyyyMMdd’),‘yyyy-MM-dd’))

3.union all
3.1 union all 的结果集需要加别名
select
a1,sum(a2),sum(a3)
from
(
select a1,a2,a3 from tmp1
union all
select a1,a2,a3 from tmp2
) a
group by a1

3.2子查询表加别名报错，如下：
select
a1,sum(a2),sum(a3)
from
(
（
select a1,a2,a3 from tmp1
）a
union all
(
select a1,a2,a3 from tmp2
) b
) c
group by a1

4.with as() 脚本开头定义
创建多个临时表：
with tmp1 as(
select xx from a
)，
tmp2 as(
select xx from b
)
select xx from
tmp1 join tmp2 on xx;

5.加载数据到hive表
5.1 load （复制或剪切文件）
语法结构：load data [local] inpath ‘filepath’ [overwrite] into table table_name [partition(part1=val1,part2=val2)]
overwrite --表示覆盖
partition – 指定分区

linux本地加载
load data local inpath ‘路径’ [overwrite] into table xxx [partition()]

先上传hdfs，然后load
load data inpath ‘hdfs路径’ [overwrite] into table xx [partition()]
原来hdfs路径下的文件会被剪切

5.2 insert
将查询数据插入表中：
insert overwrite/into table table_name
[partition(part=val,part2=val2,…)]
select fileds,… from tb_other;
单条插入：
insert into table t_name(field1,field2,…fieldn) value(v1,v2,…vn);
批量插入：
insert into table t_name(field1,field2,…fieldn) values(v11,v12,…v1n),(v21,v22,…v2n)…;
如：可以省略字段名称
insert overwrite table employees
values(10008,‘1958-02-19’,‘Saniya’,‘Kalloufi’,‘M’,‘1994-09-15’),
(10009,‘1959-02-19’,‘Saniya’,‘Kalloufj’,‘M’,‘1994-09-15’),
(10010,‘1959-02-19’,‘Saniya’,‘Kalloufk’,‘M’,‘1993-09-15’)

5.3 create table…as （将查询数据插入表中）
create table tb_create_mode [stored as rcfle] as
select fields,…from xxx;

6.数据类型
6.1 decimal
decimal(p,s) s则是小数的位数，p值称为此数值的精确度
decimal(10,2)表示共有8位整数2位小数，此例的精确度为10位

7.数据类型转换
7.1 CAST (expression AS data_type))
参数：
expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。 data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。
如：cast (new_mid_count/uv_m_count*100 as decimal(10,2))

7.2 ROUND(X,D)
ROUND(X,D):返回参数X的四舍五入的有D位小数的一个数字。如果D为0，结果将没有小数点或小数部分。如：round(1.456,0) – 1.5; round(1.456,2) --1.46

7.3 空值处理
NVL( string1, replace_with)
coalesce(expression_1, expression_2, …,expression_n)

8.split()
split(string str, string pat)
按照pat字符串分割str，会返回分割后的字符串数组

9.lateral view
lateral view为侧视图,意义是为了配合UDTF来使用,把某一行数据拆分成多行数据.不加lateral view的UDTF只能提取单个字段拆分,并不能关联原来表的其他字段

create table if not exists province_city(
province string,
city string
)
row format delimited fields terminated by ‘,’
stored as textfile;

insert into table province_city(province,city) values(‘江西’,‘南昌|九江|赣州|鹰潭|新余’);
insert into table province_city(province,city) values(‘安徽’,‘合肥|蚌埠|芜湖|马鞍山|铜陵’);

9.1单个字段
select split(city,‘\|’) from province_city;
[“南昌”,“九江”,“赣州”,“鹰潭”,“新余”]
[“合肥”,“蚌埠”,“芜湖”,“马鞍山”,“铜陵”]

9.2多个字段
select province,split(city,‘\|’) from province_city;
江西 [“南昌”,“九江”,“赣州”,“鹰潭”,“新余”]
安徽 [“合肥”,“蚌埠”,“芜湖”,“马鞍山”,“铜陵”]
说明：spilt函数返回一个数组，并未生成多行数据，sql语句可以执行

9.3对split返回的Array展开,和其他字段一起select，sql语句报错
hive> select province,explode(split(city,‘\|’)) from province_city;
FAILED: SemanticException [Error 10081]: UDTF’s are not supported outside the SELECT clause, nor nested in expressions
在select语句中使用UDTF语法的限制：
①在select语句中只支持单个UDTF表达式(即不能和其他字段一起使用)
②不支持UDTF嵌套
③不支持GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY语句

9.4侧视图生成虚拟表及字段
注意：原表字段和虚拟表字段可以直接使用,不需要加别名
select province, tmp.* from province_city lateral view explode(split(city,‘\|’)) tmp as city;
江西 南昌
江西 九江
江西 赣州
江西 鹰潭
江西 新余
安徽 合肥
安徽 蚌埠
安徽 芜湖
安徽 马鞍山
安徽 铜陵
参考：https://www.cnblogs.com/tomato0906/articles/6071457.html

9.5 lateral view explode (map())

select
    key
    ,field
    ,value
from
(
    select
        key --主键
        ,field1
        ,field2
        ...
        ,fieldn
    from table_A
)t1
lateral view explode(
map(
    'key', key
    ,'field1',field1
    ,'field2',field2
    ...
    ,'fieldn',fieldn
    )
)tmp AS field,value

10.left join on …and…
(1):ON后面的筛选条件主要是针对的是关联表【而对于主表筛选条件不适用】。
(2):对于主表的筛选条件应放在where后面，不应该放在ON后面
(3):对于关联表我们要区分对待。如果是要条件查询后才连接应该把查询件放置于ON后。如果是想再连接完毕后才筛选就应把条件放置于where后面
(4):对于关联表我们其实可以先做子查询再做join
10.1正确写法
select …from
a left join b on a.id=b.id
where b.id is null

10.2错误写法
select …from
a left join b on a.id=b.id and b.id is null
参考：https://blog.csdn.net/wang57389675/article/details/62048864

11.group by和 distinct
//去重并计算总条数
select count(distinct(name)) from tmp
或
select
count()
from
(
select
name
from tmp
group by name
)a
//输出的是每个组的条数
select
count()
from tmp
group by name
参考：https://blog.csdn.net/u010356237/article/details/94737809

12.sum 、if 组合
sum(if(order_count>0,1,0)) order_count

13.case when用法
方式一：
case 字段或表达式
when 常量1 then 值1或语句1
when 常量2 then 值2或语句2
when 常量3 then 值3或语句3
…
else ‘值n或语句n’
end as field_tmp

方式二：
case
when 条件1 then 值1或语句1
when 条件2 then 值2或语句2
when 条件3 then 值3或语句3
…
else ‘值n或语句n’
end as field_tmp
如：
case
when score<60 then ‘不及格’
when score>=60 and score <80 then ‘一般’
when score>=80 and score <90 then ‘良好’
else ‘优秀’ end as field_tmp

14.多表连接查询
笛卡尔乘积：表1 m行，表2 n行，结果为m*n行 (没有添加连接条件)
select
field1,
field2,
…
from tmpA,tmpB
where 条件1 and 条件2 …
应用场景：当查询的字段来自于多个表时

15.解析json数组
参考：https://blog.csdn.net/djz19890117/article/details/79165281

相关函数说明：
regexp_extract(field, string pattern ,  int index)
参数：
field 是被解析的字符串、字段
pattern 需要匹配的正则表达式
index 是返回结果 取表达式的哪一部分 默认值为1。
      0表示把整个正则表达式对应的结果全部返回
      1表示返回正则表达式中第一个() 对应的结果 以此类推

REGEXP_REPLACE(source_string, 
               pattern, 
               replace_string , 
               position,
               occurrence,
               match_parameter)
参数：                
1：要替换的字段   2：替换的正则表达式  3：替换成什么  4：替换起始位置（默认从1开始）  5：替换的次数（0是无限次）  6：不区分大小写

测试数据：[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]

//regexp_extract函数按照正则提取关键字符串
//regexp_extract(字符串,'^\\[(.+)\\]$',1)
//注意特殊字符转义\\[ \\]等
select regexp_extract('[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]',
'^\\[(.+)\\]$',1)
返回：{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}

//对上述结果替换 “},{” 这部分的逗号，改为"||"分隔，便于后续split函数切割
//regexp_replace(上述结果,'\\}\\,\\{','\\}\\|\\|\\{') 
//注意特殊字符转义\\} \\,  \\{ 以及双斜杠的转义\\|\\|
select regexp_replace(regexp_extract('[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]',
                        '^\\[(.+)\\]',1),
                       '\\}\\,\\{','\\}\\|\\|\\{')
返回：{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"}||{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}

//对上述结果进一步切隔，即split(上述结果,'\\|\\|')，然后explode函数展开
select explode(split(regexp_replace(regexp_extract('[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]',
                        '^\\[(.+)\\]',1),
                       '\\}\\,\\{','\\}\\|\\|\\{'),
                       '\\|\\|'))
返回：
{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"}
{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}

15.substr函数
取得字符串中指定起始位置和长度的字符串 ，默认是从起始位置到结束的子串，
下标从1开始。
substr( string, start_position, [ length ] )
substr(‘目标字符串’,开始位置,长度)
如：
截取字符串最后一位：substr(field,length(field),1)或substr(field,-1,1)"
截取字符串前两位：substr(field,1,2)

16.sort_array函数
对集合中元素进行排序

val sql01 =
  """
    |select
    |    device_id,
    |    theme_id,
    |    theme_ver,
    |    concat_ws('|',sort_array(collect_set(oper_type))) as oper_type,
    |    oper_date
    |from userActionInfoTable
    |group by device_id,theme_id,theme_ver,oper_date
  """.stripMargin

17.group by、distribute by、partition by、cluster by、sort by、order by
参考：https://www.itdiandi.net/view/2104
group by 把相同key的数据聚合到一起，后续必须是聚合操作
distribute by 控制map端数据分散到不同的reduce端，默认采用hash算法
partition by 可结合开窗函数over()，计算均值、方差等
cluster by 等价于distribute by…sort by …
sort by 对每个reduce端进行排序，结合distribute by使用
order by 全局排序。只在一个reduce中进行，效率低。

测试数据：
学号 姓名 语文  数学  英语  物理  化学 生物 
0001 张三 88 81 61 77 60 63
0002 李四 77 82 62 73 63 62
0003 王五 78 73 60 71 66 91
0004 赵六 55 84 63 74 67 90
0005 小明 54 95 66 67 45 29
0006 小红 63 76 67 68 44 28
0007 小张 62 57 69 69 53 17
0008 小李 91 68 70 77 52 88
0009 小吴 90 99 76 74 61 63
0010 小周 29 50 80 97 68 66
0011 小赵 28 49 83 76 95 67
0012 小王 17 30 65 80 76 69
0013 小孙 16 21 79 83 57 55
0014 小强 45 11 64 65 68 55
0015 小丽 44 81 61 79 66 95
0016 小花 53 81 50 90 50 76
0017 小玉 52 61 55 29 49 57
0018 小霞 61 71 58 28 30 68
0019 如花 68 88 62 17 21 33
0020 似玉 78 66 70 16 11 55

学号 姓名 性别 年龄 班级
0001 张三 男 17 1
0002 李四 男 17 1
0003 王五 男 18 1
0004 赵六 男 19 1
0005 小明 男 20 2
0006 小红 女 16 2
0007 小张 男 15 2
0008 小李 男 17 2
0009 小吴 男 19 3
0010 小周 男 18 3
0011 小赵 男 15 3
0012 小王 男 21 3
0013 小孙 男 19 4
0014 小强 男 20 4
0015 小丽 女 16 4
0016 小花 女 18 4
0017 小玉 女 17 5
0018 小霞 女 19 5
0019 如花 女 16 5
0020 似玉 女 18 5

建表：
drop table if exists student_score_info;
create table if not exists student_score_info(
sno int,
sname string,
chinese DOUBLE,
math DOUBLE,
english DOUBLE,
physics DOUBLE,
chemistry DOUBLE,
biology DOUBLE
) comment '学生成绩表'
row format delimited
fields terminated by ' '
stored as textfile;

drop table if exists student_info;
create table if not exists student_info(
sno int,
sname string,
sex string,
age int,
cno int
) comment '学生信息表'
row format delimited
fields terminated by ' '
stored as textfile;

//分区内有序，全局无序
select
     cno
    ,sno
    ,sname
    ,chinese
from
    (
    select
        sno 
        ,sname
        ,chinese
        ,math
        ,english
        ,physics 
        ,chemistry
        ,biology
        ,cno
    from
        (
        select
            t1.sno 
            ,t1.sname
            ,t1.chinese
            ,t1.math
            ,t1.english
            ,t1.physics 
            ,t1.chemistry
            ,t1.biology
            ,t2.cno
        from
            student_score_info t1
            left join
            student_info t2 on t1.sno=t2.sno
        )t3
    )t4
distribute by cno
sort by cno asc,chinese desc
输出：
1 1 张三 88.0
1 3 王五 78.0
1 2 李四 77.0
1 4 赵六 55.0
2 8 小李 91.0
2 6 小红 63.0
2 7 小张 62.0
2 5 小明 54.0
3 9 小吴 90.0
3 10 小周 29.0
3 11 小赵 28.0
3 12 小王 17.0
4 16 小花 53.0
4 14 小强 45.0
4 15 小丽 44.0
4 13 小孙 16.0
5 20 似玉 78.0
5 19 如花 68.0
5 18 小霞 61.0
5 17 小玉 52.0

//分区内有序，全局无序
select
     cno
    ,sno
    ,sname
    ,chinese
    ,row_number()over(partition by cno order by chinese desc) rnk
from
    (
    select
         sno 
        ,sname
        ,chinese
        ,math
        ,english
        ,physics 
        ,chemistry
        ,biology
        ,cno
    from
        (
        select
             t1.sno 
            ,t1.sname
            ,t1.chinese
            ,t1.math
            ,t1.english
            ,t1.physics 
            ,t1.chemistry
            ,t1.biology
            ,t2.cno
        from
            student_score_info t1
            left join
            student_info t2 on t1.sno=t2.sno
        )t3
    )t4
输出：
1 1 张三 88.0 1
1 3 王五 78.0 2
1 2 李四 77.0 3
1 4 赵六 55.0 4
2 8 小李 91.0 1
2 6 小红 63.0 2
2 7 小张 62.0 3
2 5 小明 54.0 4
3 9 小吴 90.0 1
3 10 小周 29.0 2
3 11 小赵 28.0 3
3 12 小王 17.0 4
4 16 小花 53.0 1
4 14 小强 45.0 2
4 15 小丽 44.0 3
4 13 小孙 16.0 4
5 20 似玉 78.0 1
5 19 如花 68.0 2
5 18 小霞 61.0 3
5 17 小玉 52.0 4

//指定的列只能降序，等价于distribute by cno sort by cno desc
select
     cno
    ,sno
    ,sname
    ,chinese
from
    (
    select
        sno 
        ,sname
        ,chinese
        ,math
        ,english
        ,physics 
        ,chemistry
        ,biology
        ,cno
    from
        (
        select
            t1.sno 
            ,t1.sname
            ,t1.chinese
            ,t1.math
            ,t1.english
            ,t1.physics 
            ,t1.chemistry
            ,t1.biology
            ,t2.cno
        from
            student_score_info t1
            left join
            student_info t2 on t1.sno=t2.sno
        )t3
    )t4
cluster by cno --指定的列只能降序，等价于distribute by cno sort by cno desc
输出：
1 2 李四 77.0
1 3 王五 78.0
1 1 张三 88.0
1 4 赵六 55.0
2 5 小明 54.0
2 6 小红 63.0
2 7 小张 62.0
2 8 小李 91.0
3 10 小周 29.0
3 12 小王 17.0
3 11 小赵 28.0
3 9 小吴 90.0
4 13 小孙 16.0
4 14 小强 45.0
4 15 小丽 44.0
4 16 小花 53.0
5 17 小玉 52.0
5 18 小霞 61.0
5 19 如花 68.0
5 20 似玉 78.0

//partition by结合窗口函数over()
select
     cno
    ,sno
    ,sname
    ,chinese
    ,avg(chinese) over(partition by cno) avg_chinese--错误用法partition by cno order by chinese desc
from
    (
    select
         sno 
        ,sname
        ,chinese
        ,math
        ,english
        ,physics 
        ,chemistry
        ,biology
        ,cno
    from
        (
        select
             t1.sno 
            ,t1.sname
            ,t1.chinese
            ,t1.math
            ,t1.english
            ,t1.physics 
            ,t1.chemistry
            ,t1.biology
            ,t2.cno
        from
            student_score_info t1
            left join
            student_info t2 on t1.sno=t2.sno
        )t3
    )t4
输出：
1 2 李四 77.0 74.5
1 3 王五 78.0 74.5
1 1 张三 88.0 74.5
1 4 赵六 55.0 74.5
2 5 小明 54.0 67.5
2 6 小红 63.0 67.5
2 7 小张 62.0 67.5
2 8 小李 91.0 67.5
3 10 小周 29.0 41.0
3 12 小王 17.0 41.0
3 11 小赵 28.0 41.0
3 9 小吴 90.0 41.0
4 13 小孙 16.0 39.5
4 14 小强 45.0 39.5
4 15 小丽 44.0 39.5
4 16 小花 53.0 39.5
5 17 小玉 52.0 64.75
5 18 小霞 61.0 64.75
5 19 如花 68.0 64.75
5 20 似玉 78.0 64.75

18.均值、方差、标准差、幂运算、对数
参考：https://blog.csdn.net/weixin_30920513/article/details/101985781
均值–avg()
方差–variance()
标准差–variance()
幂运算–pow(expression, y )或 power(expression, y )
expression是精确数字或近似数字数据类型类别的表达式（bit 数据类型除外）
对数：
log(X) --默认底数为e。如log(2.718)–0.999896315728952
log(B,X) --底数为指定值B。如log(10,100) --2

19.开窗函数over()
说明：over必须与聚合函数或排序函数一起使用
聚合函数为：
sum(),max(),min(),count(),avg()
排序函数为：
rank(),row_number(),dense_rank()

select
    pt_d
    ,,date_format(pt_d,'MM')  as month
    ,pay_amt
    ,sum(pay_amt)over(partition by date_format(pt_d,'MM')) as month_amt
from
(
    select '2022-05-20' as pt_d,100 as pay_amt
    union all
    select '2022-05-21' as pt_d,200 as pay_amt
    union all
    select '2022-05-22' as pt_d,300 as pay_amt
    union all
    select '2022-06-20' as pt_d,400 as pay_amt
    union all
    select '2022-06-21' as pt_d,500 as pay_amt
    union all
    select '2022-06-22' as pt_d,600 as pay_amt
)t1
+----------+-----+-------+---------+
|      pt_d|month|pay_amt|month_amt|
+----------+-----+-------+---------+
|2022-05-20|   05|    100|      600|
|2022-05-21|   05|    200|      600|
|2022-05-22|   05|    300|      600|
|2022-06-20|   06|    400|     1500|
|2022-06-21|   06|    500|     1500|
|2022-06-22|   06|    600|     1500|
+----------+-----+-------+---------+