Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Warning: Long answer. This should work, and is fairly neat, except for one step in the middle where you have to be willing to run an INSERT statement over and over until it doesn't do anything since we can't do recursive CTE things in MySQL.</p> <p>I'm going to use this data as the example instead of yours:</p> <pre><code>id Timestamp 1 1:00:00 2 1:00:03 3 1:00:06 4 1:00:10 </code></pre> <p>Here is the first query to write:</p> <pre><code>SELECT a.id as aid, b.id as bid FROM Table a JOIN Table b ON (a.Timestamp is within 3 seconds of b.Timestamp) </code></pre> <p>It returns:</p> <pre><code>aid bid 1 1 1 2 2 1 2 2 2 3 3 2 3 3 4 4 </code></pre> <p>Let's create a nice table to hold those things that won't allow duplicates:</p> <pre><code>CREATE TABLE Adjacency ( aid INT(11) , bid INT(11) , PRIMARY KEY (aid, bid) --important for later ) </code></pre> <p>Now the challenge is to find something like the <a href="http://en.wikipedia.org/wiki/Transitive_closure" rel="nofollow">transitive closure</a> of that relation. </p> <p>To do so, let's find the next level of links. by that I mean, since we have <code>1 2</code> and <code>2 3</code> in the Adjacency table, we should add <code>1 3</code>:</p> <pre><code>INSERT IGNORE INTO Adjacency(aid,bid) SELECT adj1.aid, adj2.bid FROM Adjacency adj1 JOIN Adjacency adj2 ON (adj1.bid = adj2.aid) </code></pre> <p>This is the non-elegant part: You'll need to run the above INSERT statement over and over until it doesn't add any rows to the table. I don't know if there is a neat way to do that.</p> <p>Once this is over, you will have a transitively-closed relation like this:</p> <pre><code>aid bid 1 1 1 2 1 3 --added 2 1 2 2 2 3 3 1 --added 3 2 3 3 4 4 </code></pre> <p>And now for the punchline:</p> <pre><code>SELECT aid, GROUP_CONCAT( bid ) AS Neighbors FROM Adjacency GROUP BY aid </code></pre> <p>returns:</p> <pre><code>aid Neighbors 1 1,2,3 2 1,2,3 3 1,2,3 4 4 </code></pre> <p>So</p> <pre><code>SELECT DISTINCT Neighbors FROM ( SELECT aid, GROUP_CONCAT( bid ) AS Neighbors FROM Adjacency GROUP BY aid ) Groupings </code></pre> <p>returns</p> <pre><code>Neighbors 1,2,3 4 </code></pre> <p>Whew!</p>
    singulars
    1. This table or related slice is empty.
    1. POMySQL GROUP BY DateTime +/- 3 seconds
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USChris Cunningham
    1. USChris Cunningham
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI have a concern that my Neighbors column might sometimes have `1,2,3` and sometimes `2,1,3`, so maybe there should be an `ORDER BY` in the innermost `GROUP_CONCAT` query to make sure things come out in order.
      singulars
      1. PO
      1. USChris Cunningham
    2. COOne more thing: You don't have to run the INSERT statement infinitely many times... only as many times as the largest-sized group you want to make sure you capture. If you are pretty sure that there are no groups of size 100, you should be able to run it 100 times and it will do nothing most of the time. The problem with very large groups is that the `Adjacency` table will become large since a group of `n` things has on the order of `n^2` adjacency relations.
      singulars
      1. PO
      1. USChris Cunningham
    3. COThanks! I have worked through this solution on my tables, and it does seem to be working nicely.
      singulars
      1. PO
      1. USBrad
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload