Hive语法规范

最新推荐文章于 2024-05-15 09:30:00 发布

笑面天下

最新推荐文章于 2024-05-15 09:30:00 发布

阅读量3.2k

点赞数 5

文章标签： hive big data

本文链接：https://blog.csdn.net/m0_48379126/article/details/120392300

版权

库操作

1.增

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment] // 库的注释说明
[LOCATION hdfs_path] // 库在hdfs上的路径
[WITH DBPROPERTIES (property_name=property_value, ...)]; // 库的属性

例：

create database if not exists mydb2
comment 'this is my db'
location 'hdfs://hadoop101:9000/mydb2'
with dbproperties('ownner'='jack','tel'='12345','department'='IT');

2.删

drop database 库名：只能删除空库
drop database 库名 cascade：删除非空库

3.改

use 库名：切换库

dbproperties:alter database mydb2 set dbproperties('ownner'='tom','empid'='10001')

同名的属性会覆盖，之前没有的属性会新增

4.查

show databases：查看当前所有的库
show tables in database：查看库中所有的表
desc database 库名：查看库的描述信息
desc database extended 库名：查看库的详细描述信息

表操作

1.增

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] //表中的数据要以哪种文件格式来存储，默认为TEXTFILE（文本文件）
                   可以设置为SequnceFile或 Paquret,ORC等
[LOCATION hdfs_path] //表在hdfs上的位置

建表时，不带EXTERNAL，创建的表是一个MANAGED_TABLE(管理表，内部表)
建表时，带EXTERNAL，创建的表是一个外部表

   外部表和内部表的区别是：
           内部表(管理表)在执行删除操作时，会将表的元数据(schema)和表位置的数据一起删除！
           外部表在执行删除表操作时，只删除表的元数据(schema)

   在企业中，创建的都是外部表！
       在hive中表是廉价的，数据是珍贵的！

   建表语句执行时：
           hive会在hdfs生成表的路径；
           hive还会向MySQl的metastore库中掺入两条表的信息(元数据)

   管理表和外部表之间的转换：
       将表改为外部表：   alter table p1 set tblproperties('EXTERNAL'='TRUE');

       将表改为管理表：   alter table p1 set tblproperties('EXTERNAL'='FALSE');

       注意：在hive中语句中不区分大小写，但是在参数中严格区分大小写，所以最好大写

其他建表：
           只复制表结构：create table 表名 like 表名1
           执行查询语句，将查询语句查询的结果，按照顺序作为新表的普通列：create table 表名 as select 语句
                       不能创建分区表！

2.删

       drop table 表名：删除表
       truncate table 表名：清空管理表，只清空数据

3.改

       改表的属性： alter table 表名 set tblproperties(属性名=属性值)
       对列进行调整：
               改列名或列类型： alter table 表名 change [column] 旧列名新列名新列类型 [comment 新列的注释]
                               [FIRST|AFTER column_name] //调整列的顺序

               添加列和重置列：ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

4.查

       desc 表名：查看表的描述
       desc formatted 表名：查看表的详细描述

扩展 —— 创建能够导入CSV文件的数据表

create external table ...(
[(col_name data_type [COMMENT col_comment], ...)] //表中的字段信息
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties
(
'separatorChar' = ',', -- 分隔符
'quoteChar' = '\"', -- 引号符
'escapeChar' = '\\' -- 转义符
)
location '/路径'
tblproperties('skip.header.line.count'='1') --去除首行

分区表

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

1. 分区表
       在建表时，指定了PARTITIONED BY ，这个表称为分区表
2. 分区概念
       MR：在MapTask输出key-value时，为每个key-value计算一个区号，同一个分区的数据，会被同一个reduceTask处理
               这个分区的数据，最终生成一个结果文件！

               通过分区，将MapTask输出的key-value经过reduce后，分散到多个不同的结果文件中！

       Hive: 将表中的数据，分散到表目录下的多个子目录(分区目录)中

3. 分区意义
       分区的目的是为了就数据，分散到多个子目录中，在执行查询时，可以只选择查询某些子目录中的数据，加快查询效率！
       只有分区表才有子目录(分区目录)
       分区目录的名称由两部分确定：分区列列名=分区列列值

       将输入导入到指定的分区之后，数据会附加上分区列的信息！
       分区的最终目的是在查询时，使用分区列进行过滤

分区表操作

1.创建分区表
create external table if not exists default.deptpart1(
deptno int,
dname string,
loc int
)
PARTITIONED BY(area string)
row format delimited fields terminated by '\t';

多级分区表，有多个分区字段
create external table if not exists default.deptpart2(
deptno int,
dname string,
loc int
)
PARTITIONED BY(area string,province string)
row format delimited fields terminated by '\t';

---------------------------------
create external table if not exists default.deptpart3(
deptno int,
dname string,
loc int
)
PARTITIONED BY(area string)
row format delimited fields terminated by '\t'
location 'hdfs://hadoop101:9000/deptpart3';

2.分区的查询
       show partitions 表名

3. 创建分区
       ① alter table 表名 add partition(分区字段名=分区字段值) ;
               a)在hdfs上生成分区路径
               b)在mysql中metastore.partitions表中生成分区的元数据

       ② 直接使用load命令向分区加载数据，如果分区不存在，load时自动帮我们生成分区

       ③ 修复分区：如果数据已经按照规范的格式，上传到了HDFS，可以使用修复分区命令自动生成分区的元数据
               msck repair table 表名;

