临时加入Hive查询（时间紧迫的事件）

临时加入Hive查询（时间紧迫的事件）

这将是一个幼稚的解决方案：

select  *from         messages c        cross join      messages mwhere   m.time  between c.time - interval '0.001' second     and     c.time + interval '0.001' second    and c.word1 = '2B3B'    and m.word2 = 'ABAA';

---

+----------------------------+--------+-------+-------+----------------------------+--------+-------+-------+| time | source | word1 | word2 | time | source | word1 | word2 |+----------------------------+--------+-------+-------+----------------------------+--------+-------+-------+| 2012-02-01 23:43:16.998824 |   0001 | 2B3B  | FAF0  | 2012-02-01 23:43:16.999356 |   0002 |  2326 | ABAA  |+----------------------------+--------+-------+-------+----------------------------+--------+-------+-------+

---

这是具有良好性能的解决方案

select  *from         messages c        join messages m        on   floor (cast(c.time as decimal(37,7)) / (2 * 0.001))   =  floor (cast(m.time as decimal(37,7)) / (2 * 0.001))where   m.time  between c.time - interval '0.001' second     and     c.time + interval '0.001' second    and c.word1 = '2B3B'    and m.word2 = 'ABAA'union allselect  *from         messages c        join messages m        on   floor ((cast(c.time as decimal(37,7)) + 0.001) / (2 * 0.001))   =  floor ((cast(m.time as decimal(37,7)) + 0.001) / (2 * 0.001))where   floor (cast(c.time as decimal(37,7)) / (2 * 0.001))     <>        floor (cast(m.time as decimal(37,7)) / (2 * 0.001))    and m.time  between c.time - interval '0.001' second     and     c.time + interval '0.001' second    and c.word1 = '2B3B'    and m.word2 = 'ABAA'

---

+----------------------------+--------+-------+-------+----------------------------+-------+-------+-------+| time | source | word1 | word2 |_col4 | _col5 | _col6 | _col7 |+----------------------------+--------+-------+-------+----------------------------+-------+-------+-------+| 2012-02-01 23:43:16.998824 |   0001 | 2B3B  | FAF0  | 2012-02-01 23:43:16.999356 |  0002 |  2326 | ABAA  |+----------------------------+--------+-------+-------+----------------------------+-------+-------+-------+

插图

事件A和B将被UNIOn ALL的上部捕获。
事件B和C将被UNIOn ALL的下部捕获。

    0        0.002    0.004    0.006    0.008    0.01          |        |        |        |        |        |-------------------------------------------------------|        ||        |    A  B  C     |        |     |        |-------------------------------------------------------         |        |        |        |        |   0.001    0.003    0.005    0.007    0.009