hive sql报错read timed out hive的sql

本地模式: set hive.exec.mode.local.auto = true;

DDL- 库

• 创建数据库

create database if not exists Demo1;

• 过滤查询数据库

show databases like 'D*';

• 查看数据库

-- 显示详细数据库
desc database Demo1;
-- 显示数据库
show databases;

• 删除空数据库

drop database Demo1;

• 强制删除数据库 cascade

drop database Demo1 cascade;

DDL表

建表语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], …)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], …)]
[CLUSTERED BY (col_name, col_name, …)
[SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, …)]
[AS select_statement]

2）字段解释说明

1. CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
2. EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
3. COMMENT：为表和列添加注释。
4. PARTITIONED BY创建分区表
5. CLUSTERED BY创建分桶表
6. SORTED BY不常用，对桶中的一个或多个列另外排序
7. ROW FORMAT
8. [TBLPROPERTIES (property_name=property_value, …)额外属性，如作者，创建时间等

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。

8. STORED AS指定存储文件类型常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
9. LOCATION ：指定表在HDFS上的存储位置。
10. AS：后跟查询语句，根据查询结果创建表。
11. LIKE允许用户复制现有的表结构，但是不复制数据。

• 建表语法

create table if not exists Demo1(
name string,
age int
)
row format delimited fields terminated by '\t';

• 创建默认(内部或者管理)表 注意(text1为表文件名也就是表名可以认为Text1为表别名,Text1完全等于text1)

create table if not exists Demo2(
name string,
age int
)
row format delimited fields terminated by '\t'
location '/Demo/Demo2';

• 将本地文件上传到表中

load data local inpath '/opt/module/hive/datas/Text/text1' into table Demo1

• 根据查询结果创建表

create table if not exists Demo2 as select * from Demo1;

• 创建外部表(内部表删除后,hdfs上也删除,外部表删除,hdfs上不删除)

create external table if not exists Demo3(
name string
)
row format delimited fields terminated by '\t';

• 查看表

desc formatted Demo1;

• 修改表

alter table demo1 rename to dd1;
alter table demo1 change column name nm string;
alter table demo1 add columns(score int) ;
alter table demo1 replace columns (username string,userage int);

• 删除表

drop table dd1;
drop table Demo2;
drop table demo3;

DML - 数据导入

• load

load data local inpath '/opt/module/hive/datas/Text/text1' into table Demo1

load data local inpath '/opt/module/hive/datas/Text/text1' overwrite into table demo1

• insert

insert into demo1 values('wangwu','55'),('liliu','777');

• as select

create table if not exists Demo2 as select * from Demo1;

• location
• import

DML - 数据导出

• insert

insert overwrite local directory'/opt/module/hive/datas/Text/demo1' select * from demo1;

• hadoop 命令

hadoop fs -get /Text/text1 /opt/module/hive/datas/Text/demo1

• hive shell
• export
• truncate(Truncate只能删除管理表，不能删除外部表中数据)

truncate table demo1;

DQL-数据查询语言

select – 筛选列名称
from – 确定表名称
where – where后面的条件，进行行过滤，过滤掉不满足条件的行 注意:in用法
group by – 对列进行分组
having – 行过滤
order by – 排序
limit – 返回限制的行数

• 模糊查询

-- （1）查找名字以A开头的员工信息
select  * from emp where ename like 'A%' ;
-- （2）查找名字中第二个字母为A的员工信息
select  * from emp where ename like '_A%' ;
--  （3）查找名字中带有A的员工信息
select  * from emp where ename like '%A%' ;
-- 去重:select distinct job from emp

group by

分组之后,select后面只能跟 组标识(分组字段) 及 聚合函数(组函数)

• 1)计算emp表每个部门的平均工资

select  deptno,avg(sal)
from emp
group by deptno ;

• 2)计算emp表每个岗位最高工资

select job,max(sal)
from emp
group by job;

