sql - 如何连接具有多个匹配记录的两个表？

Question

当 ID 和日期对齐时，我想加入两个表。我在想这样的事情:

SELECT * from t1 inner join t2 on t1.id = t2.id and t1.date >= t2.date;

但是每个 id 的多个条目可能存在于两个表中，我想确保最佳记录匹配——所以如果表 2 有 4/10、4/15 和 4/20 的条目，并且表1有3/15、4/11、4/16、4/21的记录，则记录匹配如下:

id | t1.date | t2.date
1  |   3/15  | -----  (No match, wouldn't be returned in the results)
1  |   4/11  | 4/10
1  |   4/16  | 4/15
1  |   4/21  | 4/20

Answer 1

一种方法是 left join id 和 t1.date >= t2.date 上的表，使用 Window 函数 row_number() 在合适的有序分区(t1.date - t2.date)上，并选择最小的日期差异，这是第一个分区行:

CREATE TABLE t1 ("id" integer, "date" date, "value" integer);

CREATE TABLE t2 ("id" integer, "date" date);

INSERT INTO t1 VALUES
  (1, '2018-03-15', 10),
  (1, '2018-04-11', 20),
  (1, '2018-04-11', 30),
  (1, '2018-04-16', 30),
  (1, '2018-04-21', 20);

INSERT INTO t2 VALUES
  (1, '2018-04-10'),
  (1, '2018-04-15'),
  (1, '2018-04-20');

WITH q AS (
  SELECT
    t1."id", t1."date" t1_date, t2."date" t2_date, t1."value", row_number() OVER
      (PARTITION BY t1."id", t1."date", t1."value" ORDER BY (t1."date" - t2."date")) row_num
  FROM
    t1 LEFT JOIN t2 ON t1."id" = t2."id" AND t1."date" >= t2."date"
)
SELECT "id", "t1_date", "t2_date", "value" FROM q
WHERE row_num = 1;

--  id |  t1_date   |  t2_date   | value 
-- ----+------------+------------+-------
--   1 | 2018-03-15 |            |    10
--   1 | 2018-04-11 | 2018-04-10 |    20
--   1 | 2018-04-11 | 2018-04-10 |    30
--   1 | 2018-04-16 | 2018-04-15 |    30
--   1 | 2018-04-21 | 2018-04-20 |    20
-- (5 rows)

请注意，如果您希望结果数据集排除 t2.date 不匹配的行，只需将 LEFT JOIN 替换为 INNER JOIN.