在SQL查询中结合内连接与Between语句：random()函数用法是否正确？

数据表结构与数据 #

建表与插入语句 #

CREATE TABLE table_a (
  name VARCHAR(255),
  date_1 DATE
);

INSERT INTO table_a (name, date_1) VALUES
('john', '2010-01-01'),
('john', '2012-02-01'),
('john', '2017-08-01'),
('sara', '2008-04-01'),
('sara', '2011-04-01'),
('tim', '2000-01-01'),
('tim', '2001-01-01'),
('alex', '2013-01-01');

CREATE TABLE table_b (
  name VARCHAR(255),
  date_2 DATE,
  date_3 DATE,
  var CHAR(1)
);

INSERT INTO table_b (name, date_2, date_3, var) VALUES
('john', '2001-01-01', '2015-01-01', 'b'),
('sara', '2000-01-01', '2015-01-01', 'c'),
('sara', '2015-01-02', '2022-01-01', 'a'),
('tim', '2020-01-01', '2021-01-01', 'a'),
('john', '1998-01-01', '1999-01-01', 'd');

表数据展示 #

#table_a
      name     date_1
 john 2010-01-01
 john 2012-02-01
 john 2017-08-01
 sara 2008-04-01
 sara 2011-04-01
  tim 2000-01-01
  tim 2001-01-01
 alex 2013-01-01

 #table_b
  name     date_2     date_3 var
 john 2001-01-01 2015-01-01   b
 sara 2000-01-01 2015-01-01   c
 sara 2015-01-02 2022-01-01   a
  tim 2020-01-01 2021-01-01   a
 john 1998-01-01 1999-01-01   d

连接需求 #

• 精确连接：基于name关联两张表，若table_a的date_1落在table_b的date_2与date_3区间内，则执行连接。
• 窗口连接：针对未在精确连接中匹配的行，若其name存在于table_b中，且table_b存在date_2早于该行date_1的记录，则匹配最接近该date_1的table_b行；否则不连接。

现有实现方案 #

随机ID方案（宣称更快） #

# random ID approach : faster

WITH exact_join AS (
  SELECT a.*, b.var, random() as random_id
  FROM table_a a
  LEFT JOIN table_b b ON a.name = b.name AND a.date_1 BETWEEN b.date_2 AND b.date_3
),
window_join AS (
  SELECT a.*, b.var
  FROM table_a a
  LEFT JOIN (
    SELECT name, var, date_2, ROW_NUMBER() OVER (PARTITION BY name ORDER BY date_2 DESC) as rn
    FROM table_b
  ) b ON a.name = b.name AND a.date_1 > b.date_2
  WHERE b.rn = 1 AND a.random_id NOT IN (SELECT random_id FROM exact_join)
)
SELECT * FROM exact_join
UNION ALL
SELECT * FROM window_join;

非随机ID方案（宣称更慢） #

# non-random id approach (slower)

WITH exact_join AS (
  SELECT a.*, b.var, ROW_NUMBER() OVER (ORDER BY 1) as id
  FROM table_a a
  LEFT JOIN table_b b ON a.name = b.name AND a.date_1 BETWEEN b.date_2 AND b.date_3
),
window_join AS (
  SELECT a.*, b.var
  FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY 1) as id
    FROM table_a
  ) a
  LEFT JOIN (
    SELECT name, var, date_2, ROW_NUMBER() OVER (PARTITION BY name ORDER BY date_2 DESC) as rn
    FROM table_b
  ) b ON a.name = b.name AND a.date_1 > b.date_2
  WHERE b.rn = 1 AND a.id NOT IN (SELECT id FROM exact_join)
)
SELECT * FROM exact_join
UNION ALL
SELECT * FROM window_join;

当前输出结果 #

name     date_1  var id
 john 2010-01-01    b  1
 john 2012-02-01    b  2
 john 2017-08-01 <NA>  3
 sara 2008-04-01    c  4
 sara 2011-04-01    c  5
  tim 2000-01-01 <NA>  6
  tim 2001-01-01 <NA>  7
 alex 2013-01-01 <NA>  8

疑问解答：使用random()函数防止行重复处理或遗漏是否正确？ #

结论：这种方式完全不正确，且存在多处严重问题

1. 语法错误：随机ID方案的window_join中，table_a a并没有random_id字段，直接引用a.random_id会导致查询报错，根本无法正常执行。
2. 随机性导致数据错误：即使修正语法（比如先给table_a每行生成唯一random_id），random()生成的值仍存在极小概率重复，一旦不同行生成相同的随机ID，会导致本该进入窗口连接的行被错误过滤，或者本该排除的行被保留，直接破坏数据准确性。
3. 结果不可重复：每次执行查询都会生成不同的random_id，导致查询结果可能不一致，无法保证结果的确定性，这在生产环境中是致命的，调试和验证都会变得异常困难。

对比非随机ID方案，虽然用ROW_NUMBER() OVER (ORDER BY 1)生成的ID依赖数据库执行顺序，不是最优方案（优先使用表的主键或唯一标识），但至少保证了每行ID唯一，结果可重复。

更优的实现方案 #

直接标记table_a的行是否存在精确匹配，逻辑更清晰且性能稳定：

WITH a_with_match_flag AS (
  SELECT 
    a.*,
    EXISTS (
      SELECT 1 
      FROM table_b b 
      WHERE a.name = b.name AND a.date_1 BETWEEN b.date_2 AND b.date_3
    ) AS has_exact_match
  FROM table_a a
),
exact_join AS (
  SELECT a.*, b.var
  FROM a_with_match_flag a
  JOIN table_b b ON a.name = b.name AND a.date_1 BETWEEN b.date_2 AND b.date_3
),
window_join AS (
  SELECT a.*, b.var
  FROM a_with_match_flag a
  LEFT JOIN (
    SELECT 
      name, var, date_2,
      ROW_NUMBER() OVER (PARTITION BY name ORDER BY date_2 DESC) as rn
    FROM table_b
  ) b ON a.name = b.name AND a.date_1 > b.date_2
  WHERE a.has_exact_match = false AND b.rn = 1
)
SELECT * FROM exact_join
UNION ALL
SELECT * FROM window_join;

内容的提问来源于stack exchange，提问作者Uk rain troll