广告定向筛选

背景：【定向筛选】定向的地区列表全部在香港、澳门中（若有一个定向不在香港、澳门，该广告则不计算）
例如：定向地区area_cn=>日本 澳大利亚 香港 澳门，则不统计，只统计定向地区area_cn=>香港 澳门 & area_cn=>澳门 & area_cn=>香港


select ds
    ,sum(str_to_map(ad_metric ,",","=>")["paidwater"]) as paidwater
from table
where ds between 20211117 and 20211117 
and str_to_map(ad_meta, ',', '=>')['aid'] in(
    with area_t as (
        select aid
            ,area_cnName
        from (
            select ds
                ,str_to_map(ad_meta, ',', '=>')['aid'] as aid
                ,split(str_to_map(ad_meta, ',', '=>')['area_cn'], ' ') as area_cn
            from table
            where ds between 20211117 and 20211117 
            and length(str_to_map(ad_meta, ',', '=>')['area_cn'])>2
        ) t
        LATERAL VIEW explode(area_cn) area_cnTable AS area_cnName
    ), aid_target_has_traval_eu as (    
        -- 提取含港澳及港澳以外地区的数据
        select aid
        from area_t
        where area_cnName in  ('香港特别行政区','澳门特别行政区')
    ), aid_target_has_traval_eu_and_other as (
        -- 提取不含港澳地区的数据
        select aid
            ,area_cnName 
        from area_t 
        where aid in (select aid from aid_target_has_traval_eu)
        and area_cnName not in ('香港特别行政区','澳门特别行政区')
    )
    -- 提取仅含港澳地区的数据
    select distinct t1.aid as aid
    from aid_target_has_traval_eu t1
    left join aid_target_has_traval_eu_and_other t2
    on t1.aid = t2.aid
    where t2.aid is null
)
group by ds