【Hive笔记】6——Hive查询,分桶,常用函数,窗口函数

11 篇文章 1 订阅
查询语句语法
SELECT [ALL | DISTINCT] select_expr, select_expr,...  
FROM table_reference 
[WHERE where_condition] 
[GROUP BY col_list] 
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]
基本查询

创建表:create table stu(id string,name string,sex string) row format delimited fields terminated by '\t' stored as textfile;

导入数据:load data local inpath '/home/lzx/file/demo.txt' into table stu;

查看数据:
在这里插入图片描述

1.全表和特定列查询
  1. 全表查询
    select * from stu;
  2. 选择特定列查询
    select id from stu;
    注意:
    SQL语言大小写不敏感
    SQL可以写在一行或者多行
    关键字不能被缩写也不能分行
    各子句一般要分行写
    使用缩进提高语句的可读性
2.列别名

列别名可以使用as 也可以紧跟列名使用
select name as username from stu;

在这里插入图片描述
在这里插入图片描述

3. 算数运算符
运算符描述
A+BA和B 相加
A-BA减去B
A*BA和B 相乘
A/BA除以B
A%BA对B取余
A&BA和B按位取与
AB
A^BA和B按位取异或
~AA按位取反

语句:select id+3,id,name from stu;
在这里插入图片描述

4. 常用函数
  1. 求总行数
    select count(1) numbers from stu;
  2. 求最大值
    select max(id) from stu;
  3. 求最小值
    select min(id) from stu;
  4. 求和
    select sum(id) from stu
  5. 求平均值
    select avg(id) avgs from stu;
5.Limit语句

使用limit语句用于限制返回的行数
select * from stu limit 15;

Where 语句

使用where子句可以将不满足条件的行过滤掉并且where子句紧随from子句

查找id大于3的学生
select * from stu where id >2;

1.比较运算符(Between/In/Is Null)

比较运算符表格:

操作符支持的数据类型描述
A=B基本数据类型如果A等于B则返回TRUE,反之返回FALSE
A<=>B基本数据类型如果A和B都为NULL,则返回TRUE,其他的和等号(=)操作符的结果一致,如果任一为NULL则结果为NULL
A<>B, A!=B基本数据类型A或者B为NULL则返回NULL;如果A不等于B,则返回TRUE,反之返回FALSE
A<B基本数据类型A或者B为NULL,则返回NULL;如果A小于B,则返回TRUE,反之返回FALSE
A<=B基本数据类型A或者B为NULL,则返回NULL;如果A小于等于B,则返回TRUE,反之返回FALSE
A>B基本数据类型A或者B为NULL,则返回NULL;如果A大于B,则返回TRUE,反之返回FALSE
A>=B基本数据类型A或者B为NULL,则返回NULL;如果A大于等于B,则返回TRUE,反之返回FALSE
A [NOT] BETWEEN B AND C基本数据类型如果A,B或者C任一为NULL,则结果为NULL。如果A的值大于等于B而且小于或等于C,则结果为TRUE,反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL所有数据类型如果A等于NULL,则返回TRUE,反之返回FALSE
A IS NOT NULL所有数据类型如果A不等于NULL,则返回TRUE,反之返回FALSE
IN(数值1, 数值2)所有数据类型使用 IN运算显示列表中的值
A [NOT] LIKE BSTRING 类型B是一个SQL下的简单正则表达式,如果A与其匹配的话,则返回TRUE;反之返回FALSE。B的表达式说明如下:‘x%’表示A必须以字母‘x’开头,‘%x’表示A必须以字母’x’结尾,而‘%x%’表示A包含有字母’x’,可以位于开头,结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP BSTRING 类型B是一个正则表达式,如果A与其匹配,则返回TRUE;反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的,因为正则也依据其中的规则。例如,正则表达式必须和整个字符串A相匹配,而不是只需与其字符串匹配。
2.LIKE 和 RLIKE

like和我们经常使用的数据库的like是一样的,%代表零个或者多个字符(任意个字符)_代表一个字符

