hive的语法使用规则

这是我参与2022首次更文挑战的第28天，活动详情查看：2022首次更文挑战

上文介绍了hive的代码编写规则和sql设计规则，本文继续介绍一些hive语法使用和范例。

Hive-Hadoop插入数据或者数据存储规则：

数据尾插形式,因此新增字段不要添加到原字段中间。

在接到需求后，一定要先看一遍产品是否提供了dw层的表、字段、枚举类型等等。

一般都是会单独维护枚举类型在dim表。

db_name 数据库名、tb_name 表名 、col_name 列名

-- 学生类型 枚举 --
left join
(
    select
        student_code
        ,student_name as group_type
    from dim.dim_dec_student_enum_da
    where pt = '${-1d_pt}'
        and db_name = "nrs_comp_group_0"
        and tb_name = "group"
        and col_name = "group_type"
) c on b.group_type = c.student_code

Grouping Sets用法练习

在实际业务中，常常会要求按照多个不同的维度进行数据的聚合展示，

例如：按照年级和楼层聚合学生人数，假定年级是g，楼层是s，人数为num

按照g，s字段分别进行统计，有三种情况：(g，s)、(g)、(s)

下面分别对比一下常规写法和Grouping Sets的写法：

SELECT g,s,sum(num) AS total_num
  FROM DW_C.student
 GROUP BY g,s
 UNION ALL
SELECT a,sum(num) AS total_num
  FROM DW_C.student
 GROUP BY g
 UNION ALL 
SELECT b,sum(num) AS total_num
  FROM DW_C.student
 GROUP BY s

Grouping Sets 写法:

select g,s,sum(num)
from DW_AAA.BBB
group by g,s
grouping sets (
    (g,s)
    ,g
    ,s
)

如上设计，可以让代码简洁很多，并且生成的job数也变少且计算的效率提高了（UNION ALL会多次扫描表，造成服务压力）

JSON字符提取

get_json_object(extra,'$.teacherId') teacherId   -- 老师

空值默认值处理:

nvl(t2.order_name,'-100') as order_name

开窗函数:

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

计数类:

count(distinct if(question_result in ('9','10'),bus_id,null)) as ticket_good_cnt
数据存储类型 = int / bigint

比例类:

count(distinct if(question_result in ('9','10'),bus_id,null))/count(distinct bus_id) as ticket_good_rate
存储的数据类型 = double

针对查询A不存在,则取B值,B值不存在,则取C值

coalesce(t3.area_org_name,t32.area_org_name,'-100') as area_org_name   -- 优先级: 老师, 学生