hive五子句及相关函数

day11课程总结
    一、HQL中的五子句
        1、什么是五子句？
            无论MySQL还是Hive中都存在5个关键词
                where
                group by
                having
                order by
                limit
        2、五子句有自己的执行顺序，位置不能颠倒
            select 字段 from 数据表  where  group by  having  order by  limit
        3、where子句
            对原表中的数据进行筛选
                等于判断
                    id = 1
                比较运算符
                    age >= 18 and age <= 60
                范围查询
                    between...and
                        between 18 and 60
                    in
                        select * from 数据表  where 字段 in (x, y, z)
                        delete from 数据表 where 字段 in (x, y, z)
                逻辑运算符
                    and
                        age >= 18 and age <= 60
                    or
                        where subject = 'bigdata' or subject = 'ui'
                    not
                        取反
                like模糊查询
                    信息检索
                        首
                            张%
                        尾
                            %强
                        中
                            %关键字%
                        _
                            代表匹配任意某个字符
        4、group by子句
            分组子句
                为什么要进行分组?为了更好的进行数据统计
                按性别分组、按学科分组、按部门分组、按国家、城市进行分组...
            聚合函数
                count()
                    返回总记录数
                max()
                    最大值
                min()
                    最小值
                sum()
                    求和
                avg()
                    求平均值
            扩展
                数据倾斜
                    简单来说就是一个大的任务会划分为多个MapTask，在Map阶段会执行分区、排序、规约操作，但是如果某个分区的数据较多（超过了其他所有分区），在Reduce阶段拉取数据时，会导致某个Reduce长时间无法完成。
                    数据分桶
                    提前进行Map端聚合
                        set hive.map.aggr=true;
        5、having子句
            havng子句与where子句类似，都是用于筛选数据，但是两者之间略有区别
                ① where子句先发生
                    group by之前
                ② having子句后发生
                    group by之后
                ③ having子句中可以使用聚合函数
                    having count(*) > 3
                ④ where子句中不可以使用聚合函数
                    因为分组聚合以后才会产生聚合结果，这个时候where已经结束了
        6、order by子句
            全局排序
                所有的结果最终汇总在一个reduce中
                    保证全局有序
                        如果数据量比较大，执行会比较缓慢
        7、limit子句
            ① 限制查询数量
                select * from 数据表  limit  数量;
            ② 分页查询
                数据分页底层就是limit
                    分页公式
                        每页显示数量
                            select * from 数据表 limit (当前页-1) * pagesize，pagesize；
    二、Hive中的四个by语句
        1、说明有哪四个by语句
            order by  字段   asc|desc
            cluster by 字段
            distribute by 字段
                数据分发（分组）
            sort by 字段
        2、order by  字段  asc或desc
            全局排序
                只有一个Reduce
                    保证全局有序
        3、cluster by 字段
            如果数据分组 与 排序是同一个字段，则可以采用cluster by
                定义reduce数量
                    2
                按照学号分发数据
                针对每个分组中的数据进行学号升序排列
        4、distribute by字段
            数据分发（分组）
                把指定字段通过哈希求余
                    分散到多个Reduce中
                        定义reduce数量
        5、sort by 字段 asc | desc
            针对Reduce中的数据进行排序操作
                asc升序
                desc降序
            配合distribute by一起使用
    三、union联合查询
        1、union的作用
            把多个select查询结果进行合并操作
                表1
                表2
        2、union有两个关键词
            union
                等价于
                    union distinct
                        合并后去重
            union all
                对多个select语句进行合并
                    合并后不去重
        3、union注意事项
            返回的select列的（字段的）数量必须一致
    四、CTE公用表达式
        1、基本语法
            with  临时表名称  as (select查询)
                select
                    多层查询
                insert
                    把前面的查询结果写入到目标表中
        2、典型应用场景
            MySQL
            Hive
            分组求每个组中的某个排名
            求Top N问题
        3、Top N问题
            ① with子句
            ② select * from 临时表 where 排名字段
    五、Hive JOIN多表连接
        1、JOIN应用场景
            在实际工作中，JOIN经常进行多表连接操作
        2、cross join交叉连接
            本身没有意义
            交叉连接是所有连接的基础
            交叉产生结果
                A表和B表
                    字段
                        A + B
                    记录
                        A * B
                            笛卡尔积
        3、inner join内连接查询
            在交叉连接基础上只返回满足on关联条件的结果
        4、左右外连接
            有主表概念
                连接完成后，会保留主表的所有数据
                    left join on
                        左表就是主表
                    right join on
                        右表就是主表
        5、全外连接查询（新增）
            full join on
                先进行左连接
                在进行右连接
        6、left semi join（新增）
            左半开连接
                A表
                B表
        7、JOIN使用注意事项
            允许使用复杂的联接表达式
            支持连接两张以上数据表
            如果每个表在联接子句中使用相同的列，则Hive将多个表上的联接转换为单个MR作业
                A
                B
                C
            大表和小表进行连接，则小表放在前面，大表放在后面！
            在join的时候，可以通过语法STREAMTABLE提示指定要流式传输的表。如果省略STREAMTABLE提示，则Hive将流式传输最右边的表。
             join在WHERE条件之前进行
             如果除一个要连接的表之外的所有表都很小，则可以将其作为仅map作业执行