• 两表关联



• join 是连接表内容

显示连接 join on => 根据on条件过滤
隐士连接 join

• 3)计算每个部门中最高薪水人

select  deptno,max(sal)
from emp
group by deptno ;

select t2.ename,t1.deptno,t1.max_sal
from(select deptno,max(sal) max_sal
from emp
group by deptno) t1 join emp t2
where t1.max_sal = t2.sal and t1.deptno = t2.deptno;

• 4)求emp中除了CLERK岗位之外的员工,部门平均工资大于2000的部门.

select job,avg(sal) avg_sal
from emp
where job != 'CLERK'
group by job
having avg_sal > 2000;

• 2)emp 和 dept共有的数据

select t1.ename,t1.deptno,t2.dname
from emp t1 join dept t2
on t1.deptno = t2.deptno;

• 3)emp表所有的数据 + dept表与emp匹配的数据

select t1.ename,t1.deptno,t2.dname,t2.deptno
from emp t1 left join dept t2
on t1.deptno = t2.deptno

• 4)dept表所有的数据 + emp表与dept匹配的数据

select t1.ename,t1.deptno,t2.dname,t2.deptno
from emp t1 right join dept t2
on t1.deptno = t2.deptno;

• 5)emp表独有的数据

select t1.ename,t1.deptno,t2.dname,t2.deptno
from emp t1 left join dept t2
on t1.deptno = t2.deptno
where t2.deptno is null;

• 6)dept表独有的数据

select t1.ename,t1.deptno,t2.dname,t2.deptno
from emp t1 right join dept t2
on t1.deptno = t2.deptno
where t1.ename is null;

• 7)emp 和 dept表所有的数据

select t1.ename,t1.deptno,t2.dname,t2.deptno
from emp t1 left join dept t2
on t1.deptno = t2.deptno
union
select t1.ename,t1.deptno,t2.dname,t2.deptno
from emp t1 right join dept t2
on t1.deptno = t2.deptno;

• union 对两个结果集进行并集操作（两个结果集上下连接）

union all : 不去重
union : 去重

• 7)emp 和 dept表所有的数据

select t1.ename,t1.deptno,t2.dname,t2.deptno
from emp t1 full join dept t2
on t1.deptno = t2.deptno;

full join 满外连接

• 8)多表连接

排序

• 按照工资进行降序

select ename,sal
from emp
order by sal desc;

• 按照工资两倍进行升序

select ename,sal*2 tsal
from emp
order by tsal;

• 按照部门和工资升序排序 (前面是谁,先按照那个排)

select ename,deptno,sal
from emp
order by deptno,sal;

• 2)每个Reduce内部排序（Sort By）

set mapreduce.job.reduces;
set mapreduce.job.reduces=3;
select * from emp sort by deptno;

仅用sort by时候,默认随机分区,防止数据倾斜

• 2)分区（Distribute By）

select *
from emp
distribute by deptno sort by sal;

结合sort by使用,按照自己意愿对指定内容进行分区,且将分区内进行排序

1. Cluster By

select * 
from emp
cluster by deptno;

当distribute by和sort by字段相同时，且按照升序排序,可以使用cluster by方式。

分区表和分桶表

1 分区表

Hive中的分区就是分目录,所以一个表对于的是文件夹.
实际则为创建的表内容为目录,每个分区名称带表一条目录.分区为了查询效率.
注意:上传数据时候,由于数据在102上存在,因此在Yarn分配任务时候,只有将上传任务给102才能上传成功.尽管存在重传机制,但仍可能上传失败.

• 创建分区表:

create table if not exists demo3(
deptno int,
dname string,
loc string
)
partitioned by(day string)
row format delimited fields terminated by '\t';

• 往分区表中load数据

