hive sql -- 看完就会了！

前言

** 想与你们共同学习，想与你们一起努力，想得到你们的支持与喜欢~**

hivesql与sql区别：

1、mysql采用了sql语言，hive采用了类sql的查询语言hql。

2、hive不支持数据的改写和添加，mysql支持。

3、hive无索引，mysql有索引。

4、hive底层是marreduce，mysql底层是执行引擎。

5、hive存储的数据量超级大，而mysql只是存储一些少量的业务数据。

6、hive是把数据存储到hdfs，而mysql数据存储在自己的系统中。

7、hive数据格式可以用户自定义，mysql有自己的系统定义格式。

8、hive支持按行分割，按字段分割，如按‘，’分割：lateral view explode(split( , ','))

9、Hive适用于大数据批处理，通常需要一些时间来处理和分析大量数据。MySQL通常适用于在线事务处理(OLTP)，需要快速处理和检索少量数据。

** SQL的书写顺序：**

select ... from ... where ... group by ... having ... order by ... limit

SQL关键词执行顺序：

from > where条件 > group by > having条件 > select >order by>limit

【注意】sql一旦出现group by，后续的关键词能够操作字段只有(分组依据字段，组函数处理结果)

1.基本查询

# 1. 各个数据类型的字段访问(array、map、struct)
select name,salary,hobbies[1],cards['中国银行'],addr.city from t_person;
# 2. 条件查询：= != >= <=
select * from t_person where addr.city='郑州';
# 3. and 、 or 、between and
select * from t_person where salary>5000 and array_contains(hobbies,'抽烟');
# 4. order by[底层会启动mapreduce进行排序]
select * from t_person order by salary desc;
# 5. limit(hive没有起始下标)
select * from t_person sort by salary desc limit 5; 
# 6. 去重
select distinct addr.city  from t_person;
select distinct(addr.city) from t_person;

2.表连接

-- 创建部门表
create table dept(
deptno int,
dname string,
loc string
)
row format delimited fields terminated by '\t';
-- 创建员工表
create table  emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';
-- 导入数据
load data local inpath '/opt/data/dept.txt' into table dept;
load data local inpath '/opt/data/emp.txt' into table emp;

dept.txt包含数据
10      ACCOUNTING      NEW YORK
20      RESEARCH        DALLAS
30      SALES   CHICAGO
40      OPERATIONS      BOSTON

emp.txt包含数据
7369    SMITH    CLERK    7902    1980-12-17    800.00        20
7499    ALLEN    SALESMAN    7698    1981-02-20    1600.00    300.00    30
7521    WARD    SALESMAN    7698    1981-02-22    1250.00    500.00    30
7566    JONES    MANAGER    7839    1981-04-02    2975.00        20
7654    MARTIN    SALESMAN    7698    1981-09-28    1250.00    1400.00    30
7698    BLAKE    MANAGER    7839    1981-05-01    2850.00        30
7782    CLARK    MANAGER    7839    1981-06-09    2450.00        10
7788    SCOTT    ANALYST    7566    1987-04-19    3000.00        20
7839    KING    PRESIDENT        1981-11-17    5000.00        10
7844    TURNER    SALESMAN    7698    1981-09-08    1500.00    0.00    30
7876    ADAMS    CLERK    7788    1987-05-23    1100.00        20
7900    JAMES    CLERK    7698    1981-12-03    950.00        30
7902    FORD    ANALYST    7566    1981-12-03    3000.00        20
7934    MILLER    CLERK    7782    1982-01-23    1300.00        10
7935    张三    CLERK    7782    1986-01-23    1300.00        

• 左连接

select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

• 内连接

select e.empno, e.ename, d.deptno from emp e inner join dept d on e.deptno = d.deptno;

• 右连接

select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

• 全连接

select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

3.分组

强调：一旦使用group by之后，则select后边可以写： 1.分组的字段 2.聚合函数 3.常量值

1. group by(查看各个城市的均薪)
select addr.city,avg(salary) from t_person group by addr.city;
2. having(查看平均工资超过5000的城市和均薪)
select addr.city,avg(salary) from t_person group by addr.city having avg(salary)>5000;

4.单行函数

-- 查看系统自带的函数
show functions;
-- 显示函数的用法
desc function upper;
-- 详细显示自带的函数的用法
desc function extended upper;

函数：单行函数(UDF)、聚合函数(UDAF)、炸裂函数（UDTF）

数值函数

1. round: 四舍五入
select round(3.1414,2)  -- 输出3.14

2. ceil : 向上取整
select ceil(3.1415)     -- 输出4

