hql---函数

• 聚合函数

  • count()
    • 表只有一个字段， count(*)最优
    • 表多个列，则 count(1)最优
    • mysql中：有主键，count(主键)最优
  • max(),min(),sum(),avg()

• 处理空值

  • nvl( column, str/colunm1)
    • 如果column值为null，返回str值，否则返回column值
    • 如果两个参数都为null，则返回null

• 统计

  • case sum(case sex when ‘’ then 1 else 0 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;根据部门分组
  

• 字符串操作

  • 截取
    • substring(column,start,end)
    • 例：substring(date1,7)=“2019-05” //截取日期的一部分
  • 拼接
    • concat(col1, “,”,col2,…)
      • 没有分隔符，连接同一行多个字段，可人为添加分隔符
    • concat_ws(",", col1, col2,…)
      • 指定分隔符","，连接多个字段，类型必须为String或array
  • 去重汇总
    • collect_set(col)
      • 对字段的所有值去重汇总，返回一个array类型字段
  • 嵌套
    • select * from table where column_name in(’’,’’,…)
    • select * from table where column_name in(select column_name1 from where…)

• 日期操作

  • year()，month(date)，day()
    • 返回年，月，日

• 排序

  • 全局排序order by
    • 一个Reducer，可以根据select中函数结果的别名
  • 内部排序sort by
    • 设置reduce数大于1，不然和order by没有区别，数据根据随机算法分发到不同reduce,防止数据倾斜，每个Reducer内部进行排序，对全局结果集来说不是排序
  • 分区排序distribute by+sort by
    • 分区distribute by
      • 设置reduce数=distribute分区数
        • reduce数>分区数，有空reduce
        • reduce数<分区数，报错
        • MR中：自定义分区号未从0开始，报错
    • map端分区，reduce端排序
    • distribute by语句要写在sort by语句之前
    • 例：select*from Table distribute by itemid sort by itemid
  • cluster by
    • 相当于distribute by和sort by字段相同，只能默认升序
  • 排名rank
    • rank() 值相同时，排名一样，总数不会变，1,1,3,4,5
    • dense_rank() 值相同时，排名一样，总数会减少，1,1,2,3,4
    • row_number() 会根据顺序计算，1,2,3,4,5