Использование Lateral Join для преобразования столбцов в строки

Преобразование столбцов в строки — это распространенная операция в ETL-обработке. Lateral — это специальное ключевое слово Join, которое может связать строку с внутренним подзапросом или табличной функцией. Используя Lateral в сочетании с unnest(), вы можете развернуть одну строку в несколько строк. Для получения дополнительной информации см. unnest.

Ограничения

• В настоящее время Lateral Join используется только с unnest() для достижения преобразования столбцов в строки. Другие табличные функции и UDTF будут поддерживаться позже.
• В настоящее время Lateral Join не поддерживает подзапросы.

Использование Lateral Join

Синтаксис:

from table_reference join [lateral] table_reference;

Примеры:

SELECT student, score
FROM tests
CROSS JOIN LATERAL UNNEST(scores) AS t (score);

SELECT student, score
FROM tests, UNNEST(scores) AS t (score);

Второй синтаксис здесь является сокращенной версией первого, где ключевое слово Lateral можно опустить при использовании ключевого слова UNNEST. Ключевое слово UNNEST — это табличная функция, которая преобразует массив в несколько строк. Вместе с Lateral Join она может реализовать общую логику расширения строк.

ПРИМЕЧАНИЕ

Если вы хотите выполнить unnest для нескольких столбцов, вы должны указать псевдоним для каждого столбца, например, select v1, t1.unnest as v2, t2.unnest as v3 from lateral_test, unnest(v2) t1, unnest(v3) t2;.

Selena поддерживает преобразование типов между BITMAP, STRING, ARRAY и Column.

Примеры использования

Вместе с unnest() вы можете достичь следующих функций преобразования столбцов в строки:

Развертывание строки в несколько строк

1. Создайте таблицу и вставьте данные в эту таблицу.

   CREATE TABLE lateral_test2 (
       `v1` bigint(20) NULL COMMENT "",
       `v2` string NULL COMMENT ""
   )
   DUPLICATE KEY(v1)
   DISTRIBUTED BY HASH(`v1`)
   PROPERTIES (
       "replication_num" = "3",
       "storage_format" = "DEFAULT"
   );
   
   INSERT INTO lateral_test2 VALUES (1, "1,2,3"), (2, "1,3");

2. Запросите данные до развертывания.

   select * from lateral_test2;
   
   +------+-------+
   | v1   | v2    |
   +------+-------+
   |    1 | 1,2,3 |
   |    2 | 1,3   |
   +------+-------+

3. Разверните v2 в несколько строк.

   -- Выполните unnest для одного столбца.
   
   select v1,unnest from lateral_test2, unnest(split(v2, ",")) as unnest;
   
   +------+--------+
   | v1   | unnest |
   +------+--------+
   |    1 | 1      |
   |    1 | 2      |
   |    1 | 3      |
   |    2 | 1      |
   |    2 | 3      |
   +------+--------+
   
   -- Выполните unnest для нескольких столбцов. Вы должны указать псевдоним для каждой операции.
   
   select v1, t1.unnest as v2, t2.unnest as v3 from lateral_test2, unnest(split(v2, ",")) t1, unnest(split(v3, ",")) t2;
   
   +------+------+------+
   | v1   | v2   | v3   |
   +------+------+------+
   |    1 | 1    | 1    |
   |    1 | 1    | 2    |
   |    1 | 2    | 1    |
   |    1 | 2    | 2    |
   |    1 | 3    | 1    |
   |    1 | 3    | 2    |
   |    2 | 1    | 1    |
   |    2 | 1    | 3    |
   |    2 | 3    | 1    |
   |    2 | 3    | 3    |
   +------+------+------+

Развертывание массива в несколько строк

Начиная с версии 1.5.0, unnest() может принимать несколько массивов разных типов и длин. Для получения дополнительной информации см. unnest().

1. Создайте таблицу и вставьте данные в эту таблицу.

   CREATE TABLE lateral_test (
       `v1` bigint(20) NULL COMMENT "",
       `v2` ARRAY NULL COMMENT ""
   ) 
   DUPLICATE KEY(v1)
   DISTRIBUTED BY HASH(`v1`)
   PROPERTIES (
       "replication_num" = "3",
       "storage_format" = "DEFAULT"
   );
   
   INSERT INTO lateral_test VALUES (1, [1,2]), (2, [1, null, 3]), (3, null);

2. Запросите данные до развертывания.

   select * from lateral_test;
   
   +------+------------+
   | v1   | v2         |
   +------+------------+
   |    1 | [1,2]      |
   |    2 | [1,null,3] |
   |    3 | NULL       |
   +------+------------+

3. Разверните v2 в несколько строк.

   select v1,v2,unnest from lateral_test , unnest(v2) as unnest;
   
   +------+------------+--------+
   | v1   | v2         | unnest |
   +------+------------+--------+
   |    1 | [1,2]      |      1 |
   |    1 | [1,2]      |      2 |
   |    2 | [1,null,3] |      1 |
   |    2 | [1,null,3] |   NULL |
   |    2 | [1,null,3] |      3 |
   +------+------------+--------+

Развертывание данных Bitmap

Вы можете использовать Lateral Join с функцией unnest_bitmap для развертывания данных Bitmap.

1. Создайте таблицу и вставьте данные в эту таблицу.

   CREATE TABLE lateral_test3 (
   `v1` bigint(20) NULL COMMENT "",
   `v2` Bitmap BITMAP_UNION COMMENT ""
   )
   AGGREGATE KEY(v1)
   DISTRIBUTED BY HASH(`v1`);
   
   INSERT INTO lateral_test3 VALUES (1, bitmap_from_string('1, 2')), (2, to_bitmap(3));

2. Запросите данные до развертывания.

   select v1, bitmap_to_string(v2) from lateral_test3;
   
   +------+------------------------+
   | v1   | bitmap_to_string(`v2`) |
   +------+------------------------+
   |    1 | 1,2                    |
   |    2 | 3                      |
   +------+------------------------+
   

3. Вставьте новую строку.

   insert into lateral_test3 values (1, to_bitmap(3));
   
   select v1, bitmap_to_string(v2) from lateral_test3;
   
   +------+------------------------+
   | v1   | bitmap_to_string(`v2`) |
   +------+------------------------+
   |    1 | 1,2,3                  |
   |    2 | 3                      |
   +------+------------------------+

4. Разверните данные в v2 в несколько строк.

   mysql> select v1, unnest_bitmap from lateral_test3, unnest_bitmap(v2) as unnest_bitmap;
   
   +------+---------------+
   | v1   | unnest_bitmap |
   +------+---------------+
   |    1 |             1 |
   |    1 |             2 |
   |    1 |             3 |
   |    2 |             3 |
   +------+---------------+