sql - 通过匹配多列查找最接近的时间 (SQL)

Question

我有两个表:表 1 是在特定时间、日期、名称访问建筑物

Accesses

    |  Date      |    Time      | Name | accesses|
    -----------------------------------------------
    | 2018-10-10 |  10:10:34.00 |  JA  |     1   | 
    | 2018-10-10 |  10:14:10.10 |  AA  |     1   |
    | 2018-10-10 |  12:15:00.45 |  BE  |     1   |
    | 2018-10-10 |  15:00:00.50 |  JA  |     1   |
    | 2018-10-10 |  16:56:56.15 |  BE  |     1   |

而表2显示了在某些访问期间是否有失败

Failure

    |  Date      |    Time      | Name | failure |
    -----------------------------------------------
    | 2018-10-10 |  10:10:40.00 |  JA  |     1   | 
    | 2018-10-10 |  10:15:06.00 |  AA  |     1   |
    | 2018-10-10 |  16:57:01.14 |  BE  |     1   |

期望的输出

   Output

    |  Date      |    Time      | Name | accesses|  Failure |
    ---------------------------------------------------------
    | 2018-10-10 |  10:10:34.00 |  JA  |     1   |    1     |
    | 2018-10-10 |  10:14:10.10 |  AA  |     1   |    1     |
    | 2018-10-10 |  12:15:00.45 |  BE  |     1   |   NULL   |  
    | 2018-10-10 |  15:00:00.50 |  JA  |     1   |   NULL   |
    | 2018-10-10 |  16:56:56.15 |  BE  |     1   |    1     |

基本上，输出将包含访问并将故障表与从访问表中检测到的最接近时间相匹配。我尝试了不同的匹配时间戳的算法，但我仍然收到错误，因为并非所有访问都失败了，这就是为什么我希望它在输出中带有 NULL。谢谢

Answer 1

数据库在时间表示方式上有所不同。但是，一般的想法是您可以使用 exists 来解决这个问题:

select a.*,
       (case when exists (select 1
                          from failures f
                          where f.name = a.name and
                                f.date = a.date and
                                f.time > a.time - interval '1' minute and
                                f.time < a.time + interval '1' minute 
             then 1 else 0
        end) as failure_flag
from accesses a;

这使用了一个带有值 0 和 1 的标志。显然，您可以删除 else 0 子句以获得 NULL 而不是 0。

如果结合日期和时间，这实际上会更准确。根据数据的性质，可能不值得在午夜添加修复程序。