Geek Answers Handbook

I have 3 columns x, y and type. I would like to know if there are at least 3 connections of the same type in a grid using SQL

I have a game table, for example:

CREATE TABLE game_piece( x Integer, y Integer, type Integer );

Each (x, y) can have only 1 part. Grid view (numbers are types):

 1235 1134 9678

By connecting, I mean that they should be directly next to the source in vertical or horizontal order, for example:

  CC=connected COC O=origin C

I would like to check if there are 3 pieces connected anywhere in the grid, without having to get the entire database grid and do it in python if there is a decent solution. Suggestions?

+4
source share
5 answers

To clarify my comments on Xophmeister, like this:

 SELECT ox, oy FROM game_piece o JOIN game_piece p ON p.type = o.type AND ( (ox = px AND py IN (oy-1,o.y+1)) OR (oy = py AND px IN (ox-1,o.x+1)) ) GROUP BY ox, oy HAVING COUNT(*) > 1

And here it works on your test data: http://sqlfiddle.com/#!3/0bd34/1

Edit:. Since you only want to know if this condition exists, the best way to do this is to simply drag LIMIT 1 to the end and see if the query returns a result or not. For some reason, sqlfiddle I don't like when I put LIMIT there, but I tested it on my server and it works fine.

+3
source

By "connected," I assume that you mean the adjacent: that is, (5,3,1234) and (4,3,1234) will be connected.

Thus, what you can do is double connect the table to yourself, where each connection depends on the one that preceded it, and the conditions include:

 on nextPiece.type = lastPiece.type and (nextPiece.x in (lastPiece.x - 1, lastPiece.x + 1) or nextPiece.y in (lastPiece.x - 1, lastPiece.x + 1))

Note that this does not consider diagonals adjacent.

The problem with this method is that it will return duplicates: if record A is connected to record B, then both A and B will be displayed in the result set. When you join twice, you will see three duplicates ... You can do select distinct , if all you are interested in is a coincidence, but the query will not be particularly fast at all anyway (depending on how big your grid is and how little it is full).

EDIT See Braiba's solution (and comments below): I made a mistake: P

+1
source

Depending on what you mean by connection, you do not need to reset the entire dB, but only 2 parts in 4 directions.

 select x, y, from game_piece where ( (x between origin_x - 2 AND origin_x + 2 AND y = origin_y) OR (y between origin_y - 2 AND origin_y + 2 AND x = origin_x) ) AND type = the_type;

origin_x , origin_y are the coordinates of the part you want to check.

This will drop 1 to 8 pieces that you will need to check.

If the game table is very large, you should add an index to the x and y column, otherwise it may not be useful.

Hope this helps.

M.

0
source

This will return the number of different types of connections:

 select count(distinct type) as connections from game_piece where ((y = $y and x between $x - 1 and $x + 1) or (x = $x and y between $y - 1 and $y + 1)) and (x != $x or y != $y) -- exclude the origin itself
0
source

You can use this solution:

 SELECT 1 FROM game_piece WHERE (x = $o_x AND y IN ($o_y + 1, $o_y - 1)) OR (y = $o_y AND x IN ($o_x + 1, $o_x - 1)) GROUP BY type HAVING COUNT(1) = 3

$o_x and $o_y are the original input parameters originX and origin.

If there are exactly 3 parts of the same type associated with the beginning (vertically or horizontally), this will return 1 , otherwise it will return an empty result set.

Edit:

What can you try to find out if there are any fragments in the grid that have 2 or more identical adjacent types:

 SELECT COUNT(1) > 0 AS doesExist FROM ( SELECT 1 FROM game_piece p INNER JOIN game_piece o ON p.type = o.type AND ( (px = ox AND py IN (oy + 1, oy - 1)) OR (py = oy AND px IN (ox + 1, ox - 1)) ) GROUP BY p.type, ox, oy HAVING COUNT(1) > 1 ) a

Which will return 1 if there is one or more parts and 0 if not.

0
source

More articles:


All Articles