BigQuery：如何按序列号与日期范围合并两个追踪器数据表？

要实现你需要的表合并，核心是同时满足序列号前缀匹配和事件日期落在运行日期范围内这两个关联条件。下面结合你的示例数据，用SQL给出具体实现方案：

核心关联逻辑 #

我们需要同时满足两个匹配规则：

• 从table.dates的Serial_id中提取前缀（比如A1提取A），和table.events的Serial字段精准匹配
• 确保table.events的Date字段落在table.dates的Start_Date与End_Date区间内

SQL 查询语句 #

这里以通用SQL语法为例，不同数据库的子串提取函数略有差异，我会标注适配说明：

SELECT
    e.Date,
    e.Serial,
    d.Serial_id,
    e.Quality,
    d.Start_Date AS "Start Date",
    d.End_Date AS "End Date"
FROM
    table.events e
JOIN
    table.dates d
ON
    -- 匹配序列号前缀：取Serial_id的第一个字符与events.Serial相等
    LEFT(d.Serial_id, 1) = e.Serial
    -- 确保事件日期在运行日期区间内
    AND e.Date BETWEEN d.Start_Date AND d.End_Date
-- 可选：按日期和序列号排序，让结果更清晰
ORDER BY
    e.Date, e.Serial;

数据库适配细节 #

• 如果是Oracle数据库，将LEFT(d.Serial_id, 1)替换为SUBSTR(d.Serial_id, 1, 1)
• 如果你的日期字段是字符串类型（而非原生日期类型），需要先转换为日期格式再比较：
  • MySQL：用STR_TO_DATE(e.Date, '%d/%m/%Y')替代e.Date，同理处理Start_Date和End_Date
  • SQL Server：用CONVERT(DATE, e.Date, 103)替代e.Date，确保日期比较的准确性

示例数据匹配结果验证 #

用你提供的示例数据，这条查询会输出以下关键匹配项：

• 14/01/2019的Serial A → 匹配Serial_id A2（14在08/01/2019-14/01/2019区间内）
• 15/01/2019的Serial A → 匹配Serial_id A1（15在15/01/2019-18/01/2019区间内）
• 13/01/2019的Serial C → 匹配Serial_id C1（13在13/01/2019-16/01/2019区间内）
• 17/01/2019的Serial C → 无匹配项（C1的End_Date是16/01，C2的Start_Date是17/02，日期无重叠）

额外说明 #

如果存在一个事件日期同时匹配多个Serial_id的情况（比如某个Serial的事件日期落在多个运行区间内），查询会返回所有符合条件的组合，完全满足你提到的“同一日期可能对应多个序列号”的需求。

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