Прикольную статью нашел. Небольшая, по делу и с юморком.
Ссылка на оригинал: Postgres Strings to Arrays and Back Again
Один из моих «любимых» форматов данных — это «CSV в CSV», файл CSV, в котором один или несколько столбцов структурированы как CSV.
CSV-столбцы в CSV-файле — это низкоуровневый подход к передаче многотабличной реляционной структуры данных в одном файле. Файл может быть прочитан всем, что может читать CSV (а «всем» — это чем?), и пересылает связанные данные в очень удобочитаемой форме:
Station North,"-1,-4,-14,-15,-16,-15,-12,-9,-3,0,1,2" Station West,"2,4,5,6,9,10,15,16,13,12,10,9,5,3,1" Station East,"5,3,2,4,5,6,9,10,15,16,13,12,10,9,5,4,2,1" Station South,"12,18,22,25,29,30,33,31,30,29,28,25,24,23,14"
Но как мы можем взаимодействовать с этими дополнительными данными?
Пример данных
Если вы хотите попробовать описанное в статье в интерактивном режиме, вы можете это сделать на нашем учебном сайте, в котором PostgreSQL будет запущен в браузере.
Вот таблица для загрузки данных.
CREATE TABLE weather_data (
station text,
temps text
);
К счастью, импортер PostgreSQL CSV будет правильно использовать формат CSV в CSV-файле:
COPY weather_data FROM 'weather_data.csv' WITH (FORMAT csv);
Для этого примера может быть проще просто вставить данные напрямую:
INSERT INTO weather_data VALUES
('Station North','-1,-4,-14,-15,-16,-15,-12,-9,-3,0,1,2'),
('Station West','2,4,5,6,9,10,15,16,13,12,10,9,5,3,1'),
('Station East','5,3,2,4,5,6,9,10,15,16,13,12,10,9,5,4,2,1'),
('Station South','12,18,22,25,29,30,33,31,30,29,28,25,24,23,14');
Массивы в помощь
Данные в таблице. Теперь возникает вопрос: что делать с этим дурацким списком температур, разделенным запятыми? Во-первых, его можно сделать удобнее для использования, преобразовав в массив с помощью функции split_to_array(string,separator):
-- Разделить на массив SELECT station, string_to_array(temps,',') AS array FROM weather_data;
station | array
---------------+------------------------------------------------
Station North | {-1,-4,-14,-15,-16,-15,-12,-9,-3,0,1,2}
Station West | {2,4,5,6,9,10,15,16,13,12,10,9,5,3,1}
Station East | {5,3,2,4,5,6,9,10,15,16,13,12,10,9,5,4,2,1}
Station South | {12,18,22,25,29,30,33,31,30,29,28,25,24,23,14}
Массив вместо строки не выглядит очень уж полезным, но мы можем показать, что на самом деле у нас теперь есть структурированные данные, выполняя с ними действия «только для массива», например возвращая длину массива:
-- Разделить на массив и, например, его проанализировать SELECT station, cardinality(string_to_array(temps,',')) AS array_size FROM weather_data;
station | array_size ---------------+------------ Station North | 12 Station West | 15 Station East | 18 Station South | 15
Расширение и анализ массива
Однако, безусловно, самое интересное, что вы можете сделать с таким массивом — это развернуть его с помощью unnest(array)! Функция unnest(array) — это «функция, возвращающая множество» — она может возвращать более одной строки. Как это работает? Все части входящей строки дублируются, так что каждая строка имеет полный набор данных:
-- Разделить на массив, развернуть SELECT station, unnest(string_to_array(temps,',')) AS temps FROM weather_data;
station | temps ---------------+------- Station North | -1 Station North | -4 Station North | -14 Station North | -15 Station North | -16 Station North | -15 Station North | -12 Station North | -9 Station North | -3 Station North | 0 Station North | 1 Station North | 2 Station West | 2 Station West | 4 Station West | 5 Station West | 6 Station West | 9 Station West | 10 Station West | 15 Station West | 16 Station West | 13 Station West | 12 Station West | 10 Station West | 9 Station West | 5 Station West | 3 Station West | 1 Station East | 5 Station East | 3 Station East | 2 Station East | 4 Station East | 5 Station East | 6 Station East | 9 Station East | 10 Station East | 15 Station East | 16 Station East | 13 Station East | 12 Station East | 10 Station East | 9 Station East | 5 Station East | 4 Station East | 2 Station East | 1 Station South | 12 Station South | 18 Station South | 22 Station South | 25 Station South | 29 Station South | 30 Station South | 33 Station South | 31 Station South | 30 Station South | 29 Station South | 28 Station South | 25 Station South | 24 Station South | 23 Station South | 14
Теперь данные очень похожи на те, что мы могли бы получить, объединив таблицы в стандартной модели данных, и теперь мы можем делать стандартные аналитические вещи, например, вычислять диапазон температур на каждой станции:
-- Разделить на массив, развернуть и проанализировать температуры WITH unnested_data AS ( SELECT station, unnest(string_to_array(temps,',')) AS temps FROM weather_data ) SELECT station, max(temps) AS max_temp, min(temps) AS min_temp FROM unnested_data GROUP BY station;
station | max_temp | min_temp ---------------+----------+---------- Station North | 2 | -1 Station West | 9 | 1 Station East | 9 | 1 Station South | 33 | 12
Примечание переводчика: чтобы получить аналогичный результат — нужно поработать над правилом сортировки (collate = C) той БД, в которой вы будете тестировать всё выше-ниже приведённое.
Reductio ad Absurdum
Наконец, для полноты картины, если вы хотите сохранить ваши данные в виде строки, но не любите запятые, вот как разделить и повторно объединить данные, используя новый разделитель:
-- Разделить на массив, соединить в строку SELECT station, array_to_string(string_to_array(temps,','),'|') AS temps FROM weather_data;
station | temps ---------------+---------------------------------------------- Station North | -1|-4|-14|-15|-16|-15|-12|-9|-3|0|1|2 Station West | 2|4|5|6|9|10|15|16|13|12|10|9|5|3|1 Station East | 5|3|2|4|5|6|9|10|15|16|13|12|10|9|5|4|2|1 Station South | 12|18|22|25|29|30|33|31|30|29|28|25|24|23|14
Автор статьи Пол Рэмзи (Paul Ramsey).
Еще раз ссылка на оригинал: Postgres Strings to Arrays and Back Again
Leave a Reply