hive array_contains

判断array是否含有某个元素

SELECT 
    falg_a,
    falg_b,
    falg_c,
    falg_d,
    ROUND(COUNT(uid)/100,4) AS u_num
FROM
    (SELECT 
        uid,
        ARRAY_CONTAINS(pages_sns, "a") AS falg_a,
        ARRAY_CONTAINS(pages_sns, "b") AS falg_b,
        ARRAY_CONTAINS(pages_sns, "c") AS falg_c,
        ARRAY_CONTAINS(pages_sns, "d") AS falg_d
    FROM
        (SELECT 
            uid, 
            -- COLLECT_LIST(page_sn)
            COLLECT_SET(page_sn) AS pages_sns
        FROM yuqiu.ddx_pv_tmp0
        WHERE 
            bigint(uid)>0
        GROUP BY 
            uid
        )t0 
    )t2
GROUP BY 
    falg_a,
    falg_b,
    falg_c,
    falg_d
ORDER BY 
    falg_a DESC,
    falg_b DESC,
    falg_c DESC,
    falg_d DESC
LIMIT 200
;

SELECT 
    uid
FROM
    (SELECT 
        uid,
        ARRAY_CONTAINS(pages_sns, "a") AS falg_a,
        ARRAY_CONTAINS(pages_sns, "b") AS falg_b,
        ARRAY_CONTAINS(pages_sns, "c") AS falg_c,
        ARRAY_CONTAINS(pages_sns, "d") AS falg_d
    FROM
        (SELECT 
            uid, 
            -- COLLECT_LIST(page_sn)
            COLLECT_SET(page_sn) AS pages_sns
        FROM yuqiu.ddx_pv_tmp0
        WHERE 
            bigint(uid)>0
        GROUP BY 
            uid
        )t0 
    )t2
WHERE !falg_a