这将是一个幼稚的解决方案：
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