RLIKE 子句Hive中这个功能的一个扩展,其可以通过JAVA的正则表达式这个更强大的语言来指定匹配条件。

查找以l开头的学生
select * from stu where name like 'l%';
在这里插入图片描述
查找含有ao的学生
select * from stu where name rlike '[ao]';

在这里插入图片描述

3.逻辑运算符
操作符含义
AND逻辑并
OR逻辑或
NOT逻辑否

查询id大于2 并且是男的学生:
select * from stu where id > 2 and sex = '男';
在这里插入图片描述

分组
1.Group by

Group by 语句通常会和聚合函数一起使用,按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作。

找到性别中最大id的学生:
select sex,max(id) from stu group by sex;
在这里插入图片描述

2.Having 语句

having 与 where 不同点:

  1. where针对表中的列发挥作用,查询数据having针对查询结果中的列发挥作用,筛选数据。
  2. where后面不能写分组函数,而having后面可以使用分组函数
  3. having 只用于group by 分组统计语句。

示例:
按性别分组并求出最大id大于5的数据:
select max(id) as maxid,sex from stu group by sex having maxid >5;
在这里插入图片描述

Join语句

创建dept表和emp表,并导入数据。
建表语句:

create external table if not exists default.emp(
	empno int,
	ename string,
	job string,
	mgr int,
	hiredate string, 
	sal double, 
	comm double,
	deptno int)
row format delimited fields terminated by '\t';

create external table if not exists default.dept(
	deptno int,
	dname string,
	loc int)
row format delimited fields terminated by '\t';

导入数据后的查询:

hive (default)> select * from dept;
OK
dept.deptno	dept.dname	dept.loc
10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700
Time taken: 2.523 seconds, Fetched: 4 row(s)
hive (default)> select * from emp;
OK
emp.empno	emp.ename	emp.job	emp.mgr	emp.hiredate	emp.sal	emp.comm	emp.deptno
7369	SMITH	CLERK	7902	1980-12-17	800.0	NULL	20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.0	300.0	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.0	500.0	30
7566	JONES	MANAGER	7839	1981-4-2	2975.0	NULL	20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.0	1400.0	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.0	NULL	30
7782	CLARK	MANAGER	7839	1981-6-9	2450.0	NULL	10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.0	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.0	NULL	10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.0	0.0	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.0	NULL	20
7900	JAMES	CLERK	7698	1981-12-3	950.0	NULL	30
7902	FORD	ANALYST	7566	1981-12-3	3000.0	NULL	20
7934	MILLER	CLERK	7782	1982-1-23	1300.0	NULL	10
Time taken: 0.618 seconds, Fetched: 14 row(s)

1. 等值Join(或者叫内连接)

Hive支持通常的SQL JOIN语句,但是只支持等值连接,不支持非等值连接。

  1. 根据员工表和部门表的部门编号相等,查询员工编号,员工名称和部门名称
    select e.empno,e.ename,d.deptno from emp e join dept d on e.deptno = d.deptno;
    在这里插入图片描述
    在hive中也可以使用表的别名,和平常使用sql的别名是一样的。
2.左外连接 left join

左外连接:JOIN操作符左边表中符号Where子句的所有记录将会被返回
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

3.右外连接 right join

右外连接:JOIN操作符右边表中符合where子句的所有记录将会被返回
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

4. 满外连接 full join

满外连接:将会返回所有表中符合where语句的所有记录。如果任一表的指定字段没有符合条件的值,呢么就会使用NULL替代。
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

如果用到多个表关联,连接n个表,至少需要n-1个连接条件。
。例如连接三个表,至少需要2个连接条件。

大多数情况下,Hive会对每对JOIN连接对象启动一个MapReduce任务。如果有a,b,c三个表,呢么会首先启动一个MapReduce job对表a和表b进行连接操作,然后会再启动一个MapReduce job将第一个MapReduce job的输出和表c进行连接操作。
注意:为什么不是表b和表c先进行连接操作呢?这是因为Hive总是按照从左到右的顺序执行的。

5. 笛卡尔积

