Как использовать базовый 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.
В первую очередь вы должны избегать хранения данных с разделителями в одном столбце, так что да, это будет болезненно. Он также будет заново разбивать всю строку с нуля для каждого столбца и еще раз для каждой отдельной строки, так что не ждите, что он будет работать очень быстро.
Кроме того, я пришел к выводу, что БД Oracle основана на from dual
(все остальные БД были достаточно умны, чтобы не нуждаться в ней уже 30 лет), но функция split_part()
— это вещь Amazon или PostgreSQL. Так что вам следует взять правильную БД самостоятельно.
Ваш вопрос помечен как Oracle, но все вопросы, на которые вы ссылаетесь, помечены как SQL Server – ни один из ответов не будет работать на Oracle, а код, который вы включаете в вопрос, выдаст исключение ORA-00904: "SPLIT_PART": invalid identifier
, потому что это недействительная функция ️ 🔁рабочий пример. Вы уверены, что используете Oracle?
Я знаю, @JoelCoehoorn, что ты отредактировал (похоже, это был ты?) теги, чтобы показать Oracle. Я использую SQL в Snowflake и опубликовал это как отдельный вопрос по кодированию SQL. Если «из двойного» людей отталкивает, возможно, мне стоит это изменить.
Так что нет, @MT0, это не вопрос Oracle! Спасибо, что отметили.
Пожалуйста, отредактируйте свой вопрос, указав правильную СУБД, и удалите недопустимый синтаксис. Кроме того, поскольку вы используете Snowflake, если эти данные поступают в файлах, просмотрите схему чтения/вывода схемы.
«Автономный» тег <sql> означает ISO/ANSI SQL. Т.е. nullif
и string_part
недоступны.
Привет, а что плохого в том, чтобы просто использовать функцию РАЗДЕЛИТЬ? docs.snowflake.com/en/sql-reference/functions/split
В 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 рад помочь!
Сохраните один CTE, используя STRTOK_SPLIT_TO_TABLE. См. документацию по адресу docs.snowflake.com/en/sql-reference/functions/…
Продвигая технику на основе сводов немного дальше и комбинируя ее с некоторыми сценариями, мы можем обойти проблему использования "'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, это тоже хорошо работает! Спасибо
Пожалуйста, отметьте свою версию СУБД