SQL для анализа очень длинной строки на множество столбцов

Как использовать базовый SQL для анализа очень и очень длинной строки на множество столбцов с помощью разделителя? Я просмотрел несколько постов (здесь например, и здесь или здесь ) о разборе на отдельные столбцы, строки и так далее. Однако все они вручную идентифицируют и создают каждый столбец. ( Вот, например, в примере три столбца, максимально необходимое количество). Мне нужно до 90 столбцов. Я ищу способ консолидировать или функционализировать синтаксический анализ нескольких столбцов, чтобы мне не приходилось повторять эквивалент ',nullif (split_part(my_string,'|',N),'') string_N' из ниже 90 раз.

with test_data (id, my_string) as
(
    select 1, 'a|b|c|d' union all
    select 2, 'abba|zabba|beta' union all
    select 5, 'x|y' union all
    select 4, 'z1|z2|z3'
)
select 
    id 
    ,nullif (split_part(my_string,'|',1),'') string_1 
    ,nullif (split_part(my_string,'|',2),'') string_2 
    ,nullif (split_part(my_string,'|',3),'') string_3 
    ,nullif (split_part(my_string,'|',4),'') string_4 
from test_data ;

Я попытался создать решение вручную, как указано выше. Выполнение этого для очень, очень длинных строк создало бы для меня проблемы. Опубликованные решения SQL не доходят, и я не могу использовать Python.

Пожалуйста, отметьте свою версию СУБД

Slava Rozhnev 13.06.2024 15:45

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

Joel Coehoorn 13.06.2024 15:52

Кроме того, я пришел к выводу, что БД Oracle основана на from dual (все остальные БД были достаточно умны, чтобы не нуждаться в ней уже 30 лет), но функция split_part() — это вещь Amazon или PostgreSQL. Так что вам следует взять правильную БД самостоятельно.

Joel Coehoorn 13.06.2024 15:58

Ваш вопрос помечен как Oracle, но все вопросы, на которые вы ссылаетесь, помечены как SQL Server – ни один из ответов не будет работать на Oracle, а код, который вы включаете в вопрос, выдаст исключение ORA-00904: "SPLIT_PART": invalid identifier, потому что это недействительная функция ️ 🔁рабочий пример. Вы уверены, что используете Oracle?

MT0 13.06.2024 15:58

Я знаю, @JoelCoehoorn, что ты отредактировал (похоже, это был ты?) теги, чтобы показать Oracle. Я использую SQL в Snowflake и опубликовал это как отдельный вопрос по кодированию SQL. Если «из двойного» людей отталкивает, возможно, мне стоит это изменить.

chriseshleman 13.06.2024 16:01
Asktom.oracle.com/ords/…
JGFMK 13.06.2024 16:01

Так что нет, @MT0, это не вопрос Oracle! Спасибо, что отметили.

chriseshleman 13.06.2024 16:06

Пожалуйста, отредактируйте свой вопрос, указав правильную СУБД, и удалите недопустимый синтаксис. Кроме того, поскольку вы используете Snowflake, если эти данные поступают в файлах, просмотрите схему чтения/вывода схемы.

Andrew 13.06.2024 16:28

«Автономный» тег <sql> означает ISO/ANSI SQL. Т.е. nullif и string_part недоступны.

jarlh 13.06.2024 16:28

Привет, а что плохого в том, чтобы просто использовать функцию РАЗДЕЛИТЬ? docs.snowflake.com/en/sql-reference/functions/split

NickW 13.06.2024 18:51
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
10
106
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

В Snowflake этого можно добиться в три шага: разбить столбец на МАССИВ, разбить массив на строки и использовать динамический PIVOT, чтобы переместить строки обратно в столбцы.

CREATE TABLE  test_data (id, my_string) as
(
    select 1, 'a|b|c|d' union all
    select 2, 'abba|zabba|beta' union all
    select 5, 'x|y' union all
    select 4, 'z1|z2|z3'
);

WITH str_to_arr AS (
    SELECT 
        id 
        , SPLIT(my_string, '|') as split_
    FROM test_data
),

flattened_data AS (  -- changed 'flattened' to 'flattened_data' for consistency 
    SELECT
        id
        , f.value as column_value
        , 'column_' || f.index as column_name
    FROM str_to_arr
         , lateral flatten(input => str_to_arr.split_) f
)

SELECT *
FROM flattened_data
     PIVOT(
        MIN(column_value)
        FOR column_name IN (ANY ORDER BY column_name)
        DEFAULT ON NULL('')
     )
ORDER BY id
;

Результат:

| ID | 'column_0' | 'column_1' | 'column_2' | 'column_3' |
|----|------------|------------|------------|------------|
|  1 | a          | b          | c          | d          |
|  2 | abba       | zabba      | beta       |            |
|  3 | z1         | z2         | z3         |            |
|  4 | x          | y          |            |            |

Производительность этого подхода будет зависеть от формы и размера ваших данных. Для повышения производительности вам следует предварительно обработать эти данные или разделить их на столбцы при приеме.

Фантастика. @Тидж, я должен тебе выпить.

chriseshleman 13.06.2024 20:53

@chriseshleman рад помочь!

Teej 13.06.2024 20:59

Сохраните один CTE, используя STRTOK_SPLIT_TO_TABLE. См. документацию по адресу docs.snowflake.com/en/sql-reference/functions/…

Dave Welden 14.06.2024 14:58
Ответ принят как подходящий

Продвигая технику на основе сводов немного дальше и комбинируя ее с некоторыми сценариями, мы можем обойти проблему использования "'column_name'" вместо просто column_name.

--dummy data
create or replace temporary table t (id, my_string) as
select 1, 'a|b|c|d' union all
select 2, 'abba|zabba|beta' union all
select 5, 'x|y' union all
select 4, 'z1|z2|z3';

--start with a clone
create or replace temporary table t_split clone t;

--add additional varchar columns (string_1 through string_N) dynamically
declare
  rs resultset;
  num_cols int;
  cols_def varchar;
begin
  num_cols:= (select max(regexp_count(my_string,'[|]')) from t_split) + 1;
  cols_def:= (select regexp_replace(array_to_string(array_generate_range(1,:num_cols+1),','),'([0-9]+)','string_\\1 varchar'));
  rs:= (execute immediate 'alter table t_split add column '||:cols_def);
  return table(rs);
end;

--insert to table
insert overwrite into t_split

with cte as
(select a.*, b.index as cols, b.value 
 from t a, lateral split_to_table(my_string,'|') as b)

select *
from cte
pivot (max(value) for cols in (any order by cols))
order by id;

--take a look
select * from t_split;

Отлично @kathmundude, это тоже хорошо работает! Спасибо

chriseshleman 14.06.2024 12:38

Другие вопросы по теме