笛卡尔积会在下面条件下产生:

  1. 省略连接条件
  2. 连接条件无效
  3. 所有表的所有行互相连接
6.连接谓词中不支持OR

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno or e.ename=d.ename;
这种是错误的。

排序

排序一般只有三种排序,order by ,sort by,distribute by,分别代表全局排序,内部排序,分区排序。
还有一个cluster by 等价于 sort by distribute by 在一起但是是同一个字段。

1. 全局排序(Order By)

order by:全局排序,则使用一个reduec

  1. 使用order by 子句排序
    ASC(ascend):升序 (默认)
    DESC(descend):降序
  2. order by 子句在select语句的结尾

示例:查询员工信息按照工资降序排序
select * from emp order by sal DESC;

2. 按照别名排序

order by 也可以使用查询的字段别名作为排序条件

按照员工工资的两倍排序:
select ename, sal*2 twosal from emp order by twosal;

3.多个列排序

按照部门和工资升序排序:
select * from emp order by deptno,sal;

4.Mapreduce内部排序(Sort By)

Sort By:每个Reduce内部进行排序,对全局结果来说不是排序
如果没有设置reduce的个数(hive默认为-1)是没有效果的。

-1代表hive会根据sql语句自行分配reduce个数,以256M大小分配一个reduce。

  1. 没有设置reduce个数示例:
    把查询结果下载到本地目录:
    insert overwrite local directory '/home/lzx/file/sort_0' select * from emp sort by empno DESC;
    运行截图:
    在这里插入图片描述
    查看本地目录:
    在这里插入图片描述
    可以看到是只有1个文件存在。
    查看reduce个数:
    	hive (default)> set mapreduce.job.reduces;
    	mapreduce.job.reduces=-1
    	hive (default)> 
    
  2. 设置reduce个数示例:
    设置mapreduce的reduce个数为3:
    hive (default)> set mapreduce.job.reduces=3;
    hive (default)> set mapreduce.job.reduces;
    mapreduce.job.reduces=3
    hive (default)>
    
    把查询结果下载到本地目录:
    insert overwrite local directory '/home/lzx/file/sort_1' select * from emp sort by empno DESC;
    运行截图:
    在这里插入图片描述
    可以看到reduce个数已经变成了3个。
    查看本地文件目录: 在这里插入图片描述
    可以看到是有3个文件的。每个文件的数据都是empno倒叙排列

如果设置reduce个数为3,使用order by查询会有几个reduce?答案是1个。order by 只会使用一个reduce做运算。
在这里插入图片描述

5. 分区排序(Distribute by)

Distribute By:类似于MR中的partirion,进行分区,结合sort by使用

注意:Hive要求Distribute By语句要写在Sort by语句之前。
对于distribute by 进行测试,一定要分配多reduce进行处理,否则无法看到distribute by的效果。

示例:先按照部门编号分区,再按照员工编号排序
insert overwrite local directory '/home/lzx/file/distribute_0' row format delimited fields terminated by '\t' select * from emp distribute by deptno sort by empno;
(把查询结果下载到本地目录并字段用\t 做分隔符)
运行截图:
在这里插入图片描述
查看本地文件目录:
在这里插入图片描述
可以看到是三个文件,有几个reduce就有几个文件,并且文件内的数据是按照empno倒叙排列。
在这里插入图片描述
如果我们设置reduce个数为1,呢么分区就无效果。这就和MR的分区原理是一样的,我们可以设置分区个数等于或者大于(大于有空文件)实际分区。但是不能小与实际分区。
还有就是在MR中我们设置分区返回值是从0开始,呢么如果我们是从3开始,比如:3,4,5,我们需要设置6个分区个数。要按照数字来设置分区个数。

6. Cluster By

当distribute by 和 sort by 字段相同时,可以使用cluster by方式。
cluster by除了具有distribute by 的功能外还兼有sort by的功能。但是排序只能是升序排序,不能指定排序规则为ASC或者DESC

示例:
按照部门编号分区,并且按照部门编号升序排序:
insert overwrite local directory '/home/lzx/file/cluster_0' row format delimited fields terminated by '\t' select * from emp cluster by deptno;

