improving my ranking and median calculation mysql request?

Question

I've got to achieve mutiple ranking and median calculation on a mysql table. The table looks like:

ps_eur_agreg_lots (id_lot, id_customer, variete, qualite_presentation, defauts_graves)

(In fact, it is an aggregate view from many tables.)

id_customer and variete are used for filtering subsets with a changing where clause (ex: WHERE id_customer=5 and variete=agata, generated from a form) on which I'll rank on qualite_presentation and defauts_graves values (which can be considered as marks), and calculate median values of these marks on my subsets.

Here is the ranking request I use for a value with a where clause:

SELECT id_lot,@curQRank := @curQRank+1 as qrank 
FROM ps_eur_agreg_lots, (SELECT @curQRank :=0) r
WHERE variete='agata'
ORDER BY qualite_presentation DESC, prc_total_def ASC)
as tqrank

This will return a ranking relation (id_lot, qrank), which I'll join with ps_eur_aggr_lots to get a ranking relation (this can be used for multiple criteria).

Here is the request I got for calculating a median value:

SELECT AVG(al.qualite_presentation) as median
FROM ps_eur_agreg_lots al 
JOIN
    (SELECT id_lot,@curQRank := @curQRank+1 as qrank 
 FROM ps_eur_agreg_lots, (SELECT @curQRank :=0) r
 WHERE variete='agata'
 ORDER BY qualite_presentation DESC, prc_total_def ASC)
     as tqrank 
ON al.id_lot=tqrank.id_lot
WHERE tqrank.qrank in (
(SELECT 
round((count(*)+1)/2,0) as qm1ind
FROM 
    (SELECT id_lot,@curQRank := @curQRank+1 as qrank 
    FROM ps_eur_agreg_lots, (SELECT @curQRank :=0) r
    WHERE variete='agata'
    ORDER BY qualite_presentation DESC, prc_total_def ASC
    ) as tqrank) ,

(SELECT 
round((count(*)+2)/2,0) as qm2ind
FROM 
    (SELECT id_lot,@curQRank := @curQRank+1 as qrank 
    FROM ps_eur_agreg_lots, (SELECT @curQRank :=0) r
    WHERE variete='agata'
    ORDER BY qualite_presentation DESC, prc_total_def ASC
    ) as tqrank)
)

This request does the job, but uses tree times the same ranking request, and looks ugly to me.

The idea of this median calculation is taking the mean of two (or one) central values (eaven or odd case). I tried tu use a temporary ranking table, but I get the "can't reopen table" error.

I imagine I could split the request in some intermediate ones, but don't see really satisfying way. Ideas welcome.

Answer 1

Eliminating the 2nd and 3rd rankings, instead using a join against a pair of unioned simple sub queries

SELECT AVG(al.qualite_presentation) as median
FROM ps_eur_agreg_lots al 
JOIN
(
    SELECT id_lot,@curQRank := @curQRank+1 as qrank 
    FROM ps_eur_agreg_lots, (SELECT @curQRank :=0) r
    WHERE variete='agata'
    ORDER BY qualite_presentation DESC, prc_total_def ASC
) as tqrank 
ON al.id_lot=tqrank.id_lot
INNER JOIN 
(
    SELECT round((count(*)+1)/2,0) as qm1ind
    FROM ps_eur_agreg_lots
    WHERE variete='agata'
    UNION
    SELECT round((count(*)+2)/2,0) as qm1ind
    FROM ps_eur_agreg_lots
    WHERE variete='agata'
) tqrank2
ON tqrank.qrank = tqrank2.qm1ind
GROUP BY qualite_presentation