如何用更简洁SQL实现重复阈值填充列并生成结果表c？

表结构与数据 #

表a #

表b #

需求 #

创建新表c，包含day_date、id和next_date字段：其中next_date为大于对应day_date的最小some_date，无符合条件值则为null。预期结果如下：

预期结果表c #

当前实现代码 #

CREATE TEMP TABLE some_next AS (
  SELECT
    day_date,
    id,
    CASE WHEN some_date > day_date THEN some_date ELSE NULL END AS next_churn_date,
    ROW_NUMBER() OVER (PARTITION BY day_date, id ORDER BY some_date ASC) AS rn_next
  FROM a CROSS JOIN b
  WHERE some_date > day_date OR day_date >= (SELECT MAX(some_date) FROM b bb WHERE bb.id = b.id)
);

CREATE TABLE c AS (
  SELECT * FROM some_next WHERE rn_next = 1 ORDER BY id, day_date
);

简洁解决方案思路 #

方案1：使用LATERAL子查询（适用于PostgreSQL、MySQL 8.0+等支持LATERAL的数据库） #

避免全量交叉连接的性能损耗，直接为每个day_date+id组合匹配目标日期：

CREATE TABLE c AS
SELECT
  a.day_date,
  ids.id,
  sub.min_some_date AS next_date
FROM a
CROSS JOIN (SELECT DISTINCT id FROM b) ids
LEFT JOIN LATERAL (
  SELECT some_date AS min_some_date
  FROM b
  WHERE b.id = ids.id AND b.some_date > a.day_date
  ORDER BY some_date ASC
  LIMIT 1
) sub ON true
ORDER BY ids.id, a.day_date;

• 先提取表b的唯一id集合，与表a交叉得到所有day_date+id组合；
• 通过LATERAL子查询，针对每个组合精准筛选大于当前day_date的最小some_date，无匹配则返回null；
• 一步生成目标表，逻辑清晰且性能更优。

方案2：窗口函数+筛选（通用SQL方案） #

针对不支持LATERAL的数据库，用窗口函数预处理表b的日期区间：

CREATE TABLE c AS
WITH b_processed AS (
  SELECT
    id,
    some_date,
    -- 标记每个日期的下一个后续日期
    LEAD(some_date) OVER (PARTITION BY id ORDER BY some_date) AS next_some_date,
    -- 标记每个id的最大日期
    MAX(some_date) OVER (PARTITION BY id) AS max_some_date
  FROM b
)
SELECT
  a.day_date,
  bp.id,
  CASE
    WHEN a.day_date < bp.some_date THEN bp.some_date
    WHEN a.day_date >= bp.some_date AND (bp.next_some_date IS NOT NULL AND a.day_date < bp.next_some_date) THEN bp.next_some_date
    WHEN a.day_date >= bp.max_some_date THEN NULL
  END AS next_date
FROM a
CROSS JOIN b_processed bp
-- 筛选每个组合的唯一匹配结果
QUALIFY ROW_NUMBER() OVER (PARTITION BY a.day_date, bp.id ORDER BY 
  CASE WHEN a.day_date < bp.some_date THEN bp.some_date ELSE bp.next_some_date END ASC) = 1
ORDER BY bp.id, a.day_date;

• 用LEAD和MAX窗口函数预处理表b，得到每个日期的后续日期和id的最大日期；
• 交叉连接后通过条件判断匹配对应的next_date；
• 最后用QUALIFY（或子查询）筛选每个day_date+id组合的唯一结果。

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