创建多个分区

alter table 表名 add partition(分区字段名=分区字段值) partition(分区字段名=分区字段值)

④ 动态分区（重点）

                设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）

                set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict

insert into table 分区表名 partition(所查询的表的字段名) select * from 表名
注意事项：
       ①如果表是个分区表，在导入数据时，必须指定向哪个分区目录导入数据
       ②如果表是多级分区表，在导入数据时，数据必须位于最后一级分区的目录

4.删除分区

alter table 表名 drop partition(分区列列名=分区列列值)

        删除多个分区

        alter table 表名 drop partition(分区列列名=分区列列值)，partition(分区列列名=分区列列值)

分桶表

[CLUSTERED BY (col_name, col_name, ...)  分桶的字段，是从表的普通字段中来取
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
1. 分桶表
       建表时指定了CLUSTERED BY，这个表称为分桶表！

       分桶：和MR中分区是一个概念！把数据分散到多个文件中！

2. 分桶的意义
       分桶本质上也是为了分散数据！在分桶后，可以结合hive提供的抽样查询，只查询指定桶的数据

3. 在分桶时，也可以指定将每个桶的数据根据一定的规则来排序
       如果需要排序，那么可以在CLUSTERED BY后根SORTED BY

分桶表操作

1.建表
create table stu_buck(id int, name string)
clustered by(id)
SORTED BY (id desc)
into 4 buckets
row format delimited fields terminated by '\t';

----临时表
create table stu_buck_tmp(id int, name string)
row format delimited fields terminated by '\t';

2.导入数据
   向分桶表导入数据时，必须运行MR程序，才能实现分桶操作！
   load的方式，只是执行put操作，无法满足分桶表导入数据！
   必须执行insert into
       insert into 表名 values(),(),(),()
       insert into 表名 select 语句

   导入数据之前：
           需要打开强制分桶开关： set hive.enforce.bucketing=true;
           需要打开强制排序开关： set hive.enforce.sorting=true;

   insert into table stu_buck select * from stu_buck_tmp

抽样检查

格式：select * from 分桶表 tablesample(bucket x out of y on 分桶表分桶字段);
要求：
①抽样查询的表必须是分桶表！
②bucket x out of y on 分桶表分桶字段
   假设当前表一共分了z个桶
   x: 从当前表的第几桶开始抽样
               0<x<=y
   y: z/y 代表一共抽多少桶！
       要求y必须是z的因子或倍数！

   怎么抽：从第x桶开始抽样，每间隔y桶抽一桶，知道抽满 z/y桶

   bucket 1 out of 2 on id：从第1桶(0号桶)开始抽，抽第x+y*(n-1)，一共抽2桶： 0号桶,2号桶
    select * from stu_buck tablesample(bucket 1 out of 2 on id)

   bucket 1 out of 1 on id：从第1桶(0号桶)开始抽，抽第x+y*(n-1)，一共抽4桶： 0号桶,2号桶,1号桶,3号桶

   bucket 2 out of 4 on id：从第2桶(1号桶)开始抽，一共抽1桶： 1号桶

   bucket 2 out of 8 on id：从第2桶(1号桶)开始抽，一共抽0.5桶： 1号桶的一半

DML导入

1.load：作用将数据直接加载到表目录中
   语法： load data [local] inpath 'xx' [overwrite] into table 表名 partition()

               local: 如果导入的文件在本地文件系统，需要加上local，使用put将本地上传到hdfs
不加local默认导入的文件是在hdfs，使用mv将源文件移动到目标目录

2. insert：insert方式运行MR程序，通过程序将数据输出到表目录！
       在某些场景，必须使用insert方式来导入数据：
               ①向分桶表插入数据
               ②如果指定表中的数据，不是以纯文本形式存储，需要使用insert方式导入

   语法： insert into | overwrite table 表名 select xxx | values(),(),()
               insert into: 向表中追加新的数据
               insert overwrite：先清空表中所有的数据，再向表中添加新的数据
   特殊情况：多插入模式(从一张源表查询，向多个目标表插入)
               from 源表
               insert xxxx 目标表 select xxx
               insert xxxx 目标表 select xxx
               insert xxxx 目标表 select xxx
   举例： from deptpart2
insert into table deptpart1 partition(area='huaxi') select deptno,dname,loc
insert into table deptpart1 partition(area='huaxinan') select deptno,dname,loc

3. location: 在建表时，指定表的location为数据存放的目录

4. import : 不仅可以导入数据还可以顺便导入元数据(表结构)。Import只能导入export输出的内容！

IMPORT [[EXTERNAL] TABLE 表名(新表或已经存在的表) [PARTITION (part_column="value"[, ...])]]
FROM 'source_path'
[LOCATION 'import_target_path']
               ①如果向一个新表中导入数据，hive会根据要导入表的元数据自动创建表
               ②如果向一个已经存在的表导入数据，在导入之前会先检查表的结构和属性是否一致
                       只有在表的结构和属性一致时，才会执行导入
               ③不管表是否为空，要导入的分区必须是不存在的

import external table importtable1 from '/export1'

如果导入的是external外部表，那么这个表是不控制数据的生命周期的，换句话说就是只拷贝源表的结构而不拷贝数据，只作引用

DML导出

1. insert : 将一条sql运算的结果，插入到指定的路径
       语法： insert overwrite [local] directory '/opt/atguigu/export/student'
           row format xxxx
select * from student;

2. export ：既能导出数据，还可以导出元数据(表结构)！
           export会在hdfs的导出目录中，生成数据和元数据！
           导出的元数据是和RDMS无关！
           如果是分区表，可以选择将分区表的部分分区进行导出！

       语法： export table 表名 [partiton(分区信息) ] to 'hdfspath'（不能一次选择多个分区，只能一个分区一个分区导入）

排序

       Hive的本质是MR，MR中如何排序的！
               全排序：结果只有一个(只有一个分区)，所有的数据整体有序！
               部分排序：结果有多个(有多个分区)，每个分区内部有序！
               二次排序：在排序时，比较的条件有多个！

               排序：在reduce之前就已经排好序了，排序是shuffle阶段的主要工作！

       排序？
       分区：使用Partitioner来进行分区！
                   当reduceTaskNum>1，设置用户自己定义的分区器，如果没有使用HashParitioner!
                   HashParitioner只根据key的hashcode来分区！

ORDER BY col_list ：全排序！
SORT BY col_list ：部分排序！设置reduceTaskNum>1。只写sort by是随机分区！
                       如果希望自定定义使用哪个字段分区，需要使用DISTRIBUTE BY

DISTRIBUTE BY col_list：指定按照哪个字段分区！结合sort by 使用！
CLUSTER BY col_list：如果分区的字段和排序的字段一致，可以简写为CLUSTER BY
                           DISTRIBUTE BY sal sort by sal asc 等价于 CLUSTER BY sal

                       要求： CLUSTER BY 后不能写排序方式，只能按照默认的asc排序！

------------------------------------------------------------
insert overwrite local directory '/home/atguigu/sortby'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from emp DISTRIBUTE BY deptno sort by sal desc ;

insert overwrite local directory '/home/atguigu/sortby'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from emp where mgr is not null CLUSTER BY mgr ;

函数

1.查看函数
       函数有库的概念，系统提供的除外，系统提供的函数可以在任意库使用！
       查看当前库所有的函数：show functions;
       查看函数的使用： desc function 函数名
       查看函数的详细使用： desc function extended 函数名

2.函数的分类
       函数的来源：

                      ①系统函数，自带的，直接使用即可
                   ②用户自定义的函数。
                           a)遵守hive函数类的要求，自定义一个函数类
                           b)打包函数，放入到hive的lib目录下，或在HIVE_HOME/auxlib
                                   auxlib用来存放hive可以加载的第三方jar包的目录