3. floor: 向下取整
select floor(3.1415)    -- 输出3

4. cast(expr as <type>) : 类型转换  [重点]
select cast('1' as bigint)   -- Integer.parseInt('1')

字符串函数

1. length：字符串长度
select length('abcde')   -- 输出5

2. concat：字符串连接
select concat('张三','李四','王五')   -- 输出张三李四王五

3. concat_ws：带分隔符的字符串连接
select concat_ws('-','李四','王五','赵六')   -- 输出李四-王五-赵六
select name,hobbies,concat_ws('-',hobbies) from t_person  -- 将数组拼接成一个字符串

concat和concat_ws有什么区别?
1. 功能描述他们的区别
2. concat拼接的字符串中有null，在最终拼接结果是空，concat_ws拼接字符串有空，不影响拼接结果，去除空

4. substr / substring：字符串截取
select substring('张无忌',1,1)  -- 输出张，参数是下标，从1开始，参数2代表截取长度

5. upper：字符串转大写
select UPPER('abc')   -- 输出ABC

6. lower：字符串转小写
select lower('AbC')    --输出abc

7. trim / ltrim / rtrim：取出空格/左空格/右空格
select length(trim(' abc '))    -- 输出: 3

8. split：分割字符串
select split('中国-美国-日本','-')   -- split函数的返回值是一个数组

9. replace：替换字符串
select replace('蔡徐坤喜欢打篮球','篮球','足球')  -- 替换指定字符串
select replace('蔡徐坤 喜欢 打篮球',' ','')      -- 去除空格

10. regexp_replace：正则替换
select regexp_replace('abc-123-ab-456-cd', '[0-9]+', '*');

11. get_json_object：从路径中提取json对象
select get_json_object('{"name":"张三","age":20}','$.name')
select get_json_object('{"name":"张三","age":20,"hobbies":["唱","跳"]}','$.hobbies[0]')

json是一种存储(传输)数据的格式，存储方式比较简单，所以受到it行业的广泛推广
1. xml和json对比
xml存储  <student>
                 <name>zs</name>
                 <age>20</age>
         </student>
json存储 {"name":"zs","age":20}

2. json的语法格式就两种
1. json对象（大括号） {"属性名":属性值,"属性名2":属性值}
   例如：{"name":"zs","age":20}
2. json数组 (中括号)  [值1,值2,值3]
   例如：["张三","李四","王五"]

存储3个人的信息(姓名+年龄+爱好)
[{"name":"zs","age":20,"hobbies":["唱","跳","Rap"]},
 {"name":"李四","age":22,"hobbies":["唱","跳","Rap"]},
 {"name":"王五","age":23,"hobbies":["抽烟","喝酒","烫头"]}]
 

流程控制函数

1. case when：条件判断  【重要】
-- 大于8000，输出缴税交20%
-- 大于5000，输出缴税交10%
-- 否则不用缴税
select  name,salary,
        case when salary >= 8000 then  salary*0.2
             when salary >= 5000 then  salary*0.1
             else 0 end  x1
from t_person 

2. if：条件判断，类似Java中的三目运算符 条件 ? 值1：值2
语法：if(条件,值1,值2)
select name,sex,if(sex=0,'女',if(sex = 1, '男','人妖')) from t_person

3. nvl(值1,值2): 如果值1不是空输出值1，否则输出值2
select nvl('a','b')   -- a
select nvl(null,'b')  -- b

select ename,sal+nvl(comm,0) from emp

日期时间函数

1.datediff：日期比较  【重要】
select datediff('2023-10-03','2023-10-01') -- 输出2

2.date_add / date_sub：日期增加/日期减少 【重要】
select date_add('2023-10-03',1)   -- 输出2023-10-04
select date_sub('2023-10-03',1)   -- 输出2023-10-02

select add_months('2023-12-03',1)  -- 2024-01-03
select cast('2023-10-03' as date) + interval 1 year  -- 2024-10-03

3.unix_timestamp：获取当前 UNIX 时间戳函数
select unix_timestamp() -- 获取当前系统时间的时间戳，单位是秒
-- 指定时间转换的时间戳差8个时区，可以通过下面函数处理
SELECT unix_timestamp(to_utc_timestamp ('2022-07-26 09:22:39','GMT+8'),'yyyy-MM-dd HH:mm:ss');

4.from_unixtime：转化unix时间戳到当前市区的时间
select from_unixtime(1700015310)         -- 返回的是UTC（国际协调时间）时间，和中国相差了8个小时
select from_unixtime(cast(1700015310 as bigint) + 28800)

