HIVE 基础-->函数操作

Hive

一、表SQL操作

1. 通过select数据集创建表

--只能是内部表，不支持分区，分桶
--通过select数据集创建表语法格式
create table table_name [stored as orc]  
as
select  ......

--例如
create table inner_test1 as
select word,num from word_table;

2 复制一个表(空表)

--只复制表结构，不复制表数据
CREATE TABLE 要复制的表名 LIKE 被复制的那张表;
--例如
CREATE TABLE empty_key_value_store LIKE key_value_store;

3.显示表列表

--显示所有表
SHOW TABLES;

--按条件（正则表达式）显示表
SHOW TABLES '.*s';
show tables 'user*';

-- 最通用的写法
show tables like '*user*';

-- 显示函数
show fuctions like '*count*';

4. 删除表

--外部表:  只删除元数据，不删除数据目录， (加PURGE也不删)
--内部表:  只删除表的元数据，表的数据放到hdfs回收站

--删除表
DROP TABLE [IF EXISTS] table_name [PURGE]; 
--例如   如果增加PURGE则不进回收站数据直接删除，不能恢复； //PURGE 不要加
drop table inner_test1 ;

5 清除表（只针对内部表）

-- 1）删除表和删除分区数据，不删除元数据（表结构）
-- 2）外部表不支持使用TRUNCATE语句。

--清除表语法:
TRUNCATE TABLE table_name [PARTITION partition_spec];
--例如:
TRUNCATE TABLE inner_test1;

二、常用修改表操作

	内部表	外部表
修改表名	元数据和hdfs目录均修改	只修改元数据,hdfs目录不改
增加表分区	自动创建hdfs目录	自动创建hdfs目录
删除表分区	元数据和hdfs目录均被删除	只删除元数据,hdfs目录不删除
修改分区路径	元数据修改,但目标分区路径的hdfs目录没有创建	元数据修改,但目标分区路径的hdfs目录没有创建
表分区重命名	元数据和hdfs目录均修改	只修改元数据,hdfs目录不修改

1.改表名

--改表名
ALTER TABLE 旧的表名 RENAME TO 新的表名;

--内部表修改了表名之后，表对应的存储文件地址也跟着改，相当于作了HDFS的目录重命名。
--外部表不会改对应的location地址。

--内部表修改表名
ALTER TABLE user_info RENAME TO user_info_2;

--外部表修改表名
alter table ext_test rename to ext_test_2;

2. 修改表属性

--语法
ALTER TABLE 表名 SET TBLPROPERTIES table_properties(''='');

--示例  修改表的描述
alter table ext_test set tblproperties ('comment' = 'EXTERNAL table a!');
-- 内部表转外部表
alter table table_name set TBLPROPERTIES ('EXTERNAL'='TRUE'); 
  -- 外部表转内部表
alter table table_name set TBLPROPERTIES ('EXTERNAL'='FALSE');

3 增加表分区

--内部表增加表分区，自动创建目录。
--外部表增加表分区，自动创建目录。