c)创建一个函数，让这个函数和之前编写的类关联
                                   函数有库的概念
                           d)使用函数

       函数按照特征分：

        UDF：用户定义的函数。一进一出。输入单个参数，返回单个结果！ cast('a' as int) 返回 null
        UDTF: 用户定义的表生成函数。一进多出。传入一个参数(集合类型)，返回一个结果集！
        UDAF：用户定义的聚集函数。多进一出。传入一列多行的数据，返回一个结果(一列一行)  count,avg,sum

常用日期函数

hive默认解析的日期必须是： 2019-11-24 08:09:10

unix_timestamp:返回当前或指定时间的时间戳

-- select unix_timestamp('2019-01-01 01:01:01')
-- select unix_timestamp('2019_01_01 01-01-01','yyyy_MM_dd HH-mm-ss')

from_unixtime：将时间戳转为日期格
current_date：当前日期
current_timestamp：当前的日期加时间
to_date：抽取日期部分
year：获取年
month：获取月
day：获取日
hour：获取时
minute：获取分
second：获取秒
weekofyear：当前时间是一年中的第几周
dayofmonth：当前时间是一个月中的第几天
months_between：两个日期间的月份，前-后
add_months：日期加减月
datediff：两个日期相差的天数，前-后
date_add：日期加天数
date_sub：日期减天数
last_day：日期的当月的最后一天

