Meilleure offre parmi plusieurs propositions

[luboman]

Bonjour.

Je m'excuse d'avance de vous solliciter pour un problème qui, bien que je sois persuadé qu'il est simple à résoudre, me pose bien des problèmes.
J'ai une table qui contient des propositions de contrats d'employeurs à des postulants.
j'ai une colonne id_entreprise, id_candidat, type de contrat (1 = CDI ou 2 = CDD principalement) et salaire annuel.
Les candidats peuvent recevoir plusieurs propositions. Les candidats acceptent en priorité les CDI puis le meilleur salaire proposé.
Je dois retourner, pour chaque candidat qui a reçu au moins une proposition, la meilleure.
Pourriez-vous m'aider ?
Merci.

[al1_24]

Quelque chose comme ça ?

Code :

1
2
3
4
5
6
7
8
9
SELECT  *
FROM    matable AS tbl
WHERE   EXISTS
        (   SELECT  1
            FROM    matable AS sel
            WHERE   tbl.id_candidat  = sel.id_candidat
            HAVING  tbl.type_contrat = MIN(sel.type_contrat)
                AND tbl.salaire      = MAX(sel.salaire)
        )

[StringBuilder]

Je suis pas sûr que ta requête marche :

Si il y a une offre en CDI (1) de 2000 €/mois et un CDD (2) de 3000 €/mois alors tu vas tenter de trouver une ligne avec min(contrat_type) = 1 et max(salaire) = 3000 alors qu'il n'existe pas de telle ligne.

[StringBuilder]

Ma requête (en gras) fonctionne avec ce jeu de test sous SQL Server.

En revanche, elle doit pouvoir être améliorée avec des fonctions analytiques.

Je confirme que celle de al1_24 ne fonctionne pas.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
create table proposition
(
	id_candidat int,
	id_entreprise int,
	t_contrat int,
	salaire decimal(8,2)
);

-- Jeu de test "avec différents cas qui vont bien"
insert into proposition (id_candidat, id_entreprise, t_contrat, salaire) values (1, 1, 1, 2000);
insert into proposition (id_candidat, id_entreprise, t_contrat, salaire) values (1, 2, 2, 3000);
insert into proposition (id_candidat, id_entreprise, t_contrat, salaire) values (1, 3, 1, 2500);
insert into proposition (id_candidat, id_entreprise, t_contrat, salaire) values (2, 5, 1, 2000);
insert into proposition (id_candidat, id_entreprise, t_contrat, salaire) values (2, 4, 2, 2500);
insert into proposition (id_candidat, id_entreprise, t_contrat, salaire) values (3, 1, 2, 2500);
insert into proposition (id_candidat, id_entreprise, t_contrat, salaire) values (3, 3, 2, 3500);

select p1.id_candidat, p1.id_entreprise
from proposition p1
inner join (
	select p2.id_candidat, MAX(p2.salaire) salaire
	from proposition p2
	inner join (
		select p3.id_candidat, MIN(p3.t_contrat) t_contrat
		from proposition p3
		group by p3.id_candidat
	) t2 on t2.id_candidat = p2.id_candidat and t2.t_contrat = p2.t_contrat
	group by p2.id_candidat
) t1 on t1.id_candidat = p1.id_candidat and t1.salaire = p1.salaire;
-- Retourne les 3 donnes propositions

SELECT  *
FROM    proposition AS tbl
WHERE   EXISTS
        (   SELECT  1
            FROM    proposition AS sel
            WHERE   tbl.id_candidat  = sel.id_candidat
            HAVING  tbl.t_contrat = MIN(sel.t_contrat)
                AND tbl.salaire      = MAX(sel.salaire)
        );
-- Ne retourne qu'une seule ligne qui ne correspond pas à ce qu'on cherche

drop table proposition;

