BigQuery中基于序列号与日期范围关联两张表的实现方法

嘿，我来帮你搞定这个关联问题！你的需求核心是把事件表和追踪器时段表对应起来，既要匹配序列号的前缀，又要让事件日期落在追踪器的运行时段里对吧？先给你梳理清楚思路，再上可直接用的代码。

首先得注意：你给的dates示例数据里有几个日期逻辑明显不对（比如B1的开始日期是10/02/2019，结束日期反而更早是18/01/2019），这会导致这些记录根本匹配不到任何事件，我先假设是输入笔误，按合理的日期逻辑写代码，你到时候根据实际数据调整就行。

直接可用的SQL代码 #

WITH formatted_events AS (
  -- 先把事件表的日期转成BigQuery能识别的DATE类型，方便后续比较
  SELECT
    PARSE_DATE('%d/%m/%Y', Date) AS event_date,
    Serial,
    Quality
  FROM `table.events`
),
formatted_dates AS (
  -- 处理时段表：转日期格式，同时提取Serial_id的字母前缀（比如A1提取成A）
  SELECT
    PARSE_DATE('%d/%m/%Y', Start_Date) AS start_date,
    PARSE_DATE('%d/%m/%Y', End_Date) AS end_date,
    Serial_id,
    -- 用正则提取开头的所有字母，适配任意字母长度的前缀
    REGEXP_EXTRACT(Serial_id, r'^[A-Za-z]+') AS serial_prefix
  FROM `table.dates`
)
SELECT
  -- 把日期转回你原来的DD/MM/YYYY格式输出
  FORMAT_DATE('%d/%m/%Y', fe.event_date) AS Date,
  fe.Serial,
  fd.Serial_id,
  fe.Quality,
  FORMAT_DATE('%d/%m/%Y', fd.start_date) AS `Start Date`,
  FORMAT_DATE('%d/%m/%Y', fd.end_date) AS `End Date`
FROM formatted_events fe
INNER JOIN formatted_dates fd
  -- 第一个条件：事件表的Serial和时段表提取的前缀完全匹配
  ON fe.Serial = fd.serial_prefix
  -- 第二个条件：事件日期必须在追踪器的运行时段内
  AND fe.event_date BETWEEN fd.start_date AND fd.end_date
-- 按日期和序列号排序，和你的示例输出顺序一致
ORDER BY fe.event_date, fe.Serial;

代码细节解释 #

1. 日期格式转换：BigQuery的日期比较必须用标准DATE类型，所以先用PARSE_DATE把你输入的字符串日期转成标准格式，最后再用FORMAT_DATE转回你要的输出格式，保证前后格式统一。
2. 序列号前缀提取：用正则表达式^[A-Za-z]+可以精准提取Serial_id开头的所有字母，不管后面跟多少数字（比如A1、A123都能提取出A），完美匹配你事件表的Serial字段。
3. 关联逻辑：用INNER JOIN同时满足两个条件，确保只有既匹配序列号又在时段内的记录才会被关联出来。如果想保留所有事件记录（哪怕没有对应的时段），把INNER JOIN改成LEFT JOIN就行，未匹配的字段会显示NULL。

关于你示例数据的小提醒 #

你给的dates表中B1、C2的日期范围是反向的（结束早于开始），这种情况下这些记录是无法匹配到任何事件的，你可以检查下是不是输入时的日期顺序写错了，调整成合理的范围后，代码就能输出你期望的结果啦。

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