5.current_date：当前日期
select current_date();  -- 例如输出 2023-11-15

6.current_timestamp：当前日期加时间，精确到毫秒
select current_timestamp();  -- 例如输出 2024-05-17 16:25:07.544000000

集合函数

1. size：集合中元素的个数
select name,hobbies,size(hobbies) from t_person 

2. map：创建map集合
select map('name','zs','age',20)

3. map_keys：返回map中的key
select map_keys(cards) from t_person 

4. array_contains：判断array中是否包含某个元素
select name,hobbies,array_contains(hobbies,'抽烟') from t_person 

5.聚合函数

# 聚合函数
概念：
max、min、sum、avg、count、collect_list、collect_set等。

select max(salary) from t_person where addr.city='北京';
select count(id) from t_person;

**

collect_list、collect_set：

**

--## 表（电影观看日志）
create table t_visit_video (
    username string,
    video_name string,
    video_date date
)row format delimited fields terminated by ',';
--## 数据：豆瓣观影日志数据。(用户观影日志数据  按照天存放 1天一个日志文件)
张三,大唐双龙传,2020-03-21
李四,天下无贼,2020-03-21
张三,神探狄仁杰,2020-03-21
李四,霸王别姬,2020-03-21
李四,霸王别姬,2020-03-21
王五,机器人总动员,2020-03-21
王五,放牛班的春天,2020-03-21
王五,盗梦空间,2020-03-21

# collect_list
作用：对分组后的，每个组的某个列的值进行收集汇总。
语法：select collect_list(列) from 表 group by 分组列;

select username,collect_list(video_name) from t_visit_video group by username;

# collect_set
作用：对分组后的，每个组的某个列的值进行收集汇总，并去掉重复值。
语法：select collect_set(列) from 表 group by 分组列;

select username,collect_set(video_name) from t_visit_video group by username;

6.炸裂函数

-- 查询所有的爱好，
select explode(hobbies) as hobby from t_person

# lateral view 
定义：lateral view通常与UDTF配合使用。lateral view可以将UDTF应用到源表的每行数据，将每行数据转换为一行或多行，
      并将源表中每行的输出结果与该行连接起来，形成一个虚拟表。

-- 为指定表，的边缘拼接一个列。(类似表连接)
-- lateral view：为表的拼接一个列(炸裂结果)
-- 语法：from 表 lateral view explode(数组字段) 别名 as 字段名;

-- 查看id，name，爱好。一个爱好一条信息。
select id,name,hobby
from t_person lateral view explode(hobbies) t_hobby as hobby

-- 炸裂函数的应用
-- 统计各个爱好的人数
select hobby,count( * )
from t_person lateral view explode(hobbies) t_hobby as hobby
group by hobby;

posexplode相比较explode会多出一列索引值
select name,`index`,hobby from t_person lateral view posexplode(hobbies) t2 as `index`,hobby

7.子查询 (嵌套查询)

-- 子查询就是将一个SQL的结果视为一个虚拟的表或者一个值
-- 在hive中，子查询可以出现在三个地方，一个是from后面，一个是where后面，还有select
-- 需要注意的是，子查询在放入到select后面时，尽可能的输出单值（单列单行）

-- 子查询结果是一行一列
select * from t_person where salary > (
  select avg(salary) from t_person 
)

-- 子查询结果是多行一列
select distinct name
from(
 select * from t_person lateral view explode(hobbies) t2 as hobby 
)t1 where t1.hobby in (
  select explode(hobbies) from t_person where name = '赵6'
) and name <> '赵6'

-- 子查询结果是多行多列，作为表使用
-- 统计有哪些爱好，并去重。
select distinct t.hobby from 
(select explode(hobbies) as hobby from t_person ) t

8.排序

# 全局排序 
语法：select * from 表 order by 字段 asc|desc;

-- 按照薪资降序排序 
select * from t_person order by salary desc;

# 局部排序(分区排序)
概念：启动多个reduceTask，对数据进行排序(预排序)，局部有序。
     局部排序关键词 sort by
        默认reducetask个数只有1个，所有分区也只有一个。所以默认和全排序效果一样。
语法：select * from 表 distribute by 分区字段 sort by 字段 asc|desc;

-- 1. 开启reduce个数
-- 设置reduce个数
set mapreduce.job.reduces = 3;
-- 查看reduce个数
 set mapreduce.job.reduces;
