hivesql -- array_contains()函数使用

array_contains函数解析

Hive中的array_contains函数与SQL中的 in关键字 操作类似，用于判定
包含（array_contains）或不包含（!array_contains）关系。与
in不同的是array_contains可以用于判断一张表中同一个id的多条记录中的同一字段是否包含指定的一个或多个值。需要注意字段类型保持一致，若不一致则需要进行强制类型转换。

语法：

array_contains(数组，值)，返回布尔类型值。

array_contains(数组，值)

array_contains()函数在where条件中的案例

解决使用某个字段条件过多，查询条件失效

select * 
from login 
where dt='20130101' 
  and (ver !='3.1' or ver !='3.2' 
                   or ver != '4.0' 
                   or ver != '5.2');

select * 
from login 
where dt='20130101' 
and (ver !='3.1' and ver !='3.2' 
                 and ver != '4.0' 
                 and ver != '5.2');

这个语句的目的是从一个用户登陆表中，查询20130101当天，版本不是3.1,3.2,4.0,5.2的登陆用户记录。但是，在hive0.90执行，最后返回的是20130101当天全部的登陆用户记录，后面的查询条件看起来是被忽略了。这里看起来同一个字段做这种多个!=或者=的条件查询，过多会失效。

于是修改了语句，如下:

select * 
from login 
where dt=20130101 
and !array_contains(split('3.1,3.2,4.0,5.2',','),ver);

array_contians()直接应用案例：

1、使用array_contians()对监控指标打标签，判断报警级别。

select
    task_id,
    task_code,
    task_version,
    case
        when array_contains(collect_set(tag_id), 599)
            or array_contains(collect_set(tag_id), 1225)
        then 'L1-级别'
        when array_contains(collect_set(tag_id), 600)
            or array_contains(collect_set(tag_id), 1226)
        then 'L2-级别'
        when array_contains(collect_set(tag_id), 601)
            or array_contains(collect_set(tag_id), 1240)
        then 'L3-级别'
        when array_contains(collect_set(tag_id), 1166)
            or array_contains(collect_set(tag_id), 1241)
        then '鹰眼监控汇总'
        else '非鹰眼监控任务'
    end as core_tag_name
from
    fdm.fdm_buffalo_uniontask_tag_theme_info_new
where
    dt = sysdate( - 1)
group by
    task_id,
    task_code,
    task_version

2、使用array_contians()对用户打标签，在用户画像，维度搭建中发挥作用

select
   user_id,
   count(*) as card_number    -- 使用过的卡数
   -- 只要任意一张卡有效即判定为VIP有效,
   case when array_contains(collect_set(status_code),cast(1 as smallint)) 
        then 1 
        else 0 
    end AS effective_flag   -- 卡有效标识
from edw_users.dwd_edw_user_vipcard_df  -- 用户VIP卡购买使用全量表
where dt = '${dt}'
and user_id > 0
and deleted_flag = 'N'
group by user_id