Hive高级篇

标签：-- 分区 高级 视图 查询 Hive 数据 SELECT

1.分区/分桶

数据模型
讲 Hive 分区之前，我们先来说一下 Hive 的数据模型，Hive 的数据模型主要有以下四种：

​ 在大数据中，最常见的一种思想就是分治，我们可以把大文件切割成一个个的小文件，这样每次操作小文件时就会容易许多。同样的道理，在 Hive 中也是支持的，我们可以把大的数据，按照每天或者每小时切分成一个个的小文件，这样去操作小文件就会容易许多，这就是分区、分桶的意思。

​ 假如现在我们公司一天产生 3 亿的数据量，为了方便管理和查询，可以先建立分区（按日期，部门等具体业务分区），分区后如果数据还是很大可以再对数据进行分桶操作。
​ 假如某电商平台每天产生 1 千万的订单数据，为了快速处理订单数据（比如分析每个用户的购买行为），可以根据用户 ID 进行分桶。

1.分区

​ 使用分区，可以避免Hive全表扫描，提升查询效率，同时可以减少数据冗余，提高特定分区的查询效率。

​ 注意，在逻辑上分区表与未分区表没有区别，在物理上分区表会将数据按照分区键的键值存储在表目录的子目录中，目录名为“分区键=键值”。你可以把建立分区想象成建了个文件夹，把一些相似（或者说相同类型）的数据存放到文件夹中。

使用分区表时，尽量带上条件查询，否则失去分区的意义。

1.静态分区

​ 分区表类型分为静态分区和动态分区。区别在于前者是我们手动指定的，后者是通过数据来判断分区的。根据分区的深度又分为单分区与多分区。

单分区

创建分区表
创建静态分区表语法（静态分区和动态分区的建表语句是一样的）：

