2

My problem is for Postgresql 10 but it probably is also relevant for other DB systems.

I have several tables in which I need to identify groups based on several criteria (some of them are PostGIS-geospatial criteria, some other are shared values in certain columns, ... that don't matter).

I am going to take a simple example. Let's say I have I have first table with people:

CREATE TABLE employees
(
id serial PRIMARY KEY,
name varchar(255),
desk_number varchar(20)
)

and that I want to group all employees that share the same desk (field desk_number). Illustration :

employees
--------------
id | name | desk_number
1  | Bill | 314
2  | Joe  | 200
3  | Bob  | 314
4  | Matt | 189
5  | Sam  | 314
6  | Anne | 150

In this example, what I want to have is the list of employees sharing desk number 314 : Bill, Bob and Sam (id: 1, 3, 5).

My current query for finding this is :

WITH findpairs AS
(   -- part 1 of the query : selecting pairs
    SELECT s1.id AS id1, s2.id AS id2
    FROM employees AS s1 
    JOIN employees AS s2
    ON s1.desk_number = s2.desk_number
    WHERE s1.id < s2.id
)
-- part2 of the query : filtering
SELECT s1.id1
FROM findpairs AS s1
LEFT JOIN findpairs AS s2
ON s1.id1 = s2.id2
WHERE s2.id2 IS NULL
;

Explanations : In the first part of the query, I find all pairs of rows that for employees that share the same desk.

Without the WHERE clause, I would obtain as a result:

id1 | id2
 1  | 3
 3  | 1
 1  | 5
 5  | 1
 1  | 1
 2  | 2
 3  | 3
 4  | 4
 5  | 5
 6  | 6

The WHERE clause "s1.id < s2.id" prevents returning matches for a row with itself and makes sure that for two rows matching each other, only one row will be returned.

With this WHERE clause, what the part 1 of the query returns is :

id1 | id2
 1  | 3
 1  | 5
 3  | 5

I only two pairs of rows (1-3 and 1-5) to define the group I need ; the row 3-5 is unnecessary. That is why the second part of the query also does some filtering : because of the previous "s1.id < s2.id" clause, I know the lowest of the id values is only present in s1.id and never in s2.id. By using a LEFT JOIN, I can identify these rows from the set returned by part 1 and discard the other ones.

The final result returned is :

id1 | id2
 1  | 3
 1  | 5

And I can store this as such in a specific table :

CREATE TABLE matched_employees
(  id1 integer, id2 integer )

Question 1 : is there a less tedious way to do this ? This takes a hell of a time to run on my server with my dataset.

Question 2 : instead to store the result in a table with pairs ( matched_employees ). Is there a better way to store this information (and easily access it later) ?

  • 1
    If you do not need pairs, you can obtain array of ids via SELECT array_agg(id) FROM employees GROUP BY desk_number; and store this array. Also you can make pairs from array by getting (1st element, n-th element) pairs for n >= 2. – Evgeny Nozdrev Aug 21 '18 at 13:16
  • @ЕвгенийНоздрев Write that up as an answer! It answers both questions in one fell swoop. NB Add order by inside the array_agg so that the result is determinate. – Colin 't Hart Aug 21 '18 at 13:22
  • Ok, then, this works when the JOIN clause is a simple equality. What about when it's more complex, such as when it is using a PostGIS function ? And another issue : how do I not keep the rows that don't match others ? – Darth Kangooroo Aug 21 '18 at 13:33
  • 1
    It should work, as long as you can put something in the group by clause that matches for rows that should be grouped together. You can add having count(*) > 1 to the end of the query to keep only rows that match others. – Colin 't Hart Aug 21 '18 at 13:42
  • The limit will be that GROUP BY is based on having values that can be compared. You can do GROUP BY (field_name) or GROUP BY (whatever formula that returns a numeric or string value), but you cannot do GROUP BY with a test that returns TRUE or FALSE. For example, with PostGIS, if you wanna group vector geometries that intersect each other, you will use the function ST_Intersect(geometry_field1, geometry_field2) that returns a boolean. – Darth Kangooroo Aug 22 '18 at 8:33
2

If you don't need real "pairs" you can get the list of employees sharing a desk using this:

select e1.*
from employees e1
where exists (select *
              from employees e2
              where e1.desk_number = e2.desk_number
                and e1.id <> e2.id);

If you want that as a single "list of IDs" you can use string_agg()

select string_agg(e1.id::text, ',') as id_list
from employees e1
where exists (select *
              from employees e2
              where e1.desk_number = e2.desk_number
                and e1.id <> e2.id);

Or use array_agg() to get those IDs as an array

Online example: http://rextester.com/AXVGA22292

  • Interesting. But your example has a flaw (compared to what I want) : It returns all rows without matching them together. – Darth Kangooroo Aug 22 '18 at 8:29
  • @DarthKangooroo: that's why I wrote "If you don't need real pairs". – a_horse_with_no_name Aug 22 '18 at 8:41
0

Partial answer, based on comments from Colin 't Hart and Евгений Ноздрев

If the criterion for grouping rows is a field or a formula that will return a field-like value (numeric, text), it is possible to use array_agg :

SELECT * FROM
(
    SELECT desk_number, array_agg (id) AS liste_ids FROM employees
    GROUP BY desk_number
) AS groups
WHERE array_length (liste_ids,1)>1

Or :

SELECT desk_number, array_agg (id) AS liste_ids FROM employees
GROUP BY desk_number
HAVING COUNT(1) > 1

Limitations :

  • Does not work for grouping rows based on a boolean test (like PostGIS ST_Intersect or ST_DWithin functions).
  • Does not select one of the items as being the 'master' row and the others duplicate like the code I originally proposed does.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.