Оставшиеся комбинации перекрестного соединения

Я пытаюсь построить таблицу, в которой будут представлены все продукты, которые я мог бы продать, на основе текущих.

Таблица статусов товаров

+-------------+--------------+----------------+
| customer_id | product_name | product_status |
+-------------+--------------+----------------+
|           1 | A            | Active         |
|           2 | B            | Active         |
|           2 | C            | Active         |
|           3 | A            | Cancelled      |
+-------------+--------------+----------------+

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

Таблица портфолио


+--------------+------------+----------+
| product_name |  status_1  | status_2 |
+--------------+------------+----------+
| A            | Inelegible | Inactive |
| B            | Inelegible | Inactive |
| C            | Ineligible | Inactive |
| D            | Inelegible | Inactive |
+--------------+------------+----------+

В моем коде я попытался использовать CROSS JOIN, чтобы получить 4 строки для каждого customer_id. К сожалению, для клиентов, у которых есть более одного продукта, у меня есть двойные/тройные строки.

Это мой код:

SELECT
    p.customer_id,
    CASE WHEN p.product_name = pt.product_name THEN p.product_name ELSE pt.product_name END AS product_name,
    CASE 
        WHEN p.product_name = pt.product_name THEN p.product_status 
        ELSE pt.status_1
    END AS product_status
FROM 
    products AS p
CROSS JOIN
    portfolio as pt

Это мой текущий вывод:


+----+-------------+--------------+----------------+
| #  | customer_id | product_name | product_status |
+----+-------------+--------------+----------------+
|  1 |           1 | A            | Active         |
|  2 |           1 | B            | Inelegible     |
|  3 |           1 | C            | Inelegible     |
|  4 |           1 | D            | Inelegible     |
|  5 |           2 | A            | Ineligible     |
|  6 |           2 | A            | Ineligible     |
|  7 |           2 | B            | Active         |
|  8 |           2 | B            | Ineligible     |
|  9 |           2 | C            | Active         |
| 10 |           2 | C            | Ineligible     |
| 11 |           2 | D            | Ineligible     |
| 12 |           2 | D            | Ineligible     |
| 13 |           3 | A            | Cancelled      |
| 14 |           3 | B            | Ineligible     |
| 15 |           3 | C            | Ineligible     |
| 16 |           3 | D            | Ineligible     |
+----+-------------+--------------+----------------+

Как видите, для customer_id 2 у меня есть две строки для каждого продукта, содержащие продукты B и C с разными статусами, чем у меня в таблице product_status.

В этом случае я хотел бы получить таблицу с 12 строками, в которой показан текущий продукт/статус из таблицы product_status, а оставшиеся продукты/статусы из таблицы портфолио добавлен.

Ожидаемый результат


+----+-------------+--------------+----------------+
| #  | customer_id | product_name | product_status |
+----+-------------+--------------+----------------+
|  1 |           1 | A            | Active         |
|  2 |           1 | B            | Inelegible     |
|  3 |           1 | C            | Inelegible     |
|  4 |           1 | D            | Inelegible     |
|  5 |           2 | A            | Ineligible     |
|  6 |           2 | B            | Active         |
|  7 |           2 | C            | Active         |
|  8 |           2 | D            | Ineligible     |
|  9 |           3 | A            | Cancelled      |
| 10 |           3 | B            | Ineligible     |
| 11 |           3 | C            | Ineligible     |
| 12 |           3 | D            | Ineligible     |
+----+-------------+--------------+----------------+

Не уверен, что CROSS JOIN — лучшая альтернатива, но теперь у меня заканчиваются идеи.


hive hiveql cross-join
person Heber Brandao    schedule 02.12.2020    source источник
comment
ожидаемый результат ??   -  person Srinivas    schedule 02.12.2020
comment
@Srinivas готово!   -  person Heber Brandao    schedule 02.12.2020
comment
@Srinivas Я использую чистый улей. не искра   -  person Heber Brandao    schedule 02.12.2020

Ответы (1)


arrow_upward
0
arrow_downward

ИЗМЕНИТЬ:

Я подумал о другом более чистом решении. Сначала выполните перекрестное соединение, затем правое соединение для customer_id и product_name и объедините статусы продуктов.

SELECT customer_id, product_name, coalesce(product_status, status_1)
FROM products p
RIGHT JOIN (
    SELECT * 
    FROM (SELECT DISTINCT customer_id FROM products) pro
    CROSS JOIN portfolio
) pt
USING (customer_id, product_name)
ORDER BY customer_id, product_name

Старый ответ: идея состоит в том, чтобы включить информацию обо всех названиях продуктов для customer_id в список и проверить, находится ли продукт в портфолио в этом списке.

(SELECT customer_id, pt_product_name as product_name, first(status_1) as product_status
FROM (
    SELECT
        customer_id,
        p.product_name as p_product_name,
        pt.product_name as pt_product_name,
        product_status,
        status_1,
        status_2,
        collect_list(p.product_name) over (partition by customer_id) AS product_list
    FROM products p
    CROSS JOIN portfolio pt
    )
WHERE NOT array_contains(product_list, pt_product_name)
GROUP BY customer_id, product_name)

UNION ALL

(SELECT customer_id, p_product_name as product_name, first(product_status) as product_status
FROM (
    SELECT
        customer_id,
        p.product_name as p_product_name,
        pt.product_name as pt_product_name,
        product_status,
        status_1,
        status_2,
        collect_list(p.product_name) over (partition by customer_id) AS product_list 
    FROM products p
    CROSS JOIN portfolio pt)
WHERE array_contains(product_list, pt_product_name)
GROUP BY customer_id, product_name)

ORDER BY customer_id, product_name;

который дает

+-----------+------------+--------------+
|customer_id|product_name|product_status|
+-----------+------------+--------------+
|          1|           A|        Active|
|          1|           B|    Inelegible|
|          1|           C|    Ineligible|
|          1|           D|    Inelegible|
|          2|           A|    Inelegible|
|          2|           B|        Active|
|          2|           C|        Active|
|          2|           D|    Inelegible|
|          3|           A|     Cancelled|
|          3|           B|    Inelegible|
|          3|           C|    Ineligible|
|          3|           D|    Inelegible|
+-----------+------------+--------------+

К вашему сведению, фрагмент перед UNION ALL дает:

+-----------+------------+--------------+
|customer_id|product_name|product_status|
+-----------+------------+--------------+
|          1|           B|    Inelegible|
|          1|           C|    Ineligible|
|          1|           D|    Inelegible|
|          2|           A|    Inelegible|
|          2|           D|    Inelegible|
|          3|           B|    Inelegible|
|          3|           C|    Ineligible|
|          3|           D|    Inelegible|
+-----------+------------+--------------+

И кусок после UNION ALL дает:

+-----------+------------+--------------+
|customer_id|product_name|product_status|
+-----------+------------+--------------+
|          1|           A|        Active|
|          2|           B|        Active|
|          2|           C|        Active|
|          3|           A|     Cancelled|
+-----------+------------+--------------+

Надеюсь, это поможет!

person mck    schedule 02.12.2020
comment
Действительно хорошее понимание массивов и FIRST. Мне пришлось немного подправить, так как в улье доступно только значение first_value, но мои результаты соответствуют ожиданиям! спасибо - person Heber Brandao; 02.12.2020
comment
@HeberBrandao Я добавил более приятное решение - посмотрите, поможет ли это! - person mck; 03.12.2020