date_format格式化日期

--date_format( 2019-11-24 08:09:10,'yyyy-MM') mn

常用取整函数

round：四舍五入

--select round(4.5) 结果：5

ceil：向上取整

--select ceil(4.4) 结果：5

floor：向下取整

--select floor(4.6) 结果：4

常用字符串操作函数

upper：转大写
lower：转小写
length：长度
trim：前后去空格
lpad( string, padded_length, [ pad_string ] )：向左补齐，到指定长度

string

准备被填充的字符串；

padded_length

填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;

pad_string

填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。

rpad：向右补齐，到指定长度
regexp_replace： SELECT regexp_replace('100-200', '(\d+)', 'num')='num-num
使用正则表达式匹配目标字符串，匹配成功后替换

regexp_extract(string subject, string pattern, int index)

返回值: string

说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。

第一参数：要处理的字段

第二参数: 需要匹配的正则表达式

第三个参数:

0是显示与之匹配的整个字符串

1 是显示第一个括号里面的

2 是显示第二个括号里面的字段...

集合操作

size：集合（map和list）中元素的个数
map_keys：返回map中的key
map_values: 返回map中的value
array_contains: 判断array中是否包含某个元素
sort_array：将array中的元素排序

其他常用函数（重点）

1.NVL
       NVL( string1, replace_with)：判断string1是否为null，如果为null，使用replace_with替换null，否则
                   不做操作！

       在以下场景使用： ①将NULL替换为默认值
②运行avg()

2.concat: 字符串拼接。可以在参数中传入多个string类型的字符串，一旦有一个参数为null，返回Null！

3.concat_ws: 使用指定的分隔符完成字符串拼接！
       concat_ws(分隔符,[string | array<string>]+)

4. collect_set：collect_set(列名) ：将此列的多行记录合并为一个set集合，去重

5. collect_list：collect_list(列名) ：将此列的多行记录合并为一个set集合，不去重

name | age

‘张三’ 111

‘张三’ 222

‘张三’ 333

转成 ‘张三’ [111,222,333]

6. explode: explode(列名)
                       参数只能是array或map!
                       将array类型参数转为1列N行
                       将map类型参数转为2列N行

小节练习

练习一

emp_sex.name | emp_sex.dept_id | emp_sex.sex
求每个部分男女各有多少人
思路一：先按照性别过滤，求这个性别有多少人。再将同一个部分男女性别各多少人Join后拼接成一行结果！
select t1.dept_id,male_count,female_count
from
(select dept_id,count(*) male_count from emp_sex
where sex='男'
group by dept_id) t1
join
(select dept_id,count(*) female_count from emp_sex
where sex='女'
group by dept_id) t2
on t1.dept_id=t2.dept_id

尽量避免子查询！
思路一：在求男性个数时，求男性总数！求总数，可以使用sum(数字),需要将每个人的性别由男|女转为数字！
           在求男性总人数，如果当前人的性别为男，记1，
           在求女性总人数，如果当前人的性别为女，记1，

