如何从指定日期开始将BigQuery数组元素与日期逐一匹配

要实现数组元素按顺序匹配从指定日期开始的连续日期，核心思路是拆分数组并获取每个元素的位置索引，再通过索引计算对应的日期偏移量。以下是主流数据库的具体实现方案：

PostgreSQL 实现 #

PostgreSQL原生支持数组类型，用UNNEST(... WITH ORDINALITY)可同时拆分数组并得到元素的位置序号（从1开始），再通过日期偏移计算匹配日期：

SELECT 
    elem AS number,
    start_date + (pos - 1) * INTERVAL '1 day' AS match_date
FROM 
    (SELECT [1, 2, 3] AS numbers, CAST('2022-01-01' AS DATE) AS start_date) t,
    UNNEST(t.numbers) WITH ORDINALITY AS u(elem, pos);

执行结果：

MySQL 8.0+ 实现 #

MySQL可通过JSON_TABLE解析数组（需将数组转为JSON格式），获取元素和索引后用DATE_ADD计算对应日期：

SELECT 
    j.number,
    DATE_ADD(t.start_date, INTERVAL (j.pos - 1) DAY) AS match_date
FROM 
    (SELECT JSON_ARRAY(1,2,3) AS numbers, CAST('2022-01-01' AS DATE) AS start_date) t,
    JSON_TABLE(
        t.numbers,
        '$[*]' COLUMNS(
            number INT PATH '$',
            pos INT FOR ORDINALITY
        )
    ) j;

SQL Server 实现 #

SQL Server用OPENJSON解析数组，通过key字段获取元素的索引（从0开始），直接用索引值计算日期偏移：

SELECT 
    CAST(value AS INT) AS number,
    DATEADD(DAY, CAST([key] AS INT), start_date) AS match_date
FROM 
    (SELECT '[1,2,3]' AS numbers, CAST('2022-01-01' AS DATE) AS start_date) t
    CROSS APPLY OPENJSON(t.numbers);

内容的提问来源于stack exchange，提问作者hazalciplak