Join连接条件

Hive 只支持等值连接（equality joins）、外连接（outer joins）和（left semi joins）。Hive 不支持所有非等值的连接，因为非等值连接非常难转化到 map/reduce 任务。

  • LEFT，RIGHT和FULL OUTER关键字用于处理join中空记录的情况
  • LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现
    • 限制：JOIN 子句中右边的表只能在 ON 子句中设置过滤条件

  • join 时，每次 map/reduce 任务的逻辑是这样的：reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统

    With AS中间临时表

  • 格式

    • with temp_tablename as （select ），as （select ）….
  • 可读性强
  • 执行时间快，每次只需要创建一次表结果

开窗函数

  • 格式
    • xxx() over(partition by fileName order by fileName desc/asc) as num
      • 如果 over 不提供分组方法,那就计算后的值会显示在每一列的值
  • rank（）
    • 函数对于排序后相等的值，排序序号是一样的且下一个不相等值之后序号是间隔的例如：1,2,2,4
  • 与group by的区别
    • 执行顺序
      • gb 先进行计算
      • 窗口函数在group by之后计算
    • 排序范围不同
      • 普通的order by整体排序
      • 窗口函数order by只决定开窗内的排序顺序
    • 展示的数据形式
      • group by只保留于分组字段聚合的结果
      • 开窗可以保留表中的全部数据

  • 其他开窗函数

    • 首尾值
      • first_value
      • end_value
    • 移动
      • lag（expr[,offset][,default]）向下位移，滞后
      • lead(expr[,offset][,default]）向上位移，超前

        Hive中的自定义函数

  • UDF(User-Defined-Function)，一进一出，即最基本的自定义函数，类似to_char,to_date等

  • UDAF（User- Defined Aggregation Funcation），多进一出，聚合函数，类似在 group by 之后使用的 sum,avg 等
  • UDTF(User-Defined Table-Generating Functions)，一进多出，生成函数，有点像stream 里面的 flatMap

分组聚合函数，Collect 列转行

  • collect_list 重复 和 collect_set 去重
  • 格式定义
    • — 将 colname 指定的列值聚合为一个数组
    • array collect_list();
  • 语法
    • SELECT a, collect_list(b) as list FROM tb group by a；
  • 可结合Distinct使用
    • SELECT collect_list(DISTINCT name) FROM students;
  • array 元素有序可用sort_array
    • select sort_array(collect_set(col)) AS col;
  • 优点
    • 突破行数限制，先聚合在分解

ntile 分组切片函数

概念：

Hive SQL 的 ntile 函数用于将分组数据按照顺序切分成n组，并返回当前切片值。如果切片不均匀，默认增加第一个切片的分布。它把有序的数据集合「平均分配」到指定的数量（n）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差 1。

语法格式：
  1. bigint ntile(bigint <n>) over (partition by <col1>[, <col2>…]  
  2. [order by <col1>[asc|desc][, <col2>[asc|desc]…]] [<windowing_clause>]))

参数：

  • n：必填，代表切片的数量，为 BIGINT 类型；
  • partition by [, …]：至少指定 1 个开窗口的列；
  • order by col1[asc|desc][, col2[asc|desc]…]：可选，指定返回结果的排序方式。

返回：

  • 返回 BIGINT 类型

它需要家配合窗口函数一起使用，将窗口分组数据按照顺序切片，并返回切片值。