时间戳

首页 > 解决方案 > Postgres：使用数字作为分隔符拆分字符串以分隔列

sql - Postgres：使用数字作为分隔符拆分字符串以分隔列

问题描述

我得到了一个包含大约 2000 行的 Postgres 数据集，其中一列是一串年份，并且在这些年份完成的维护活动如下：

id        maintenance
1         2012- Filled holes, Painted, 2017- Nailed
3         2018- Booger removal
2         2012- Painted, 2017- Filled holes, 2018- Wallpaper

我正在尝试找到一种方法将这些数据分成如下结构的列：

id    year_1    year_1_maint             year_2    year_2_maint...

1     2012      Filled holes, Painted    2017      Nailed
2     2018      Booger removal

我在想一个可能的解决方案使用类似以下的内容（除了这会删除年份，因为它被用作分隔符）：

select regexp_split_to_array(maintenance, '\d{4}') 
from maintenance_database 
where maintenance is not null;

我可以使用以下方法找到需要多少列：

select max(array_length(regexp_split_to_array(maintenance, '\d{4}'),1)) 
from maintenance_database 
where maintenance is not null;

但这就是我在尝试获取一系列更新查询或单个查询以按照我想要的方式格式化数据时遇到的问题。

标签： sqlpostgresql

解决方案

第一个想法是将“特殊”字符附加到年份以将其用作分隔符：

db=# with maintenance_database(maintenance) as (values('2012- Painted, 2017- Filled holes, 2018- Wallpaper'))
select regexp_split_to_array(regexp_replace(maintenance, '(\d{4})', '\1'||chr(1),'g'),chr(1)) from maintenance_database;
                     regexp_split_to_array
---------------------------------------------------------------
 {2012,"- Painted, 2017","- Filled holes, 2018","- Wallpaper"}
(1 row)

在这里，我“准备”维护regexp_replace(maintenance, '(\d{4})', '\1'||chr(1),'g')：

db=# with maintenance_database(maintenance) as (values('2012- Painted, 2017- Filled holes, 2018- Wallpaper'))
select regexp_replace(maintenance, '(\d{4})', '\1'||chr(1),'g') from maintenance_database;
                         regexp_replace
----------------------------------------------------------------
 2012\x01- Painted, 2017\x01- Filled holes, 2018\x01- Wallpaper
(1 row)

进一步使用您的代码...

自然地使用年份作为值，您还需要在前面加上人工分隔符：

db=# with maintenance_database(maintenance) as (values('2012- Painted, 2017- Filled holes, 2018- Wallpaper'))
select regexp_split_to_array(regexp_replace(maintenance, '(\d{4})', chr(1)||'\1'||chr(1),'g'),chr(1)) from maintenance_database;
                       regexp_split_to_array
--------------------------------------------------------------------
 {"",2012,"- Painted, ",2017,"- Filled holes, ",2018,"- Wallpaper"}
(1 row)

推荐阅读