Hive / SQL-левое соединение с резервным вариантом

В Apache Hive у меня есть таблицы, которые я хотел бы соединить слева, сохраняя все данные из левых данных и добавляя данные, где это возможно, из правой таблицы. Для этого я использую два соединения, потому что соединение основано на двух полях (material_id и location_id). Это прекрасно работает с двумя традиционными левыми соединениями: 

SELECT 
   a.*, 
   b.*
FROM a
INNER JOIN (some more complex select) b
   ON a.material_id=b.material_id 
   AND a.location_id=b.location_id;

Для location_id база данных содержит только два различных значения, скажем 1 и 2.

Теперь у нас есть требование, что если нет " идеального соответствия", это означает, что только material_id могут быть объединены, и не существует правильного сочетания material_id и чего ты больше (например, material_id=100 а-не боитесь ли=1) для соединения для чего ты больше в б-таблица, присоединяйтесь должны "по умолчанию" или "запасной вариант" на другие возможные значения-не боитесь ли, например, material_id=001 и-не боитесь ли=2, и наоборот. Это должно иметь место только для location_id.

Мы уже рассмотрели все возможные ответы также с CASE и т. д. но безрезультатно. Установка типа 

...
ON a.material_id=b.material_id AND a.location_id=
CASE WHEN a.location_id = b.location_id THEN b.location_id ELSE ...;

Мы попытались или не поняли, как на самом деле сделать в языке запросов hive.

Спасибо за вашу помощь! Может быть, у кого-то есть умная идея.

Вот некоторые примеры данных: 

Table a
| material_id | location_id | other_column_a |
| 100         | 1           | 45            |
| 101         | 1           | 45            |
| 103         | 1           | 45            |
| 103         | 2           | 45            |



Table b
| material_id | location_id | other_column_b |
| 100         | 1           | 66            |
| 102         | 1           | 76            |
| 103         | 2           | 88            |


Left - Join Table
| material_id | location_id | other_column_a | other_column_b
| 100         | 1           | 45            | 66
| 101         | 1           | 45            | NULL (mat. not in b)
| 103         | 1           | 45            | DEFAULT TO where location_id=2 (88)
| 103         | 2           | 45            | 88

PS: Как сказано здесь существует и т. д. не работает в подзапросе ON.

2 2
sql hadoop hive

2 ответа:

Решение состоит в том, чтобы оставить соединение без a.location_id = b.location_id и пронумеровать все строки в порядке предпочтения. Затем выполните фильтрацию по номеру строки. В коде ниже соединение будет дублировать строки сначала, потому что все соответствующие material_id будут объединены, затем row_number() Функция назначит 1 строкам, где a.location_id = b.location_id и 2 строкам, где a.location_id <> b.location_id Если существуют также строки, где a.location_id = b.location_id и 1, если таких нет. b.location_id добавляется к функции order by в функции row_number (), поэтому она будет" предпочитать " строки с более низким значением b.location_id, если нет точных совмещение. Надеюсь, вы уловили мою мысль. 

select * from 
(
SELECT 
   a.*, 
   b.*,
   row_number() over(partition by material_id 
                     order by CASE WHEN a.location_id = b.location_id THEN 1 ELSE 2 END, b.location_id ) as rn
FROM a
LEFT JOIN (some more complex select) b
   ON a.material_id=b.material_id 
)s 
where rn=1
;
0

Может быть, это полезно для кого-то в будущем:

Мы также придумали другой подход.

Во-первых, мы создаем другую таблицу для вычисления средних значений из таблицы b на основе material_id по всем (!) пункты назначения.

Во-вторых, в таблице join мы создаем три столбца: c1-значение, в котором material_id и location_id совпадают (результат левого соединения таблицы a с таблицей b). Этот столбец является нулевым, если нет идеального соответствия.

C2-значение из таблица, в которую мы записываем число из таблицы averages (fallback) для этого material_id (независимо от местоположения)

C3-столбец" фактическое значение", в котором мы используем оператор case, чтобы решить, является ли столбец 1 нулевым (нет идеального соответствия материала и местоположения), а затем мы используем значение из столбца 2 (среднее значение по всем другим местоположениям для материала) для дальнейших вычислений.

0