-- 1. 分区表外部表
CREATE EXTERNAL TABLE ext_task(
word STRING,
num INT
)
PARTITIONED BY (taskname STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/user/xsmpf/hive32/ext_task';

--2. 分区表添加分区     字段值=指定hdfs目录位置
--alter table ext_task add partition(分区字段名='字段值') location '指定hdfs目录位置';

alter table ext_task add partition(taskname='wordcount') location 'wordcount';

--3. Navicat 查询具体表的分区目录

select t1.`NAME`, t2.TBL_NAME,t4.PART_NAME, t3.LOCATION from DBS t1, TBLS t2 , SDS t3 , `PARTITIONS` t4 
where t1.DB_ID=t2.DB_ID and 
            t4.SD_ID = t3.SD_ID AND 
            t2.TBL_ID = t4.TBL_ID and 
            t1.`NAME` = '数据库名' AND 
            t2.TBL_NAME like '%表名' 

4 删除分区

--内部表删除分区，删除元数据和 分区数据        
--外部表删除分区不删除分区对应的目录。 只删除元数据, 分区数据不删除

--删除表分区的语法格式
ALTER TABLE 表名 DROP [IF EXISTS] PARTITION(分区字段名 = 分区字段值);

--例如
alter table inner_task DROP IF EXISTS partition(taskname='sortword');

5 修改表或分区的路径

-- 内部表修改分区路径，元数据修改，但hdfs目录没有创建，等导入数据时创建，或者自己主动创建。
-- 外部表修改分区路径，元数据修改，但hdfs目录没有创建，等导入数据时创建，或者自己主动创建。

-- 无论是外部表还是内部表，修改分区路径，需要自己手动创建路径，或者等导入数据时创建。
--修改完分区后,原来的分区还会存在???

--修改表或分区路径: 语法:
ALTER TABLE 表名 (字段名= 字段值) SET LOCATION "要修改的分区路径";

--内部表修改分区路径               location的值可以和分区的名字不一样
alter table inner_task PARTITION  (taskname='wordcount') set location "hdfs://ns1/hive/warehouse/class12.db/inner_task/wordcount1";

6 分区重命名

--如果是内部表，分区重命名，分区对应的 元数据和hdfs目录均修改会跟着改变，
--外部表不会。 --只修改元数据,hdfs目录不修改

--分区重命名语法格式
ALTER TABLE 表名 PARTITION (字段名= 字段值) RENAME TO PARTITION (字段名= 字段值);

--内部表分区重命名会改变分区对应的地址
alter table inner_task partition (taskname='maxword') rename to partition (taskname='maxword01');

--外部表分区重命名不会改变分区对应的地址
alter table ext_task partition (taskname='maxword') rename to partition (taskname='maxword01');

7 增加表字段

--增加表字段、使用新列集合替换现有数据列的语法格式
ALTER TABLE 表名 ADD|REPLACE COLUMNS (字段名 字段类型 , ...)

--1）ADD COLUMNS 可以在表列的最后和分区字段前面增加字段。

--例如
alter table ext_test_c add columns (test_col string);

8 修改表字段

--语法格式
ALTER TABLE 表名 CHANGE [COLUMN] 字段源名 字段新名 新的字段类型 ;

--示例：
alter table ext_test_c CHANGE  COLUMN col_new col_new2 string;

三、视图操作

--只有逻辑视图，没有物化视图； 
--视图只能查询，
-- 无论 使用视图还是不使用视图，执行的任务数不变。主要是为了简化SQL的编写；

--视图在创建时候，只是保存了一份元数据，
--在hdfs中没有体现，当查询的时候，才执行视图对应的那些子查询，如果子查询简单，不会生成MapReduce任务；

1.创建视图

--语法
CREATE VIEW [IF NOT EXISTS] 视图名称 
AS SELECT ...;--查询语句,把查询结果装进视图

-- 查看创建视图的语句
show create table xxx;

--查看列表
show tables;

--例子
create view tt_country_view
as 
select count(1) n,country from user_install_status_limit group by country;

-- 创建视图
create view query1 as select country,count(distinct aid) c from user_install_status_limit group by country order by c desc limit 1;
-- 查询视图
select * from query1;

四、数据加载(hive 数据导入与导出实现)

1.语法

-- local  filepath  overwrite
--1 语法:
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename  PARTITION(分区名=分区值)

--1）hive 的数据加载不会对本地数据文件做任何处理，只是将文件或目录中的所有文件拷贝到表定义的数据目录。
--2）filepath 可以是目录，但该目录下不能包含子目录。
--3）指定LOCAL本地文件上传，如果没有指定LOCAL，则使用表一样的文件系统 (本地文件不删除)。
--4）文件加载hive没有做严格校验，文件格式和压缩选项等匹配需要用户自己保证。
--5）分区表要指定具体加载数据分区。
--6）如果指定OVERWRITE会覆盖相应表数据或分区数据，相当于 rm 原有目录数据，然后上传新数据文件HDFS执行MV     操作。

2 .将hdfs中的文件load到表中(move)

hdfs load

--mv操作
--分区表不创建分区,导入时自动创建分区,并创建分区目录
--*******如果把hdfs本地数据加载到分区下,需要先手动创建分区,否则会自动创建 分区名=分区值 的的分区******

-- 以覆盖或追加的方式 ， 从linux本地 copy文件到表或表分区里
LOAD DATA LOCAL INPATH 'filepath' [OVERWRITE] INTO TABLE tablename PARTITION(分区名=分区值)

--以覆盖或追加的方式 ， 从hdfs move 文件到表或表分区里
LOAD DATA INPATH 'filepath' [OVERWRITE] INTO TABLE tablename  PARTITION(分区名=分区值)

3 .将本地文件load到表中

本地load

--copy操作
--导入时会自动创建分区,并创建分区目录,如果有分区和分区目录,导入时也不用那个分区目录,自己创建分区目录
--本地load到表中时，即使给表创建分区，也不用创建分区目录，load时会自动创建目录

--例子:
--添加partition
alter table ext_task add IF NOT EXISTS partition(taskname='wordcount04') location 'wordcount04';

--本地文件load到表中，相当于将本地文件上传到hdfs中，本地文件不删除
load data local INPATH '/home/panniu/hive_test/word' overwrite into table ext_task PARTITION (taskname='wordcount04'); 

4.通过select加载数据

--1）当加了 IF NOT EXISTS，如果存在分区就跳过下面的select语句。
--2）INSERT OVERWRITE 会覆盖表或分区数据，但覆盖分区时增加IF NOT EXISTS，如果分区已经存在则不会覆盖。
--3）INSERT INTO 向表或分区追加数据，不影响历史数据。

--通过select，将select数据覆盖表或分区的语法格式
INSERT OVERWRITE TABLE 表名 PARTITION(分区名= 分区值)  [IF NOT EXISTS]] 
select_statement1 FROM from_statement;

