Array validation elements

Question

I have a Hive table made from user_id and item_id (identifier of items that were purchased by the user). I want to get a list of all users who purchased item 1, but not 2 and 3.

To do this, I wrote a simple query:

SELECT user_id, collect_set(item_id) itemslist FROM mytable
WHERE item_id in (1, 2)
GROUP BY user_id
HAVING -- what should I put here???

As you can see, I do not know how to check if the list of elements in the array contains 1, not 2.

How do you do this? If there is a more efficient way, can you tell me both (or more) of the methods?

Answer 1

Hive (. : https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF), .

array_contains(Array<T>, value), 1, size(Array<T>), , 1. , .