一、内置函数
> 内置函数时hive中自带的函数方法,用来对不同类型的字段数据进行操作
>
> 字符串,数值,条件判断
>
> 字符串方法
>
> ```sql
> length
> concat
> concat_ws
> substr
> split
> regexp_replace
> ```
>
> ```sql
> %
> round()
> ceil()
> floor()
> ```
>
> ```sql
> if()
>
> case
> when 条件1 then 结果
> when 条件2 then 结果
> when 条件3 then 结果
> else
> 前面条件都不成返回的结果
> end 字段名称
> ```
>
> ```sql
> -- 查看hive中的所有内置函数
> show functions;
> -- 查看函数的具体用户
> desc function extended 函数名;
> ```
### 1-1 日期类型操作
```sql
-- 获取当前日期
select `current_date`();
-- 获取当前日期时间
select `current_timestamp`();
-- 获取unix时间(时间戳) 从1970年1月1号0时0分0秒 到现在过去了多少秒
select unix_timestamp();
-- unix时间 和日期时间的转化
-- 日期时间转为unix
select unix_timestamp('2023-10-01 15:30:28');
-- 将unix时间转为日期时间
select from_unixtime(12390886789);
-- 年月日的取值
select year('2023-10-01 15:30:28');
select month('2023-10-01 15:30:28');
select day('2023-10-01 15:30:28');
select dayofmonth('2023-10-12 15:30:28');
select dayofweek('2023-10-12 15:30:28');
select hour('2023-10-12 15:30:28');
select minute('2023-10-12 15:30:28');
select second('2023-10-12 15:30:28');
-- 时间加减
select date_add('2023-10-12 15:30:28',5);
select date_add('2023-10-12 15:30:28',-5);
-- 比较时间相差多少天
select datediff(`current_date`(),'2023-10-12');
1-2 类型转化
-- 字段类型不符合计算需求,可以进行类型转化
-- 隐式转化 hive会自动判断进行转化数据然后计算
select '123'+'456';
-- 手动指定转化
select cast('123' as int) + cast('456' as int);
select * from itcast.tb_hero;
desc itcast.tb_hero;
-- 转化只是在计算时进行,并不会改变字段本身类型
select cast(blood as bigint) from itcast.tb_hero;
1-3 字符串数据转json,array,map操作
- josn字符串操作
- 数据是一个 “{key:value}” 格式
- 使用方法取值value
create table tb_order_detail(
json_field string
);
select * from tb_order_detail;
-- 对字段中的json字符串数据进行取值,按照key取value值
-- 方法一 get_json_object 每次只能取一个字段数据 ,可以向下一直取值
select
get_json_object(json_field,'$.orderid') as orderid,
get_json_object(json_field,'$.total_price') as total_price,
get_json_object(json_field,'$.total_num') as total_num,
get_json_object(json_field,'$.goods') as goods,
get_json_object(json_field,'$.goods[0]') as good1,
get_json_object(json_field,'$.goods[0].name') as good1_name,
get_json_object(json_field,'$.goods[1]') as good2
from tb_order_detail;
-- json_tuple 一次取多个字段值,不能对嵌套数据往下取值
select json_tuple(json_field,'orderid','total_price','total_num','goods') as(orderid,total_price,total_num,goods) from tb_order_detail
将字符串数据切割转为数组数据
create table tb_user(
id int,
name string,
hobby string
)row format delimited fields terminated by ',';
select id,name,split(hobby,'-') from tb_user;
将字符串数据切割转为map数据
-- 使用map方法
select `map`('name','张三','age',29);
create table tb_hero(
id int,
name string,
blood int,
skin string
)row format delimited fields terminated by ',';
-- 西部大镖客:288-大圣娶亲:888-全息碎片:0-至尊宝:888-地狱火:1688 --> {'西部大镖客':288,'大圣娶亲':88}
select id,name,blood,map(split(split(skin,'-')[0],":")[0], cast(split(split(skin,'-')[0],":")[1] as int),split(split(skin,'-')[1],":")[0],cast(split(split(skin,'-')[1],":")[1] as int))from tb_hero;
mysql中的内置函数操作
select concat(name,sex) from member;
select concat_ws(',',name,sex) from member;
select substr(s_birth,1,4) from student;
select replace(s_birth,'-','/') from student;
select round(3.1421,2);
select round(3.123);
select pow(2,3);
select current_timestamp;
select current_date;
select unix_timestamp();
select unix_timestamp('2023-10-10 10:10:10');
select from_unixtime(127381923);
select date_add('2023-10-10 10:10:10',interval 1 day);
select date_add('2023-10-10 10:10:10',interval 1 month );
select date_add('2023-10-10 10:10:10',interval 1 year );
select date_add('2023-10-10 10:10:10',interval -1 year );
select date_add('2023-10-10 10:10:10',interval -1 hour );
select timestampdiff(year ,'2020-02-02','2023-02-02');
select timestampdiff(month ,'2020-02-02','2023-02-02');
select timestampdiff(day ,'2020-02-02','2023-02-02');
select timestampdiff(hour ,'2020-02-02 16:23:12','2023-02-02 15:12:12');
select if(s_sex='男',1,2),s_sex from student;
select *,
case
when year(s_birth) between 1980 and 1989 then '80后'
when year(s_birth) between 1990 and 1999 then '90后'
end
from student;
二、DQL的查询计算(掌握)
对表进行查询计算
select 字段 from 表;
select 字段1,字段2,字段3,常量值,内置函数计算 from tb
2-1 单表查询计算
where 的条件过滤
select 字段1,字段2,字段3,常量值,内置函数计算 from tb where 过滤条件
过滤条件,条件成立的将对的行数据返回
- 比较大小
- 字段 = 数值 判断字段和数值是否相等
- 字段 > 数值
- 字段 < 数值
- 字段 >= 数值
- 字段 <= 数值
- 字段 != 数值
-- 大小比较
-- 年龄大于19岁
select * from tb_stu where age >19;
-- 查询性别为女性的学生信息
select * from tb_stu where gender='女';
-- 查询学科不是IS的学生信息
select * from tb_stu where cls !='IS';
- 判断空值
- 字段 is null 字段为空
- 字段 is not null
-- 空值判断
insert into tb_stu values(9023,null,'男',20,'MA');
select * from tb_stu where name is not null;
select * from tb_stu where name is null;
select * from tb_stu where name !=''; -- 空字符过滤是会将null值一起过滤掉
select * from tb_stu where name =''; -- 相等判断是,空字符是不会过滤出null值的
- 范围判断
- 字段 between 数值1 and 数值2
- 字段 >=数值 and 字段 <=数值
- 字段 in (数值1,数值2…) 字段的值等于任意一个值就返回结果
- 字段 between 数值1 and 数值2
-- 范围判断
select * from tb_stu where age between 20 and 25;
select * from tb_stu where age in(19,22);
select * from tb_stu where age not in(19,22);
- 模糊查询
- 字段 like ‘% _ 数据’ % 可以匹配任意多个 _ 匹配任意一个字符
- 字段 rlink ‘正则表达式’
create table tb_stu2(
id int,
name string,
gender string,
age int,
cls string,
email string
)row format delimited fields terminated by ',';
select * from tb_stu2;
-- like的模糊查询
-- 查询姓名为刘的学生
select * from tb_stu where name like '刘%'; -- % 代表任意多个字符
-- 查询姓名为刘的学生 名字个数时2个字的
select * from tb_stu where name like '刘_';
select * from tb_stu where name like '刘__'; -- 查询三个字的
-- rlike 的正则表达式
-- 表的是就是通过不同的符号来表示不同的数据进行匹配
-- \\d 匹配数据的表达式 \\w 匹配字符字母 \\s 匹配空格
select * from tb_stu2;
-- ^ 表是什么开头
select * from tb_stu2 where email rlike '^\\d'; -- 表是以数字开头
select * from tb_stu2 where email rlike '^\\w';
select * from tb_stu2 where email rlike '^\\S';
-- ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$
select email,split(email,'@')[1] from tb_stu2;
select email,split(split(email,'@')[1],'\\.')[0] from tb_stu2;
- 与或非
- 条件1 and 条件2 and 条件3 … 多个条件都成立,返回对应的行数据
- 条件1 or 条件2 or 条件3 … 多个条件满足任意一个,返回对应的行数据
-- 与 多个条件都成立
select * from tb_stu;
-- 查询性别为男性,学科是is的
select * from tb_stu where gender='男' and cls = 'IS';
-- 查询性别为男性或学科是is的
select * from tb_stu where gender='男' or cls = 'IS';
聚合计算 sum,count
select * from tb_stu;
select sum(age) from tb_stu2;
select count(*) from tb_stu where name is not null;
select avg(age) from tb_stu2;
select max(age) from tb_stu;
select min(age) from tb_stu;
分组聚合 group by
select sum(age) from tb_stu group by gender;
select sum(age),gender from tb_stu group by gender;
注意分组后,select 中不能出现非分组字段
分组后过滤 having
select sum(age),gender from tb_stu group by gender having sum(age)> 200;
排序
order by 全局排序
select * from tb_stu order by age; -- 默认是升序 从小到大
select * from tb_stu order by age desc ; -- 降序 从大到小
分页 limit
-- 分页
select * from tb_stu limit 5;
select * from tb_stu limit 10,5; -- 页数 m 每页数量是n (m-1)*n,n
2-2 多表关联查询
join的列关联
- 内关联
- 找关联字段相同的数据
- 左关联
- 展示保留左边表的所有数据,右边表有相同数据显示,没有相同数据则为null
- 右关联
- 展示保留右边表的所有数据,左边表有相同数据显示,没有相同数据则为null
-- table1: 员工表
CREATE TABLE employee(
id int,
name string,
deg string,
salary int,
dept string
) row format delimited
fields terminated by ',';
-- table2:员工家庭住址信息表
CREATE TABLE employee_address (
id int,
hno string,
street string,
city string
) row format delimited
fields terminated by ',';
-- table3:员工联系方式信息表
CREATE TABLE employee_connection (
id int,
phno string,
email string
) row format delimited
fields terminated by ',';
-- on 当成where使用,进行条件顾虑
select * from employee t1 join employee_address t2 on t1.id = t2.id and salary> 30000;
select * from employee t1 left join employee_address t2 on t1.id = t2.id;
select * from employee t1 right join employee_address t2 on t1.id = t2.id;
-- 实现内关联的效果
select * from employee,employee_address where employee.id = employee_address.id;
union的行关联
将select查询计算后的结果表合并
-- union合并
select 'tb_stu',count(*) from tb_stu where name is not null
union
select 'tb_stu2', count(*) from tb_stu2 where name is not null;
-- 保留重复数据
select id,name from tb_stu
union all
select id,name from tb_stu2;
三、窗口聚合
默认没有窗口函数进行计算时全表数据获取计算
根据指定的窗口范围计算数据,将计算结果单独呈现一列展示,不会因为聚合改变行数
聚合使用,取值函数,排序函数 over(partition by 分组字段 order by 排序字段 rows between 起始行 and 结束行)
rows 指定计算的行范围
3-1 聚合窗口
-- 按照性别分组统计年龄和
select sid,sname,age,gander,province,tuition,sum(age) over(partition by gander) from stu;
select sid,sname,age,gander,province,tuition,sum(age) over(partition by province,gander) from stu;
-- order by 排序后会将前面的数据进行累加
select sid,sname,age,gander,province,tuition,sum(tuition) over(partition by gander order by age) from stu;
select sid,sname,age,gander,province,tuition,avg(tuition) over(partition by gander order by age) from stu
### 3-2 取值窗口
```sql
-- 向上一行取值
select empno,ename,lag(ename) over() from emp;
-- 向上两行行取值
select empno,ename,lag(ename,2) over() from emp;
-- 去不到值给默认值
select empno,ename,lag(ename,2,'itcast') over() from emp;
-- 向下一行取值
select empno,ename,lead(ename) over() from emp;
-- 向下两行行取值
select empno,ename,lead(ename,2) over() from emp;
-- 去不到值给默认值
select empno,ename,lead(ename,2,'itcast') over() from emp;
-- 分组后只进行组内查找
select *,lag(ename) over(partition by job) from emp;
-- 取第一个值
select *,first_value(ename)over(partition by job order by sal desc ) from emp;
3-3 排序窗口
按照指定字段排序后生成序号
-- 排序
select *,
rank() over (order by sal) as rnk, -- 如果有并列生成序号是不连续的
dense_rank() over (order by sal) as dernk, -- 生成连续序号
row_number() over (order by sal) as rw -- 生成行号
from emp;
-- 查找薪资前三
select * from (
select *,
dense_rank() over (order by sal desc ) as dernk -- 生成连续序号
from emp) tb1
where dernk <=3;