--通过select，将select数据追加到表或分区的语法格式
INSERT INTO TABLE tablename1 PARTITION(分区名= 分区值) 
select_statement1 FROM from_statement;

--例子:

--1.添加分区
alter table ext_task add IF NOT EXISTS partition(taskname='wordcount05') location 'wordcount05';

--2.导入数据
insert overwrite table ext_task partition(taskname='wordcount05')
select
word,
num
from ext_task
where taskname='wordcount01';

五、hive分区

1.动态分区

--至少有一个静态分区才能用动态分区

-- hive默认不开启动态分区
--开启动态分区
set hive.exec.dynamic.partition=true;  
--这个属性默认是strict，即限制模式，strict是避免全分区字段是动态的，必须至少一个分区字段是指定有值即静态的，且必须放在最前面。设置为nonstrict之后所有的分区都可以是动态的了。
set hive.exec.dynamic.partition.mode=nonstrict;

-----------------------------------------------------------------------------------------

--表示每个节点生成动态分区的最大个数，默认是100
set hive.exec.max.dynamic.partitions.pernode=10000;  

--表示一个DML操作可以创建的最大动态分区数，默认是1000
set hive.exec.max.dynamic.partitions=100000;

--表示一个DML操作可以创建的最大文件数，默认是100000
set hive.exec.max.created.files=150000

--将select数据，覆盖到表的动态分区
insert overwrite table table1 partition (ds, hr)  --会自动创建 ds  和   hr 的动态分区
select key, value, ds, hr FROM table2 WHERE ds is not null;

六、将数据写入文件系统(HDFS)

1.查询表数据导出到某个文件【linux本地/ hdfs】

--语法格式
INSERT OVERWRITE [LOCAL] DIRECTORY '要写入的目录'
  [ROW FORMAT row_format] [STORED AS file_format] 
  SELECT ... FROM ...   --sql语句

2.示例 往本地/HDFS 目录倒

