Блог

Я запрашиваю некоторые data (SQL, presto) , я изо всех сил пытаюсь объединить 2 таблицы. Мне нужно знать, с какими командами связаны проблемы, на основе тегов команд, указанных в таблице тегов. Каждая проблема может быть связана с многочисленными командами.

Таблица 1 team :

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

Таблица 2 issues :

 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 с ними могут быть динамическими, но не уверен, как подойти к этой проблеме

Приведенный ниже SQL преобразует значения тегов в обеих таблицах для преобразования из массива в отдельные значения, чтобы их можно было объединить в 2 таблицы для групп запросов и отображения и их соответствующих проблем.

   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;
  

Я последовал вашему примеру в 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