以下是 BigQuery 标准 SQL
#standardSQL
SELECT id, path,
(
SELECT STRING_AGG(part, '/' ORDER BY index)
FROM UNNEST(SPLIT(path, '/')) part WITH OFFSET index
WHERE index BETWEEN 1 AND 3
) adjusted_path
FROM `project.dataset.table`
您可以使用问题中的示例数据来测试、玩上面的内容,如下例所示
#standardSQL
WITH `project.dataset.table` AS (
SELECT 1 id, 'foo/bar/baz/foo1/bar1/baz1/' path UNION ALL
SELECT 2, 'foo/bar/quux/blat/foo2/bar2/quux2/blat2'
)
SELECT id, path,
(
SELECT STRING_AGG(part, '/' ORDER BY index)
FROM UNNEST(SPLIT(path, '/')) part WITH OFFSET index
WHERE index BETWEEN 1 AND 3
) adjusted_path
FROM `project.dataset.table`
有结果
Row id path adjusted_path
1 1 foo/bar/baz/foo1/bar1/baz1/ bar/baz/foo1
2 2 foo/bar/quux/blat/foo2/bar2/quux2/blat2 bar/quux/blat
如果由于某种原因你想让你的查询“内联/相似”到你在 PostgreSQL 中使用的 (array_to_string((regexp_split_to_array(path, '/'))[1:3], '/')) - 你可以引入 SQL UDF(我们将其命名为ARRAY_SLICE
)如下例所示
#standardSQL
CREATE temp FUNCTION ARRAY_SLICE(arr ARRAY<STRING>, start INT64, finish INT64)
RETURNS ARRAY<STRING> AS (
ARRAY(
SELECT part FROM UNNEST(arr) part WITH OFFSET index
WHERE index BETWEEN start AND finish ORDER BY index
)
);
SELECT id, path,
ARRAY_TO_STRING(ARRAY_SLICE(SPLIT(path, '/'), 1, 3), '/') adjusted_path
FROM `project.dataset.table`
显然,如果应用到相同的样本数据 - 你会得到相同的结果
#standardSQL
CREATE temp FUNCTION ARRAY_SLICE(arr ARRAY<STRING>, start INT64, finish INT64)
RETURNS ARRAY<STRING> AS (
ARRAY(
SELECT part FROM UNNEST(arr) part WITH OFFSET index
WHERE index BETWEEN start AND finish ORDER BY index
)
);
WITH `project.dataset.table` AS (
SELECT 1 id, 'foo/bar/baz/foo1/bar1/baz1/' path UNION ALL
SELECT 2, 'foo/bar/quux/blat/foo2/bar2/quux2/blat2'
)
SELECT id, path,
ARRAY_TO_STRING(ARRAY_SLICE(SPLIT(path, '/'), 1, 3), '/') adjusted_path
FROM `project.dataset.table`
Row id path adjusted_path
1 1 foo/bar/baz/foo1/bar1/baz1/ bar/baz/foo1
2 2 foo/bar/quux/blat/foo2/bar2/quux2/blat2 bar/quux/blat