mysql - Vertica - 使用 A 列和近似日期时间连接 2 个表

Question

TABLE1
ID  COL1 DATE
1   AA   2013-01-01 00:10:21
2   BB   2013-01-02 01:10:23
3   VV   2013-02-01 13:11:21
1   SD   2013-02-11 10:13:33
2   BB   2013-01-02 09:10:23
1   CC   2013-01-01 10:10:21
3   VV   2013-02-01 19:11:21

TABLE2
ID1  COL2 DATE1
1   A2A   2013-01-01 08:20:21
2   BDB   2013-01-02 01:30:23
3   V4V   2013-02-01 13:31:21

结果应该是

ID  DATE 
1   2013-01-01 10:10:21
2   2013-01-02 01:10:23
3   2013-02-01 13:11:21

我的查询:

SELECT B.ID,B.DATE
FROM TABLE1 B 
LEFT JOIN TABLE2 A 
ON B.ID=A.ID1
AND to_char(B.DATE,'YYYY-MM-YY') = to_char(A.DATE1,'YYYY-MM-YY');

结果我正在寻找 B.ID=A.ID1 并且 B.DATE 应该是最接近 A.DATE1 的间隔

预先感谢您的帮助。

Answer 1

问题不清楚，但您可以通过下一个查询获得 HP Vertica 中所需的输出:

daniel=> \p
SELECT B.ID,
       B.DATE
FROM TABLE1 B INNER JOIN TABLE2 A ON
        B.ID = A.ID1
           AND
        B.DATE < A.DATE1;

daniel=> \g
 ID |        DATE
----+---------------------
  1 | 2013-01-01 00:10:21
  2 | 2013-01-02 01:10:23
  3 | 2013-02-01 13:11:21
(3 rows)

更新

I am looking for something like this B.DATE = (A.DATE1 - interval 2 hours)

输出与问题定义不匹配。让我们检查一下时间戳之间的差异:

daniel=> \p
SELECT B.ID,
       B.DATE,
       B.DATE - A.DATE1 AS DATE_DIFF,
       DECODE(B.DATE - A.DATE1 <= INTERVAL '02:00:00', 1, 'MATCH', 'NOT MATCH') AS MATCH
FROM TABLE1 B INNER JOIN TABLE2 A ON
        B.ID = A.ID1;

daniel=> \g
 ID |        DATE         |  DATE_DIFF  |   MATCH
----+---------------------+-------------+-----------
  1 | 2013-01-01 00:10:21 | -08:10      | MATCH
  1 | 2013-01-01 10:10:21 | 01:50       | MATCH
  1 | 2013-02-11 10:13:33 | 41 01:53:12 | NOT MATCH
  2 | 2013-01-02 01:10:23 | -00:20      | MATCH
  2 | 2013-01-02 09:10:23 | 07:40       | NOT MATCH
  3 | 2013-02-01 13:11:21 | -00:20      | MATCH
  3 | 2013-02-01 19:11:21 | 05:40       | NOT MATCH
(7 rows)

因此根据定义输出将是:

daniel=> SELECT B.ID,
daniel->        B.DATE
daniel-> FROM TABLE1 B INNER JOIN TABLE2 A
daniel->        ON B.ID = A.ID1
daniel->        AND B.DATE - A.DATE1 <= INTERVAL '02:00:00';
 ID |        DATE     
----+---------------------
  1 | 2013-01-01 00:10:21
  1 | 2013-01-01 10:10:21
  2 | 2013-01-02 01:10:23
  3 | 2013-02-01 13:11:21
(4 rows)

干杯；)