将单个SQL列拆分为五个

Question

我尝试将一个列根据 " > " 分隔符划分为多达五个部分，但尝试过的方法都没有成功：

我尝试过

select
id, 
compoundColumn,
split(compoundColumn," > ")[1] as "first"
split(compoundColumn," > ")[2] as "second"
from table
where compoundColumn is not null

这个没有起作用，而这个似乎有点起了作用（至少第一部分有，不是第n部分）

select
id, 
compoundColumn,
first(split(compoundColumn," > ")) as "first"
nth(compoundColumn," > ")[n] as "second"
from table

我在这里找到了很多例子，但它们似乎都在说要使用方括号，但是方括号会引发错误：

异常：格式错误的SQL。更多信息：SQL语句出错：您的MySQL服务器版本所对应的语法正确性不在 [1] as "first" from table where compoundColumn IS NOT NULL 此行附近。

- J. G.

---

不太熟悉MySQL，但我认为您不需要在列别名（“first”和“second”）周围加引号。 - ekolis

你使用的MySQL版本是什么？ - Rob

它应该是Cloud SQL。（很难确定，因为它是 Google App Maker 中特定部分所内置的内容）。或者，如果我想将其包含在我的BigQuery拉取中，也可以使用传统的SQL。 - J. G.

请发送一些样本数据以及您期望的数据。 - Saeid Amini

Answer 1

• 你的SQL语句中"first"后面缺少逗号
• 我猜CloudSQL是基于一些旧版本的MySQL，只能使用substring_index进行分割(参见下面的查询-是的，它是冗长而笨拙的，case子句必须清理短字符串)
• 也许尝试在[offset(0)]或[ordinal(1)]中使用括号，这是我们用Postgres方言使用的方法，同时作为#standardSql，而不是#legacySql

第二点的SQL: (fiddle)

select id,
  case when substring_index(cc,' > ',0) = cc then null else substring_index(substring_index(cc,' > ',1),' > ',-1) end as a1,
  case when substring_index(cc,' > ',1) = cc then null else substring_index(substring_index(cc,' > ',2),' > ',-1) end as a2,
  case when substring_index(cc,' > ',2) = cc then null else substring_index(substring_index(cc,' > ',3),' > ',-1) end as a3,
  case when substring_index(cc,' > ',3) = cc then null else substring_index(substring_index(cc,' > ',4),' > ',-1) end as a4,
  case when substring_index(cc,' > ',4) = cc then null else substring_index(substring_index(cc,' > ',5),' > ',-1) end as a5
from d

- Tomáš Záluský

---

哎呀，遗留的 SQL 不允许我使用子字符串 :/. - J. G.

Answer 2

我终于通过正则表达式提取，在bigquery中而不是在appmaker中，到达了我需要去的地方：

SELECT 
  CompoundColumn,

  REGEXP_EXTRACT(CompoundColumn+">",  r'^(.*?)>') first_number,
  REGEXP_EXTRACT(CompoundColumn+">",  r'^(?:(?:.*?)>){1}(.*?)>') second_number,
  REGEXP_EXTRACT(CompoundColumn+">", r'^(?:(?:.*?)>){2}(.*?)>') third_number,
  REGEXP_EXTRACT(CompoundColumn+">",  r'^(?:(?:.*?)>){3}(.*?)>') fourth_number
FROM
  myTable
WHERE
  CompoundColumn IS NOT NULL

代码中的“+”和“>”部分看起来有些丑陋，但我无法匹配不以括号结尾的字符串（“>?”会破坏整个代码），所以我只好让它们都以括号结尾。

- J. G.

Answer 3

所需的“Legacy SQL”将是：

SELECT id, 
       compoundColumn,
       FIRST(SPLIT(compoundColumn, " > ")) AS "first",
       NTH(2, SPLIT(compoundColumn, " > ")) AS "second"
FROM table

请参阅此BigQuery文档页面，了解有关SPLIT、FIRST和NTH函数的更多信息。

- Steve Chambers