BigQuery实现按最近日期左连接：选取早于Open_Date_Book的最近Open_Date_Score

需求说明 #

左连接Customer Book Table（主表）与Customer Score Table，为每条Book记录匹配早于其Open_Date_Book的最近一条Score记录（即最大的Open_Date_Score ≤ Open_Date_Book）。

示例表结构 #

Customer Book Table #

Customer Score Table #

期望结果 #

实现方法 #

方法一：使用ROW_NUMBER()窗口函数 #

先关联两表并筛选符合日期条件的记录，再通过窗口函数为每条Book记录的匹配Score按日期倒序排名，最终取排名第一的最近记录。

WITH ranked_scores AS (
  SELECT
    b.Customer_ID,
    b.Open_Date_Book,
    b.Book_Value,
    s.Open_Date_Score,
    s.Score_Value,
    ROW_NUMBER() OVER (
      PARTITION BY b.Customer_ID, b.Open_Date_Book
      ORDER BY s.Open_Date_Score DESC
    ) AS score_rank
  FROM
    `your-project.your-dataset.Customer_Book_Table` b
  LEFT JOIN
    `your-project.your-dataset.Customer_Score_Table` s
  ON
    b.Customer_ID = s.Customer_ID
    AND s.Open_Date_Score <= b.Open_Date_Book
)
SELECT
  Customer_ID,
  Open_Date_Book,
  Book_Value,
  Open_Date_Score,
  Score_Value
FROM
  ranked_scores
WHERE
  score_rank = 1;

方法二：使用MAX()子查询匹配最近日期 #

先为每条Book记录找到符合条件的最大Score日期，再关联回Score表获取对应分数值，适合数据量较大时优化性能。

WITH book_max_score_date AS (
  SELECT
    b.Customer_ID,
    b.Open_Date_Book,
    b.Book_Value,
    MAX(s.Open_Date_Score) AS latest_score_date
  FROM
    `your-project.your-dataset.Customer_Book_Table` b
  LEFT JOIN
    `your-project.your-dataset.Customer_Score_Table` s
  ON
    b.Customer_ID = s.Customer_ID
    AND s.Open_Date_Score <= b.Open_Date_Book
  GROUP BY
    b.Customer_ID, b.Open_Date_Book, b.Book_Value
)
SELECT
  m.Customer_ID,
  m.Open_Date_Book,
  m.Book_Value,
  s.Open_Date_Score,
  s.Score_Value
FROM
  book_max_score_date m
LEFT JOIN
  `your-project.your-dataset.Customer_Score_Table` s
ON
  m.Customer_ID = s.Customer_ID
  AND m.latest_score_date = s.Open_Date_Score;

关键注意事项 #

• 替换代码中的your-project.your-dataset为实际的BigQuery项目和数据集名称
• 若某客户无符合条件的Score记录，结果中Open_Date_Score和Score_Value会显示为NULL，符合左连接预期
• 方法一适合处理同一日期存在多条Score的场景（仅取一条），方法二在大数据量下性能更优

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