load data local inpath '/opt/module/hive/datas/Demo/dept_20200401.log' into table demo3 partition(day = '1');
load data local inpath '/opt/module/hive/datas/Demo/dept_20200402.log' into table demo3 partition(day = '2');
load data local inpath '/opt/module/hive/datas/Demo/dept_20200403.log' into table demo3 partition(day= '3');

• 查看分区表的分区

show partitions demo3;

• 增加分区和删除分区

-- 注意连续增加分区中间用空格,而连续删除分区用逗号.
-- 增加分区
alter table demo3 add partition(day= '4');
alter table demo3 add partition(day= '5') partition(day='6');
-- 删除分区
alter table demo3 drop partition(day='4'),partition(day='5'),partition(day='6');

• 二级分区表

create table if not exists demo4(
    deptno int,
    dname string,
    loc string
    )
    partitioned by(day string,hour string)
    row format delimited fields terminated by '\t';

• 加载数据

load data local inpath '/opt/module/hive/datas/Demo/dept_20200401.log' into table demo4 partition(day = '1',hour='1');
load data local inpath '/opt/module/hive/datas/Demo/dept_20200402.log' into table demo4
partition(day='1',hour='2');

分区表和数据产生关联的方式

• 先创建目录并上传数据， 执行修复分区的操作

hadoop fs -mkdir /user/hive/warehouse/demo3/day=4;
hadoop fs -put dept_20200403.log /user/hive/warehouse/demo3/day=4;

• 修复分区(msck repair)

msck repair table demo3;

• 先创建目录并上传数据， 执行手动添加分区

hadoop fs -mkdir /user/hive/warehouse/demo3/day=5;
hadoop fs -put dept_20200403.log /user/hive/warehouse/demo3/day=5;

• 手动添加分区

alter table demo3 add partition(day='5');

• 先创建目录,在load数据到分区中

hadoop fs -mkdir /user/hive/warehouse/demo3/day=6;
load data local inpath '/opt/module/hive/datas/Demo/dept_20200401.log' into table demo3 partition(day='6');

• 动态分区
• 动态分区是根据指定字符串(建表时候partitioned by)和最后一行插入表的内容后，进行分区，且最后一行插入表的内容不插入该表。
• （1）开启动态分区功能（默认true，开启）
  hive.exec.dynamic.partition=true
• （2）动态一级分区采用的是非严格模式(动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区)
  hive.exec.dynamic.partition.mode=nonstrict
• （3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000
  hive.exec.max.dynamic.partitions=1000
• 4）在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。
  hive.exec.max.dynamic.partitions.pernode=100
• （5）整个MR Job中，最大可以创建多少个HDFS文件。默认100000
  hive.exec.max.created.files=100000
• （6）当有空分区生成时，是否抛出异常。一般不需要设置。默认false
  hive.error.on.empty.partition=false

create table if not exists demo5(
deptno int,
dname string
)
partitioned by (loc string)
row format delimited fields terminated by '\t'
location '/Demo/demo5';

-- 载入数据
load data local inpath '/opt/module/hive/datas/Demo/dept_20200401.log' into table demo5;
load data local inpath '/opt/module/hive/datas/Demo/dept_20200402.log' into table demo5;
load data local inpath '/opt/module/hive/datas/Demo/dept_20200403.log' into table demo5;

2 分桶表

对于一张表或者分区，Hive 可以进一步组织成桶(将同表或同区的数据区分开),
分桶是将数据集分解成更容易管理的若干部分的另一个技术,
分区针对的是数据的存储路径；分桶针对的是数据文件

create table if not exists demo6(
id int,
name string
)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';
-- 根据id对桶数进行求余操作得到桶号
load data local inpath '/opt/module/hive/datas/Demo/testbucket.txt' into table demo6;

查看表结构

hive (default)> desc formatted stu_bucket;
Num Buckets:            4

导入数据到分桶表中，load的方式

hive (default)> load data inpath   '/student.txt' into table stu_bucket;

抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。
语法: TABLESAMPLE(BUCKET x OUT OF y)
查询表stu_buck中的数据。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

注意：x的值必须小于等于y的值，否则

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

函数

1 常用函数:

一、常用日期函数

1. unix_timestamp:返回当前或指定时间的时间戳	
select unix_timestamp();
select unix_timestamp("2020-10-28",'yyyy-MM-dd');

2. from_unixtime：将时间戳转为日期格式
select from_unixtime(1603843200);

3. current_date：当前日期
select current_date;

4. current_timestamp：当前的日期加时间
select current_timestamp;

5. to_date：抽取日期部分
select to_date('2020-10-28 12:12:12');

6. year：获取年
select year('2020-10-28 12:12:12');

7. month：获取月
select month('2020-10-28 12:12:12');

8. day：获取日
select day('2020-10-28 12:12:12');

9. hour：获取时
select hour('2020-10-28 12:13:14');

10. minute：获取分
select minute('2020-10-28 12:13:14');

11. second：获取秒
select second('2020-10-28 12:13:14');

12. weekofyear：当前时间是一年中的第几周
select weekofyear('2020-10-28 12:12:12');

13. dayofmonth：当前时间是一个月中的第几天
select dayofmonth('2020-10-28 12:12:12');

14. months_between： 两个日期间的月份
select months_between('2020-04-01','2020-10-28');

15. add_months：日期加减月
select add_months('2020-10-28',-3);

16. datediff：两个日期相差的天数
select datediff('2020-11-04','2020-10-28');

17. date_add：日期加天数
select date_add('2020-10-28',4);

18. date_sub：日期减天数
select date_sub('2020-10-28',-4);

19. last_day：日期的当月的最后一天
select last_day('2020-02-30');

20. date_format(): 格式化日期  (第一个参数必须是表中日期)
select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');


二、常用取整函数
21. round： 四舍五入
select round(3.14);
select round(3.54);

22. ceil：  向上取整
select ceil(3.14);
select ceil(3.54);

floor： 向下取整
23. select floor(3.14);
select floor(3.54);

三、常用字符串操作函数
24. upper： 转大写
select upper('low');

25. lower： 转小写
select lower('low');

26. length： 长度
select length("atguigu");

27. trim：  前后去空格
select trim(" atguigu ");

28. lpad： 向左补齐，到指定长度
select lpad('atguigu',9,'g');

29. rpad：  向右补齐，到指定长度
select rpad('atguigu',9,'g');

30. regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace('2020/10/25', '/', '-');

四、集合操作
31. size： 集合中元素的个数
select size(friends) from test3;

32. map_keys： 返回map中的key
select map_keys(children) from test3;

33. map_values: 返回map中的value
select map_values(children) from test3;

34. array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test3;

35. sort_array： 将array中的元素排序
select sort_array(friends) from test3;

五、多维分析
36. grouping sets:多维分析

• 1 系统内置函数

1）查看系统自带的函数
show functions;
2）显示自带的函数的用法
desc function upper;
3）详细显示自带的函数的用法
desc function extended upper;

常用简单函数看文档

2 CASE WHEN THEN ELSE END

1. 数据 emp_sex
   ±--------------±-----------------±-------------+
   | emp_sex.name | emp_sex.dept_id | emp_sex.sex |
   ±--------------±-----------------±-------------+
   | 悟空 | A | 男 |
   | 大海 | A | 男 |
   | 宋宋 | B | 男 |
   | 凤姐 | A | 女 |
   | 婷姐 | B | 女 |
   | 婷婷 | B | 女 |
   ±--------------±-----------------±-------------+
2. 结果
   dept_Id 男 女
   A 2 1
   B 1 2

select dept_id,
sum(case sex when '男' then 1 else 0 end) man,
sum(case sex when '女' then 1 else 0 end) woman
from emp_sex
group by dept_id;

3 行转列

1. 相关函数

