判断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
