sql - 扫描列以获取值并使用结果选择其他列

Question

我有一个包含 30 列的 bigquery 表：

strcol,strcol1,strcol2,..  startstr, startstr1, startstr2,..   endstr, endstr1, endstr2,..
1111, 2343, 1012......     "car", "boat", "scooter".....       "plane", "bike", "boat"
9999, 1012, 3333......     "scooter", "boat", "scooter".....       "boat", "bike", "boat"

我需要扫描所有列“strcol”以找到一个数字。如果我找到我需要使用对应的“startstr”和“endstr”的号码。

示例：“strcol2”列中存在数字 1012，然后我需要使用“startstr2”和“endstr2”。我的选择语句只需要两列。结果将类似于：

start, end
scooter, boat
boat, bike

我正在考虑创建一个 strcol...strcol9 数组并尝试查找数字并返回索引，然后使用该索引找到正确的 startstr 和 endstr。但我不知道该怎么做。也许有更好的选择？有任何想法吗？

数字 1012 永远是固定的，永远不会改变。

干杯，克里斯

标签： sqlgoogle-bigquery

Answer 1

以下是 BigQuery 标准 SQL，可能看起来不那么“高级/冗长”，因此您可能很容易处理/维护

#standardSQL
select 
  regexp_extract(line, r'"startstr' || pattern) start,
  regexp_extract(line, r'"endstr' || pattern) `end`,
from `project.dataset.table` t, 
  unnest([to_json_string(t)]) line,
  unnest(generate_array(0, array_length(split(line))/3 - 1)) index,
  unnest([if(index > 0, cast(index as string), '') || '":"?([^,"]+)"?']) pattern
where regexp_extract(line, r'"strcol' || pattern) = cast(1012 as string)

当应用于您问题的样本数据时 - 输出是