postgresql - Postgresql - 将聚合、逗号分隔值拆分为查询内的单独列 - 使用 Amazon Aws 和 PostgreSql 9.6

Question

我有以下情况，我需要解决在列内显示数据的问题，对于串联字符串的每个数据部分。

我对此比较陌生，可悲的是我无法将其他已经阅读过的例子转移到我目前的情况中。我已经阅读了一些关于交叉表和数据透视表的内容，但我只有一列包含该数据，而且我不知道该列内有多少逗号分隔值，所以它无论如何都需要是动态的，我不知道如何解决这个问题。

假设我们有一个这样的 postgres 表(Amawon Aws 上的版本 9.6.3 - 遗憾的是它们不支持数组之类的东西):

CREATE TABLE touchpoints (
customer_id varchar(50) PRIMARY KEY,
purchase_timestamp date,
touchpoints_till_purchase text,
purchase_quantity int,
purchase_value int);

让我们用数据填充它，就像我在 View 中一样:

INSERT INTO touchpoints (customer_id, purchase_timestamp, touchpoints_till_purchase, purchase_quantity, purchase_value) 
VALUES ('testuser0@example.com', 'Jun 01, 2018 11:12', 'SEO,Direct', 2, 180),('testuser1@example.com', 'Jun 03, 2018 15:56', 'Direct,Facebook_Paid,SEO', 1, 100),
('testuser2@example.com', 'Jun 04, 2018 21:44', 'Direct,Direct,Direct,Direct,Direct,Direct,Direct,Direct', 3, 170),('testuser3@example.com', 'Jun 06, 2018 15:42', 'Direct,SEO,SEO,Direct', 5, 270),
('testuser4@example.com', 'Jun 06, 2018 15:42', 'Direct,Direct,Direct,Direct,Direct,Direct,Direct', 5, 270);

创建后的表应该是这样的，这也是我实际表的样子。

实际情况:

所以我正在尝试设置一个查询或函数，将“touchpoints_till_purchase”字段内的值拆分为单独的列，就像在数据透视表中一样。

我想要建立的是这样的东西:

预期结果:

应该没有我想的那么难，但我无法用我在 PostgreSQL 的实际低技能来解决它​​。我观察和尝试的所有解决方案都没有成功，或者我猜我用错了它们。

预期查询应牢记以下几点:

• 我不知道应该拆分的接触点列中有多少逗号分隔值
• 我没有对应的列来标记接触点(比如循环计数之类的)
• 它需要是动态的，并且应该在 Amazon AWS 上运行 --> 遗憾的是，有一些不受支持的东西，您可以在这里查看:https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-functions.html
  • 我不能使用像 Json 格式这样的替代方法，因为我需要用单独的列来解决它

就我尝试解决这么多小时但没有找到解决方案而言，我真的很期待像您这样的 sql 专业人员的帮助。

非常非常感谢!

Answer 1

一种应该可行的方法是使用子字符串的正则表达式版本，但它并不漂亮。

SELECT customer_id,purchase_timestamp
   ,substring (touchpoints_till_purchase from '^([^,]*)') as touchpoint1
   ,substring (touchpoints_till_purchase from '^(?:(?:[^,]*),){1}([^,]*)') as touchpoint2
   ,substring (touchpoints_till_purchase from '^(?:(?:[^,]*),){2}([^,]*)') as touchpoint3
   ,substring (touchpoints_till_purchase from '^(?:(?:[^,]*),){3}([^,]*)') as touchpoint4
   ,substring (touchpoints_till_purchase from '^(?:(?:[^,]*),){4}([^,]*)') as touchpoint5
   ,substring (touchpoints_till_purchase from '^(?:(?:[^,]*),){5}([^,]*)') as touchpoint6
   ,substring (touchpoints_till_purchase from '^(?:(?:[^,]*),){6}([^,]*)') as touchpoint7
   ,substring (touchpoints_till_purchase from '^(?:(?:[^,]*),){7}([^,]*)') as touchpoint8
   ,purchase_quantity
   ,purchase_value
FROM touchpoints;

thgis 一个也可能有效。

SELECT customer_id,purchase_timestamp
   ,substring (touchpoints_till_purchase from '^(?:([^,]*)(,|$)){1}') as touchpoint1
   ,substring (touchpoints_till_purchase from '^(?:([^,]*)(,|$)){2}') as touchpoint2
   ,substring (touchpoints_till_purchase from '^(?:([^,]*)(,|$)){3}') as touchpoint3
   ,substring (touchpoints_till_purchase from '^(?:([^,]*)(,|$)){4}') as touchpoint4
   ,substring (touchpoints_till_purchase from '^(?:([^,]*)(,|$)){5}') as touchpoint5
   ,substring (touchpoints_till_purchase from '^(?:([^,]*)(,|$)){6}') as touchpoint6
   ,substring (touchpoints_till_purchase from '^(?:([^,]*)(,|$)){7}') as touchpoint7
   ,substring (touchpoints_till_purchase from '^(?:([^,]*)(,|$)){8}') as touchpoint8
   ,purchase_quantity
   ,purchase_value
FROM touchpoints;

但是，如果 Amazon 的子字符串使用 8.0 正则表达式引擎，情况会变得更糟。

SELECT customer_id,purchase_timestamp
   ,substring (substring (touchpoints_till_purchase from '^(([^,]*)(,|$)){1}') from '([^,]*),?$') as touchpoint1
   ,substring (substring (touchpoints_till_purchase from '^(([^,]*)(,|$)){2}') from '([^,]*),?$') as touchpoint2
   ,substring (substring (touchpoints_till_purchase from '^(([^,]*)(,|$)){3}') from '([^,]*),?$') as touchpoint3
   ,substring (substring (touchpoints_till_purchase from '^(([^,]*)(,|$)){4}') from '([^,]*),?$') as touchpoint4
   ,substring (substring (touchpoints_till_purchase from '^(([^,]*)(,|$)){5}') from '([^,]*),?$') as touchpoint5
   ,substring (substring (touchpoints_till_purchase from '^(([^,]*)(,|$)){6}') from '([^,]*),?$') as touchpoint6
   ,substring (substring (touchpoints_till_purchase from '^(([^,]*)(,|$)){7}') from '([^,]*),?$') as touchpoint7
   ,substring (substring (touchpoints_till_purchase from '^(([^,]*)(,|$)){8}') from '([^,]*),?$') as touchpoint8
   ,purchase_quantity
   ,purchase_value
FROM touchpoints;

使用数组等不允许的功能的解决方案要简洁得多。