Hive 检查数组中的元素 [英] Hive check elements in array

问题描述

我有一个由 user_id 和 item_id(用户购买的物品的 id)组成的 Hive 表.我想获取购买了第 1 项但不购买第 2 项和第 3 项的所有用户的列表.

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

为此我写了一个简单的查询:

To do this I wrote the 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???

如您所见，我不知道如何检查数组 itemslist 是否包含 1 而非 2.

As you can see, I don't know how to check whether the array itemslist contains 1 and not 2.

你是怎么做到的?如果有更有效的方法，你能告诉我两种(或更多)方法吗?

How do you do this? If there is some more efficient way can you please tell me both (or more) methods?

推荐答案

Hive 中有一些集合功能`(参见 集合函数 在这里:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF ) 可以在这里使用.

There are some collection functions in Hive `(See collection functions here : https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF ) which can use here.

您可以使用 array_contains(Array, value) 函数来检查项目 1 是否存在，而 size(Array) 函数可以使确保长度为1.如果两个条件都满足，您将获得所需的输出.

You can use the array_contains(Array<T>, value) function to check if item 1 is present and the size(Array<T>) function to make sure the length is 1. If both conditions are satisfied, you will get the desired output.

这篇关于Hive 检查数组中的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！