BigQuery中按最近日期关联并丰富同表数据

方法一：关联+QUALIFY过滤（推荐，逻辑清晰） #

假设你的表名为your_table，可以通过拆分数据、关联后用窗口函数筛选最近匹配的推广记录：

WITH orders AS (
  -- 提取订单数据
  SELECT clientId, revenue, orderId, order_date
  FROM your_table
  WHERE orderId IS NOT NULL
),
promotions AS (
  -- 提取推广数据
  SELECT clientId, w_date, w_source, w_campaign
  FROM your_table
  WHERE w_date IS NOT NULL
)
SELECT 
  o.clientId,
  o.revenue,
  o.orderId,
  o.order_date,
  p.w_date,
  p.w_source,
  p.w_campaign
FROM orders o
LEFT JOIN promotions p
ON o.clientId = p.clientId
AND p.w_date <= o.order_date
-- 按订单分组，取匹配推广中日期最晚的那条
QUALIFY ROW_NUMBER() OVER (PARTITION BY o.orderId ORDER BY p.w_date DESC) = 1
ORDER BY o.clientId, o.order_date;

关键说明：

• 用CTE拆分两类数据，避免重复扫描原表
• LEFT JOIN确保无匹配推广记录的订单也能保留（对应字段返回NULL）
• QUALIFY是BigQuery专属语法，直接在窗口函数计算后过滤行：按每个订单分组，把匹配的推广数据按日期倒序排列，取第一行就是最近的有效推广记录
• 如果同一天有多个推广记录，可以在ORDER BY后加额外字段（比如w_campaign）来确定优先级

方法二：向前填充推广数据（适合大数据量场景） #

如果你的表数据量很大，关联查询效率不高，可以用LAST_VALUE窗口函数向前填充最近的推广信息：

WITH combined_data AS (
  -- 合并两类数据并标记类型
  SELECT 
    clientId,
    revenue,
    orderId,
    order_date,
    w_date,
    w_source,
    w_campaign,
    CASE WHEN orderId IS NOT NULL THEN 'order' ELSE 'promo' END AS record_type
  FROM your_table
),
ordered_data AS (
  SELECT 
    *,
    -- 向前填充最近的推广日期、来源、活动
    LAST_VALUE(w_date IGNORE NULLS) OVER (
      PARTITION BY clientId 
      ORDER BY COALESCE(order_date, w_date) 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS latest_w_date,
    LAST_VALUE(w_source IGNORE NULLS) OVER (
      PARTITION BY clientId 
      ORDER BY COALESCE(order_date, w_date) 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS latest_w_source,
    LAST_VALUE(w_campaign IGNORE NULLS) OVER (
      PARTITION BY clientId 
      ORDER BY COALESCE(order_date, w_date) 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS latest_w_campaign
  FROM combined_data
)
-- 只保留订单数据，填充后的推广信息已关联
SELECT 
  clientId,
  revenue,
  orderId,
  order_date,
  latest_w_date AS w_date,
  latest_w_source AS w_source,
  latest_w_campaign AS w_campaign
FROM ordered_data
WHERE record_type = 'order'
ORDER BY clientId, order_date;

关键说明：

• 合并数据后按clientId分组，用COALESCE(order_date, w_date)统一排序字段（订单用下单日期，推广用推广日期）
• LAST_VALUE IGNORE NULLS会自动跳过NULL值，把最近的推广数据填充到后续的订单行中
• 这种方法无需关联，扫描一次表即可完成，适合百万级以上数据量的场景

注意事项： #

• 确保order_date和w_date是DATE或DATETIME类型，避免字符串格式导致的日期比较错误
• 如果需要给无匹配推广的订单设置默认值，可以用IFNULL(p.w_source, '无推广')这类函数处理
• 如果同一clientId下没有早于订单日期的推广记录，两种方法都会返回对应字段为NULL，可根据业务需求调整

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