运行截图:
在这里插入图片描述
查看本地目录文件:
在这里插入图片描述

小总结:
sort by:如果reduce个数为3 呢么分区采用随机算法分区,数据均匀分布
cluster by:根据hash值 分区

分桶
1. 分通表数据存储

分区针对的是数据的存储路径,分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。不过,并非所有的数据集都可以形成合理的分区。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。

  1. 创建分通表,通过直接导入数据文件的方式
    1. 创建分通表:clustered by(col_name) into X buckets
      create table stu_buck(id int, name string) clustered by(id) into 4 buckets row format delimited fields terminated by '\t';
      在这里插入图片描述
    2. 查看表结构:
      desc formatted stu_buck;在这里插入图片描述
      可以看到属性里显示的是4个桶,以id分桶。
    3. 导入数据到分通表:
      load data local inpath '/home/lzx/file/stu_buck.txt' into table stu_buck;在这里插入图片描述
      可以看到提示不让使用load方式导入数据。那我们使用insert。。。select 的方式。
      创建一张普通表stu_demo,把数据导入到该表中。
      在这里插入图片描述
      执行insert into table stu_buck select id,name from stu_demo;结果还是一个文件,还是只有一个分桶。如果想要实现分桶还需要设置几个配置:
      set hive.enforce.bucketing=true;set mapreduce.job.reduces=-1;
      设置允许分桶,设置reduce个数为-1,让sql语句自己设置reduce个数。
      在这里插入图片描述
      可以看到是4个reduce,结果路径也是四个文件,说明分桶已经有效果了。 其实和MR的分区是一样的,分桶原理就是key的hash值整除与分桶个数,整除的在0里面,余数为1的在1里面。在这里插入图片描述
      查询分桶数据:
      select * from stu_buck;在这里插入图片描述
2. 分桶抽样查询

对于非常大的数据集,有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

查询表stu_buck中的数据
select * from stu_buck tablesample(bucket 1 out of 4 on id);
注意:tablesample是抽样语句,语法:tablesample(bucket x out of y),y必须是table总bucket数的倍数或者因子。hive根据y的大小决定抽样的比例。例如,table总共分了4份,当y=2时,抽取(4/2=2)2个bucket的数据,当y=8时,抽取(4/8=1/2)1/2个bucket的数据。
在这里插入图片描述
x表示从哪个bucket开始抽取,如果需要取多个分区,以后的分区号为当前分区号加上y。
例如,table 的bucket总数是4,tablesample(bucket 1 out of 2),x=1,y=2表示总共抽取(4/2)2个桶,从第一个桶开始抽取。最终抽取 1,3(x+y)桶。

注意:x的值必须小于等于y的值,否则报错:
hive (default)> select * from stu_buck tablesample(bucket 5 out of 4 on id); FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck hive (default)>
因为最后一个桶是x + (z/y -1)*y = x+z-y
可想而知x最大就是等于y

其他常用函数
1. 空字段赋值 NVL
  1. 函数说明
    NVL:给值为NULL的数据赋值,它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数都为NULL ,则返回NULL。
    replace_with可以为自定义值也可以是其他列
  2. 示例操作
    select nvl(comm,'no data') as comm from emp;
    在这里插入图片描述
2. 时间类

准备:
创建表语句create table student(id int,name string,time string) row format delimited fields terminated by '\t';
导入数据load data local inpath '/home/lzx/file/student.txt' into table student;
在这里插入图片描述
查询数据:
在这里插入图片描述

  1. date_add:时间跟天数相加
    select id,name,time, date_add(time,2) from student;
    在这里插入图片描述
  2. date_sub:时间跟天数相减
    select id,name,time, date_sub(time,2) from student;
    在这里插入图片描述
  3. datediff:两个时间相减
    select id,name,time, datediff(time,'2019-12-01') from student;
    在这里插入图片描述
  4. from_unixtime 时间戳转日期函数
    select from_unixtime(1505456567); 获取时间戳的日期
    在这里插入图片描述
    还可以指定时间格式
    select from_unixtime(1505456567,'yyyyMMdd');
    在这里插入图片描述
    获取当前时间戳并转为时间
    select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss');
    在这里插入图片描述
  5. unix_timestamp 日期转时间戳函数
    unix_timestamp() 获取当前时间戳
    在这里插入图片描述
    获取指定时间的时间戳
    select unix_timestamp('2019-05-12 1:12:12');
    在这里插入图片描述
    获取指定时间,并指定时间格式
    select unix_timestamp('20190915 14:23:00','yyyyMMdd HH:mm:ss');
    在这里插入图片描述
