Блог

У меня есть 3 разные модели рекомендаций, которые дают мне результаты в трех разных таблицах.

Рекомендация 1 : В идеальной ситуации я хочу взять 2 лучшие рекомендации для каждого пользователя из этой таблицы, упорядоченной по возрастанию продуктовых рекомендаций.

Рекомендация 2 : В идеальной ситуации я хочу взять 3 лучшие рекомендации для каждого пользователя из этой таблицы на основе наивысшего балла.

Рекомендация 3 : В идеальной ситуации возьмите оставшиеся рекомендации из этой таблицы, чтобы добавить до 5 рекомендаций на пользователя

В конце концов, я хочу увидеть окончательный результат, который представляет собой объединение всех рекомендаций в одну, которая выглядела бы так.

Я хочу взять 5 лучших рекомендаций по 3 разным таблицам. К вашему сведению, не все идентификаторы пользователей могут отображаться во всех таблицах. В идеале я хочу взять ТОП-2 из рекомендации 1, ТОП-3 из рекомендации 2. Рекомендация 3 просто существует, так что, если в первых двух таблицах недостаточно рекомендаций, рекомендация 3 компенсирует это, поэтому в конце я получу 5 результатов на идентификатор пользователя. Мне не нужно ссылаться на рекомендацию 3, если я могу получить рекомендацию 5 (2 из рекомендации 1 и 3 из рекомендации 2). когда рекомендация 1 имеет Например, если в Рекомендации1 есть 1 рекомендация, то получите 4 рекомендации из Рекомендации2. В качестве альтернативы, если в рекомендации 1 содержится 0 рекомендаций, то получите 5 рекомендаций из рекомендации 2. Если Рекомендация 1 и рекомендация 2 не складываются в 5, тогда мне нужно обратиться к рекомендации 3. Мне нужно сделать это в SQL с большими запросами. Не могли бы вы, пожалуйста, помочь?

Спасибо за вашу помощь.

Рассмотрим следующий подход

 with output1 as (
  select *, null as Score, row_number() over win pos
  from Recommendation1 
  where true 
  qualify row_number() over win <= 2
  window win as (partition by UserID order by ProductRecommendation)
), output2 as ( 
  select *, 2   row_number() over win pos
  from Recommendation2 
  where not (UserID, ProductRecommendation) in (select as struct UserID, ProductRecommendation from output1)  
  qualify row_number() over win <= 5
  window win as (partition by UserID order by Score desc)
), output3 as (
  select *, 7   row_number() over win pos
  from Recommendation3
  where not (UserID, ProductRecommendation) in (select as struct UserID, ProductRecommendation from output1)  
  and not (UserID, ProductRecommendation) in (select as struct UserID, ProductRecommendation from output2)  
  qualify row_number() over win <= 5
  window win as (partition by UserID order by Score desc)
)
select * except(pos) from (
  select * from output1 union all 
  select * from output2 union all 
  select * from output3
)
where true 
qualify row_number() over win <=5
window win as (partition by UserID order by pos)
# order by UserID, pos           
 

если применить к образцам данных в вашем вопросе — результат будет

Ваше описание немного неясно. Ниже приведены 2 строки из первой таблицы для каждого пользователя, 3 строки из второй и дополнительные строки из третьей. Затем внешний запрос гарантирует, что для каждого пользователя имеется 5 строк (если они доступны).:

 select r.*
from ((select userid, recommendation, 1 as which
       from recommendation1
       where 1=1
       qualify row_number() over (partition by userid order by recommendation) <= 2
      ) union all
      (select userid, recommendation, 2 as which
       from recommendation2
       where 1=1
       qualify row_number() over (partition by userid order by score desc) <= 3
      ) union all
      (select userid, recommendation, 3 as which
       from recommendation3
      )
     ) r
where 1=1
qualify row_number() over (partition by userid order by which) <= 5;