Matching algorithm in SQL

Question

I have the following table in my database.

# select * FROM matches;
 name | prop |  rank
------+------+-------
 carl |    1 |     4
 carl |    2 |     3
 carl |    3 |     9
 alex |    1 |     8
 alex |    2 |     5
 alex |    3 |     6
 alex |    3 |     8
 alex |    2 |    11
 anna |    3 |     8
 anna |    3 |    13
 anna |    2 |    14
(11 rows)

Each person is ranked at work by different properties/criterias called 'prop' and the performance is called 'rank'. The table contains multiple values of (name, prop) as the example shows. I want to get the best candidate following from some requirements. E.g. I need a candidate that have (prop=1 AND rank > 5) and (prop=3 AND rank >= 8). Then we must be able to sort the candidates by their rankings to get the best candidate.

EDIT: Each person must fulfill ALL requirements

How can I do this in SQL?

That's really elementary. Search for "SELECT WHERE ORDER BY sql example" — Enno Shioji, Jun 18 '14 at 12:59
You should consider data normalization. If i understand you correctly, you need a standard self-join on these two conditions followed by an order-by statement. — OnWhenReady, Jun 18 '14 at 13:02
What do you want to get, if a candidate has multiple `rank` for a selected `prop`? More specifically, if *some* of their ranks passes your condition **and** *some* of it doesn't. — pozs, Jun 18 '14 at 13:05
@EnnoShioji: That's a misunderstanding. Relational division is not elementary at all. — Erwin Brandstetter, Jun 18 '14 at 17:34

Lennart - Slava Ukraini · Accepted Answer · 2014-06-18T14:05:55.700

3

select x.name, max(x.rank) 
from matches x
join (
    select name from matches where prop = 1 AND rank > 5
    intersect
    select name from matches where prop = 3 AND rank >= 8
) y
    on x.name = y.name 
group by x.name
order by max(rank);

edited Jun 18 '14 at 14:05

answered Jun 18 '14 at 13:03

Lennart - Slava Ukraini

6,936
1
20
32

`max(x.rank)` fails, because, most rank values would come from `y` - where you forgot to include `rank` ... – Erwin Brandstetter Jun 18 '14 at 13:56
@Erwin, not sure I understand your comment, Y is a derived table consisting of those names who full-fills both conditions. Rank cannot be part of y since that would mean that the rank must be the same in both cases. x on the other hand contains all ranks for those users. Can you give an example when the query would fail? – Lennart - Slava Ukraini Jun 18 '14 at 14:09
Ah, I see. My thinko. You are joining back to `matches` and retrieve *all* rows for each qualifying name. So my remark is wrong. – Erwin Brandstetter Jun 18 '14 at 14:12
There was a typo in the query before, I had no tables in y so that might have messed things up, sorry about that. – Lennart - Slava Ukraini Jun 18 '14 at 14:13

score 2 · Answer 2 · answered Jun 18 '14 at 13:18

Filtering the data to match your criteria here is quite simple (as shown by both Amir and sternze):

SELECT *
FROM matches
WHERE prop=1 AND rank>5) OR (prop=3 AND rank>=8

The problem is how to aggregate this data so as to have just one row per candidate.

I suggest you do something like this:

SELECT m.name,
   MAX(DeltaRank1) AS MaxDeltaRank1,
   MAX(DeltaRank3) AS MaxDeltaRank3
FROM (
  SELECT name,
    (CASE WHEN prop=1 THEN rank-6 ELSE 0 END) AS DeltaRank1,
    (CASE WHEN prop=3 THEN rank-8 ELSE 0 END) AS DeltaRank3,
  FROM matches
) m
GROUP BY m.name
HAVING MaxDeltaRank1>0 AND MaxDeltaRank3>0
SORT BY MaxDeltaRank1+MaxDeltaRank3 DESC;

This will order the candidates by the sum of how much they exceeded the target rank in prop1 and prop3. You could use different logic to indicate which is best though.

In the case above, this should be the result:

 name | MaxDeltaRank1 | MaxDeltaRank3
------+---------------+--------------
 alex |       3       |       0

... because neither anna nor carl reach both the required ranks.

score 1 · Answer 3 · edited May 23 '17 at 11:57

A typical case of relational division. We assembled a whole arsenal of techniques under this related question:
How to filter SQL results in a has-many-through relation

Assuming you want the minimum rank of a person, I might solve your particular case with LEAST():

SELECT m1.name, LEAST(m1.rank, m2.rank, ...) AS best_rank
FROM   matches m1
JOIN   matches m2 USING (name)
...
WHERE  m1.prop = 1 AND m1.rank >  5 
AND    m2.prop = 3 AND m2.rank >= 8
...
ORDER  BY best_rank;

Also assuming name to be unique per individual person. You'd probably use some kind of foreign key to a pk column of a person table in reality.
And if you have such a person table like you should, the best rank would be stored in a column there ...

score -1 · Answer 4 · answered Jun 18 '14 at 12:59

If I understand you question, then you just need to execute the following operation:

SELECT * FROM matches where (prop = 1 AND rank > 5) OR (prop = 3 AND rank >= 8) ORDER BY rank

It gives you the canidates that either have prop=1 and rank > 5 or prop=3 and rank >= 8 sorted by their rankings.

Matching algorithm in SQL

4 Answers4

Linked

Related