3. CASE WHEN

数据准备:

namedept_idsex
悟空A
大海A
宋宋B
凤姐A
婷姐B
婷婷B

创建表并导入数据以及查询准备数据:

hive (default)> create table emp_tmp(name string,dept_id string,sex string) row format delimited fields terminated by '\t';OK
Time taken: 3.742 seconds
hive (default)> load data local inpath '/home/lzx/file/emp_tmp.txt' into table emp_tmp;
Loading data to table default.emp_tmp
OK
Time taken: 2.836 seconds
hive (default)> select * from emp_tmp;
OK
emp_tmp.name	emp_tmp.dept_id	emp_tmp.sex
悟空	A	男
大海	A	男
宋宋	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女
Time taken: 0.454 seconds, Fetched: 6 row(s)
hive (default)> 

求不同部门男女各多少人:

select dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from emp_tmp group by dept_id;
4. if()

if(bool类型表达式,为true,为false) 等同于oracle的decode,数据同上:
select dept_id,sum(if(sex='男',1,0)) as man,sum(if(sex='女',1,0)) as woman from stu group by dept_id;
在这里插入图片描述

5. 行转列
  1. 相关函数说明
    CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字符串;
    CONCAT_WS(separator, str1, str2,...):它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
    COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段。是一个聚合函数
  2. 查询示例
    CONCAT示例:select concat(name,'_',dept_id) from stu;
    在这里插入图片描述
    CONCAT_WS:select concat_ws('_',name,dept_id) from stu;
    在这里插入图片描述
    COLLECT_SET:select collect_set(dept_id) from stu;
    也有collect_list 一个去重一个不去重
    在这里插入图片描述
  3. 从下面的数据中把血型和星座的一样的人归为一类
nameconsblood
孙悟空白羊座A
大海射手座A
宋宋白羊座B
猪八戒白羊座A
凤姐射手座A

执行语句:select tt.k,concat_ws('|',collect_set(tt.name)) as value from (select concat_ws('_',cons,blood) as k,name from stus) tt group by tt.k;
在这里插入图片描述
collect_set也是可以在concat_ws(标识,多个字段或者array)中使用。

6. 列转行
  1. 函数说明
    EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。
    LATERAL VIEW:用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
    解释:用于和split, explode等UDTF一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。也可以说是对数据的一种测写。

建表语句:
create table mv(name string,categ array<string>) row format delimited fields terminated by '\t' collection items terminated by ',';
预览:
在这里插入图片描述
explode(col)示例:
select explode(categ) from mv;
在这里插入图片描述
lateral view 示例:
select name,categname from mv lateral view explode(categ) tmp as categname;
在这里插入图片描述

