Hive语法规范_serdeproperties指定每行结尾

库操作

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

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