跨表比较数组

Question

我正在查询一些data (SQL, presto)，我很难连接2个表。我需要根据标签表中列出的团队标签来了解问题与哪些团队相关。每个问题都可能与多个团队相关联。

表1team:

name  | tags
------+--------------------
team1 | [1234, 2345, 5678]
team2 | [6789, 4321]
team3 | [4530]
team4 | [6532]
team5 | [3452, 34234, 868686]

表2issues:

issue_id | tags
---------+--------------------------------------------
1     | [312312, 45345, 4535,1111,4533,4530, 4321 ]
2     | [312312, 45345, 6532]
3     | [6532]
4     | [312312, 1234, 4321]
5     | [312312]

我需要它看起来像：

issue_id | team
---------+--------
1        | team3
1        | team2
2        | team4
3        | team4
4        | team1
4        | team2

我希望避免硬编码，因为teams及其关联的tags的列表可以是动态的，但不确定如何处理这个问题

Answer 1

下面的SQL分解两个表中标记的值，将其从数组转换为单个值，这样它们就可以跨两个表进行连接，以查询和显示团队&他们相应的问题。

Select T2.issue_id as issue_id,
T1.name as team
from
(SELECT name, tag
FROM table1
CROSS JOIN UNNEST(tags) AS t (tag)) T1
JOIN
(SELECT issue_id, tag
FROM table2
CROSS JOIN UNNEST(tags) AS t (tag)) T2
ON T1.tag = T2.tag;

Answer 2

我在Snowflake中以您为例，它具有flatten函数。从我在presto的文档中看到的，它确实也有。也许这会有所帮助？

在Snowflake，我设法满足了你的要求。

with ISSUES as(
SELECT 
T.ISSUE_ID,
X.VALUE::INTEGER AS TAGS
FROM PUBLIC.ISSUES T, LATERAL FLATTEN(T.TAGS) X),
TEAMS as(
SELECT 
T.TEAM,
X.VALUE::INTEGER AS TAGS
FROM PUBLIC.TEAMS T, LATERAL FLATTEN(T.TAGS) X)

SELECT 
ISSUES.ISSUE_ID, 
TEAMS.TEAM FROM TEAMS
INNER JOIN ISSUES on TEAMS.TAGS = ISSUES.TAGS