hive
1.hive简介
hive：由Facebook开源用于解决海量 结构化数据 的统计工具。
hive是基于hadoop的一个 数据仓库 工具， 可以将 结构化的数据文件 映射为一张表，并提供类SQL（HQL）查询功能。
2.hive本质：
hive的本质是将HQL(HiveSQL)转化成MapReduce程序(java代码).----可以将hive看成是MapReduce的一个客户端；
hive处理的数据存储在HDFS
hive分析数据底层实现是MapReduce
执行程序运行在Yarn
3.hive优缺点
3.1优点
1.操作接口采用类SQL语法，提供快速开发能力(简单，易上手)
2.避免去写MapReduce，减少开发人员的学习成本
3.hive优势在于处理大数据，对于处理小数据没有优势，因为hive执行延迟较高
4.hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数
3.2缺点
1.hive执行延迟高，因此常用于数据分析，对实时性要求不高的场合
2.hive的HQL表达能力有限
4.hive与数据库比较
1.查询语言
针对hive的特性设计了类SQL的查询语言，熟悉SQL的开发者可以方便的使用hive
2.数据更新
hive是针对数据仓库应用设计的，而数仓的内容读多写少，因此，hive中不建议对数据的改写，所有数据加载时确定好
而数据库中数据通常是需要修改，因此使用insert into添加数据，update修改数据
3.执行的延迟
hive查询时由于没有索引，需要扫描整个表，因此延迟较高。另一个导致hive延迟较高的因素是MapReduce。
4.数据规模
hive海量数据的支持(TB,PB级别)，对应数据库(GB级别)处理数据量规模较小。
5.hive操作
5.1hive启动
5.1.1 hive
5.1.2 cd /opt/module/apache-hive-2.1.1-bin/
bin/hive
5.2hive退出
5.2.1 exit;
5.2.2 quit;
5.3常用交互命令
hive -e
-e 直接调用hivesql语句 --不必进入到hive
hive -e "select * from test.emp_3;"
hive -f --不必进入到hive
-f 调用文件中hivesql语句
vim hivef.sql
select * from test.emp_3;
hive -f /home/hivef.sql > /home/hivef.txt
5.4在hive交互窗口里查看HDFS文件系统 -----dfs 打头
hive (default)> dfs -ls /; --dfs -cat/dfs -mkidr .............;
6.hive数据类型
基本数据类型：
TINYINT 1byte有符号整数 -127~+127；
SMALLINT 2byte有符号整数
INT 4byte有符号整数
BIGINT 8byte有符号整数
BOOLEAN 布尔类型，true或者false
FLOAT 单精度浮点数 8个有效数字 4个字节
DOUBLE 双精度浮点数 16个有效数字 8个字节
decimal(整个数字部分最大长度，小数位数) 自定义小数类型 decimal(38,6)
decimal;默认长度是10，小数位数0；
STRING 字符串
varchar(100) 字符串
char(10) 字符串
TIMESTAMP 时间类型 current_timestamp 当前系统时间 current_date 当前系统日期
集合数据类型：
STRUCT 结构体类型 地址 struct<street:string,city:string> {"street":"ruyilu", "city":"guangzhou"}
MAP 映射类型 分数 map<string,int> {"语文":97,"数学":100,"英语":95} 键值对形式 key:value对形式
ARRAY 数组类型 爱好 array<string> ["dance","swimming"]
数据类型实例演示：
show databases;
use test;
1.(hive)在hive中建表：使用"建表语句.txt"
2.(linux)vim l_test.txt 编辑测试数据
fulaoshi,lilaoshi_luolaoshi,xiaofu:6_xiaofufu:5,ruyilu_guangzhou
lilaoshi,fulaoshi_luolaoshi,xiaoli:7,ruyilu_guangzhou
3.(hive)导数据进入test.l_test表：load data local inpath '/home/l_test.txt' into table l_test;
4.(hive)查看表数据：select * from l_test;
类型转化
隐式类型转换规则
1.任何整数类型都可以隐式转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT
2.所有整数类型，FLOAT和STRING(数字)类型都可以隐式转换成DOUBLE
3.TINYINT，SMALLINT，INT都可以转换成FLOAT
4.BOOLEAN类型不能转换成任何其他类型
显式转换 cast() --cast(要被转换的数据 as 新数据类型)
cast('1' as int)
select '1'+2,cast('1' as int)+2;
7.DDL数据定义
7.1创建数据库
7.1.1 create database test_2;
7.1.2 create database if not exists test_2;
7.1.3 指定HDFS路径创建hive数据库：create database test_loc location '/test_loc.db';
7.2查看数据库
7.2.1 show databases;
7.2.2 show databases like 'tes*';
7.2.3 desc database test; --查看库的信息
7.2.4 desc database extended test; --查看库的详细信息
7.3 alter database test set dbproperties('createtime'='20240125');
7.4删除数据库
7.4.1删除空数据库
drop database test_loc;
7.4.2判断数据库是否存在然后执行删除
drop database if exists test_loc;
7.4.3如果库不为空，强制删除
drop database test_2 cascade;
show create table 表名； --显示建表语句
7.5使用数据库
use databasename；
7.6创建表
create table if not exists
external 外部表
comment 注释
partitioned by 分区 --分文件夹
clustered by 分桶 --分文件
sorted by 对桶中的数据排序
row format delimited fields terminated by ',' -----------指定列分隔符
collection items terminated by '_' -----------指定MAP STRUCT ARRAY 的数据分隔符
map keys terminated by ':' -----------指定MAP中key和value的分隔符
lines terminated by '\n' -----------指定行分隔符
stored as 指定文件类型：textfile(文本)（行式存储）,rcfile( 列式存储 格式文件), orc( 列式存储 格式文件)
,sequencefile(二进制序列文件)（行式存储）
列式存储：查询指定列,可以避免整表读取;
location 指定表在HDFS上的存储位置
as 后跟查询语句，根据查询结果创建表 create table 新表名 as select 语句; --复制不了分隔符
like 允许用户复制现有表的结构，但不复制数据 create table 新表名 like 旧表;
7.6.1管理表(内部表)
默认创建的表都是管理表(内部表)。hive会控制着数据的生命周期，当我们删除一个管理表(内部表)时，hive会删除这个表中的数据。
管理表不适合与其他工具共享数据。
(1)普通创建表
create table if not exists
hsstd (id int, name string)
row format delimited fields terminated by '\t'
stored as textfile;
load data local inpath '/home/hsstd.txt' into table hsstd;
(2)根据查询结果创建表(查询的结果会添加到新创建的表中)
create table if not exists hsstd_2 as select id,name from hsstd where id<=8;
(3)根据已经存在的表结构创建表
create table if not exists hsstd_3 like hsstd;
(4)查看表的类型
desc formatted hsstd;
7.6.2外部表
因为表外部表，所以hive并非认为其完全拥有这份数据，删除该表并不会删掉这份数据，不过描述表的元数据会被删掉。
实例：
(1)创建外部表
create external table if not exists
dept_2 (deptno int,
dname string,
loc string)
row format delimited fields terminated by '\t';
(2)删除外部表
drop table dept_2;
7.6.3内外部表相互转换
内部表转外部表
alter table hsstd_3 set tblproperties('EXTERNAL'='TRUE');
外部表转内部表
alter table dept_2 set tblproperties('EXTERNAL'='FALSE');
7.7修改表 --只是修改元数据，不会影响到表真实数据
添加列
alter table hsstd_3 add columns (age int);
更新列
alter table hsstd_3 change [column] age age2 string; --更改数据类型 要由低范围往高范围改；
替换列(整体) --hive不支持 alter table 表名 drop column 句式
alter table hsstd_3 replace columns (id int, name2 string); --可以用替换方式来间接删除相关列；
修改表名
alter table 表名 rename to 新表名;
--如果表中有重复数据，如何删除？？？
-----------------insert overwrite table 表 select distinct * from 表；
8.DML数据操作
8.1数据导入
8.1.1向表中装载数据load
load data：表示加载数据
local：表示从本地加载到hive表，否则从HDFS加载到hive
inpath:加载数据的路径
overwrite：覆盖表中已有数据，否则追加
into table：表示加载到哪张表
partition：表示上传到指定分区
实例：
加载本地数据到hive
load data local inpath '/home/hsstd.txt' into table test.hsstd_3;
上传本地文件到HDFS
(hive)dfs -put /home/hsstd.txt /hongshan; ---hongshan目录要提前创建好；
(linux)hadoop fs -put /home/hsstd.txt /
加载HDFS的文件到hive表中 --剪切式
load data inpath '/hsstd.txt' into table test.hsstd_3;
加载数据使用overwrite覆盖表中已有数据
load data local inpath '/home/hsstd.txt' overwrite into table test.hsstd_3;
8.1.2通过查询语句向表中插入数据(insert)
insert into
insert into table hsstd values (11,'hs11'),(12,'hs12'); --少用，会产生小文件，加大namenode负担；
insert overwrite
insert overwrite table hsstd select id,name from hsstd_3 where id<8;
8.1.3多表(多分区)插入模式
实例：
from hsstd --只读取一次表 --提高效率
insert overwrite table hsstd_3 --7条
select id,name
insert overwrite table hsstd_4 --4条
select id,name where id<5;
8.1.4 import导入(空表或不存在的表)
只有export导出的表目录才能使用import
1.先export导出现有表
export table test.hsstd
to '/hongshan/hsstd_dc';
2.根据1中导出的目录，import导入新的表
import table hsstd_5 --空表或不存在的表
from '/hongshan/hsstd_dc';
8.2数据导出
8.2.1 insert导出
(1)将查询结果导出到Linux本地
insert overwrite local directory
'/home/hsstd_dc'
select * from hsstd;
(2)将查询结果格式化导出到Linux本地
insert overwrite local directory
'/home/hsstd_dc2'
row format delimited fields terminated by '\t'
select * from hsstd;
(3)将查询结果格式化导出到HDFS
insert overwrite directory
'/hongshan/hsstd_dc3'
row format delimited fields terminated by '\t'
select * from hsstd;
8.3清除表中的数据(truncate)
注意：只能删除管理表中的数据，不能删除外部表中的数据
truncate table hsstd_5;
9.查询
9.1基本查询
9.1.1全表查询
select * from hsstd;
select id,name from hsstd;
9.1.2特定列查询
select name from hsstd;
注意：
(1)大小写不敏感
(2)SQL可以写在一行或者多行
(3)关键字不能被缩写也不能分行
(4)各子句一般分行写
(5)使用缩进提高语句可读性
9.1.3列别名
select id as st_id,name from hsstd;
9.1.4算术运算符
+ - * / % & | ^ ~
select sal + 10000 from emp_2;
9.1.5常用聚合函数
count sum min max avg
select count(*) cnt from emp_2;
9.1.6where语句
select * from emp_2 where sal > 1000;
9.1.7比较运算符
A=B
A<=>B 如果A和B都为NULL则返回TRUE，如果一边为NULL返回FALSE
A<>B,A!=B
A<B
A<=B
A>B
A>=B
A NOT BETWEEN B AND C
A IS NOT NULL
A NOT IN (B,C)
select * from emp_3 where sal between 500 and 1000;
select * from emp_3 where mgr <=> comm;
select * from emp_3 where mgr is null and comm is null;
9.1.8 LIKE和RLIKE --等价于regexp_like
RLIKE通过java实现正则表达式
select * from emp_3 where ename rlike '[A]';
select * from emp_3 where ename like '%A%';
9.1.9逻辑运算符
AND OR NOT
select * from emp_3 where sal > 1000 and deptno = 30;
select * from emp_3 where sal > 1000 or deptno = 30;
select * from emp_3 where deptno not in(20,30);
9.2分组
9.2.1 GROUP BY
select t.deptno,avg(t.sal) avg_sal from emp_2 t group by t.deptno;
9.2.2 HAVING
select t.deptno,avg(t.sal) avg_sal from emp_2 t group by t.deptno having avg(t.sal) > 2000;
9.3JOIN
9.3.1内连接
只有进行连接的两个表中都存在于连接条件相匹配的数据才会被保留下来。（取交集）
select e.empno,e.ename,d.deptno,d.dname from emp_2 e join dept_2 d on e.deptno=d.deptno;
9.3.2左外连接
JOIN操作符左边表中符合where子句的所有记录都会被返回。（左表为主表，驱动表。）
select e.empno,e.ename,d.deptno,d.dname from emp_2 e left join dept_2 d on e.deptno=d.deptno;
9.3.3右外连接
JOIN操作符右边表中符合where子句的所有记录都会被返回。（右表为主表，驱动表。）
select e.empno,e.ename,d.deptno,d.dname from dept_2 d right join emp_2 e on e.deptno=d.deptno;
9.3.4满外关联
将会返回所有表中符合where条件的所有记录，如果任一表的指定字段没符合条件的话，那么使用NULL替代。
select e.empno,e.ename,d.deptno,d.dname from emp_2 e full join dept_2 d on e.deptno=d.deptno;
9.3.5多表连接
select e.ename,d.dname,l.loc_name
from emp_2 e
join dept_3 d
on e.deptno=d.deptno
join location_2 l
on d.loc=l.id;
9.3.6笛卡尔积
在下面条件下产生
(1)省略连接条件
(2)连接条件无效
(3)所有表中的所有行相互连接
实例：
select e.empno,e.ename,d.deptno,d.dname from emp_2 e,dept_2 d;
9.4排序
9.4.1全局排序(order by)
order by:全局排序，只有一个reducer
(1)升序(默认)ASC
(2)降序 DESC
(3)可以使用字段别名排序，因为执行顺序在select之后
实例：
select * from emp_3 order by sal;
select ename,sal * 2 twosal from emp_3 order by twosal desc;
9.4.2每个reduce内部排序(sort by) --有几个reduce,就会有几个输出文件
查看reduce数量：set mapreduce.job.reduces;
设置reduce数量：set mapreduce.job.reduces=3;
加载sort by结果到本地
insert overwrite local directory
'/home/sortby-result'
row format delimited fields terminated by '\t'
select * from emp_3 sort by deptno desc;
9.4.3分区(distribute by) --分区中的sort by排序
--根据分区字段计算每条数据的哈希值(正整数)，用哈希值%reduce数，余数一样的进入同一个reduce；
在有些情况下，需要控制某个特定的行进入指定reducer，为了后续的操作，结合sort by使用
查看reduce数量：set mapreduce.job.reduces;
设置reduce数量：set mapreduce.job.reduces=3;
insert overwrite local directory
'/home/sortby-result2'
row format delimited fields terminated by '\t'
select * from emp_3 distribute by deptno sort by empno desc;
9.4.4cluster by 排序(默认只能升序排序) ，cluster by后面不能跟 asc，desc;
当distribute by和sort by字段相同时，可以使用cluster by来替换；
查看reduce数量：set mapreduce.job.reduces;
设置reduce数量：set mapreduce.job.reduces=3;
实例：
insert overwrite local directory
'/home/sortby-result3'
row format delimited fields terminated by '\t'
select * from emp_3 cluster by deptno;
----等价于 select * from emp_3 distribute by deptno sort by deptno;
10.分区分桶 --都属于优化方式 把 大的数据集 分割成小的数据集
10.1分区
hive中分区就是分目录，把一个大的数据集根据业务分割成小的数据集，
在查询时通过where子句表达式选择查询所需的指定分区，这样查询效率更高。
10.1.1创建分区表
分区字段不能存在于表中，可以将分区字段视为伪列。
create table if not exists
dept_partition (id int,
deptno string,
dname string,
loc string)
partitioned by (day string)
row format delimited fields terminated by '\t' ;
10.1.2加载数据
load data local inpath
'/home/dept_p.txt' into table dept_partition
partition(day='20240223');
load data local inpath
'/home/dept_p.txt' into table dept_partition
partition(day='20240222');
10.1.3查看分区数据
(1)select * from dept_partition where day='20240126';
(2)
select * from dept_partition where day='20240126'
union
select * from dept_partition where day='20240125';
(3)select * from dept_partition where day='20240126' or day='20240125';
10.1.4增加分区
增加单个分区
alter table dept_partition add partition(day='202402221');
增加多个分区
alter table dept_partition add partition(day='20240220')partition(day='20240119');
10.1.5删除分区
删除单个分区
alter table dept_partition drop partition(day='20240124');
删除多个分区
alter table dept_partition drop partition(day='20231208'),partition(day='20231209');
10.1.6查看分区表有多少个分区
show partitions dept_partition;
10.1.7查看分区表结构
desc formatted dept_partition;
10.1.8二级分区
10.1.8.1创建二级分区表
create table if not exists
dept_partition2 (id int,
deptno string,
dname string,
loc string)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t' ;
10.1.8.2load data
load data local inpath
'/home/dept_p.txt' into table dept_partition2
partition(day='20240223',hour='12');
10.1.8.3查询二级分区表
select * from dept_partition2 where day='20240126' and hour='12';
10.1.9动态分区
关系型数据库中，对分区表insert数据时，数据库会自动根据分区字段的值，将数据插入相应的分区中，
hive中也提供了类似的机制，即动态分区(Dynamic Partition)，使用hive动态分区需要进行相应设置。
(1)开启动态分区功能(默认true，开启)
查看set hive.exec.dynamic.partition;
(2)设置为非严格模式(默认strict，表示必须指定至少一个分区为静态分区，
nonstrict模式表示允许所有的分区字段都可以使用动态分区)
set hive.exec.dynamic.partition.mode=nonstrict;
(3)在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000.
查看set hive.exec.max.dynamic.partitions;
(4)在每个执行MR的节点上，最大可以创建多少个动态分区。默认100.
查看set hive.exec.max.dynamic.partitions.pernode;
(5)整个MR job中，最大可以创建多少个HDFS文件，默认100000
查看set hive.exec.max.created.files;
(6)当有空分区生成时，是否抛出异常，一般不需要设置。默认false。
查看set hive.error.on.empty.partition;
实例：
(1)建分区表：
create table if not exists
dept_partition_dy (id int,
deptno string,
dname string)
partitioned by (loc string)
row format delimited fields terminated by '\t' ;
(2)插入动态分区 --要用insert into插入数据,不能用load data方式
insert into table dept_partition_dy partition(loc)
select id,deptno,dname,loc from dept_partition;
(3)查看目标分区表的分区情况
show partitions dept_partition_dy;
10.2分桶
分桶是将数据集分解成更容易管理的若干部分的 另一个技术。
分区针对的是数据的存储路径，分桶针对的是数据文件。
10.2.1创建分桶表 --分桶字段用的是 表的真实字段，
根据分桶字段，计算每条数据的哈希值，用哈希值%桶数，余数一样的进入同一个桶；
--分桶一大优点：提高表关联效率（join）; 提高抽样查询效率；
create table if not exists
stu_buck1 (id int,
name string)
clustered by (id)
into 4 buckets
row format delimited fields terminated by '\t' ;
10.2.2设置reduce数量：set mapreduce.job.reduces=-1;
10.2.3加载数据
load data local inpath
'/home/hsstd.txt' into table stu_buck1;
10.2.4查看分桶表结构
desc formatted stu_buck;
10.2.5查询分桶表数据
select * from stu_buck;
注意事项：
1.reduce的个数设置为-1，让job自行决定有多少个reduce
set mapreduce.job.reduces=-1;
2.load数据时确定文件路径，避免找不到文件的问题
3.不要使用本地模式 --本地模式适用于查询数据量少（小于128M）的场景；
查看set hive.exec.mode.local.auto;
11.函数
11.1系统内置函数
(1)查看系统自带函数
show functions;
(2)显示自带函数用法
desc function upper;
desc function extended upper;
hive常用函数：
数字函数：abs,round,mod,power,ceil,floor,trunc.......;
日期函数：--current_timestamp,current_date;
--add_months(),months_between()
datediff(日期1，日期2) --算间隔天数
date_add() 日期往后推
date_sub() 日期往前推
--获取日期时间中 的 相应的 年，月，日，时，分，秒
year()
month()
day()
hour()
minute()
second()
to_date() 获取日期时间中 的年月日;
extract() 时间提取函数
--格式化输出日期时间 date_format('2024-01-26','YYYY-MM-dd');
字符串函数
concat ,concat_ws,substr,instr,replace,trim,ltrim,rtrim,lpad,rpad,length,
---regexp_replace,regexp_extract()--解析函数
逻辑函数 nvl ,coalesce--返回第一个非空的值,case when,if(表达式，为真的结果，为假的结果) ; --- select coalesce(null,12,'qqq');
分析函数
sum()over(),avg()over()........
平移函数 lag()over(),lead()over()
排名函数 row_number()over,rank()over(),dense_rank()over();
表示一个数组 array(12,'qqq',322,'rrrr');
跟数组相关的函数 size(数组) --算元素个数
--数组名[下标号] --获取特定元素 --下标号从0开始
sort_array() --对数组元素进行排序
表示一个映射 map('aaa',111,'bbb',222);
map_keys()
map_values()
size()
映射[key值] --获取指定key的value值；
11.2行转列
concat(stringA,stringB):返回输入字符串连接后的结果，支持任意个输入字符串
concat_ws($eparator,str1,str2):是特殊形式的concat()，第一个参数是所有字符串的分隔符
concat_ws($eparator, array):将数组中各个元素用分隔符连接起来；
collect_set(col):函数只接受基本数据类型，作用主要将某字段的值进行去重汇总，产生array类型字段
collect_list(col)函数只接受基本数据类型，作用主要将某字段的值进行汇总，产生array类型字段
1.原数据形式:
fulaoshi 白羊座 A
lilaoshi 处女座 A
chenlaoshi 白羊座 B
luolaoshi 白羊座 A
azhen 处女座 A
aqiang 白羊座 B
2.需求数据形式：
白羊座,A fulaoshi|luolaoshi
白羊座,B chenlaoshi|aqiang
处女座,A lilaoshi|azhen
select concat_ws(',',t.constellation ,t.blood_type) xx , concat_ws('|', collect_list(t.name)) name
from hstc t
group by concat_ws(',',t.constellation ,t.blood_type )
order by substr(xx,1,3) desc,substr(xx,5,1);
3.建表语句：
create table if not exists
hstc (name string,
constellation string,
blood_type string)
row format delimited fields terminated by '\t' ;
4.load data：
load data local inpath
'/home/hadoop/123/hstc.txt' into table hstc;
5.HQL语句：
select t1.c_b,concat_ws('|',collect_set(t1.name)) name_lt
from
(select name,concat_ws(',',constellation,blood_type) c_b from hstc) t1
group by t1.c_b;
11.3列转行
explode(col):将hive中一列复杂的array或者map结构拆分成多行
lateral view 侧视图
用法：lateral view udtf(expression) tableAlias AS columnAlias --新表名，新列名
解释：用于和split explode等UDTF函数一起使用，它能将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合
split :函数 能将一个字符串 根据切割符 切割成一个数组
split(字符串，切割符)
1.原数据形式
教父 黑帮,警匪,心理,剧情
侏罗纪公园 动作,科幻,剧情,灾难
战狼 战争,动作,灾难
2.需求形式：
教父 黑帮
教父 警匪
教父 心理
教父 剧情
侏罗纪公园 动作
侏罗纪公园 科幻
侏罗纪公园 剧情
侏罗纪公园 灾难
战狼 战争
战狼 动作
战狼 灾难
select movie,new_category
from movie_info
lateral view explode(split(category,',')) A as new_category;
3.建表语句
create table if not exists movie_info (movie string, category string) row format delimited fields terminated by '\t' ;
4.load data
load data local inpath '/home/movie_info.txt' into table movie_info;
5.HQL语句：
select movie,category_name
from movie_info
lateral view
explode(split(category,',')) movie_info_tmp as category_name;
11.4自定义函数 --用Java代码编写
UDF(user defined function) --upper()
一进一出
UDAF(user defined aggregation function) --sum
聚集函数，多进一出
UDTF(user defined table-generating function) --explode()
一进多出
11.5开窗函数
11.5.1开窗函数基本使用
OVER():指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化
CURRENT ROW:当前行
n PRECEDING:往前n行
n FOLLOWING:往后n行
UNBOUNDED:起点
UNBOUNDED PRECEDING：表示从前面的起点
UNBOUNDED FOLLOWING：表示从后面的起点
LAG(col,n,default_value):往前第n行数据
LEAD(col,n,default_value):往后第n行数据
NTILE(n):把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。
需求：
1.查询在2022年4月购买过的客户及总人数；
select name,count(*) over()
from business
where substring(orderdate,1,7)='2022-04'
group by name;
select distinct name,count(distinct name)over()
from business
where substring(orderdate,1,7)='2022-04';
2.查询客户的购买明细及 月购买总额；
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;
--若 月购买总额 是 每个客户的月购买总额
select name,orderdate,cost,sum(cost) over(partition by name，month(orderdate)) from business;
3.上述的场景，每个客户的cost按照日期进行累加；
select name,orderdate,cost,
sum(cost) over() as s1, --所有行相加
sum(cost) over(partition by name) as s2, --按name分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as s3, --按name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) as s4, --和s3效果相同个，由起点到当前行聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and CURRENT ROW) as s5, --当前行和前1行进行聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING) as s6, --当前行，前一行及后一行聚合
sum(cost) over(partition by name order by orderdate rows between CURRENT ROW and UNBOUNDED FOLLOWING) as s7 --当前行及后面所有行聚合
from business;
4.查询每个客户上次的购买时间；
select name,orderdate,cost,
lag(orderdate,1,'1970-01-01') over(partition by name order by orderdate) as t1,
lag(orderdate,2) over(partition by name order by orderdate) as t2 ----------看上上次的购买时间
from business;
11.5.2RANK
RANK():排序相同时会重复，总数不变
DENSE_RANK():排序相同时会重复，总数会减少
ROW_NUMBER():会根据顺序计算
select name,subject,score,
rank() over(partition by subject order by score desc) rk,
dense_rank() over(partition by subject order by score desc) de_rk,
row_number() over(partition by subject order by score desc) row_rk
from score;
345
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
