J'ai donc ces 2 tableaux:

Travaux:

-------------------------------------------------
id        business_id        other_columns
-------------------------------------------------
1         223                 xxxxxx
-------------------------------------------------
1         12                  xxxxxx
-------------------------------------------------

Business_ratings:

--------------------------------------------------------------------------------------
id   business_id   professional   communication   safety   respectful   dependability        
--------------------------------------------------------------------------------------
1    223           4              2               5        4            3      
--------------------------------------------------------------------------------------
2    223           3              5               2        4            5
--------------------------------------------------------------------------------------
3    223           1              2               5        4            4
-------------------------------------------------------------------------------------- 

Je veux sélectionner les emplois d'un business_id particulier et ajouter à chaque emploi la note globale de cet business_id, calculé comme AVG ((AVG (professionnel), AVG (communication), AVG (sécurité), AVG (respectueux), AVG ( fiabilité))

Puis-je réaliser cela en une seule requête?

LE: Je vais ajouter ici la requête que j'ai essayée (contenant également la clause WHERE, cela aidera peut-être à mieux expliquer ce que j'ai besoin de réaliser. Aussi l'erreur générée:

SELECT * FROM jobs 
CROSS JOIN (
    SELECT count(*) totalJobs FROM jobs 
    WHERE (
        ( JSON_CONTAINS(skills, '{"id":1,"val":"Carpenter"}') ) 
        AND  NOT JSON_CONTAINS(workers, '{"id":6,"fullname":"Cip"}') 
        AND NOT JSON_CONTAINS(applicants, '{"id":6,"fullname":"Cip"}')
    )
) ttl
CROSS JOIN (
    SELECT AVG(
        (SELECT AVG(professional) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
        (SELECT AVG(communication) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
        (SELECT AVG(safety) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
        (SELECT AVG(respectful) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
        (SELECT AVG(dependability) FROM businesses_ratings WHERE business_id=jobs.business_id)
    ) business_rating FROM businesses_ratings WHERE business_id=jobs.business_id
) avg
WHERE (
    ( JSON_CONTAINS(skills, '{"id":1,"val":"Carpenter"}') ) 
    AND  NOT JSON_CONTAINS(workers, '{"id":6,"fullname":"Cip"}') 
    AND NOT JSON_CONTAINS(applicants, '{"id":6,"fullname":"Cip"}')
) 
ORDER BY start_date LIMIT 3

Et l'erreur:

Unknown column 'jobs.business_id' in 'where clause'
1
xyboox 3 sept. 2020 à 16:15

2 réponses

Meilleure réponse

Votre requête semble bien plus compliquée que nécessaire. Je pense que tu veux:

select br.business_id,
       avg(professional),
       avg(communication),
       avg(safety),
       avg(respectful),
       avg(dependability),
       (avg(professional) + avg(communication) + avg(safety) + avg(respectful) + avg(dependability)) / 5 as overall_avg
from businesses_ratings br
group by br.business_id;
0
Gordon Linoff 3 sept. 2020 à 13:52

Je pense que vous voulez des fonctions d'agrégation et de fenêtre:

select 
    business_id, 
    rank() over(
        order by avg(professional) + avg(communication) + avg(safety)
    ) as rn
from businesses_ratings
group by business_id

Vous pouvez développer la clause order by de la fonction de fenêtre avec des colonnes supplémentaires si nécessaire.

Je suis assez sceptique quant à l'intérêt du classement par la moyenne des 3 moyennes - mais la requête ci-dessus semble être une interprétation raisonnable de ce que vous demandez.

Dans les versions antérieures, une option utilise des variables définies par l'utilisateur pour calculer le classement:

select t.*, @rn := @rn + 1 rn
from (
    select 
        business_id, 
        avg(professional) + avg(communication) + avg(safety) sum_avg
    from businesses_ratings
    group by business_id
    order by sum_avg
) t
cross join (select @rn := 0) x
1
GMB 3 sept. 2020 à 13:55