SQL-запрос JOIN с таблицей

Я думаю, вы можете делать то, что хотите, с двумя запросами, но я не уверен на 100%. Часто в такой ситуации достаточно найти в первой таблице то, что не совпадает во второй таблице. Вы также пытаетесь получить «самое близкое» совпадение, поэтому это сложно.

Следующий запрос ищет совпадения для идентификатора пользователя и ровно одного из двух других полей, а затем объединяет их:

SELECT table2.buyer_id, table2.item_id, table2.created_time, prod_and_ts.*
from (select user_id, prod_and_ts.product_id as product_id, prod_and_ts.timestamps as timestamps
      from testingtable2 LATERAL VIEW
           explode(purchased_item) exploded_table as prod_and_ts
     ) prod_and_ts JOIN
     table2
     on prod_and_ts.user_id = table2.buyer_id and
        prod_and_ts.product_id = table2.item_id and
        prod_and_ts.timestamps <> UNIX_TIMESTAMP(table2.created_time)
union all
SELECT table2.buyer_id, table2.item_id, table2.created_time, prod_and_ts.*
from (select user_id, prod_and_ts.product_id as product_id, prod_and_ts.timestamps as timestamps
      from testingtable2 LATERAL VIEW
           explode(purchased_item) exploded_table as prod_and_ts
     ) prod_and_ts JOIN
     table2
     on prod_and_ts.user_id = table2.buyer_id and
        prod_and_ts.product_id <> table2.item_id and
        prod_and_ts.timestamps = UNIX_TIMESTAMP(table2.created_time)

Это не позволит найти ситуации, когда нет совпадения ни в одном из полей.

Кроме того, я написал это, используя синтаксис «on», а не «where». Я предполагаю, что HIVE поддерживает это.

Ваша репутация слишком высока, чтобы открыть дубликат и особенно 2 дубликата одного и того же вопроса.

Объединение двух таблиц в Hive с помощью HiveQL(Hadoop)

Объединить две таблицы и получить результат из обеих

У вас недостаточно информации, чтобы связать записи для третьего сценария.
Вы можете выполнить FULL OUTER JOIN с OR и получить все обратно, сопоставив строки, о которых у вас достаточно информации, как в первом, так и во втором случае. list и определите записи, которые вам не нужны, возвращая строки с нулевыми значениями для полей из несоответствующей таблицы в третьем сценарии.

SELECT DATEPART(d,B.T1time),DATEPART(d,A.Created_TIME),* 
FROM SO_Table1HIVE A
FULL OUTER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] 
AND (B.t1time = A.Created_TIME OR B.PRODUCTID = A.ITEM_ID)

Попытка сопоставления по третьему сценарию является взломом — информации там нет

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

SELECT DATEPART(d,B.T1time),DATEPART(d,A.Created_TIME),* 
FROM SO_Table1HIVE A
FULL OUTER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] 
AND (
    (B.t1time = A.Created_TIME OR B.PRODUCTID = A.ITEM_ID)
    OR
    (
        (A.Created_TIME <>  B.t1time AND B.PRODUCTID <> A.ITEM_ID AND DATEPART(d,B.T1time) = DATEPART(d,A.Created_TIME)) 
        AND a.ITEM_ID NOT IN(SELECT ITEM_ID
                   FROM SO_Table1HIVE A2
                   INNER JOIN SO_Table2HIVE B2 ON A2.BUYER_ID = B2.[USER_ID] AND (A2.Created_TIME =  B2.t1time OR B2.PRODUCTID = A2.ITEM_ID)
                   )

        AND B.PRODUCTID NOT IN(SELECT PRODUCTID
                   FROM SO_Table1HIVE A2
                   INNER JOIN SO_Table2HIVE B2 ON A2.BUYER_ID = B2.[USER_ID] AND (A2.Created_TIME =  B2.t1time OR B2.PRODUCTID = A2.ITEM_ID)
                   )
    )

)

Вы могли бы использовать RANK() или попробовать top one и т. д. RANK() или ROW_NO, вероятно, были бы лучшими из этих хаков, если бы это не был вопрос Hive, но, поскольку я знаю, что вы используете HQL, я не собираюсь это описывать. Вы можете вытащить их в отдельную таблицу и выполнить несколько логических запросов на обновление, чтобы обновить ее, а затем использовать ее в качестве таблицы поиска для привязки.

 tbl1Tbl2Lookup
 ---------------
 id int identity
 table1info FK
 table2info FK 

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