基于有效期区间关联两张表：BigQuery价格与促销数据整合

针对你需要关联售价表和促销表，生成包含原价与折后价的时间区间数据的需求，可以通过区间重叠关联结合时间函数实现，以下是适配BigQuery的SQL方案：

假设两张表分别命名为product_prices（售价表）和promotions（促销表），执行以下SQL：

SELECT
  p.article_id,
  p.sell_price_at,
  -- 根据促销规则计算折后价：无促销时等于原价，立减优惠则原价减去优惠值
  CASE
    WHEN prom.promotion_value IS NOT NULL THEN p.sell_price_at - prom.promotion_value
    ELSE p.sell_price_at
  END AS sell_price_at_after_discount,
  -- 取两个区间的最晚起始时间作为新区间的开始
  GREATEST(p.validity_start_at, prom.real_start_datetime) AS interval_start_at,
  -- 取两个区间的最早结束时间作为新区间的结束
  LEAST(p.validity_end_at, prom.real_end_datetime) AS interval_end_at,
  prom.promotion_id,
  prom.promotion_value,
  prom.promotion_mechanism
FROM
  `your-project.your-dataset.product_prices` p
JOIN
  `your-project.your-dataset.promotions` prom
ON
  p.article_id = prom.article_id
  -- 核心：判断两个时间区间存在重叠
  AND p.validity_start_at < prom.real_end_datetime
  AND p.validity_end_at > prom.real_start_datetime
ORDER BY
  interval_start_at DESC;

逻辑说明 #

1. 区间关联：通过article_id匹配同一商品，并使用区间重叠条件筛选出有时间交集的记录，避免无意义的关联。
2. 区间重构：用GREATEST和LEAST函数精准计算两个区间的重叠部分，生成新的时间区间，确保每个区间内的售价和促销规则唯一有效。
3. 折后价计算：根据促销表的promotion_value判断，存在促销时按立减规则计算折后价，无促销时直接使用原价。

边界处理 #

如果你的时间区间是左闭右闭（即结束时间包含该时点），可以将关联条件中的<和>改为<=和>=，避免遗漏衔接点的关联。

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