我想计算路径中有多少相似的单词(将在 delimiter 处拆分/
)并返回一个匹配的整数数组。
为了复制这种情况,这是我正在使用的查询:
CREATE TEMP FUNCTION HOW_MANY_MATCHES_IN_PATH(src_path ARRAY<STRING>, test_path ARRAY<STRING>) RETURNS ARRAY<INTEGER> AS (
-- WHAT DO I PUT HERE?
);
SELECT
*,
HOW_MANY_MATCHES_IN_PATH(src_path, test_path) as dir_path_match_count
FROM (
SELECT
ARRAY_AGG(x) AS src_path,
ARRAY_AGG(y) as test_path
FROM
UNNEST([
'lib/client/core.js',
'lib/server/core.js'
]) AS x, UNNEST([
'test/server/core.js'
]) as y
)
我已经尝试在函数中使用ARRAY
and ,但我最终会遇到错误或 4 个项目的数组(在此示例中)UNNEST
HOW_MANY_MATCHES_IN_PATH
考虑以下方法
create temp function how_many_matches_in_path(src_path string, test_path string) returns integer as (
(select count(distinct src)
from unnest(split(src_path, '/')) src,
unnest(split(test_path, '/')) test
where src = test)
);
select *,
array( select how_many_matches_in_path(src, test)
from t.src_path src with offset
join t.test_path test with offset
using(offset)
) dir_path_match_count
from your_table t
是否适用于您问题中的输入数据样本
with your_table as (
select
['lib/client/core.js', 'lib/server/core.js'] src_path,
['test/server/core.js', 'test/server/core.js'] test_path
)
输出是
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句