vendredi 10 juin 2016

Create a query that fetches rows plus their adjacent rows


I am having problems with a specific query - respectively creating the query in the first place.
The columns can be reduced to id, seconds and status.

=============================
|  id  | seconds |  status  |
-----------------------------
|   0  |     0   |     0    |
|   1  |    12   |     1    |
|   2  |    25   |     0    |
|   3  |    37   |     1    |
|   4  |    42   |     0    |
=============================

What I'd like to have: All entries with status = 1 PLUS all entries that are less than 10 seconds away from those entries. Basically, I want to fetch all possible pairs (or triplets, etc.) of rows to check manually (later automatically) whether they need to be paired (there is a column parent_id for this purpose, but we don't need that for the query). I could do this in code (first select all status=1, then loop), but I wonder whether it is possible to do this purely in the database.

Thus, my desired output would be the following:

=============================
|  id  | seconds |  status  |
-----------------------------
|   1  |    12   |     1    | <- status = 1
|   3  |    37   |     1    | <- status = 1
|   4  |    42   |     0    | <- only 5 seconds after status = 1
=============================

My current best guess is this:

SELECT * FROM entries e0 
WHERE 
  e0.status = 1 OR 
  e0.status = 0 AND
  0 < (SELECT count(*) 
       FROM entries e1 
       WHERE e1.status = 1 AND abs(e1.seconds - e0.seconds) < 10)

But this fetches the whole table, and I don't really know why - and it takes a long time to do so (there is an index on the column seconds, the table has 9000 entries).

Is there a way to do this (maybe even effiently)?


Aucun commentaire:

Enregistrer un commentaire