concat(参一，参二，参三): 字符串拼接函数 例子 concat(name，‘-’，age);
CONCAT_WS(): 字符串拼接函数
CONCAT_WS(参数一,参数二) -->将array数组(参数二)按照字符串(参数一)进行拼接

collect_set(): 去重汇总，汇总后成为数组
collect_list(): 汇总，汇总后成为数组。

2. 数据 person_info

| person_info.name | person_info.constellation | person_info.blood_type |
±------------------±---------------------------±------------------------+
| 孙悟空 | 白羊座 | A |
| 大海 | 射手座 | A |
| 宋宋 | 白羊座 | B |
| 猪八戒 | 白羊座 | A |
| 凤姐 | 射手座 | A |
| 苍老师 | 白羊座 | B |
±------------------±---------------------------±------------------------+
3) 需求
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋|苍老师

select concat(constellation,'-',blood_type ) tip,name
from person_info;

select t1.tip,collect_list(t1.name) name
from (select concat(constellation,'-',blood_type ) tip,name
from person_info) t1
group by t1.tip;

select t2.tip, concat_Ws('|',t2.name)
from (select t1.tip,collect_list(t1.name) name
from (select concat(constellation,'-',blood_type ) tip,name
from person_info) t1
group by t1.tip)t2;

4 列转行（UDTF一进多出）

1 相关函数

explode(): 炸裂函数,将array或者map拆分成多行
split(): 将给定字符串通过分隔符进行拆分，返回一个数组
lateral view : 侧写，侧面描述

– 根据需要切割的属性炸裂,由一行炸裂为多行
lateral view explode(split(需要切割名字,‘按什么切割’)) 炸裂后临时名 as 最终名;

2. 数据准备 movie_info 数据库

±------------------±---------------------+
| movie_info.movie | movie_info.category |
±------------------±------------------------------------+
| 《疑犯追踪》 | 悬疑,动作,科幻,剧情 |
| 《Lie to me》 | 悬疑,警匪,动作,心理,剧情 |
| 《战狼2》 | 战争,动作,灾难 |
±------------------±-----------------------------------+
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难

3. 需求

《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难

select	movie , category_name
from movie_info
lateral view explode(split(category,',')) name as category_name;

5 窗口函数

• 相关函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的改变而变化。
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING 表示到后面的终点
LAG(col,n,default_val)：往前第n行数据
LEAD(col,n, default_val)：往后第n行数据
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

① over(): 默认的窗口大小为当前数据集的大小. 会给窗口中的每条数据都开启一个窗口.
② over( partition by… ) :默认的窗口大小为当前分区数据集的大小. 会给窗口中的每条数据都开启一个窗口.
③ over( order by …):默认的窗口大小为当前数据集中从第一行到当前行. 会给窗口中的每条数据都开启一个窗口.
④ over( partition by …order by …) : 默认的窗口大小为当前分区数据集中从第一行到当前行. 会给窗口中的每条数据都开启一个窗口.
⑤ over(rows between … and … ) : 具体划分窗口的大小

• 数据准备 business

business.name | business.orderdate | business.cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

1. 需求一： 查询在2017年4月份购买过的顾客及总人数

-- 2017年4月份购买过的顾客
select distinct name
from business
where orderdate like '2017-04%';

-- 顾客及总人数
select t1.name,count(t1.name) over()
from (select distinct name
from business
where orderdate like '2017-04%') t1;

• 需求二: 查询顾客的购买明细 及 所有顾客的月购买总额

-- 查询顾客的购买明细 所有顾客的月购买总额
select name,orderdate,cost,
sum(cost) over()
from business;

• 需求二扩展: 查询顾客的购买明细 及 每个顾客的月购买总额

select name,orderdate,cost,
sum(cost) over(partition by name) sume_cost
from business;

• 3)需求三: 求每个顾客的购买明细及将所有顾客的cost按照日期进行累加