-- 2. 使用sort by排序 +distribute by 指定分区列。
select * from t_person distribute by addr.city sort by salary desc;
-- 3.可以将查询结果写入本地磁盘，用于测试sort by的效果
insert overwrite local directory '/opt/data/sortby'
select * from t_person distribute by addr.city sort by salary desc;

9.开窗函数(分析函数、窗口函数)

开窗函数：在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在 2003 年 ISO SQL 标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，MySQL8.0支持以及hive也支持。 开窗函数简介:与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。在 ISO SQL 规定了这样的函数为开窗函数，在 Oracle 中则被称为分析函数。

1.准备数据

Tom,BeiJing,20,3000
Tim,ChengDu,21,4000
Jim,BeiJing,22,3500
Lily,London,21,2000
John,NewYork,22,1000
YaoMing,BeiJing,20,3000
Swing,London,22,2000
Guo,NewYork,20,2800
YuQian,BeiJing,24,8000
Ketty,London,25,8500
Kitty,ChengDu,25,3000
Merry,BeiJing,23,3500
Smith,ChengDu,30,3000
Bill,BeiJing,25,2000
Jerry,NewYork,24,3300

2.建表/导入数据

create table t_person2 (
    fname string,
    fcity string,
    fage int,
    fsalary int
)row format delimited
fields terminated by ','

load data local inpath '/root/kaichuang.txt' into table t_person2 ;

3.开窗函数的语法

开窗函数格式： 函数名([列]) OVER([选项])
OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。

如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

PARTITION BY 子句：
开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响

ORDER BY子句：
开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，而且有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算

排序(排名、序号)开窗函数

RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

select 
 fname,fcity,fage,fsalary,
 ROW_NUMBER() over(order by fsalary desc) x1,
 Rank() over(order by fsalary desc) x2,
 DENSE_RANK () over(order by fsalary desc) x3
from t_person2 ;

聚合开窗函数

-- 聚合函数(列)  over(partition by 列名 order by 列名)
select 
 fname,fcity,fage,fsalary,
 max(fsalary) over(partition by fage order by fsalary desc) x1
from t_person2 ;

create table t_sales(
    name        string,
    mt          int,
    sales_num   int  
);

insert into t_sales 
values ('苹果旗舰店',1,500),('苹果旗舰店',2,100),('苹果旗舰店',3,400),('苹果旗舰店',4,500),('苹果旗舰店',5,100),
       ('华为旗舰店',1,800),('华为旗舰店',2,200),('华为旗舰店',3,400),('华为旗舰店',4,300),('华为旗舰店',5,200);

insert into t_sales 
values ('苹果旗舰店',7,500),('苹果旗舰店',6,100)      
       
-- 需求1：统计每个店铺的总销售额
select *,sum(sales_num) over(partition by shop) x1
from t_sales 

-- 需求2：统计每个店铺的累计销售额
select *,sum(sales_num) over(partition by shop order by mt) x1
from t_sales 

其他开窗函数

① lag(字段名,n,0) -- 移位开窗函数，表示返回向上第n行指定字段对应数据。其中n代表向上偏移n行，0代表若偏移行数超出表范围则返回0也可以改成其他值，若不写则默认null
create table t_student(
   name  string,
   mt    int,
   score int
)

insert into t_student values('张三',1,100),('张三',2,15),('张三',3,85),('张三',4,95),
                            ('李四',1,60),('李四',2,99),('李四',3,75),('李四',4,34)

-- 语法 lag(列名) 向上一行，找到指定列的值，如果本身就是第一行，则返回null
--    lag(列名,n,默认值)  向上n行，找到指定列的值，如果本身就是第一行，则返回默认值
select name,mt,score,lag(score) over(partition by name order by mt)
from t_student                 

select name,mt,score,lag(score,1,score) over(partition by name order by mt)
from t_student    

-- 应用场景  计算每个学员考试成绩的环比  (当前月成绩-上个月成绩)/上个月成绩*100%
select name,mt,score,round((score-x1)/x1*100,2) x2
from(
        select name,mt,score,lag(score) over(partition by name order by mt) x1
        from t_student   
)t1 where x1 is not null 

② lead(字段名,n,0) -- 移位开窗函数，与lag()相反，表示返回向下第n行指定字段对应数据
语法：lead(字段名)、lead(字段名,行号,默认值)

③ first_value(列名) -- 取分组内排序后，截止到当前行，第一个值
select name,mt,score,first_value(mt) over (partition by name order by mt) x1
from t_student;

④ last_value(列名) -- 取分组内排序后，截止到当前行，最后一个值
select name,mt,score,last_value(mt) over (partition by name order by mt) x1
from t_student;