sql - 将字符串行拆分为多列-PostgreSQL

如何将一列/行拆分为多列和同一行。想法是这样的：
我有个问题是这样的：

SELECT
          i.account_id                               AS account_id,
          c.url_tags                                 AS url_tags,
          CAST(to_char(date_start, 'YYYYMM') AS INT) AS month_id,
          sum(clicks)                                AS clicks,
          sum(impressions)                           AS impressions,
          sum(reach)                                 AS reach,
          sum(spend)                                 AS spend,
          sum(total_actions)                         AS total_actions
        FROM schema.ads_insights               AS i
          INNER JOIN schema.ads as a ON a.id=i.ad_id
          INNER JOIN schema.adcreative as c ON c.id=a.creative__id
        WHERE
          EXTRACT(YEAR FROM date_start) = '2018'
        GROUP BY i.account_id, month_id, c.url_tags

输出如下：（删除了一些列）

|----||--------------------------------------------------------------||----|
| ID || url                                                          || mo |
|----||--------------------------------------------------------------||----|
| 1  || utm_source=source&utm_medium=medium&utm_term=term            || 12 |
|----||--------------------------------------------------------------||----|
|----||--------------------------------------------------------------||----|
| 2  || utm_source=source2&utm_medium=medium2&utm_term=term2         || 43 |
|----||--------------------------------------------------------------||----|

我想要的没有任何后端逻辑。我们有一个从数据库到可视化平台（Google Data Studio）的连接器，这个可视化平台不需要任何形式的操作选项就可以提供数据。这就是为什么我要操作查询，以便它在没有任何后端代码的情况下兼容。
这就是我希望得到的查询结果：

|----||------------||------------||----------||--------|
| ID || utm_source || utm_medium || utm_term ||   mo   |
|----||------------||------------||----------||--------|
| 1  || source     || medium     || term     ||   12   |
|----||------------||------------||----------||--------|
| 2  || source2    || medium2    || term2    ||   43   |
|----||------------||------------||----------||--------|

这可能吗？

最佳答案

使用SUBSTRING

select substring(s from 'utm_source=(.*?)(&|$)') as utm_source,
       substring(s from 'utm_medium=(.*?)(&|$)') as utm_medium,
       substring(s from 'utm_term=(.*?)(&|$)') as utm_term
from t;

Demo

关于sql - 将字符串行拆分为多列-PostgreSQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55061743/