判断函数：
case ... when :
           case 列名
                   when 值1 then 值2
                   when 值3 then 值4
                   ...
                   else 值5
           end


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_sex
group by dept_id


if(判断表达式,值1(表达式为true),值2(表达式为false))

select dept_id,
sum(if(sex='男',1,0)) male_count,
sum(if(sex='女',1,0)) female_count
from emp_sex
group by dept_id

练习二

行转列： 1列N行转为 1列1行

select后面只能写分组后的字段和聚集函数！
       聚集函数：多进一出

person_info.name | person_info.constellation | person_info.blood_type

把星座和血型一样的人归类到一起。结果如下：
射手座,A 大海|凤姐

select concat(constellation,',',blood_type),concat_ws('|',collect_list(name))
from person_info
group by constellation,blood_type

练习三

列转行： 1列1行转为 1列N行
   explode函数属于UDTF，UDTF在使用时，不能和其他表达式一起出现在select子句后！
               只能单独出现在select子句后！

movie_info.movie | movie_info.category
《疑犯追踪》 | ["悬疑","动作","科幻","剧情"]
期望结果：
《疑犯追踪》悬疑
《疑犯追踪》动作
《疑犯追踪》科幻
《疑犯追踪》剧情
《Lie to me》悬疑

-------------不允许----------
select movie,explode(category)
from movie_info

--------不符合要求--------------
select movie,col1
from movie_info
join
(select explode(category) col1
from movie_info) tmp

处理：
①先explode
②需要将炸裂后的1列N行，在逻辑上依然视作1列1行，实际是1列N行，和movie进行笛卡尔集
       这个操作在hive中称为侧写(lateral vIEW)

       Lateral view explode(列名) 临时表名 as 临时列名

select movie,col1
from movie_info Lateral view explode(category) tmp1 as col1

练习四

数据：

names               tags                       hobbys
jack|tom|jerry   阳光男孩|肌肉男孩|直男   晒太阳|健身|说多喝热水
marry|nancy   阳光女孩|肌肉女孩|腐女   晒太阳|健身|看有内涵的段子

create table person_info2(names array<string>,tags array<string>,hobbys array<string>)
row format delimited fields terminated by '\t'
collection items terminated by '|'

期望结果：
jack   阳光男孩   晒太阳
jack   阳光男孩   健身
jack   阳光男孩   说多喝热水
jack   肌肉男孩   晒太阳
jack   肌肉男孩   健身
jack   肌肉男孩   说多喝热水
.....

select name,tag,hobby
from person_info2
lateral view explode(names) tmp1 as name
lateral view explode(tags) tmp1 as tag
lateral view explode(hobbys) tmp1 as hobby

窗口函数

官方文档地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
           oracle,sqlserver都提供了窗口函数，但是在mysql5.5和5.6都没有提供窗口函数！

           窗口函数：窗口+函数
               窗口：函数运行时计算的数据集的范围
               函数：运行的函数！
                   仅仅支持以下函数：
                       Windowing functions：（前置函数）
                               LEAD:
                                       LEAD (scalar_expression [,offset] [,default])：返回当前行以下N行的指定列的列值！
                                               如果找不到，就采用默认值
                               LAG:
                                       LAG (scalar_expression [,offset] [,default])：返回当前行以上N行的指定列的列值！
                                               如果找不到，就采用默认值
                               FIRST_VALUE:
                                       FIRST_VALUE(列名,[false(默认)])：返回当前窗口指定列的第一个值，
                                               第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！
                               LAST_VALUE:
                                       LAST_VALUE(列名,[false(默认)])：返回当前窗口指定列的最后一个值，
                                               第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！
                       统计类的函数(一般都需要结合over使用)： min,max,avg,sum,count
                       排名分析：
                               RANK
                               ROW_NUMBER
                               DENSE_RANK
                               CUME_DIST
                               PERCENT_RANK
                               NTILE

               注意：不是所有的函数在运行都是可以通过改变窗口的大小，来控制计算的数据集的范围！
                       所有的排名函数和LAG,LEAD，支持使用over()，但是在over()中不能定义 window_clause

               格式： 内置函数 over( partition by 字段 ,order by 字段 window_clause )

               窗口的大小可以通过windows_clause来指定：

   (rows | range) between (unbounded | [num]) preceding and ([num] preceding | current row | (unbounded | [num]) following)
(rows | range) between current row and (current row | (unbounded | [num]) following)
(rows | range) between [num] following and (unbounded | [num]) following

          特殊情况：

                     ①在over()中既没有出现windows_clause，也没有出现order by，