-- 单分区：创建分区表 PARTITIONED BY (分区字段名 分区字段类型)
-- 多分区：创建分区表 PARTITIONED BY (分区字段名 分区字段类型, 分区字段名2 分区字段类型2)
CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
)
PARTITIONED BY (grsde int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

PARTITIONED BY ( ) 栝号中字段名不能和表中的一样。

-- 添加分区
ALTER TABLE t_student ADD IF NOT EXISTS PARTITION (grade=1);
-- 删除分区 
ALTER TABLE t_student ADD IF NOT EXISTS PARTITION (grade=1);
-- 查看分区
SHOW PARTITIONS t_student;

载入数据：

LOAD DATA INPATH '/yjx/s1.txt' INTO TABLE t_student PARTITION (grade=1);

载入结果：

需要注意的是，分区查询会将分区中所有的数据都查询出来，所以如果文件中的数据本身已经出问题了，那么查询的结果也会出问题，会按分区目录的值显示。

多分区

-- 注意：前后两个分区的关系为父子关系，也就是 grade 文件夹下面有多个 clazz 子文件夹。
PARTITIONED BY (grade int , clazz int)

增删改查分区方法和载入数据方法同上。

也可以使用分区表的 INSERT 语句插入数据（会执行 MR 任务）：

INSERT INTO TABLE t_student PARTITION (grade=1,clazz=2) VALUES (...);

2.动态分区

​ 与静态分区的区别在于静态分区是手动指定，而动态分区是通过数据来判断分区的。详细来说，静态分
区的列是在编译时期通过用户传递来决定的；动态分区只有在 SQL 执行时才能决定。

开启动态分区首先要在 Hive 会话中设置以下参数：

-- 开启动态分区支持（默认true）
SET hive.exec.dynamic.partition=true;
-- 是否允许所有分区都是动态的，strict 要求至少包含一个静态分区列，nonstrict 则无此要求（默认 strict）
SET hive.exec.dynamic.partition.mode=nonstrict;

其余参数详细配置如下：

-- 配置MAP,REDUCE的最大分区个数。默认100
-- 比如：源数据中包含了一年的数据，如果按天分区，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。
hive.exec.max.dynamic.partition.pernode=100;
-- 一个动态分区创建可以创建的最大动态分区个数（默认为 1000）
hive.exec.max.dynamic.partitions=1000;
-- 全局可以创建的最大文件个数（默认为 100000）
hive.exec.max.created.files=100000;
-- 当有空分区产生时，是否抛出异常（默认为 false）
hive.error.on.empty.partition=false;
-- 是否开启严格模式 strict（严格模式）和 nostrict（非严格模式，默认）
hive.mapred.mode=nostrict;

严格模式

​ Hive 通过参数 hive.mapred.mode 来设置是否开启严格模式。目前参数值有两个：strict（严格模式）和 nostrict（非严格模式，默认）。
​ 开启严格模式，主要是为了禁止某些查询（这些查询可能会造成意想不到的坏结果），目前主要禁止三种类型的查询：
​ 分区表查询时，必须在 WHERE 语句后指定分区字段，否则不允许执行。因为在查询分区表时，如果不指定分区查询，会进行全表扫描。而分区表通常有非常大的数据量，全表扫描非常消耗资源。
ORDER BY 查询必须带有 LIMIT 语句，否则不允许执行。因为 ORDER BY 会进行全局排序，这个过程会将处理的结果分配到一个 Reduce 中进行处理，处理时间长且影响性能。
​ 笛卡尔积查询（多使用 JOIN 和 ON 语句查询）。数据量非常大时，笛卡尔积查询会出现不可控的情况，因此严格模式下也不允许执行。
​ 在开启严格模式下，进行上述三种不符合要求的查询时，通常会报类似 FAILED: Error in semantic analysis:
In strict mode, XXX is not allowed. If you really want to perform the operation,+set
hive.mapred.mode=nonstrict+ 的错误。

创建动态分区表语法（静态分区和动态分区的建表语句是一样的）：

并且动态分区的 HDFS 文件中不会存储分区列数据，起到节省空间的作用，但是查询结果并不会受到影响。

外部分区表

CREATE EXTERNAL TABLE IF NOT EXISTS t_teacher (
tno int,
tname string
)
PARTITIONED BY (grade int, clazz int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
-- LOCATION 使用通配符 * 无效，例如 /yjx/teacher/*/*
LOCATION '/yjx/teacher';

此时是查询不到任何数据的，不是说 LOCATION 下面没有数据，而是分区表需要通过添加分区而载入数据。

载入数据：

ALTER TABLE t_teacher ADD IF NOT EXISTS PARTITION (grade=1, clazz=1) LOCATION '/yjx/teacher/1/1';
ALTER TABLE t_teacher ADD IF NOT EXISTS PARTITION (grade=1, clazz=2) LOCATION '/yjx/teacher/1/2';
ALTER TABLE t_teacher ADD IF NOT EXISTS PARTITION (grade=1, clazz=3) LOCATION '/yjx/teacher/1/3';
ALTER TABLE t_teacher ADD IF NOT EXISTS PARTITION (grade=2, clazz=1) LOCATION '/yjx/teacher/2/1';
ALTER TABLE t_teacher ADD IF NOT EXISTS PARTITION (grade=2, clazz=2) LOCATION '/yjx/teacher/2/2';

删除分区数据不会被删除：

ALTER TABLE t_teacher DROP IF EXISTS PARTITION (grade=1, clazz=1);

3.分桶

​ 分区提供了一个隔离数据和优化查询的便利方式，不过并非所有的数据都可形成合理的分区，尤其是需要确定合适大小的分区划分方式。不合理的数据分区划分方式可能会导致有的分区数据过多，而某些分区没有什么数据的尴尬情况。
​ 所以就有了分桶，分桶是将数据集分解为更容易管理的若干部分的另一种技术，也就是更为细粒度的数据范围划分，将数据按照字段划分到多个文件中去。
注意：分区针对的是数据的存储路径，分桶针对的是数据文件。

使用分桶表时，尽量利用分桶字段进行查询，如果不使用分桶字段查询，就会全部扫描，这样就失去了分桶的意义。

1.分桶原理

Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条记录要存放在哪个桶中。
计算公式： bucket num = hash_function(bucketing_column) mod num_buckets 。
假如某电商平台每天产生 1 千万的订单数据，为了快速处理订单数据（比如分析每个用户的购买行为），可以根据用户 ID 进行分桶。比如将表按照 ID 分成 100 个桶，其算法是 hash(id) % 100，这样 hash(id) % 100 = 0 的数据会被放到第一个桶中，hash(id) % 100 = 1 的记录被放到第二个桶中。

2. 分桶优势

• 方便抽样：使抽样（Sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
• 提高 JOIN 查询效率：获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理某些查询的时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side Join）高效的实现。比如 JOIN 操作。对于 JOIN 操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行 JOIN 操作就可以，可以大大较少 JOIN 的数据量。

3. 分桶实践

将数据导入分桶表主要通过以下步骤：

• 创建表并设置分桶
• 从 HDFS 或本地磁盘中 Load 数据至中间表
• 通过从中间表查询的方式完成数据导入（导入桶）

开启分桶功能首先要在 Hive 会话中设置以下参数：

-- 开启分桶功能，默认为 false
SET hive.enforce.bucketing=true;
-- 设置 Reduce 的个数，默认是 -1，-1 时会通过计算得到 Reduce 个数，一般 Reduce 的数量与表中的 BUCKETS 数量
一致
-- 有些时候环境无法满足时，通常设置为接近可用主机的数量即可
SET mapred.reduce.tasks=-1;

建表：

-- 创建表并设置分桶，BUCKETS 个数会决定在该表或者该表的分区对应的 HDFS 目录下生成对应个数的文件
-- 桶的个数尽可能多的拥有因数
CREATE TABLE IF NOT EXISTS t_citizen_bucket (
idcard int,
username string,
province int
)
CLUSTERED BY (idcard) SORTED BY (username DESC) INTO 12 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

把文件上传到hdfs，创建一个外部表关联，然后载入数据到分桶表：

INSERT OVERWRITE TABLE t_citizen_bucket SELECT * FROM t_citizen_e;

执行结果：

1. 数据处理目的和应用场景的区别

   • SELECT * FROM emp DISTRIBUTE BY deptno SORT BY sal DESC

     • 目的

       ：

       • 这条语句主要用于在查询emp表时，先按照部门编号（deptno）对数据进行划分（DISTRIBUTE BY），使得相同部门的数据尽量在一起。然后在每个部门的数据内部，按照工资（sal）降序（SORT BY sal DESC）进行排序。它侧重于在查询阶段对数据进行重新组织，以方便查看每个部门内工资从高到低的员工信息。

     • 应用场景

       ：

       • 适用于数据分析场景，例如分析每个部门内员工工资的分布情况。当需要快速了解每个部门的高薪员工情况或者进行部门内工资相关的统计（如部门内最高工资、最低工资等）时，这种查询方式很有用。

   • CLUSTERED BY (idcard) SORTED BY (username DESC) INTO 12 BUCKETS

     • 目的

       ：

       • 主要用于数据的存储组织。通过CLUSTERED BY按照身份证号（idcard）对数据进行聚类，将具有相同身份证号的数据放在一起。然后在每个聚类内部按照用户名（username）降序排列。最后，将所有数据划分到 12 个桶（INTO 12 BUCKETS）中。这种方式构建了数据的物理存储结构，使得数据在存储时就具有一定的规律。

     • 应用场景

       ：

       • 常用于大数据存储和管理场景。例如，在一个大型用户数据库中，通过这种方式存储数据可以提高基于身份证号的查询效率（因为相同身份证号的数据聚类在一起），同时桶的划分也有助于在分布式存储系统中进行高效的数据处理，如并行计算和分布式查询。

2. 数据操作方式的区别

   • 数据划分方式
     • SELECT * FROM emp DISTRIBUTE BY deptno SORT BY sal DESC
       • DISTRIBUTE BY是在查询过程中对数据进行逻辑划分，它是基于现有的数据表结构（根据deptno列）来划分数据，划分后的结果主要用于排序和展示。它不会改变数据的物理存储方式，每次执行查询时才会按照这种方式划分和排序。
     • CLUSTERED BY (idcard) SORTED BY (username DESC) INTO 12 BUCKETS
       • CLUSTERED BY是对数据的物理存储进行聚类操作，将数据按照idcard进行重新组织存储。这种聚类是持久化的存储结构改变，而不仅仅是查询阶段的操作。
   • 排序操作方式
     • SELECT * FROM emp DISTRIBUTE BY deptno SORT BY sal DESC
       • SORT BY是在查询阶段对已经DISTRIBUTE BY划分后的数据进行排序，排序结果只在本次查询的结果集中体现。如果再次查询，需要重新进行排序操作。
     • CLUSTERED BY (idcard) SORTED BY (username DESC) INTO 12 BUCKETS
       • SORTED BY是在数据进行聚类（CLUSTERED BY）之后，在每个聚类内部进行排序，并且这种排序是在数据存储时就完成的。数据在存储系统中的每个聚类内始终保持按照username降序排列的状态。
   • 数据划分程度
     • SELECT * FROM emp DISTRIBUTE BY deptno SORT BY sal DESC
       • 只涉及数据的逻辑划分（基于deptno）和查询结果的排序，没有像桶划分这样的物理存储层面的进一步划分操作。
     • CLUSTERED BY (idcard) SORTED BY (username DESC) INTO 12 BUCKETS
       • 不仅对数据进行聚类和排序，还将数据划分到 12 个桶中，这是一种更深入的物理存储组织方式，有助于在分布式环境中更好地管理和处理数据。

CLUSTERED BY 的使用场景

• 提高查询性能
  • 当经常需要根据特定列进行查询或聚合操作时，使用CLUSTERED BY可以将相关数据聚集在一起。例如，在一个电商订单数据表中，如果经常查询某个用户的所有订单，使用CLUSTERED BY (user_id)将用户订单数据聚类存储，可以加快查询速度。因为在读取数据时，具有相同用户 ID 的数据可以一次性读取，减少了磁盘寻道时间和数据传输时间。
• 优化聚合操作
  • 对于需要对数据进行聚合计算（如求和、计数、平均值计算等）的情况，聚类数据可以减少数据的移动和重新组织成本。例如，在一个销售数据表中，若要计算每个产品类别的销售总额，使用CLUSTERED BY (product_category)将数据聚类存储，可以使相同产品类别的数据相邻，在聚合计算时可以更高效地处理这些数据。
• 在分布式环境中的并行处理
  • 结合桶划分（INTO num_buckets BUCKETS），CLUSTERED BY可以将数据合理地分配到多个桶中。在分布式计算环境中，不同的桶可以分配到不同的计算节点进行并行处理。例如，在一个大数据分析任务中，将数据CLUSTERED BY (date)并划分到多个桶中，可以让每个桶代表一天的数据，然后将不同的桶分配到不同的计算节点，同时处理多天的数据，提高整体的计算效率。

2.数据抽样

​ 在大规模数据量的数据分析及建模任务中，往往针对全量数据进行挖掘分析时会十分耗时和占用集群资源，因此一般情况下只需要抽取一小部分数据进行分析及建模操作。Hive提供了数据取样（SAMPLING）的功能，能够根据一定的规则进行数据抽样，目前支持数据块抽样，分桶抽样和随机抽样。

1. 块抽样（Block Sampling）

​ 使用 TABLESAMPLE(n percent) 函数根据 Hive 表数据的大小按比例抽取数据，并保存到新的 Hive 表中。如：抽取原 Hive 表中 10% 的数据。

注意：测试过程中发现，SELECT 语句不能带 WHERE 条件且不支持子查询，可通过新建中间表或使用随机抽样解决。

块抽样语法如下：

-- 该方式允许 Hive 随机抽取 N 行数据，数据总量的百分比（n百分比）或 N 字节的数据
SELECT * FROM <Table_Name> TABLESAMPLE(N PERCENT|ByteLengthLiteral|N ROWS) s;

块抽样实践：

-- 数字与 ROWS 之间要有空格
CREATE TABLE IF NOT EXISTS t_citizen_block_sample AS SELECT * FROM t_citizen_e TABLESAMPLE(1000 ROWS);
CREATE TABLE IF NOT EXISTS t_citizen_block_sample AS SELECT * FROM t_citizen_e TABLESAMPLE(10 PERCENT);
CREATE TABLE IF NOT EXISTS t_citizen_block_sample AS SELECT * FROM t_citizen_e TABLESAMPLE(1M);

• TABLESAMPLE(nM) ：指定抽样数据的大小，单位为 M。
• TABLESAMPLE(n ROWS) ：指定抽样数据的行数，其中 n 代表每个 Map 任务均取 n 行数据，Map 数量可通过 Hive 表的简单查询语句确认（关键词：number of mappers: x)。
• TABLESAMPLE(n PERCENT) ：按百分比抽样数据，如果数据不超过 128M 还是全量数据。

缺点：不随机。该方法实际上是按照文件中的顺序返回数据，对分区表，从头开始抽取，可能造成只有前面几个分区的数据。
优点：速度快。

2.分桶抽样 (Smapling Bucketized Table)

Hive 中分桶其实就是根据某一个字段 Hash 取模，放入指定数据的桶中，比如将表 Table_1 按照 ID 分成 100 个桶，其算法是 hash(id) % 100，这样 hash(id) % 100 = 0 的数据会被放到第一个桶中，hash(id) % 100 = 1 的记录被放到第二个桶中。
分桶抽样语法如下：

-- 其中 x 是要抽样的桶编号，桶编号从 1 开始，colname 表示抽样的列，y 表示桶的数量
TABLESAMPLE(BUCKET x OUT OF y [ON colname])

x 表示从哪个 Bucket 开始抽取。y 必须是 Table 总 Bucket 数的倍数或者因子。Hive 会根据 y 的大小，决定抽样的比例。例如，Table 总共分了 64 个桶，当 y=32 时，抽取 64/32=2 个 Bucket 的数据，当 y=128 时，抽取 64/128=1/2 个 Bucket的数据。分桶抽样实践:

-- 假设 Table 总共分了 64 个桶
-- 取一桶且只取第一桶
SELECT * FROM t_citizen_bucket TABLESAMPLE(BUCKET 1 OUT OF 64 ON idcard);
-- 取第一桶的半桶
SELECT * FROM t_citizen_bucket TABLESAMPLE(BUCKET 1 OUT OF 128 ON idcard);
-- 取 16 桶，分别取第 2、6、10、14、18、22、26、30、34、38、42、46、50、54、58、62 桶
SELECT * FROM t_citizen_bucket TABLESAMPLE(BUCKET 2 OUT OF 4 ON idcard);

优点：随机且速度最快（不走 MR）。

3.随机抽样（Random() Sampling）

使用 rand() 函数进行随机抽样，Limit 关键字限制抽样返回的数据，其中 RAND() 函数前的 DISTRIBUTE 和 SORT 关键字可以保证数据在 Mapper 和 Reducer 阶段是随机分布的。
随机抽样语法如下：

-- 使用RAND()函数和LIMIT关键字来获取样例数据，使用DISTRIBUTE和SORT关键字来保证数据随机分散到Mapper和Reducer
-- SORT BY 提供了单个 Reducer 内的排序功能，但不保证整体有序
SELECT * FROM <Table_Name> DISTRIBUTE BY RAND() SORT BY RAND() LIMIT <N rows to sample>;
-- ORDER BY RAND() 语句可以获得同样的效果，但是性能会有所降低
SELECT * FROM <Table_Name> WHERE col=xxx ORDER BY RAND() LIMIT <N rows to sample>;

SELECT * FROM t_citizen_bucket DISTRIBUTE BY RAND() SORT BY RAND() LIMIT 10;

优点：真正的随机抽样。

缺点：速度慢。

3.事务

1. 原因

Hive 在设计之初时，是不支持事务操作的，因为 Hive 的核心目标是将已存在的结构化数据文件映射成表，然后提供基于表的SQL分析处理；是一款面向分析的工具。且映射的文件存在 HDFS 中，其本身也不支持随机修改文件的数据。这个定位就意味着早期的 HQL 本身就不支持 UPDATE、DELETE 语法，也就没有所谓的事务支持。
从 Hive 0.14 版本开始，引入了事务特性，能够在 Hive 表上实现 ACID 语义，包括 INSERT/UPDATE/DELETE/MERGE 语
句，以解决缓慢变化维表或部分数据不正确，需要更正的情况。Hive 3.0 又对该特性进行了优化，包括改进了底层的文件组织方式，减少了对表结构的限制，以及支持谓词下推和向量化查询。
最终 Hive 支持了具有 ACID 语义的事务，但做不到和传统关系型数据库那样的事务级别，仍有很多局限如：
不支持 BEGIN、COMMIT、ROLLBACK，所有操作自动提交；
事务表仅支持 ORC 文件格式；
表参数 transactional 必须为 true；
外部表不能成为 ACID 表，不允许从非 ACID 会话读取/写入 ACID 表（例如会话开启了事务，才可以查询事务表，否则查询就会报错）；
默认事务关闭，需要额外配置，具体如下。

2. 实践

事务功能相关参数如下：

# 开启 hive 并发
SET hive.support.concurrency=true;
# 配置事务管理类
SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
# 如果事务表配合分区分桶一起使用建议开启以下参数
# 开启分桶功能
SET hive.enforce.bucketing=true;
# 启用自动压缩
SET hive.compactor.initiator.on=true;
# 这里的压缩线程数必须大于 0，理想状态和分桶数一致
SET hive.compactor.worker.threads=2;
# 是否允许所有分区都是动态的，strict 要求至少包含一个静态分区列，nonstrict 则无此要求（默认 strict）
SET hive.exec.dynamic.partition.mode=nonstrict;

创建事务表：

CREATE TABLE IF NOT EXISTS test.t_user (
id int,
name string,
age int
)
STORED AS ORC
TBLPROPERTIES('transactional'='true');
-- 事务表并分区分桶
CREATE TABLE IF NOT EXISTS test.t_user (
id int,
name string,
age int
)
PARTITIONED BY (times string)
CLUSTERED BY (id) INTO 2 BUCKETS
STORED AS ORC
-- 开启事务
TBLPROPERTIES('transactional'='true')

因为使用的是事务表，所以写入数据时，INSERT 语句会在一个事务中运行。它会创建名为 delta 的目录，存放事务的信息和表的数据。
目录名称的格式为 delta_minWID_maxWID_stmtID ，即 delta 前缀、写事务的 ID 范围、以及语句 ID。

​ 具体来说：所有 INSERT 语句都会创建 delta 目录。UPDATE 语句也会创建 delta 目录，但会先创建一个 delete 目录，即先删除、后插入。delete 目录的前缀是 delete_delta。Hive 会为所有的事务生成一个全局唯一的 ID，包括读操作和写操作。针对写事务(INSERT、DELETE 等)，Hive 还会创建一个写事务 ID(Write ID)，该 ID 在表范围内唯一。写事务 ID 会编码到delta 和 delete 目录的名称中。语句 ID(Statement ID) 则是当一个事务中有多条写入语句时使用的，用作唯一标识。
文件中的数据会按 (originalTransaction, bucket, rowId) 进行排序，可以通过 row__id 虚拟列查看分桶信息。

• currentTransaction 当前的写事务 ID；
• originalTransaction 是该条记录的原始写事务 ID。对于 INSERT 操作，该值和 currentTransaction 是一致的。对
  于 DELETE，则是该条记录第一次插入时的写事务 ID；
• bucket 是一个 32 位整型，由 BucketCodec 编码，各个二进制位的含义为：
• 1-3 位：编码版本，当前是 001；
  4 位：保留；
  5-16 位：分桶 ID，由 0 开始。分桶 ID 是由 CLUSTERED BY 子句所指定的字段、以及分桶的数量决定的。该值和
  bucket_N 中的 N 一致；
  17-20 位：保留；
  21-32 位：语句 ID；
  举例来说，整型 536936448 的二进制格式为 00100000000000010000000000000000，即它是按版本 1 的格式编码
  的，分桶 ID 为 1；
• rowId 是一个自增的唯一 ID，在写事务和分桶的组合中唯一。

4.视图/物化视图

1. 视图

视图是一个虚拟的表，只保存定义，不实际存储数据，实际查询的时候改写 SQL 去访问实际的数据表。不同于直接操作数据表，视图是依据 SELECT 语句来创建的，所以操作视图时会根据创建视图的 SELECT 语句生成一张虚拟表，然后在这张虚拟表上做 SQL 操作。

-- 创建视图：查询部门经理人中的最低薪水
CREATE VIEW IF NOT EXISTS vw_emp_mgr_minsal (empno, minsal, deptno) AS
SELECT empno, sal minsal, deptno FROM emp WHERE empno IN (
SELECT mgr FROM emp WHERE mgr IS NOT NULL GROUP BY mgr
) ORDER BY sal LIMIT 1;

总结：

• Hive 中的视图是一种虚拟表，只保存定义，不实际存储数据；

• 通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图；

• 创建视图时，将冻结视图的结构，如果删除或更改基础表，则视图将失败；

• 视图是用来简化操作的，不缓冲记录，也不会提高查询性能。

• 优点：
  通过视图可以提高数据的安全性，将真实表中特定的列提供给用户，保护数据隐私；
  上层的业务查询和底层数据表解耦，业务上可以查的一张表，但是底层可能映射的是三张或多张表的数据；
  修改底层数据模型只需要重建视图即可，不需要上层业务修改业务逻辑；
  降低查询复杂度，优化查询语句（注意不是提高查询效率）。

• 缺点：
  无法再对视图进行优化，而且并没有提升查询速度，只是使上层的业务逻辑变得更清晰简洁。

2.物化视图

​ 物化视图 Materialized View，是一个包括查询结果的数据库对象，可以用于预先计算并保存表连接或聚集等耗时较多的操作结果。在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。
使用物化视图的目的就是通过预计算，提高查询性能，所以需要占用一定的存储空间。Hive 还提供了物化视图的查询自动重写机制（基于 Apache Calcite 实现）和物化视图存储选择机制，可以本地存储在 Hive，也可以通过用户自定义Storage Handlers 存储在其他系统（如 Apache Druid）。
Hive 引进物化视图的目的就是为了优化数据查询访问的效率，相当于从数据预处理的角度优化数据访问。Hive 从 3.0开始丢弃了索引的语法支持，推荐使用物化视图和列式存储文件格式来加快查询速度。

提示：物化视图只可以在事务表上创建。

1. 语法&实践

物化视图语法：

CREATE MATERIALIZED VIEW test.emp_analysis
AS
SELECT
deptno,
COUNT(*) cnt,
AVG(sal) avg_sal,
MAX(sal) max_sal,
MIN(sal) min_sal
FROM test.emp GROUP BY deptno;

​ 物化视图的创建过程会比较慢，因为 SELECT 查询执行的数据会自动落地。“自动”也即在 Query 的执行期间，任何用户对该物化视图是不可见的，执行完毕之后物化视图可用。默认情况下，创建好的物化视图可被用于查询优化器Optimizer 查询重写，在物化视图创建期间可以通过 Disable Rewrite 参数设置禁止使用。
创建成功后，通过 HDFS 发现物化视图是会真正创建表的。

通过上图可以看到速度非常快，这就是物化视图中，物化一词的体现。

2.刷新物化视图

1. 增量刷新

​ 当物化视图满足一定条件时，默认会执行增加刷新，即只刷新原始源表中的变动会影响到的数据，增量刷新会减少重建步骤的执行时间。要执行增量刷新，物化视图的创建语句和更新源表的方式都须满足一定条件：

• 物化视图只使用了事务表.
• 如果物化视图中包含 GROUP BY，则该物化视图必须存储在 ACID 表中，因为它需要支持 MERGE 操作。对于由 Scan-Project-Filter-Join 组成的物化视图，不存在该限制。

2. 定时刷新

​ 可以通过 SET hive.materializedview.rewriting.time.window=10min; 设置定期刷新，默认为 0min。该参数
也可以作为建表语句的一个属性，在建表时设置。

3. 全量刷新

​ 若只用 INSERT 更新了源表数据，可以对物化视图进行增量刷新。若使用 UPDATE、INSERT 更新了源表数据，那么只能进行重建，即全量刷新(REBUILD)。
​ 当数据源变更（新数据插入 Inserted、数据被修改 Modified），物化视图也需要更新以保持数据一致性，需要用户主动触发 Rebuild，命令如下：
注意：如果一张表创建了许多物化视图，那么在数据写入这张表时，可能会消耗许多机器的资源，比如数据带宽占
满、存储增加等等。

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;

注意：如果一张表创建了许多物化视图，那么在数据写入这张表时，可能会消耗许多机器的资源，比如数据带宽占满、存储增加等等。

2.查询重写

​ 物化视图创建后即可用于相关查询的加速，并且优化器能够利用其定义语义来使用物化视图自动重写传入的查询，从而加快查询的执行速度。即：用户提交查询 Query，若该 Query 经过重写后可以命中已经存在的物化视图，则直接通过物化视图查询数据返回结果，以实现查询加速。
​ 物化视图是否开启重写查询功能可以通过全局参数控制 set hive.materializedview.rewriting=true; ，默认为
true。除此之外，用户还可以选择性的控制指定的物化视图的查询重写机制，命令如下：

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;

实践：

假设我们要经常获取有关 1981 年之后按不同时期聘用的员工及其部门的信息，我们可以创建以下物化视图：

CREATE MATERIALIZED VIEW test.mv1
AS
SELECT empno, dname, hiredate
FROM test.emp AS e
INNER JOIN test.dept AS d ON e.deptno = d.deptno
WHERE hiredate >= '1981-01-01';

然后，以下查询提取有关 1982 年第一季度雇用的员工的信息（该查询会触发查询重写）:

SELECT empno, dname
FROM test.emp AS e
INNER JOIN test.dept AS d ON e.deptno = d.deptno
WHERE hiredate >= '1982-01-01' AND hiredate <= '1982-03-31';

在实际查询时，Hive 将使用物化视图重写传入的查询，包括实例化扫描之上的补偿谓词。查询重写的等价 SQL 如下：

SELECT empno, dname
FROM test.mv1
WHERE hiredate >= '1982-01-01' AND hiredate <= '1982-03-31';

此时会直接返回结果，没有走MR流程，速度很快。

5.高级查询

1. 一行变多行（行转列）

1,这个杀手不太冷,剧情-动作-犯罪
2,七武士,动作-冒险-剧情
3,勇敢的心,动作-传记-剧情-历史-战争
4,东邪西毒,剧情-动作-爱情-武侠-古装
5,霍比特人,动作-奇幻-冒险

将这个数据存入表。

需求是得到如下结果：

==EXPLODE()可以将Hive一行中复杂的Array或者Map结构拆分成多行，可以配合SPLIT()==函数一起使用。

SPLIT（）可以将数据转换成数组。

SELECT EXPLODE(SPLIT(TYPES,"-")) FROM t_movie1;
+------+
| col |
+------+
| 剧情 |
| 动作 |
| 犯罪 |
| 动作 |
| 冒险 |
| 剧情 |
| ... |
+------+

如果还想查看一下数组中这些电影类型属于哪个电影，需要配合侧视图LATERAL VIEW一起使用。

LATERAL VIEW 后面跟处理逻辑。

SELECT id,name,type
FROM t_movie1
-- 生成侧视图
LATERAL VIEW EXPLODE(SPLIT(types,"-")) movie_type AS type;
-- 得到结果

2.多行变一行（列转行）

1,这个杀手不太冷,剧情
1,这个杀手不太冷,动作
1,这个杀手不太冷,犯罪
2,七武士,动作
2,七武士,冒险
2,七武士,剧情
3,勇敢的心,动作
3,勇敢的心,传记
3,勇敢的心,剧情
3,勇敢的心,历史
3,勇敢的心,战争
4,东邪西毒,剧情
4,东邪西毒,剧情
4,东邪西毒,剧情
4,东邪西毒,武侠
4,东邪西毒,古装
5,霍比特人,动作
5,霍比特人,奇幻
5,霍比特人,冒险

将以上数据转换成原来的数据。

要用到==COLLECT_SET()和COLLECT_LIST()==函数。他们的返回结果是一个数组。

他们都可以将多行数据转成一行数据，区别是LIST的元素可以重复，SET不能重复。

SELECT id,name,
CONCAT_WS(':',COLLECT_SET(type)) AS type_set,
CONCAT_WS(':',COLLECT_LIST(type)) AS type_list
from movie2 group by id,name;
-- 结果
+-----+----------+-----------------+-----------------+
| id | name | type_set | type_list |
+-----+----------+-----------------+-----------------+
| 1 | 这个杀手不太冷 | 剧情:动作:犯罪 | 剧情:动作:犯罪 |
| 2 | 七武士 | 动作:冒险:剧情 | 动作:冒险:剧情 |
| 3 | 勇敢的心 | 动作:传记:剧情:历史:战争 | 动作:传记:剧情:历史:战争 |
| 4 | 东邪西毒 | 剧情:武侠:古装 | 剧情:剧情:剧情:武侠:古装 |
| 5 | 霍比特人 | 动作:奇幻:冒险 | 动作:奇幻:冒险 |
+-----+----------+-----------------+-----------------+

3.URL 解析

侧视图 LATERAL VIEW配合 PARSE_URL_TUPLE 函数可以实现 URL 字段的一列变多列。

原数据如下：

1,jingdong,https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为
2,taobao,https://s.taobao.com/search?q=苹果

SELECT a.id,a.name,b.protocol,b.host,b.path,b.query
FROM t_mail a
LATERAL VIEW PARSE_URL_TUPLE(url,'PROTOCOL','HOST','PATH','QUERY') b AS protocol, host, path, query;
-- 结果
+-------+-----------+-------------+----------------+----------+-----------------------------+
| a.id | a.name | b.protocol | b.host | b.path | b.query |
+-------+-----------+-------------+----------------+----------+-----------------------------+
| 1 | jingdong | https | search.jd.com | /Search | keyword=华为&enc=utf-8&wq=华为 |
| 2 | taobao | https | s.taobao.com | /search | q=苹果 |
+-------+-----------+-------------+----------------+----------+-----------------------------+

4.JSON 解析

​ JSON 数据格式是数据存储及数据处理中最常见的结构化数据格式之一，很多场景下公司都会将数据以 JSON 格式存储在 HDFS 中，当构建数据仓库时，需要对 JSON 格式的数据进行处理和分析，那么就需要在 Hive 中对 JSON 格式的数据进行解析读取。
​ Hive 为了实现 JSON 格式的数据解析，提供了两种解析 JSON 数据的方式，在实际工作场景下，可以根据不同数据，不同的需求来选择合适的方式对 JSON 格式数据进行处理。分别是：

• 使用 JSON 函数处理：
  • GET_JSON_OBJECT(json_txt,path)
    • 第一个参数：指定要解析的JSON字符串
    • 第二个参数：指定要返回的字段，通过$.column_name的方式指定
  • JSON_TUPLE(jsonStr,p1,p2,…,pn)
    • 第一个参数：指定要解析的JSON字符串
    • 第二个参数：指定要返回的第一个字段
    • …
• 使用JsonSerDe:建表时指定JSON序列化器，加载JSON文件到表中时会自动解析为对应的表格式。

1. JSON 函数

SELECT 
GET_JSON_OBJECT(user_json,'$.id') AS id,
GET_JSON_OBJECT(user_json,'$.username') AS username,
GET_JSON_OBJECT(user_json, '$.gender') AS gender,
GET_JSON_OBJECT(user_json, '$.age') AS age
FROM t_user_json;
-- 结果
+-----+-----------+---------+------+
| id | username | gender | age |
+-----+-----------+---------+------+
| 1 | admin | 男 | 18 |
| 2 | zhangsan | 男 | 23 |
| 3 | lisi | 女 | 16 |
+-----+-----------+---------+------+
SELECT 
JSON_TUPLE(user_json,'id','username','gender','age') AS (id,username,gender,age)
FROM t_user_json;
+-----+-----------+---------+------+
| id | username | gender | age |
+-----+-----------+---------+------+
| 1 | admin | 男 | 18 |
| 2 | zhangsan | 男 | 23 |
| 3 | lisi | 女 | 16 |
+-----+-----------+---------+------+

2.JsonSerDe

​ Hive 内置了很多的 SerDe 类，可以使用 JsonSerDe 序列化器来处理。建表时指定 JSON 序列化器，加载 JSON 文件到表中时会自动解析为对应的表格式。

-- 创建 t_user_json2 表并使用 JsonSerDe 序列化器。
CREATE TABLE IF NOT EXISTS t_user_json2 (
id int,
username string,
gender string,
age int
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;

6.窗口函数

​ 窗口函数是用于分析用的一类函数，要理解窗口函数要先从聚合函数说起。聚合函数是将某列中多行的值合并为一行，比如 SUM、COUNT 等。这类函数往往无法与单独列一起进行查询，比如：

-- MySQL 中可以执行，但是返回结果 ename 无意义
-- Hive 中直接报错：FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key
'ename'
SELECT ename, COUNT(*) FROM emp;

​ 而窗口函数可以为每行都返回一个值，就是在查询的结果上再多出一列，这一列可以是聚合，也可以是排序值。

​ 窗口函数和GROUP BY聚合函数的区别在于：窗口函数仅仅只会把结果附加到当前结果上，他不会对现有的行或列任何改变，而GROUP BY 对于各个GROUP只会保留一行聚合结果。

1.语法

窗口函数指的就是==OVER()==函数，其窗口时由一个OVER子句定义的多行记录。有三类：聚合型，分析型，取值型。

SELECT XX函数() OVER (PARTITION BY 用于分组的列 ORDER BY 用于排序的列 ROWS/RANGE BETWEEN 开始位置 AND 结束位置);

• XX函数() ：聚合型窗口函数/分析型窗口函数/取值型窗口函数
• OVER() ：窗口函数
• PARTITION BY ：后跟分组的字段，划分的范围被称为窗口
• ORDER BY ：决定窗口范围内数据的排序方式
• 移动窗口：
  • 移动方向：
    • CURRENT ROW:单前行
    • PRECEDING:向当前行之前移动
    • FOLLOWING：向当前行之后移动
    • UNBOUNDED:起点或终点（一般结合 PRECEDING，FOLLOWING 使用）
      • UNBOUNDED PRECEDING ：表示该窗口第一行（起点）
      • UNBOUNDED FOLLOWING: 表示该窗口最后一行（终点）
  • 移动范围
    • ROWS 和 RANGE

2.基本使用

1.OVER

如果 OVER 不提供分组方法，则将所有数据分为一组，如下：

SELECT ename, deptno,
AVG(sal) OVER()
FROM emp;
-- 以上将所有员工的薪水进行了平均计算，然后显示在每行数据后边。

2… PARTITION BY

​ 如果我们按部门对薪水求平均数呢？这就要使用 PARTITION BY 语句，PARTITION BY 的作用和 GROUP BY 是类似，用于分组。

SELECT ename,deptno,
AVG(sal) OVER(PARTITION BY deptno) AS avgsal
FROM emp;

3.ORDER BY

​ 在每个窗口（分组）内，如果我们想按每个人的薪水进行排序，可以使用 ORDER BY 子句，这里我们用 RANK() 指定序号，相同的薪水序号是一样的：

SELECT ename,deptno,
RANK() OVER(PARTITION BY deptno ORDER BY sal) AS salorder
FROM emp;

​ 当ORDER BY 与聚合函数一起使用时，会形成顺序聚合，如SUM聚合和ORDER BY 结合使用，就会实现类似于累计和的效果：

SELECT ename,deptno,
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) AS sumsal
FROM emp;
+---------+---------+-------+---------+
| ename | deptno | sal | sumsal |
+---------+---------+-------+---------+
| MILLER | 10 | 1300 | 1300 |
| CLARK | 10 | 2450 | 3750 |
| KING | 10 | 5000 | 8750 |
| SMITH | 20 | 800 | 800 |
| ADAMS | 20 | 1100 | 1900 |
| JONES | 20 | 2975 | 4875 |
| SCOTT | 20 | 3000 | 10875 |
| FORD | 20 | 3000 | 10875 |
| JAMES | 30 | 950 | 950 |
| MARTIN | 30 | 1250 | 3450 |
| WARD | 30 | 1250 | 3450 |
| TURNER | 30 | 1500 | 4950 |
| ALLEN | 30 | 1600 | 6550 |
| BLAKE | 30 | 2850 | 9400 |
+---------+---------+-------+---------+

4.总结

与 GROUP BY 的区别：

• 结果数据形式
  窗口函数可以在保留原表中的全部数据
  GROUP BY 只能保留与分组字段聚合的结果
• 排序范围不同
  窗口函数中的 ORDER BY 只是决定着窗口里的数据的排序方式
  普通的 ORDER BY 决定查询出的数据以什么样的方式整体排序
• SQL 顺序
  GROUP BY 先进行计算
  窗口函数在 GROUP BY 后进行计算

3.移动窗口（滑动窗口）

​ 刚刚我们了解了窗口函数的原理和用法，接下来我们了解一下移动窗口（又称滑动窗口）。之前的窗口是固定的分组窗口，但有时候我们需要根据数据的前后重新分配窗口，比如在股票、气温等数据场景下，数据的前后会有影响，就适用于移动窗口计算。
移动方向:

• CURRENT ROW :当前行
• PRECEDING:向当前行之前移动
• FOLLOWING:向当前行之后移动
• UNBOUNDED:起点或终点（一般结合 PRECEDING，FOLLOWING 使用）
  • UNBOUNDED PRECEDING ：表示该窗口第一行（起点）
  • UNBOUNDED FOLLOWING: 表示该窗口最后一行（终点）

移动范围：

• ROWS ：ROWS 后定义窗口从哪里开始（当前行也参与计算），与 BETWEEN 搭配可以表示范围。如果省略 BETWEEN仅指定一个端点，那么将该端点视为起点，终点默认为当前行。ROWS 会根据 ORDER BY 子句排序后，按分组后排序列的顺序取前 N 行或后 N 行进行计算（当前行也参与计算）。

  • ROWS 2 PRECEDING:窗口从当前行的前两行开始计算，计算到当前行；
  • ROWS BETWEEN 2 PRECEDING AND CURRENT ROW:等同上一句；
  • ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING:窗口从当前行开始计算，计算到当前行的后两行；
  • ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING ：窗口从当前行的前两行开始计算，计算到当前行的下一行，当前行也参与计算；
  • ROWS UNBOUNDED PROCEDING窗口从第一行计算到当行
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ：等同于上一句；
  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ：窗口从当前行计算到最后一行（终点）；

• RANGE ：RANGE 后定义窗口从哪里开始（当前行也参与计算），与 BETWEEN 搭配可以表示范围。如果省略BETWEEN 仅指定一个端点，那么将该端点视为起点，终点默认为当前行。RANGE 会根据 ORDER BY 子句排序后，按分组后排序列的值的整数区间取前 N 行或后 N 行进行计算（相同排序值的行都会被算进来，当前行也参与计算）。

  • RANGE 的窗口范围子句语法与 ROWS 一模一样，唯一的区别就在于 RANGE 会根据 ORDER BY 子句排序后，按分组后排序列的值的整数区间取前 N 行或后 N 行进行计算（相同排序值的行都会被算进来，当前行也参与计算）。

  • 整数区间解释：如果窗口范围子句为 RANGE BETWEEN 2 PRECEDING AND CURRENT ROW ，假设排序列的值为
    1 2 3 4 5 7 8 11，计算规则如下：
    1 => 1，因为前面没有任何行，所以只有自己
    2 => 1 + 2，因为前面只有一行，所以只加了 1
    3 => 1 + 2 + 3，前面两行加当前行
    4 => 2 + 3 + 4，前面两行加当前行
    5 => 3 + 4 + 5，前面两行加当前行
    7 => 5 + 6 + 7，因为 6 不存在，所以实际上只加了 5
    8 => 6 + 7 + 8，因为 6 不存在，所以实际上只加了 7
    11 => 9 + 10 + 11，因为 9 和 10 都不存在，所以实际上只有自己

​ 当 ORDER BY 缺少窗口范围子句时，窗口范围子句默认为： RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 。
​ 当 ORDER BY 和窗口范围子句都缺失时，窗口范围子句默认为： ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 。

select EMPNO,DEPTNO,SAL,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sumSal
from emp;
-- 结果见上图

select EMPNO,DEPTNO,SAL,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sumSal
from emp;
-- 结果见下图

4.聚合型窗口函数

• SUM() ：求和
• MAX() ：最大值
• MIN() ：最小值
• AVG() ：平均值
• COUNT() ：计算总数

5. 分析型窗口函数

• RANK():间断，相同值同序号，例如1、2、2、2、5.
• DENSE_RANK():不间断，相同值同序号，例如1、2、2、2、3.
• ROW_NUMBER():不间断，相同值不同序号，例如1、2、3、4、5（2、3 可能是相同的值）。

SELECT ename, deptno, sal,
RANK() OVER(PARTITION BY deptno ORDER BY sal) AS salorder1,
DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal) AS salorder2,
ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal) AS salorder3
FROM emp;
-- 结果
+---------+---------+-------+------------+------------+------------+
| ename | deptno | sal | salorder1 | salorder2 | salorder3 |
+---------+---------+-------+------------+------------+------------+
| MILLER | 10 | 1300 | 1 | 1 | 1 |
| CLARK | 10 | 2450 | 2 | 2 | 2 |
| KING | 10 | 5000 | 3 | 3 | 3 |
| SMITH | 20 | 800 | 1 | 1 | 1 |
| ADAMS | 20 | 1100 | 2 | 2 | 2 |
| JONES | 20 | 2975 | 3 | 3 | 3 |
| SCOTT | 20 | 3000 | 4 | 4 | 4 |
| FORD | 20 | 3000 | 4 | 4 | 5 |
| JAMES | 30 | 950 | 1 | 1 | 1 |
| MARTIN | 30 | 1250 | 2 | 2 | 2 |
| WARD | 30 | 1250 | 2 | 2 | 3 |
| TURNER | 30 | 1500 | 4 | 3 | 4 |
| ALLEN | 30 | 1600 | 5 | 4 | 5 |
| BLAKE | 30 | 2850 | 6 | 5 | 6 |
+---------+---------+-------+------------+------------+------------+

• PERCENT_RANK() ：计算小于当前行的值在所有行中的占比，类似百分比排名。可以用来计算超过了百分之多少的人。计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：小于 x 的行数 / 窗口或 PARTITION 分区内的总行数。其中 x 等于 ORDER BY 子句中指定的列的当前行中的值。
• CUME_DIST() ：计算小于等于当前行的值在所有行中的占比。
• NTILE(N) ：如果把数据按行数分为 N 份，那么该行所属的份数是第几份。注意：N 必须为 INT 类型。

6.取值型窗口函数

LAG(COL, N, DEFAULT_VAL)：往前取第N行数据，没有数据的话用DEFAULT_VAL代替。

LEAD(COL ,N ,DEFAULT_VAL):往后取第N行数据，没有数据的话用DEFAULT_VAL代替。

FIRST_VALUE(EXPR): 分组内第一个值，但不是真正意义上第一个，而是截止到当前行的第一个。

LAST_VALUE(EXPR)：分组内最后一个值，但是不是真正意义上的最后一个，而是截至到当前行的最后一个。

SELECT ename, deptno, sal,
FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal) AS firstsal,
LAST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal) AS lastsal,
LAG(sal, 2, 1) OVER(PARTITION BY deptno ORDER BY sal) AS lagsal,
LEAD(sal, 2, -1) OVER(PARTITION BY deptno ORDER BY sal) AS leadsal
FROM emp;
+---------+---------+-------+-----------+----------+---------+----------+
| ename | deptno | sal | firstsal | lastsal | lagsal | leadsal |
+---------+---------+-------+-----------+----------+---------+----------+
| MILLER | 10 | 1300 | 1300 | 1300 | 1 | 5000 |
| CLARK | 10 | 2450 | 1300 | 2450 | 1 | -1 |
| KING | 10 | 5000 | 1300 | 5000 | 1300 | -1 |
| SMITH | 20 | 800 | 800 | 800 | 1 | 2975 |
| ADAMS | 20 | 1100 | 800 | 1100 | 1 | 3000 |
| JONES | 20 | 2975 | 800 | 2975 | 800 | 3000 |
| SCOTT | 20 | 3000 | 800 | 3000 | 1100 | -1 |
| FORD | 20 | 3000 | 800 | 3000 | 2975 | -1 |
| JAMES | 30 | 950 | 950 | 950 | 1 | 1250 |
| MARTIN | 30 | 1250 | 950 | 1250 | 1 | 1500 |
| WARD | 30 | 1250 | 950 | 1250 | 950 | 1600 |
| TURNER | 30 | 1500 | 950 | 1500 | 1250 | 2850 |
| ALLEN | 30 | 1600 | 950 | 1600 | 1250 | -1 |
| BLAKE | 30 | 2850 | 950 | 2850 | 1500 | -1 |
+---------+---------+-------+-----------+----------+---------+----------+

RTITION BY deptno ORDER BY sal) AS leadsal
FROM emp;
±--------±--------±------±----------±---------±--------±---------+
| ename | deptno | sal | firstsal | lastsal | lagsal | leadsal |
±--------±--------±------±----------±---------±--------±---------+
| MILLER | 10 | 1300 | 1300 | 1300 | 1 | 5000 |
| CLARK | 10 | 2450 | 1300 | 2450 | 1 | -1 |
| KING | 10 | 5000 | 1300 | 5000 | 1300 | -1 |
| SMITH | 20 | 800 | 800 | 800 | 1 | 2975 |
| ADAMS | 20 | 1100 | 800 | 1100 | 1 | 3000 |
| JONES | 20 | 2975 | 800 | 2975 | 800 | 3000 |
| SCOTT | 20 | 3000 | 800 | 3000 | 1100 | -1 |
| FORD | 20 | 3000 | 800 | 3000 | 2975 | -1 |
| JAMES | 30 | 950 | 950 | 950 | 1 | 1250 |
| MARTIN | 30 | 1250 | 950 | 1250 | 1 | 1500 |
| WARD | 30 | 1250 | 950 | 1250 | 950 | 1600 |
| TURNER | 30 | 1500 | 950 | 1500 | 1250 | 2850 |
| ALLEN | 30 | 1600 | 950 | 1600 | 1250 | -1 |
| BLAKE | 30 | 2850 | 950 | 2850 | 1500 | -1 |
±--------±--------±------±----------±---------±--------±---------+

标签：--,分区,高级,视图,查询,Hive,数据,SELECT
From： https://blog.csdn.net/m0_63924864/article/details/143613539