Redshift - Как разбить массив на строки [duplicate]

Поздно к партии, но у меня что-то работает (хотя и очень медленно)

with nums as (select n::int n
from
  (select 
      row_number() over (order by true) as n
   from table_with_enough_rows_to_cover_range)
cross join
  (select 
      max(json_array_length(json_column)) as max_num 
   from table_with_json_column )
where
  n <= max_num + 1)
select *, json_extract_array_element_text(json_column,nums.n-1) parsed_json
from  nums, table_with_json_column
where json_extract_array_element_text(json_column,nums.n-1) != ''
and nums.n <= json_array_length(json_column) 

Благодаря ответу Боба Бэкли для вдохновения

Небольшое улучшение по сравнению с существующим ответом заключается в использовании второй таблицы «числа», которая перечисляет все возможные длины списка, а затем использует cross join, чтобы сделать запрос более компактным.

Redshift не имеет прямого метода для создания таблицы чисел, о которой я знаю, но мы можем использовать немного взлома из https://www.periscope.io/blog/ generate-series-in-redshift-and-mysql.html , чтобы создать один, используя номера строк.

В частности, если предположить, что число строк в cmd_logs больше максимального числа запятых в столбце user_action, мы можем создать таблицу чисел, посчитав строки. Для начала предположим, что в столбце user_action не более 99 запятых:

select 
  (row_number() over (order by true))::int as n
into numbers
from cmd_logs
limit 100;

Если мы хотим получить фантазию, мы можем вычислить количество запятых из таблицы cmd_logs, чтобы создать более точный набор строк в numbers:

select
  n::int
into numbers
from
  (select 
      row_number() over (order by true) as n
   from cmd_logs)
cross join
  (select 
      max(regexp_count(user_action, '[,]')) as max_num 
   from cmd_logs)
where
  n <= max_num + 1;

. Когда есть таблица numbers, мы можем сделать:

select
  user_id, 
  user_name, 
  split_part(user_action,',',n) as parsed_action 
from
  cmd_logs
cross join
  numbers
where
  split_part(user_action,',',n) is not null
  and split_part(user_action,',',n) != '';

Вы можете получить ожидаемый результат со следующим запросом. Я использую «UNION ALL» для преобразования столбца в строку.

select user_id, user_name, split_part(user_action,',',1) as parsed_action from cmd_logs
union all
select user_id, user_name, split_part(user_action,',',2) as parsed_action from cmd_logs
union all
select user_id, user_name, split_part(user_action,',',3) as parsed_action from cmd_logs

Простое улучшение ответа выше https://stackoverflow.com/a/31998832/1265306

Является ли таблица чисел с использованием следующего SQL https: / /discourse.looker.com/t/generating-a-numbers-table-in-mysql-and-redshift/482

SELECT 
  p0.n 
  + p1.n*2 
  + p2.n * POWER(2,2) 
  + p3.n * POWER(2,3)
  + p4.n * POWER(2,4)
  + p5.n * POWER(2,5)
  + p6.n * POWER(2,6)
  + p7.n * POWER(2,7) 
  as number  
INTO numbers
FROM  
  (SELECT 0 as n UNION SELECT 1) p0,  
  (SELECT 0 as n UNION SELECT 1) p1,  
  (SELECT 0 as n UNION SELECT 1) p2, 
  (SELECT 0 as n UNION SELECT 1) p3,
  (SELECT 0 as n UNION SELECT 1) p4,
  (SELECT 0 as n UNION SELECT 1) p5,
  (SELECT 0 as n UNION SELECT 1) p6,
  (SELECT 0 as n UNION SELECT 1) p7
ORDER BY 1
LIMIT 100

«ORDER BY» существует только в том случае, если вы хотите вставить его без предложения INTO и посмотреть результаты

Вы можете попробовать скопировать команду, чтобы скопировать файл в таблицы красного смещения

copy table_name from 's3://mybucket/myfolder/my.csv' CREDENTIALS 'aws_access_key_id=my_aws_acc_key;aws_secret_access_key=my_aws_sec_key' delimiter ','

Вы можете использовать опцию разделителя ','.

Для получения дополнительной информации о параметрах команды копирования вы можете посетите страницу

http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html

Вот мой страшный ответ.

У меня есть таблица users, а затем таблица events со столбцом, который представляет собой только строку с разделителями-запятыми в указанном событии. например,

event_id | user_ids
1        | 5,18,25,99,105

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

SELECT e.event_id, u.id as user_id
FROM events e
LEFT JOIN users u ON e.user_ids like '%' || u.id || '%'

Это не очень , но я бросаю его в WITH, так что мне не нужно запускать его более одного раза за запрос.

Кроме того, это работает только в том случае, если у вас есть вторая таблица, в которой имеет одну строку за уникальную возможность. Если нет, вы можете сделать LISTAGG, чтобы получить одну ячейку со всеми вашими значениями, экспортировать ее в CSV и повторно загрузить , которая будет использоваться в качестве таблицы.

Как я сказал: ужасное, нехорошее решение.

Еще одна идея состоит в том, чтобы сначала преобразовать вашу строку CSV в JSON с последующим извлечением JSON в следующих строках:

... '["' || replace( user_action, '.', '", "' ) || '"]' AS replaced

... JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replaced, numbers.i) AS parsed_action

Где «числа» - таблица из первого ответа. Преимуществом этого подхода является возможность использования встроенных функций JSON.