[mysql] join su data più prossima [era: query complessa... almeno per me]

[guidino]

Ciao a tutti,
ho bisogno del vostro aiuto su una query.
Ho due tabelle, in una memorizzo la tariffa oraria degli utenti (tariffa che può cambiare come indicato nell'esempio) e in un altra memorizzo le ore di lavoro svolto.

tariffe
id_tariffa, id_utente, data, importo_orario
1 2 2010-01-10 30
2 2 2010-01-12 35
3 2 2010-02-15 40

Lavori
id_lavoro, id_utente, ore, data
1 2 3 2010-01-09
2 2 2 2010-01-13
3 2 5 2010-01-14
4 2 3 2010-01-16

Quello che voglio ottenere è di agganciare ad ogni record di Lavori la tariffa oraria della data corrispondente al lavoro svolto, se esiste, altrimenti la tariffa oraria della data più vicina.
Così nell'esempio per il lavoro della prima riga svolto in data 2010-01-09 vorrei agganciare la tariffa corrispondente alla data più vicina cioè quella del 2010-01-10 (importo 30).

E' possibile?

Ho provato con questa query ma sono fuori strada:
SELECT lav.ore, tar.importo, lav.data from lavori as lav left join tariffe as tar on (lav.id_utente=tar.id_utente) where lav.id_utente = 2 and lav.data <=tar.data

Qualcuno sa aiutarmi?


Grazie

[nicola75ss]

Vedi se così risolvi

select lav.*,tar.*
from lavori as lav 
left join (
select * from tariffe order by data asc ) tar
on lav.id_utente=tar.id_utente and lav.id_utente = 2 
and lav.data <= tar.data
group by id_lavoro

[guidino]

Grazie nicola75ss,
mi sembra che funzioni a dovere.

[luca200]

Io avrei seri dubbi invece :master:

Per prima cosa, trovo alquanto bizzarro il principio per cui prima si fa il lavoro e poi si decide il prezzo. Il concetto di "data più vicina" è un po' nebuloso, ma se non sbaglio la query di nicola prende solo le tariffe successive all data del lavoro, il che non corrisponde necessariamente alla data più vicina.

Un'altra cosa che mi lascia assai perplesso è il "trucco" usato per estrapolare una sola riga per ogni lavoro dalla join fra le due tabelle. Si sfrutta un comportamento non standard di mysql (ammettere il raggruppamento in presenza di valori scalari), dando per scontato che il valore scalare restituito sarà quello della "prima" riga, stabilita in base ad un ordinamento fatto prima della join. Ammesso che abbia capito lo "spirito" della query, mi sembra esageratamente azzardato confidare su tutti questi comportamenti al di fuori dello standard sql. Il fatto che funzionino su una base dati di tre righe non garantisce che sarebbe lo stesso su un database serio.

[guidino]

Grazie Luca,
Il funzionamento però non l'ho deciso io; io devo far funzionare la query.
la query di nicola prende la tariffa successiva alla data del lavoro se non trova una tariffa per la data in cui il lavoro è stato svolto ma se trova una tariffa per la data del lavoro svolto prende quella, almeno dalle prove così mi sembra perchè usa la condizione lav.data <= tar.data
E questo è quello che volevo ottenere.

Per quanto riguarda gli standard sql cui ti riferisci chiederei anche il tuo contributo, fermo restando il principio di funzionamento per il motivo sopra spigato.
Quali correzioni faresti alla query?

Se funziona su una tabella di 3 righe perchè non dovrebbe funzionare su una tabella di 10.000?

Grazie

[luca200]

Se il lavoro ha data 10 gennaio, e in tabella c'è una tariffa per il 9 gennaio e una per il 20 giugno, è giusto prendere quella del 20 giugno?

[guidino]

Si, potrei assumere che è corretto prendere la tariffa corrispondente alla data del lavoro se esiste, o in alternativa quella successiva. Basta che si sappia come funziona la query.

Riferendoci all'esempio che tu hai fatto, come andrebbe modificata la query per prendere la tariffa del 9 gennaio al posto di quella del 20 giugno?

E' possibile con una sola query riferirisi alla tariffa che dia lo scarto di giorni minore rispetto alla data del lavoro?
Ragionando con la logica tutto fila, ma tradurlo in sql mi sembra molto difficile.
Se tu potessi farlo sarebbe meraviglioso.

Grazie

[nicola75ss]

Originariamente inviato da luca200
Ammesso che abbia capito lo "spirito" della query, mi sembra esageratamente azzardato confidare su tutti questi comportamenti al di fuori dello standard sql. Il fatto che funzionino su una base dati di tre righe non garantisce che sarebbe lo stesso su un database serio.

In effetti l'ho testata su pochissime righe di prova. Non garantirei minimamente sul corretto funzionamento in tutti i casi.

[guidino]

Faccio un pò di test e vi faccio sapere.

In linea teorica è possibile con una sola query riferirisi alla tariffa che dia lo scarto di giorni minore rispetto alla data del lavoro?

Grazie

[luca200]

Ci vuole una query un po' complicata, adesso non ho tempo di mettermi a farla.
Magari stasera