-- 将select的数据写入到hdfs文件中，多层目录会帮你创建

-- 查询ext_task 表中分区为wordcount03 的数据，以AVRO 格式写入到hdfs指定目录
insert overwrite directory 'hdfs://ns1/user/su/hive/output1' STORED AS AVRO
select r_key, r_val from inner_task where taskname='wordcount';

-- 查询ext_task 表中分区为wordcount03 的数据，以AVRO 格式写入到本地目录中
insert overwrite local directory  '/home/hadoop/myhive/output1' STORED AS AVRO 
select r_key, r_val from inner_task where taskname='wordcount';

3.avro、orc文件格式，如果不加分区导出时，会报错

--如果是分区表，导出所有数据时，导出的格式是avro、orc文件格式，如果不加分区导出时，会报错。

1).将数据写入多个文件

--语法格式
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 row_format
select_statement1 where 
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 row_format
select_statement2 where ] ...
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]
--1）导出到文件系统的数据都序列化成text非原始类型字段会序列化成json，导出文件以^A分隔 \n结尾的文本数据。
--2）INSERT OVERWRITE到HDFS目录，可以通过MRjob实现并行写入。在集群上抽取数据不仅速度快，而且还很方便。
--3）批量导出多个文件，需要导出文件的类型一致，如果一个是avro，一个是text，报错。

-- 从一张表中导出两个文件到不同的路径
-- 分区表：需要两个分区一致，在一个分区下，来导出不同条件的数据 

--举例 分区表：
from ext_task 
insert overwrite directory 'hdfs://ns1/user/xsmpf/hive32/output_avro5' STORED AS AVRO 
select 
word,
num 
where taskname='wordcount01' and word='aa' --where 分区名= 分区值
insert overwrite local directory '/home/xsmpf/hive_test/output_avro4' STORED AS AVRO 
select 
word,
num 
where taskname='wordcount01' and word='bb';

--举例 不是分区表：
insert overwrite directory 'hdfs://ns1/user/xsmpf/hive32/output_avro5' STORED AS AVRO 
select 
id,name
where age=12
insert overwrite local directory '/home/xsmpf/hive_test/output_avro4' STORED AS AVRO 
select 
id,name
where age=13

--********坑坑坑******
--如果多个输出目录，导出的格式不同，也报错。      

2).通过hive -e 命令导出

hive -e "sql"  --> 推荐 小规模SQL

-- 后台运行，错误信息不关心，放到无底洞里   /dev/null 
nohup hive -e "use db1;select r_key, r_val from inner_task where taskname='wordcount';" 1>./out1 2>/dev/null &

-- 导出的数据导出到out1
-- 错误的信息，导出到err.log
nohup hive -e "use db1;select r_key, r_val from inner_task where taskname='wordcount';" 1>./out1 2>./err.log &

3).hive -f sqlfile 大规模SQL 执行sql文件

vim test.sql --把sql放到文件里面去  --test.sql --文件名

hive -f ./test.sql 1>./out2 2>>./err.log --执行这个文件

七、Explain慢查询

--以下顺序不是绝对的，会根据语句的不同，有所调整

--Map阶段：
    1.执行from加载，进行表的查找与加载
    2.执行where过滤，进行条件过滤与筛选
    3.执行select查询：进行输出项的筛选
    4.执行group by分组：描述了分组后需要计算的函数
    5.map端文件合并：map端本地溢出写文件的合并操作，每个map最终形成一个临时文件。
                   然后按列映射到对应的Reduce阶段：

--Reduce阶段：
    1.group by：对map端发送过来的数据进行分组并进行计算。
    2.select：最后过滤列用于输出结果
    3.limit排序后进行结果输出到HDFS文件

八、HIVE连接查询 (Hive Join)

--hive可以join两个以上的表。          
    --如果两个以上join，join的字段都一样，类型也一样，那就只生成一个mapreduce任务。
--hive只支持等连接，外连接。

1.Hive Join 等值连接

