UNNEST Несколько значений?

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

------------------------
| col1 | col 2 | col 3 |
------------------------
| item | a,b,c | 1,2,3 |
------------------------

Я пытаюсь взять эту строку и поместить ее в таблицу следующим образом:

------------------------
| col1 | col 2 | col 3 |
------------------------
| item |   a   |   1   |
------------------------
| item |   b   |   2   |
------------------------
| item |   c   |   3   |
------------------------

Итак, в основном я пытаюсь UNNEST две строки, разделенные запятыми, одновременно. До сих пор лучшее, что я придумал, - это UNNEST для каждого столбца по отдельности, а затем попытаться объединить две полученные таблицы (с которыми я тоже борюсь), но в идеале я надеялся сделать это за один шаг.

Вот мой запрос к UNNEST по одной строке за раз:

SELECT
  col1, col2, col3
FROM 
  tableName,
UNNEST(SPLIT(col2)) AS col2

Вот моя попытка выполнить UNNEST как подзапрос, но она дает массу результатов:

SELECT sub.*
FROM (
  SELECT
    col1, col2, col3 AS col3
  FROM 
    tableName,
  UNNEST(SPLIT(col2)) AS col2
  WHERE
    randomCol = 'something'
  ) sub,
UNNEST(SPLIT(sub.col3)) AS col3

sql subquery unnest
person zqillini4    schedule 04.09.2018    source источник
comment
Какой продукт СУБД вы используете? Постгрес?   -  person a_horse_with_no_name    schedule 04.09.2018
comment
На самом деле это настраиваемая СУБД, но соответствующая стандарту SQL 2011.   -  person zqillini4    schedule 04.09.2018

Ответы (3)


arrow_upward
0
arrow_downward

Вы можете использовать стратегию unnest(split(col)), но не соединяйте оба столбца перекрестно. Ваш ответ подразумевает неявный порядок значений, разделенных запятыми, поэтому вам нужно будет установить поле (RowNumber ниже), чтобы указать этот порядок.

with Expanded2 as (
  select
    tableName.col1,
    col2.col2,
    row_number() over (partition by col1 order by 1) RowNumber
  from
    tableName,
    unnest(split(col2)) col2
), Expanded3 as (
  select
    tableName.col1,
    col3.col3,
    row_number() over (partition by col1 order by 1) RowNumber
  from
    tableName,
    unnest(split(col3)) col3
)
select
  Expanded2.col1,
  Expanded2.col2,
  Expanded3.col3
from
  Expanded2
  full outer join Expanded3 on 
    Expanded2.col1 = Expanded3.col1
    and Expanded2.RowNumber = Expanded3.RowNumber

Я не уверен, как ваши rdbms эффективно справляются с нулевым разбиением окна. Вышеупомянутое работает в PostgreSQL. SQL Server потребует order by (select null). ммв.

person Jeremy Fortune    schedule 05.09.2018

arrow_upward
4
arrow_downward

Стандарт SQL позволяет передавать несколько значений в функцию unnest().

Итак, следующее должно работать (и работает в Postgres):

select d.col1, 
       t.*
from data d
  cross join unnest(string_to_array(d.col2, ','), string_to_array(d.col3, ',')) as t(col1, col2)

Это также правильно работает с разным количеством элементов в списках.

Однако я не знаю, поддерживает ли это ваша проприетарная СУБД.

Онлайн-пример: http://rextester.com/XPN48947

person a_horse_with_no_name    schedule 05.09.2018

arrow_upward
0
arrow_downward

говоря, что в исходной таблице есть «строка»: вы имеете в виду ровно одну? если да, то это помогает:

with 
num_rows_ as (
  select length( regexp_replace((select b from t), '[^,]+')) + 1 value_ from dual),
a_ as (
  select a from t),
b_ as (
  select regexp_substr( (select b from t), '[^,]', 1, level ) b,rownum rownum_
  from dual
  connect by level <= (select value_ from num_rows_)),
c_ as (
  select regexp_substr( (select c from t), '[^,]', 1, level ) c,rownum rownum_
  from dual
  connect by level <= (select value_ from num_rows_))  
select a_.a,b_.b,c_.c 
  from a_ 
  full outer join b_ on 1=1
  inner join c_ on b_.rownum_ = c_.rownum_;

http://sqlfiddle.com/#!4/f795b9/29

или короче, за один шаг:

with a_ as
(select a from t),
b_c_ as (
  select regexp_substr( (select b from t), '[^,]', 1, level ) b,regexp_substr( (select c from t), '[^,]', 1, level ) c
  from dual
  connect by level <= (length( regexp_replace((select b from t), '[^,]+')) + 1)
)
select * from a_ cross join b_c_;

http://sqlfiddle.com/#!4/f795b9/32/0

person Peter    schedule 04.09.2018