SQL/BigQuery匹配同索引数组元素并输出对应日期的实现方法

实现方案 #

你可以通过BigQuery的UNNEST函数搭配数组索引绑定来实现需求，以下是可直接运行的完整代码：

WITH sample_data AS (
  -- 这里是你的示例数据，实际使用时替换为你的业务表即可
  SELECT 121 AS Match_ID, [101, 102] AS Match_Home, [103, 121] AS Match_Away, ['01-02-2021', '01-05-2021'] AS Match_Home_Date, ['01-07-2021', '01-09-2021'] AS Match_Away_Date UNION ALL
  SELECT 131 AS Match_ID, [131, 140] AS Match_Home, [117, 115] AS Match_Away, ['02-02-2021', '02-15-2021'] AS Match_Home_Date, ['02-20-2021', '02-25-2021'] AS Match_Away_Date
)
SELECT 
  COALESCE(
    -- 匹配主场数组的ID，返回对应索引的日期
    (SELECT date FROM UNNEST(Match_Home) id WITH OFFSET idx JOIN UNNEST(Match_Home_Date) date WITH OFFSET idx USING(idx) WHERE id = Match_ID),
    -- 匹配客场数组的ID，返回对应索引的日期
    (SELECT date FROM UNNEST(Match_Away) id WITH OFFSET idx JOIN UNNEST(Match_Away_Date) date WITH OFFSET idx USING(idx) WHERE id = Match_ID)
  ) AS `Final Date`
FROM sample_data

逻辑说明 #

• UNNEST函数展开数组时通过WITH OFFSET拿到元素对应的索引，将ID数组和日期数组按索引对齐绑定，保证匹配到ID后拿到的是同位置的日期
• COALESCE函数会优先返回主场侧匹配到的日期，主场无匹配时返回客场侧的匹配结果
• 实际使用时删除CTE的sample_data部分，将FROM后的表名替换为你的业务表即可

运行后输出结果和你预期的完全一致：

Final Date
01-09-2021
02-02-2021

内容的提问来源于stack exchange，提问作者Jay Lorenz Dungog