--hive只支持等值连接，外连接。
--hive不支持非相等的join条件（通过其他方式实现，如left outer join），因为它很难在map/reduce job实现这样的条件。           
hive可以join两个以上的表。

--1.inner join

-- 关闭mapjon inner join 
set hive.auto.convert.join=false;
set hive.ignore.mapjoin.hint=false;
select * from test_a a inner join test_b b on a.id=b.id;

--等值连接：inner join
select * from test_a a inner join test_b b on a.id=b.id;
--左连接：left   join  空值展示位null 
select a.*,b.* from test_a a left join test_b b on a.id=b.id;
--右连接: right join  空值展示为null
select a.*,b.* from test_a a right join test_b b on a.id=b.id;
--full join 全连接 
select a.*,b.* from test_a a full join test_b b on a.id=b.id;

2.实现非等值连接(重要)

-- 查询a有，b没有，用 left join  + is null
--在得到join结果后，再根据where条件筛选

select a.*,b.* from test_a a 
left join test_b b 
on a.id=b.id 
where b.id is null;

3.多表join

--三表inner join 
select a.*,b.name,c.name from test_a a
inner join test_b b on a.id=b.id
inner join test_c c on b.id=c.id;
--join字段相同，只生成一个任务

--join字段不同，不一定生成一个任务

-- 关闭mapjon
set hive.auto.convert.join=false;
set hive.ignore.mapjoin.hint=false;
select * from test_a a 
inner join test_b b on a.id=b.id 
inner join test_c c on a.name=c.name;

计算百分比

select t1.country, t1.num, t2.num, concat(round(t1.num/t2.num * 100,2), '%') from 
(select country, count(*) as num, 'link' as link from user_install_status_limit group by country) t1 
inner join 
(select count(*) as num, 'link' as link from user_install_status_limit) t2 
on t1.link=t2.link;

要避免的查询操作

--笛卡尔积的SQL
select * from test_a inner join test_b;

4.hive查询严格模式

-- 设置hive的严格校验(建议 很麻烦 确实可以帮助我们进行数据上的查询失误)
set hive.mapred.mode=strict;

设置这个参数，可以限制以下情况：
1）限制执行可能形成笛卡尔积的SQL；
2）partition表使用时不加分区；
3）order by全局排序的时候不加limit的情况；

-- 取消严格校验模式
set hive.mapred.mode=nonstrict;

5.无条件关联 full outer join

--为每个表加一个相同的字段名称 jc 进行关联
select a.*,b.* from test_a a
full outer join
test_b b
on a.id=b.id;

6.union的使用(不去重) unionall结果去重

--用 left join union right join 实现 full outer join  字段名必须打全
select a.id aid,a.name aname, b.id bid,b.name bname from test_a a left join test_b b on a.id=b.id
union
select a.id aid,a.name aname, b.id bid,b.name bname from test_a a right join test_b b on a.id=b.id;

hive执行优化

#增加reducer任务数量
set mapred.reduce.tasks=20;

#在同一个sql中的不同的job是否可以同时运行,默认为false  开启并行运算
set hive.exec.parallel=true;

#增加同一个sql允许并行任务的最大线程数
set hive.exec.parallel.thread.number=8;

#设置reducer内存大小
set mapreduce.reduce.memory.mb=4096;
set mapreduce.reduce.java.opts=-Xmx3584m;

九、连接查询的优化( map端的join)

mapjoin semijoin 自动开启

--类似于mapreduce的semijoin，在map端join，无reduce。
--小表放内存，与大表的数据在map端进行join，
--hive默认开启map端join。

1.配置参数 -- 开启自动mapjoin

-- 开启自动mapjoin
-- 将小表刷入内存中，默认是true 
set hive.auto.convert.join=true;
set hive.ignore.mapjoin.hint=true; 

--hive 0.11之后，在表的大小符合设置时
-- 1.是否自动转换为mapjoin
hive.auto.convert.join.noconditionaltask=true