select name,orderdate,cost,
sum(cost) over(order by orderdate) sume_cost
from business;

• 需求三扩展: 求每个顾客的购买明细及将每个顾客的cost按照日期进行累加

select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate) sume_cost
from business;

• 需求三扩展: 查询顾客的购买明细 及 所有顾客按照日期排序后,
  第一行到当前行
  上一行到当前行
  当前行到下一行
  上一行到下一行
  当前行到最后一行钱

-- n PRECEDING  n FOLLOWING    UNBOUNDED FOLLOWING

select name,orderdate,cost,
sum(cost) over(order by orderdate) f_c,
sum(cost) over(order by orderdate rows between 1 preceding and current row) p_c,
sum(cost) over(order by orderdate rows between current row and 1 following ) c_f,
sum(cost) over(order by orderdate rows between 1 preceding and 1 following) p_f,
sum(cost) over(order by orderdate rows between current row and unbounded following) c_l
from business;

1. 需求四 : 查询每个顾客上次的购买时间 下次的购买时间

-- LAG(col,n,default_val)：往前第n行数据		LEAD(col,n, default_val)：往后第n行数据

select name,cost,orderdate,
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate) p_orderdate,
lead(orderdate,1,'2500-01-01') over(partition by name order by orderdate) n_orderdate
from business;

1. 需求五:查询前20%时间的订单信息

-- NTILE(n)：

select name,cost,orderdate,
ntile(5) over(order by orderdate) gid
from business;

select t1.name,t1.cost,t1.orderdate
from (select name,cost,orderdate,
ntile(5) over(order by orderdate) gid
from business) t1
where t1.gid = 1;

6 Rank

• 函数说明

RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

• 数据准备 score

| score.name | score.subject | score.score
孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
大海 语文 94
大海 数学 56
大海 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78

• 需求: 计算每门学科成绩排名。

select name,subject,score,
rank() over(partition by subject order by score desc) rk,
dense_rank() over(partition by subject order by score desc) drk,
row_number() over(partition by subject order by score desc) rn
from  score;

每日新增记录

• if(条件，参数一，参数二) ; 如果条件成立，则为参数一，如果不成立，则为参数二。
• nvl(参一，参二) ; 如果参数一非空则用参数一，若空用二，若都空则空。
• coalesce(参数一,参数二,…,参数n); 如果参数一非空则用参数一，若二非空用二，…，若都空则空。
• sum(if(条件，参数一，参数二)); 相当于有条件的count
• max(if(条件，参数一，参数二)); null不参与运算
• case when用法…

case
when 条件1 then 参数一
when 条件2 then 参数二
when 条件3 then 参数三
end

• first_value(参数一，参数二)/last_value(参数一，参数二)
  first_value取第一个值，last_value取最后一个值。
  参数一为所需列，参数二为ture（默认为false），则不取null（取null）。
• insert into table 表名 select…
  为追加表，但在dfs上为会创建一个新的文件夹。
  insert overwrite table 表名 select* from union select… 操作相当于 insert into table 表名 select…，不会创建新文件，但需要注意数据类型。
  注意： insert overwrite table 表名 select* from union all select… 操作相当于 insert into table 表名 select…，因为并未经过计算。
• cast(参数一 as 参数二)
  将参数一转为参数二（基本数据类型）类型

lateral view inline(参数一) tmp as 参数二

参数一为：结构体数组
参数二为：炸开后拥有字段表名
先将结构体数组每个结构体炸成单独行（一进多出），然后将每个结构体value单独成列。

• 如果使用pivot 旋转列 维度列 聚合列
• 行转列 也可以用sum（if) 提取
• with as 用法

with 表1 as 
( ..... ),
表2 as
( ..... ),
表3 as
( ..... )
select
表1.name,
表2.age,
表3.score
from 表1
join 表2 on 表1.id = 表2.id
join 表3 on 表2.id = 表3.id