У меня есть некоторые данные (образец из полной таблицы), которые выглядят так:
| prov_id | hotel_id | m_id | apis_xml | company_id | yyyy_mm_dd |
|---------|----------|------|----------|------------|------------|
| 945 | 78888 | 3910 | [5] | 998 | 2020-05-20 |
| 1475 | 78888 | 6676 | [1,2,4] | 37 | 2020-05-20 |
| 1475 | 78888 | 6670 | [1,2,4] | 37 | 2020-05-20 |
| 945 | 78888 | 2617 | [5] | 998 | 2020-05-20 |
Я хочу найти самое низкое значение apis_xml для отеля и установить связанный prov_id как "primary_prov". Я могу сделать это, объединив и отсортировав массив по возрастанию, прежде чем взять первую строку. У меня есть запрос к этому:
SELECT
yyyy_mm_dd,
hotel_id,
prov_id as primary_prov
FROM(
SELECT
yyyy_mm_dd,
hotel_id,
prov_id,
apis_xml,
ROW_NUMBER() OVER(PARTITION BY yyyy_mm_dd, hotel_id ORDER BY apis_xml) rn
FROM(
SELECT
t.yyyy_mm_dd,
t.hotel_id,
t.prov_id,
t.apis_xml,
CAST(e.apis_xml AS INT) AS api
FROM
my_table t
LATERAL VIEW EXPLODE(apis_xml) e AS apis_xml
)s
)s
WHERE rn=1
Приведенный выше запрос работает для получения primary_prov, который соответствует наименьшему значению apis_xml. Однако я хочу иметь дополнительный столбец, содержащий дополнительные значения xml для справки. Возможно, карта здесь не лучший вариант использования, но это то, что приходит на ум. Ключом будет prov_id, а значением будут значения apis_xml. Вывод будет выглядеть так, так как мне нужно сохранить одну строку для каждого hotel_id:
| hotel_id | primary_prov | detailed_prov | yyyy_mm_dd |
|----------|--------------|--------------------------|------------|
| 78888 | 1475 | {1475: [1,2,4], 945: [5] | 2020-05-20 |
