[MySql] - Ricerca di un valore associato al massimo di un'altra colonna

[unomichisiada]

Salve, dopo secoli di assenza torno a postare sul forum di html.it, e lo faccio per chiedere un aiuto. Espongo il più chiaramente possibile il mio problema.

Supponete di avere una tabella costituita dalle colonne

id,a,b,c,d,date,p,q,r,s 

in cui id è un identificativo univoco autoincrementale che ci interessa poco, a,b,c e d sono dei campi che chiameremo "di filtro" ed insieme al campo date costituiscono la chiave unica della tabella, mentre i campi p,q,r e s sono dei campi dato.
Detto in altri termini, se trascuriamo l'id, ogni riga è identificata univocamente da una combinazione dei valori a,b,c,d, più un valore per la data. Due righe possono avere gli stessi valori per questi quattro campi solo se sono riferiti a giorni diversi. I quattro campi da p in poi memorizzano dei valori, calcolati in un certo modo (che non ci interessa), relativi a quella particolare combinazione di a,b,c,d in quel particolare giorno indicato dal campo date.

Per fare un esempio

un istanza della tabella potrebbe essere

1  |3|5|7|2|2006-12-13|25|678|510|0
2  |3|5|7|2|2006-12-14|324|37|76|2
3  |3|5|7|2|2006-12-15|57|24|6|0
4  |5|5|7|2|2006-12-13|25|37|6765|0
5  |5|5|7|2|2006-12-14|435|37|510|767
6  |3|6|9|2|2006-12-13|34|4|9|4
7  |3|6|9|2|2006-12-14|25|457|4|0
8  |3|6|9|2|2006-12-15|0|0|67|2
9  |3|6|9|2|2006-12-16|233|7|510|0
10|3|6|9|2|2006-12-17|1|37|67|0 

Notate come le prime tre righe sono caratterizzate dallo stesso valore del filtro ma memorizzano valori diversi per tre giorni consecutivi diversi.Lo stesso si può dire per le righe 4 e 5 e per le righe dalla 6 in poi.
Se ora volessimo ottenere una tabella "mensile" calcolata da questa giornaliera aggregandone in qualche maniera (ad esempio con una somma) i valori dato p,q,r,s, dovremmo semplicemente scrivere per ogni mese una query del tipo:

SELECT a,b,c,d, sum(p),sum(q),sum(r),sum(s)
from my_table
where date >= giorno_inizio_mese and date <= giorno_fine_mese
group by a,b,c,d 

ottenendo un unico valore aggregato da tutte le righe con lo stesso valore del filtro e date comprese nel mese considerato.
Fin qui tutto semplice perchè per aggregare i valori possiamo usare uno degli operatori sum, avg ,max eccetera. Supponete però che qualcuno vi chieda di calcolare un aggregazione in questo modo:
per p,q,s calcolare la somma ma per r calcolare il valore corrispondente alla data più recente. Riportandoci all'esempio sopra e supponendo di voler calcolare i valori aggregati relativi al mese di dicembre 2006, la query risolutiva dovrebbe produrre i seguenti record (gli asterischi servono solo a indicare meglio la separazione tra campi identificativi della riga e campi dato):

1  |3|5|7|2|***|406|739|6|2
2  |5|5|7|2|***|460|74|510|767
3  |3|6|9|2|***|293|505|67|6 

Si vede subito che il quart'ultimo terz'ultimo e ultimo valore della prima riga sono stati ottenuti come somma dei valori corrispondenti delle prime tre righe nella giornaliera mentre il penultimo valore è quello corrispondente alla riga con la data più alta tra le prime tre della giornaliera. Con lo stesso metodo si sono trovate le altre due righe sulla base rispettivamente della 4 e 5 riga nella giornaliera e delle righe 6,7,8,9,10 nella giornaliera.

Ora, dato che calcolare assieme (cioè con un un'unica query) i 4 valori è piuttosto difficile (se non impossibile), ho pensato di calcolare con due query separate i 3 valori p,q,s ed il valore r.
Il problema sta proprio nel formulare la query che calcola quest'ultimo.Dovete tenere presente che le due query devono restituire lo stesso numero di record (perchè per ogni combinazione del filtro devo ottenere un'unico valore del campo dato in entrambi i casi, sia che prenda la somma sia che prenda il valore corrispondente alla data massima). Non trascurabile è anche il fatto che la query deve essere efficiente perchè la tabella giornaliera ha in generale un numero n di righe dell'ordine del centinaio di migliaia.

Si accettano suggerimenti, le query che ho elaborato io sono così inefficienti da piantare il dbms per un tempo imprecisato (mi tocca addirittura terminare il client).

[unomichisiada]

up

[unomichisiada]

Una soluzione abbastanza efficiente al problema è la seguente:

SELECT t1.a, t1.b, t1.c, t1.d, t1.data, t1.r
FROM my_table AS t1,
     (SELECT a, b, c, d , MAX(data) AS data
      FROM my_table
      WHERE data >= giorno_inizio_mese AND data <= giorno_fine_mese
      GROUP BY a,b,c,d) as t2
WHERE t1.data >= giorno_inizio_mese AND t1.data <=giorno_fine_mese
and t1.a= t2.a
and t1.b= t2.b
and t1.c= t2.c
and t1.d = t2.d
and t1.data = t2.data 

[enzo845555]

Potresti anche usare questa:

SELECT a, b, c, d, date, SUM(p), SUM(q), SUM(s),
(SELECT r FROM my_table WHERE date =
    (SELECT MAX(date) AS data
    FROM my_table 
    WHERE date >= giorno_inizio_mese AND date <= giorno_fine_mese)
) AS r
FROM my_table     
WHERE date >= giorno_inizio_mese and date <= giorno_fine_mese 
GROUP BY a,b,c,d 

Non so se sia più efficiente della tua e non ho potuto neanche testarla, quindi non so se funge correttamente...provala e fammi sapere!!

[enzo845555]

Dovrebbe fare sia la somma dei campi p,q,s che trovare il valore r della data più recente!