“ split_part”；将列结构化成表格？ [英] "split_part" to structure a column into a table?

问题描述

我是一位数字营销经理，正在尝试细分我的广告系列以进行切片分析。我的广告系列在以下一栏中列出。

  FR-SRC-PLATFORM | MISC {bw} 
 FR-SRC-M2M SIM | Sim {bw} 
 ES-SRC-IOT |连接性{e} 
  

我想先基于-然后将每个| |然后在{。

上结果

 可乐| b科尔克| Col d | Col e 
 fr | SRC |平台|其他| {bw} 
  

我发现以下公式有效，但只有一个问题...

 选择split_part（your_col，'-'，1）可乐，
 split_part（your_col，'-'，2） colb，
 split_part（split_part（your_col，'-'，2），'|'，1）colc，
 split_part（split_part（your_col，'|'，2），'{'，1）冷，
 split_part（split_part（your_col，'{'，2），'}'，1）科尔
 / *或
 split_part（your_col，'{'，2）科尔
 * / 
 from your_table 
  

有时我的广告系列会有不同的情况结构（更多关键字），导致更多条件由|分隔符号。作为例子； ES-SRC-IOT |连接性| SIM卡|美国{e}。从理论上讲，我希望所有内容都保持在第一|

$ b

是否有一种方法可以挽救该公式，还是应该从头开始搜索？ $ b

谢谢您。

谢谢

解决方案

我将使用正则表达式子字符串

 和e AS（值（'FR-SRC-PLATFORM | MISC {bw} '）
，（'FR-SRC-M2M sim | Sim {bw}'）
，（'ES-SRC-IOT |连接性{e}'）
，（'AXE | SPADE | SHOVEL {test}'）
）
 SELECT column1，substring（column1 from $$ ^（[^ |] *）co | $$）as 
，substring（column1从$$ ^ [^ |] * \ |（[^ {] *）$$）作为b 
，substring（column1 from $ x $ [{]（。*）[}] $$ x $ ）作为c 
来自e; 
  

给予

  column1 |一个| b | c 
 ------------------------------ + -------------- ---- + ---------------- + ------ 
 FR-SRC-PLATFORM | MISC {bw} | FR-SRC-平台| MISC | bw 
 FR-SRC-M2M SIM | Sim {bw} | FR-SRC-M2M SIM | Sim | bw 
 ES-SRC-IOT |连接性{e} | ES-SRC-IOT |连接性| e 
 AXE | SPADE | SHOVEL {test} | AX | SPADE | SHOVEL |测试
  

I am a digital marketing manager and am trying to breakdown my campaigns for sliced analysis. My campaigns are listed in one column as follows.

  FR-SRC-PLATFORM | MISC {bw}
  FR-SRC-M2M sim  | Sim {bw}
  ES-SRC-IOT | Connectivity {e}

I would like to separate each first based on - then | and then on {.

The result being

Cola | Colb | Colc    | Col d | Col e
fr   | SRC  | Platform| Misc  | {bw}

I have found the following formula that works but there is only one problem...

SELECT split_part(your_col,'-',1) cola,
 split_part(your_col,'-',2) colb,
 split_part(split_part(your_col,'-',2),' | ',1) colc,
 split_part(split_part(your_col,' | ',2),' {',1) cold,
 split_part(split_part(your_col,'{',2),'}',1) cole
 /* or 
   split_part(your_col,'{',2) cole
*/
 FROM your_table

It will sometimes occur that my campaigns will have a different structure (more keywords) which results in more criteria being separated by the | symbol. As and example; ES-SRC-IOT | Connectivity | SIM | USA {e}. I would in theory like to keep everything between the first | and first { within the same column.

Is there a way of salvaging this formula or should I search for something from scratch?

Thank you in advanced.

Thank you

解决方案

I'd go with regex substring

with e AS ( VALUES  ('FR-SRC-PLATFORM | MISC {bw}')
      ,('FR-SRC-M2M sim  | Sim {bw}')
      ,('ES-SRC-IOT | Connectivity {e}')
      ,('AXE|SPADE|SHOVEL{test}')
)
SELECT column1,substring(column1 from $$^([^|]*)\|$$ ) as a
    ,substring(column1 from $$^[^|]*\|([^{]*)$$ ) as b
    ,substring(column1 from $x$[{](.*)[}]$$x$ ) as c
FROM e;

gives

            column1            |        a         |       b        |  c   
 ------------------------------+------------------+----------------+------
 FR-SRC-PLATFORM | MISC {bw}   | FR-SRC-PLATFORM  |  MISC          | bw
 FR-SRC-M2M sim  | Sim {bw}    | FR-SRC-M2M sim   |  Sim           | bw
 ES-SRC-IOT | Connectivity {e} | ES-SRC-IOT       |  Connectivity  | e
 AXE|SPADE|SHOVEL{test}        | AXE              | SPADE|SHOVEL   | test

这篇关于“ split_part”；将列结构化成表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！