PS : Ceci dit, j'ai l'impression que ma requête a un trou dans la raquette aussi... J'arrive pas à filtrer à la fois sur le contrat et le salaire... je pense qu'il manque un join ou un truc du genre. Mais ça fait mal à la tête les sous-requêtes dès le matin

[CinePhil]

Essaie ceci :

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT t1.id_candidat, t1.id_entreprise, t1.type_contrat, t1.salaire
FROM la_table t1
INNER JOIN
(
    SELECT id_candidat,
        MAX(((100000 / type_contrat) + salaire)) AS poids
    FROM la_table
    GROUP BY id_candidat
) t2 
    ON t2.id_candidat = t1.id_candidat
    AND t2.poids = ((100000 / t1.type_contrat) + t1.salaire)

Cette requête suppose que :
- le critère type de contrat prime sur le salaire ;
- l'identifiant du type de contrat soit un entier ;
- que ces identifiants classent dans l'ordre de préférence les différents types de contrats (1 : CDI, 2 : CDD, 3 : Freelance, 4 : Stage rémunéré, 5 : Stage non rémunéré).

[al1_24]

Je me suis sans doute un peu précipité pour répondre

Deux réponses plus correctes, je pense :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT  *
FROM    matable AS tbl
WHERE   EXISTS
        (   SELECT  1
            FROM    matable AS sl1
            WHERE   tbl.id_candidat  = sl1.id_candidat
            HAVING  tbl.salaire      = MAX(sl1.salaire)
            WHERE   EXISTS
                    (   SELECT  1
                        FROM    matable AS sl2
                        WHERE   sl1.id_candidat  = sl2.id_candidat
                        HAVING  sl1.type_contrat = MIN(sl2.type_contrat)
                    )
        )
;

Avec fonction analytique :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT  tbl.*
FROM    matable AS tbl
    INNER JOIN
        (   SELECT  RANK()  OVER (GROUP BY id_candidat ORDER BY type_contrat, salaire DESC) rng
                ,   type_contrat
                ,   salaire
            FROM    matable
        )   AS sel
        ON  tbl.id_candidat  = sel.id_candidat
        AND tbl.type_contrat = sel.type_contrat
        AND tbl.salaire      = sel.salaire
    WHERE   sel.rng = 1
;

Dans les deux cas, on retourne la meilleure offre (salaire le plus élevé pour le type de contrat du code le plus faible).

Les tenants d'un SQL hors norme trouveront aussi une réponse avec LIMIT.

[CinePhil]

Al1_24, l'utilisation du HAVING sans GROUP BY dans la première requête me choque !
Ça fonctionne ça ?

Les tenants d'un SQL hors norme trouveront aussi une réponse avec LIMIT.

Et ma solution consistant à calculer un poids pour chaque offre et à en ressortir le MAX pour chaque candidat, elle fonctionne aussi non ?

[al1_24]

Le GROUP BY id_candidat dans la première requête est implicite puisque la sélection porte à chaque fois sur un seul candidat.

Quant à ta requête, si elle est correcte sur le principe, l'usage de la constante 100000 pour pondérer le salaire en fonction du type de contrat sera peut-être à valider en fonction des éléments de salaire pris en compte (mensuel, annuel, devise, ...).
De plus, il reste à en extraire LA meilleure offre, qui est la demande exprimée par luboman

[CinePhil]

Envoyé par al1_24

De plus, il reste à en extraire LA meilleure offre, qui est la demande exprimée par luboman

Ben il y a un MAX dans ma requête !

Je dois retourner, pour chaque candidat qui a reçu au moins une proposition, la meilleure.

Pour chaque candidat, j'extrais le poids MAX.

[al1_24]

C'est vraiment pas mon jour

En effet, tu sélectionnes LA meilleure en fonction des critères utilisés.

[luboman]

Merci tout le monde !
J'ai essayé d'appliquer la proposition de CinePhil et pour autant que je puisse en juger, j'ai bien le résultat obtenu.
Finalement, c'était assez compliqué non ? Est-ce un défaut d'élaboration de la base ?