CODEWITHSUNDEEP
sqlpostgresql
user2957954
user2957954

Reputation: 1249

Sql: SELECT rows with little time difference

I have a table

event_id eventtypeid eventtimestamp

that describes time events that can be close to each other. I want to find the pairs of events, where eventtypes are equal and the difference between timestamps is no logner than 3 miliseconds.

Is it in generally possible in sql? How can I formulate such a request?

Thank you in advance.

Upvotes: 1

Views: 547

Answers (2)

Patrick
Patrick

Reputation: 32244

Queries like these are generally best solved with a window function:

SELECT eventtypeid, first, second, diff
FROM (
  SELECT eventtypeid, event_id AS first, lead(event_id) OVER w AS second,
         lead(eventtimestamp) OVER w - eventtimestamp AS diff
  FROM event_table
  WINDOW w AS (PARTITION BY eventtypeid ORDER BY eventtimestamp)
) sub
WHERE diff <= interval '3 milliseconds';

This is usually much faster than a self-join.

Upvotes: 5

MatBailie
MatBailie

Reputation: 86735

SELECT
  *
FROM
  event_table   AS first
INNER JOIN
  event_table   AS second
    ON  second.eventtypeid     = first.eventtypeid
    AND second.eventtimestamp >  first.eventtimestamp
    AND second.eventtimestamp <= first.eventtimestamp + INTERVAL '3 milliseconds'

Upvotes: 4

Related Questions