hive 常见小技能_hive %

实习期间整理小技能，如有问题，欢迎指正！

1、a left anti join b : 在查询过程中，剔除a表中和b表中有交集的部分

常见应用场景：求增量数据时常用此功能剔除之前数据。

2、查询未进行聚合运算的字段时，需对其进行group by。

注意group by 和distinct 不能同时使用。

3、substr（a,2,5）: 提取a字段中2至5位置的字符；例如：substr(getdate(),1,7) 提取当天日期的年月信息。

date_sub(date,3) : 提取date往前第三天的日期；例如：date_sub(substr(getdate(),1,10),1)提取昨天的日期；

***date_sub(getdate(),pmod(datediff(getdate(),'1900-01-08'),7)+1)  提取上周日的日期；

dp between date_sub(getdate(),pmod(datediff(getdate(),'1900-01-08'),7)+7) and date_sub(getdate(),pmod(datediff(getdate(),'1900-01-08'),7)+1) 限定日期为上周

4、nvl(a,0) :  去除空值；例如：nvl(is_operation,0) 表示如果是空值，则返回结果0;

     coalesce ( expression,value1,value2……,valuen)  返回包括expression在内的所有参数中的第一个非空表达式。

例如：coalesce(b.hospital_id,a.hospital_id) 表示若b表hospital_id 不为空则返回b.hospital_id,否则返回a.hospital_id。

5、字符串转换成整数：cast(字段名 as int)

6、dp 带参运行：例如  dp= '${rundt}'        dp between '${startdt}' and '${enddt}'

7、计数方式：例如，统计x=3687的数量  count(if(x=3687,1,null)) 或者 sum(if(x=3687,1,0))

8、字符连接函数：concat( ) 例如，concat( round(sum(use_gmv/all_gmv)*100,2),'%') 给比值加百分号

9、提取机构最新名称范例

select hospital_id ,name

    from (select hospital_id,name,row_number() over (partition by hospital_id order by dp DESC)  as  rk1----以hospital_id 进行分组，以dp降序排序

               from soyoung_dw.dim_hospital_info 

              where dp between date_sub(substr(getdate(),1,10),7) and date_sub(substr(getdate(),1,10),1) )

    where rk1 =1