窗口默认为rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING

②在over()中(没有出现windows_clause)，指定了order by，

窗口默认为rows between UNBOUNDED PRECEDING and CURRENT ROW

               窗口函数和分组有什么区别？
                   ①如果是分组操作，select后只能写分组后的字段
                   ②如果是窗口函数，窗口函数是在指定的窗口内，对每条记录都执行一次函数
                   ③如果是分组操作，有去重效果，而partition不去重！

business.name | business.orderdate | business.cost

（1）查询在2017年4月份购买过的顾客及总人数

count()在分组后，统计一个组内所有的数据！

传统写法：效率低
with tmp as (select name
from business
where year(orderdate)=2017 and month(orderdate)=4
group by name)

select tmp.name,tmp1.totalcount
from
(select count(*) totalcount
from tmp ) tmp1 join tmp;

---------------
select name,count(*) over(rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)
from business
where substring(orderdate,1,7)='2017-04'
group by name

等价于

select name,count(*) over()
from business
where substring(orderdate,1,7)='2017-04'
group by name

（2）查询顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost) over(partition by name,substring(orderdate,1,7) )
from business

（3）查询顾客的购买明细要将cost按照日期进行累加
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate )
from business

（4）查询顾客的购买明细及顾客上次的购买时间
select name,orderdate,cost,lag(orderdate,1,'无数据') over(partition by name order by orderdate )
from business

（5）查询顾客的购买明细及顾客下次的购买时间
select name,orderdate,cost,lead(orderdate,1,'无数据') over(partition by name order by orderdate )
from business

（6）查询顾客的购买明细及顾客本月第一次购买的时间
select name,orderdate,cost,FIRST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate )
from business

（7）查询顾客的购买明细及顾客本月最后一次购买的时间
select name,orderdate,cost,LAST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate rows between CURRENT row and UNBOUNDED FOLLOWING)
from business

（8）查询顾客的购买明细及顾客最近三次cost花费

最近三次：当前和之前两次或当前+前一次+后一次

当前和之前两次:
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 2 PRECEDING and CURRENT row)
from business

当前+前一次+后一次:
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING)
from business
或
select name,orderdate,cost,cost+
lag(cost,1,0) over(partition by name order by orderdate )+
lead(cost,1,0) over(partition by name order by orderdate )
from business
（9）查询前20%时间的订单信息
精确算法：
select *
from
(select name,orderdate,cost,cume_dist() over(order by orderdate ) cdnum
from business) tmp
where cdnum<=0.2

不精确计算：
select *
from
(select name,orderdate,cost,ntile(5) over(order by orderdate ) cdnum
from business) tmp
where cdnum=1

排名函数

注意：排名函数可以跟Over()，但是不能定义window_clause.
在计算名次前，需要先排序！

RANK: 允许并列，一旦有并列跳号！
ROW_NUMBER: 行号！连续的，每个号之间差1！
DENSE_RANK：允许并列，一旦有并列不跳号！
CUME_DIST：从排序后的第一行到当前值之间数据占整个数据集的百分比！
PERCENT_RANK： rank - 1 / 总数据量 - 1
NTILE(x): 将数据集均分到X个组中，返回每条记录所在的组号

select *,rank() over(order by score) ranknum,
ROW_NUMBER() over(order by score) rnnum,
DENSE_RANK() over(order by score) drnum,
CUME_DIST() over(order by score) cdnum,
PERCENT_RANK() over(order by score) prnum
from score

select *,ntile(5) over()
from score

count row_number rank dense_rank
3 1 1 1
3 2 1 1
2 3 3 2
1 4 4 3

一般 rk<=3

二、练习

score.name | score.subject | score.score

// 按照科目进行排名

select *,rank() over(partition by subject order by score desc)
from score

// 给每个学生的总分进行排名
// 输出4条记录
select name,sumscore,rank() over( order by sumscore desc)
from
(select name,sum(score) sumscore
from score
group by name) tmp

// 求每个学生的成绩明细及给每个学生的总分和总分排名

select *,DENSE_RANK() over(order by tmp.sumscore desc)
from
(select *,sum(score) over(partition by name) sumscore
from score) tmp

// 只查询每个科目的成绩的前2名
select *
from
(select *,rank() over(partition by subject order by score desc) rn
from score) tmp
where rn<=2