窗口函数
  1. 函数说明
    OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化
    rows between X1 and X2:表示获取从X1到X2的数据 该关键字用于over的()内部使用
    X1关键字包括:n PRECEDING:往前n行数据UNBOUNDED PRECEDING:数据的起点,也就是开始的第一行数据
    X2关键字包括: n FOLLOWING:往后n行数据UNBOUNDED FOLLOWING:数据的终点,也就是结束的最后一行数据
    这里面还有一个关键字CURRENT ROW:当前行可以在X1和X2中都能使用

    LAGLEAD是用于和over在外面搭配使用的,如果找不到数据就可以使用默认值,不设置默认值就默认显示NULL
    LAG(col,n,默认值) over(...):往前第n行数据
    LEAD(col,n,默认值):往后第n行数据
    NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号,n代表要分几个组。注意:n必须为int类型。

  2. 准备数据
    创建表:create table business(name string,orderdate string,cost int) row format delimited fields terminated by ',';
    导入数据:load data local inpath '/home/lzx/file/business.txt' into table business;
    数据预览:select * from business;
    在这里插入图片描述

  3. 示例
    (1)查询2017-04这个时间购买过的人的姓名和总人数
    执行sql:select name,count(*) over() from business where substring(orderdate,1,7) = '2017-04' group by name;
    结果:over() 内没有限定条件,所以开窗是面向所有的数据,每一条数据
    在这里插入图片描述
    开窗函数会对查询到的数据每条记录都做处理,都做开窗处理。如果没有开窗函数呢么结果会是这样的: 在这里插入图片描述
    即共两条记录,而窗口函数所处理的数据也是图上所查询到的数据。count统计的也是这两条记录并给每条数据都添加上这个数字。
    (2)按顾客姓名分组查询顾客的消费记录和总额
    over()使用distribute by 对查询的结果数据分组 等同于partition by 同是分区 分组 但是不能使用group by 因为over() 中没有group by

    执行语句:select *,sum(cost) over(distribute by name) as money from business;
    等价于:select *,sum(cost) over(partition by name order by orderdate) as money from business;
    在这里插入图片描述
    (3)按顾客姓名分组查询顾客的消费记录和消费增长记录
    当我们使用了over()并配合distribute by完成了数据分组,在搭配sort by col 实现组内按照col排序。sort by只能和distribute by配合 不能和partition by配合使用,但是partition by 可以和order by搭配,order bysort by 有相同的效果。
    在这里插入图片描述
    (4)rows between示例
    select *,sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row) as money1, sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as money2, sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING) as money3 from business;
    在这里插入图片描述
    (5)查看顾客上次或者下次的购买时间
    查询顾客上次的购买时间:select *, lag(orderdate,1,'1999-99-99') over(partition by name order by orderdate) as lastdate from business;
    在这里插入图片描述
    查询顾客下次的购买时间:select *, lead(orderdate,1,'1999-99-99') over(partition by name order by orderdate) as lastdate from business;
    在这里插入图片描述
    (5)查询前20%时间的订单信息
    数据分5个组:select *,ntile(5) over(order by orderdate) from business;
    在这里插入图片描述
    数据分3个组:select *,ntile(3) over(order by orderdate) from business;
    在这里插入图片描述
    数据分10个组:select *,ntile(10) over(order by orderdate) from business; 如果n大于数据总数,呢么一条数据就是一个组
    在这里插入图片描述
    20%代表分为5各组取组号为1的数据:select * from (select *,ntile(5) over(order by orderdate) as sorted from business) tt where tt.sorted =1;
    在这里插入图片描述

排名函数-RANK,DENSE_RANK(),ROW_NUMBER()

这些也是要配合窗口函数over使用的

  1. 函数说明
    RANK():排序相同时会重复,总数不会变
    DENSE_RANK(): 排序相同时会重复,总数会减少
    ROW_NUMBER(): 会根据顺序计算
  2. 准备数据
    (1) 建表:create table scored (name string,subject string,score int) row format delimited fields terminated by '\t';
    (2) 导入数据:load data local inpath '/home/lzx/file/scored.txt' into table scored;
    (3) 数据预览:select * from scored;
    在这里插入图片描述
  3. 示例
    (1)rank示例
    排序总数不变,但是碰到相同排名会跳过排名数字
    select *,rank() over(partition by subject order by score desc) from scored;
    在这里插入图片描述
    (2)DENSE_RANK()示例
    可重复总数减少,存在并列排名不会跳过排名数字
    select *,dense_rank() over(partition by subject order by score desc) from scored;
    在这里插入图片描述
    (3)row_number()示例
    序列值,排序的序号值
    select *,row_number() over(partition by subject order by score desc) from scored;
    在这里插入图片描述
  • 1
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值