Блог

У меня есть следующий набор данных в snowflake с двумя столбцами — user и matched user. Я хочу пройти по таблице через matched_user и присвоить то же значение пользователя, если найдено совпадение. Например, user1000 соответствует user100, а user 100 соответствует user200, user1000, поэтому user1000 присваивается этим трем пользователям. В то время как для user2000 совпадения нет. Есть ли эффективный способ добиться этого без процедурного кода и циклов?

 user   | matched_user| Desired Output
user1000|   user100 | User1000
user1000|   user1000| User1000
user200 |   user200 | User1000
user200 |   user100 | User1000
user100 |   user100 | User1000
user100 |   user200 | User1000
user100 |   user1000| User1000
user2000|   user2000| user2000

  

Это проблема обхода графа.

Для этого вы можете использовать рекурсивный CTE. Начните сверху и продвигайтесь вниз. Поскольку у вас есть циклы, это довольно сложно. У меня нет Snowflake для тестирования, но логика такая:

 with recursive tt as (
      select usr, matched_user from t
      union  -- on purpose to get bidirectional edges
      select matched_user, usr from t
     ), 
     cte as (
      select usr, matched_user, usr as desired, to_array('') as visited, 1 as lev
      from tt t
      union all
      select t.usr, t.matched_user, cte.desired, array_concat(cte.visited, t.usr), cte.lev   1
      from cte join
           tt t
           on t.usr = cte.matched_user
      where lev < 5 and
            array_position(t.usr, cte.visited,) is null
     )
select usr, max(desired)
from cte
group by usr;
  

Это присваивает desired столбец на уровне каждого пользователя. Вы можете присоединиться к исходным данным, если вам это нужно, с помощью деталей edge. Кроме того, desired для первой группы это user200 потому, что это максимальное значение.

Вот скрипка db<> с использованием Postgres.