//查询学生成绩明细，并显示当前科目最高分
select *,max(score) over(partition by subject)
from score

或
select *,FIRST_VALUE(score) over(partition by subject order by score desc)
from score

//查询学生成绩，并显示当前科目最低分
select *,min(score) over(partition by subject)
from score

或
select *,FIRST_VALUE(score) over(partition by subject order by score )
from score

扩展题 —— 种树题

一、数据
plant_carbon.plant_id | plant_carbon.plant_name | plant_carbon.low_carbon
user_low_carbon.user_id | user_low_carbon.data_dt | user_low_carbon.low_carbon

二、需求一
1.蚂蚁森林植物申领统计
问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），
假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，
剩余的能量全部用来领取“p002-沙柳”。
统计在10月1日累计申领“p002-沙柳”排名前10的用户信息；以及他比后一名多领了几颗沙柳。

①统计用户在 2017-1-1 至 2017-10-1期间一共收集了多少碳量

select user_id,sum(low_carbon) sumCarbon
from user_low_carbon
where regexp_replace(data_dt,'/','-') between '2017-1-1' and '2017-10-1'
group by user_id //t1

②统计胡杨和沙柳单价

胡杨单价：
select low_carbon huyangCarbon from plant_carbon where plant_id='p004'; //t2

沙柳单价：
select low_carbon shaliuCarbon from plant_carbon where plant_id='p002'; //t3

③计算每个用户领取了多少棵沙柳

select user_id, floor((sumCarbon-huyangCarbon)/shaliuCarbon) shaliuCount
from t1 join t2 join t3
order by shaliuCount desc
limit 11 //t4

④统计前10用户，比后一名多多少

select user_id,shaliuCount,rank() over(order by shaliuCount desc),
shaliuCount-lead(shaliuCount,1,0) over(order by shaliuCount desc)
from t4

------------------组合后的sql----------------------
select user_id,shaliuCount,rank() over(order by shaliuCount desc),
shaliuCount-lead(shaliuCount,1,0) over(order by shaliuCount desc)
from (select user_id, floor((sumCarbon-huyangCarbon)/shaliuCarbon) shaliuCount
from
(select user_id,sum(low_carbon) sumCarbon
from user_low_carbon
where regexp_replace(data_dt,'/','-') between '2017-1-1' and '2017-10-1'
group by user_id )t1
join
(select low_carbon huyangCarbon from plant_carbon where plant_id='p004')t2
join
(select low_carbon shaliuCarbon from plant_carbon where plant_id='p002')t3
order by shaliuCount desc
limit 11)t4

三、题目二
问题：查询user_low_carbon表中每日流水记录，条件为：
用户在2017年，连续三天（或以上）的天数里，
每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。

plant_carbon.plant_id | plant_carbon.plant_name | plant_carbon.low_carbon
user_low_carbon.user_id | user_low_carbon.data_dt | user_low_carbon.low_carbon
①过滤2017年的数据，统计每个用户每天共收集了多少碳

select user_id,regexp_replace(data_dt,'/','-') dt,sum(low_carbon) carbonPerDay
from user_low_carbon
where year(regexp_replace(data_dt,'/','-'))=2017
group by user_id,data_dt
having carbonPerDay >= 100 //t1

②过滤复合连续3天的数据
       如果判断当前记录复合连续三天的条件？
       a)如果当前日期位于连续三天中的第一天，使用当前日期减去当前日期后一天的日期，差值一定为-1
                                               使用当前日期减去当前日期后二天的日期，差值一定为-2

       b)如果当前日期位于连续三天中的第二天，使用当前日期减去当前日期前一天的日期，差值一定为1
                                               使用当前日期减去当前日期后一天的日期，差值一定为-1

       c)如果当前日期位于连续三天中的第三天，使用当前日期减去当前日期前一天的日期，差值一定为1
                                               使用当前日期减去当前日期前二天的日期，差值一定为2

       满足a,b,c其中之一，当前日期就复合要求


求当前日期和当前之前，前1，2天和后1，2天日期的差值

select user_id,dt,carbonPerDay,
datediff(dt,lag(dt,1,'1970-1-1') over(partition by user_id order by dt)) pre1diff,
datediff(dt,lag(dt,2,'1970-1-1') over(partition by user_id order by dt)) pre2diff,
datediff(dt,lead(dt,1,'1970-1-1') over(partition by user_id order by dt)) after1diff,
datediff(dt,lead(dt,2,'1970-1-1') over(partition by user_id order by dt)) after2diff
from t1 //t2