-- 2.是否将多个mapjoin合并为一个这个参数控制多大的表可以放进内存，默认值为10000000L（10M），该值表示可      以被转换为哈希映射的表大小的总和。  
-- 刷入内存表的大小(字节),根据自己的数据集加大
hive.auto.convert.join.noconditionaltask.size=10000

-- 3.小表的最大文件大小，默认为25000000，即25M   
--设置太大也不会校验，所以要根据实际情况来设置
hive.mapjoin.smalltable.filesize=25000000

1.1不开启mapjoin 设置：

set hive.auto.convert.join=false;
set hive.ignore.mapjoin.hint=false;

2.在设置成false 或 true时，可以手动的 /+ MAPJOIN(c) / 。

--MAPJOIN(小表的别名)
--其中：/*+ MAPJOIN(c) */ 这里面的c，就代表那个表的数据放入内存

--示例:
select /*+ MAPJOIN(c) */ 字段名 from 数据库名.表名 u
inner join country_dict c
on u.country=c.code
where u.dt='20141228'
limit 10;

--当用mapjoin实现左连接时，必须右表数据放内存；(数据库名.表名)
select /*+ MAPJOIN(u) */  字段名 from 数据库名.表名 u
left join country_dict c
on u.country=c.code
where u.dt='20141228'
limit 10;

--当用mapjoin实现右连接时，必须左表数据放内存；

3.如果是自动mapjoin,在使用MAPJOIN时，需要注意：

1、LEFT OUTER JOIN的左表必须是大表;
2、RIGHT OUTER JOIN的右表必须是大表； 
3、INNER JOIN左表或右表均可以作为大表；
4、FULL OUTER JOIN不能使用MAPJOIN；
5、MAPJOIN支持小表为子查询；
6、使用MAPJOIN时需要引用小表或是子查询时，需要引用别名；
7、在MAPJOIN中，可以使用不等值连接或者使用OR连接多个条件；
mapjoin里写的是小表，且left outer join时小表写在join的后面；
hive中使用mapjoin有时可以大大提高sql语句的执行效率。
--其原理是：它会把小表全部读入内存中，在map的时候直接拿另外一张表的数据和内存中表的数据做匹配，进行join操作，这样省去了reduce。
在“关联操作中有一个表非常小，另一个表很大”的场景下，mapjoin就不会由于数据倾斜而导致某个reduce上落数据太多而失败；

十、hive 分组查询

1.GROUPING SETS

--grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。
--将多个group by 放在一个括号里面

--GROUP BY a, b GROUPING SETS ((a, b), a, b, ())
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ((a, b), a, b, ())

--等于
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b 
UNION ALL
SELECT a, null, SUM(c) FROM tab1 GROUP BY a
UNION ALL
SELECT null, b, SUM(c) FROM tab1 GROUP BY b 
UNION ALL
SELECT null, null, SUM(c) FROM tab1

2.select中的条件判断

--类似三目运算符  条件表达式 ? true的结果 : false的结果
IF(条件表达式, true的结果, false的结果)

if(a is null,'all',a)

--COALESCE
--返回参数中的第一个非空值
COALESCE( value1,value2,… )     
--
select coalesce(null,null,5,null,6) from dual;

3.with cube

--with cube是 需要 group by 字段中所有key的组合 

with cube = GROUPING SETS ((a,b,c),(a,b),(b,c),(a,c),a,b,c,())

--with cube 代替了GROUPING SETS全部字段
SELECT if(age is not null, age, 'ALL'), 
case when sex is not null then sex else 'ALL' end as age,
count(id) 
FROM student_grouping 
GROUP BY age, sex with cube;

-- GROUP BY age, sex
-- with cube (age,sex),age,sex,()

4.with rollup

--右侧递减的意思就是从左到右 依次减去最右面的;
GROUPING SETS ( (age,sex),age,() );   --从右侧每次减少一个参数

-- 相当于按右侧递减的顺序group by
SELECT if(age is not null, age, '-'), 
case when sex is not null then sex else '-' end as age,
count(id) FROM student_grouping GROUP BY age, sex 
with rollup;
--等于
SELECT if(age is not null, age, '-'), 
case when sex is not null then sex
else '-'
end as age,
count(id) FROM student_grouping GROUP BY age, sex
--GROUPING SETS ( (age,sex),age,() );

十一、hive 排序查询

1.order by

-- 保证全局结果有序 (一个ruduce) 
--order by只能有一个reducer，设置了reducer也不起作用
select * from user_install_status_limit order by uptime limit 10;

2.sort by

--不是全局排序，其在数据进入reducer前完成排序。 因此，如果用sort by进行排序，并且设置        mapred.reduce.tasks>1， 则sort by只保证每个reducer的输出有序，不保证全局有序

--sort by只能保证单个文件内有序，如果设置成一个reducer那作用和order是一样的

3.cluster by 集群排序

 --cluster by column = distribute by column + sort by column  
 --（注意，都是针对column列，且采用  默认ASC，不能指定排序规则为asc 或者desc）

4.distribute by

--distribute by + sort by 多reduce排序 =cluster by

select * from user_install_status distribute by country sort by uptime desc;

--distribute by and sort by = cluster by 
--如果distribute by和sort by字段一样 可以用 cluster by 

--例如
select * from join_table1 cluster by country desc; --正序排序
-- cluster by country  = distribute by + sort by

--倒序排序 先sex排序,再cluster by  排序
select * from join_table1 cluster by sex country desc;

--排序的时候一次排序只能排一个
--想实现降序，需要用  distribute by country sort by country组合

十二、hive 的窗口函数

--把原本的查询结果分成多分查询结果之后再进行排序
语法：主要是 over ( PARTITION BY (根据某条件分组，形成一个小组)….ORDER BY(再组内进行排序) …. ) 

三种 : 序列函数

--row_number：
  会对所有数值，输出不同的序号，序号唯一且连续，如：1、2、3、4、5。
--rank：
  会对相同数值，输出相同的序号，而且下一个序号间断，如：1、1、3、3、5。
--dense_rank：
  会对相同数值，输出相同的序号，但下一个序号不间断，如：1、1、2、2、3。

ROW_NUMBER() 不固定行数

row_number：会对所有数值，输出不同的序号，序号唯一且连续，如：1、2、3、4、5。

-- rn_id 按照性别分组，按照id排序
select sex,name,age,id,
row_number() over(partition by sex order by age desc) as rn_id,
rank() over(partition by sex order by age desc) as rank_id,
dense_rank() over(partition by sex order by age desc) as dense_id
from student_grouping;

--数据会从 每个partitioner 从1 开始重新排序 

over中partition by和distribute by区别

1）partition by [key..] order by [key..]只能在窗口函数中使用，而distribute by [key...] sort by [key...]在窗口函数和select中都可以使用。

2）窗口函数中两者是没有区别的

3）where后面不能用partition by

-- partition  by  不在窗口函数中使用-->报错

ROWS 窗口函数 固定行数

--语法:
rows between 
[n|unbounded preceding]|[n|unbounded following]|[current row]
and 
[n|unbounded preceding]|[n|unbounded following]|[current row]

--参数解释：
n行数
unbounded不限行数
preceding在前N行
following在后N行
current row当前行

-- 组合出的结果：

rows between unbounded preceding and unbounded following
-- 从 前无限行 到 当前行
rows between unbounded preceding and current row
-- 从 当前行的前两行 到 当前行
rows between 2 preceding and current row
-- 从 当前行 到 当前行后两行
rows between current row and 2 following
-- 当前行 到 后不限行
rows between current row and unbounded following

-- 查询当月销售额和近三个月的销售额
SELECT 
y,
m,
sales,
sum(sales) OVER(order by y asc,m desc ROWS BETWEEN 2 preceding AND current row) AS s1
FROM sale_table where m in ('09','08','07') and y = '2019' limit 1;