③过滤数据
select user_id,regexp_replace(dt,'-','/') newdt,carbonPerDay
from t2
where (after1diff=-1 and after2diff=-2) or (pre1diff=1 and after1diff=-1) or (pre1diff=1 and pre2diff=2) //t3

④关联原表，求出每日的流水
select u.*
from t3 join user_low_carbon u
on t3.user_id=u.user_id and t3.newdt=u.data_dt

----------------------组合最终SQL-------------------
select u.*
from
(select user_id,regexp_replace(dt,'-','/') newdt,carbonPerDay
from
(select user_id,dt,carbonPerDay,
datediff(dt,lag(dt,1,'1970-1-1') over(partition by user_id order by dt)) pre1diff,
datediff(dt,lag(dt,2,'1970-1-1') over(partition by user_id order by dt)) pre2diff,
datediff(dt,lead(dt,1,'1970-1-1') over(partition by user_id order by dt)) after1diff,
datediff(dt,lead(dt,2,'1970-1-1') over(partition by user_id order by dt)) after2diff
from (select user_id,regexp_replace(data_dt,'/','-') dt,sum(low_carbon) carbonPerDay
from user_low_carbon
where year(regexp_replace(data_dt,'/','-'))=2017
group by user_id,data_dt
having carbonPerDay >= 100)t1 )t2
where (after1diff=-1 and after2diff=-2) or (pre1diff=1 and after1diff=-1) or (pre1diff=1 and pre2diff=2))t3 join user_low_carbon u
on t3.user_id=u.user_id and t3.newdt=u.data_dt


四、题目二解法二

①过滤2017年的数据，统计每个用户每天共收集了多少碳

select user_id,regexp_replace(data_dt,'/','-') dt,sum(low_carbon) carbonPerDay
from user_low_carbon
where year(regexp_replace(data_dt,'/','-'))=2017
group by user_id,data_dt
having carbonPerDay >= 100 //t1

如何判断当前数据是连续的？
   如何理解连续？
   当前有A,B两列，A列的起始值从a开始，B列的起始值从b开始,
           假设A列每次递增X，B列每次递增Y。
           如果A列和B列都是连续递增！A列和B列之间的差值，总是相差(x-y)。
           如果X=Y，A列和B列之间的差值，总是相差0。

   A           B
1.   a           b               a-b
2.    a+X           b+Y               (a-b)+(x-y)
3.   a+2x       b+2y           (a-b)+2(x-y)
4.   a+3x       b+3y
n   a+(n-1)x   b+(n-1)y

1   1   0           0
4   2   2           3
7   3   4           6
10   4   6           9
13   5   8
16   6   10   差值相差2

2   1   1
3   2   1
4   3   1
5   4   1
6   5   1   差值相差0
判断日期是连续的？连续的日期，每行之间的差值为1
   连续的日期每次递增1，再提供一个参考列，这个参考列每次也是递增1
   dt，从2017-1-1开始递增，每次递增1
   B列，从1开始递增，每次递增1
   如果dt列和B列都是连续的！
       此时 dt列-B列=每行的差值
               每行的差值之间的差值，一定等于0，每行之间的差值相等！
   dt               列B               diff
   2017-1-1               1               2016-12-31
   2017-1-3               2               2017-1-1
   2017-1-5               3               2017-1-2
   2017-1-6               4               2017-1-2
   2017-1-7               5               2017-1-2
   2017-1-8               6               2017-1-2
   2017-1-12           7               2017-1-5
   2017-1-13           8               2017-1-5
   2017-1-15           9               2017-1-6
   2017-1-16           10               2017-1-6
   2017-1-17           11               2017-1-6



//判断连续
select user_id,dt,carbonPerDay,date_sub(dt,row_number() over(partition by user_id order by dt)) diff
from t1 //t2

//判断连续的天数超过3天

select user_id,dt,carbonPerDay,diff,count(*) over(partition by user_id,diff) diffcount
from t2 //t3

// 过滤超过3天的数据
select user_id,dt
from t3
where diffcount>=3 //t4

// 关联原表求出结果即可

笑面天下

关注

5
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
Hive语法规范

库操作1.增 CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] // 库的注释说明 [LOCATION hdfs_path] // 库在hdfs上的路径 [WITH DBPROPERTIES (property_name=property_value, ...)]; // 库的属性例： create database if not exists ...
复制链接

扫一扫