Access & C.

Sono stato incuriosito dal seguente post trovato su it.comp.appl.access:

<<Il mio problema è che io devo fare più di una semplice classifica: ho in una tabella un elenco di letture di giochi installati in vari locali. Ogni lettura riporta il totale incassato da quella macchina, così che facendo le differenze tra record di può calcolare l'incassato in un periodo di tempo.

La tabella contiene, tra gli altri, questi campi:

NomeLocale

Scheda (Testo) - nome della macchina. Non univoco;

CodeID (Testo) - identificativo univoco della macchina;

Data (Data/ora) - data con ore:minuti:secondi

CNT (precisione doppia) - contatore del totale incassato al momento della lettura.

Dato un tipo di [Scheda] ed un periodo di tempo prescelto, devo ottenere una classifica che mi dica, quante volte risulta prima per incasso (differenza [cnt(finale) - cnt(iniziale)] ), quante II, quante III e via dicendo, senza limiti di posizione. Cioè in teoria voglio anche sapere quante volte è arrivata cinquantesima.

Se potessi fare , ma non si può) un raggruppamento per [NomeLocale]sulla classifica assoluta (filtrata ovviamente per scheda e periodo), potrei fare un SELECT miaTabella.Posizione, count(miaTabella.posizione) AS NrVolte FROM miaTabella;>>

Provo a dare una risposta perchè il quesito è stimolante.

A) PASSO 1: DATI

Supponiamo di avere una tabella (test_classifica) con i campi essenziali del nostro amico frequentatore del NG e popolata da dati di prova:

CodeID	Data	CNT
1	28/04/07	5
2	28/04/07	16
3	28/04/07	10
4	28/04/07	8
1	29/04/07	6
2	29/04/07	21
3	29/04/07	20
4	29/04/07	25
1	30/04/07	15
2	30/04/07	22
3	30/04/07	27
4	30/04/07	29
1	02/05/07	37
2	03/05/07	27
3	06/05/07	35
4	06/05/07	37

B) Calcolo dei punteggi tra una misura e la successiva

A questo punto calcoliamo il punteggio totalizzato in un intervallo temporale intercorrente tra due date di misurazione e vediamo la classifica. In pratica misuriamo quanti punti ha totalizzato ciascuna scheda dall’ultimo campionamento:

SELECT t1.CodeID, t2.Data, MAX(t2.CNT-t1.CNT) AS massimo

FROM Test_Classifica AS t1 INNER JOIN Test_Classifica AS t2 ON t1.CodeId=t2.CodeId

WHERE t2.data = (SELECT MIN(t3.Data) from test_classifica as t3 WHERE t3.Data > t1.data AND t3.CodeId=t1.CodeId)

GROUP BY t1.CodeID, t2.Data

ORDER BY t2.Data DESC, t1.CodeId DESC , MAX(t2.CNT-t1.CNT) DESC;

CodeID	Data	massimo
4	06/05/07	8
3	06/05/07	8
2	03/05/07	5
1	02/05/07	22
4	30/04/07	4
3	30/04/07	7
2	30/04/07	1
1	30/04/07	9
4	29/04/07	17
3	29/04/07	10
2	29/04/07	5
1	29/04/07	1

La data della query rappresenta la data di campionamento. Ad esempio:

la scheda n° 1 alla data di campionamento del 02/05/2007 ha 9 punti, totalizzati nel periodo tra il 29/04/2007 ed il 30/04/2007, la scheda n°4 alla data del 06/05/2007 ha 8 punti, cioè quelli totalizzati periodo tra il 30/04/2007 ed il 06/05/2007.

Questa è una query fondamentale che si ritroverà in tutte le prossime query.

C) Creiamo la classifica per un certa data prefissata:

Come ben illustrato nel post:
http://groups.google.it/group/it.comp.appl.access/browse_thread/thread/23535e4e6e03b9e0/9858f46cafe88838?lnk=gst&q=c
per creare un classifica alla data del 30/04/2007 si deve eseguire la seguente doppia JOIN

SELECT Y.CodeId, Y.Data, count(Y.CodeId) AS classifica FROM

[SELECT t1.CodeID, t2.Data, MAX(t2.CNT-t1.CNT) AS massimo

FROM Test_Classifica AS t1 INNER JOIN Test_Classifica AS t2 ON t1.CodeId=t2.CodeId

WHERE t2.data = (SELECT MIN(t3.Data) from test_classifica as t3 WHERE t3.Data > t1.data and t3.CodeId=t1.CodeId)

GROUP BY t1.CodeID, t2.Data

ORDER BY MAX(t2.CNT-t1.CNT) DESC, t1.CodeId DESC, t2.Data DESC]. AS X

INNER JOIN

[SELECT t1.CodeID, t2.Data, MAX(t2.CNT-t1.CNT) AS massimo

FROM Test_Classifica AS t1 INNER JOIN Test_Classifica AS t2 ON t1.CodeId=t2.CodeId

WHERE t2.data = (SELECT MIN(t4.Data) from test_classifica as t4 WHERE t4.Data > t1.data and t4.CodeId=t1.CodeId)

GROUP BY t1.CodeID, t2.Data

ORDER BY MAX(t2.CNT-t1.CNT) DESC, t1.CodeId DESC, t2.Data DESC]. AS Y

ON (X.massimo >= Y.massimo) AND (X.Data= Y.Data)

WHERE X.Data = Format('30/04/2007', 'dd/mm/yyyy')GROUP BY Y.CodeID, Y.Data

ORDER BY Y.Data, count(Y.CodeId);

Il cui risultato è il seguente

CodeId	Expr1001
1	1
2	4
3	2
4	3

D) Rendiamo indipendente la query dalla data

Per rendere indipendente la precedente query dalla data, basta aggiungere una banale query annidata nella clausola WHERE evidenziata in blu.

SELECT Y.CodeId, Y.Data, count(Y.CodeId) AS classifica

FROM

[SELECT t1.CodeID, t2.Data, MAX(t2.CNT-t1.CNT) AS massimo

FROM Test_Classifica AS t1 INNER JOIN Test_Classifica AS t2 ON t1.CodeId=t2.CodeId

WHERE t2.data = (SELECT MIN(t3.Data) from test_classifica as t3 WHERE t3.Data > t1.data and t3.CodeId=t1.CodeId)

GROUP BY t1.CodeID, t2.Data

ORDER BY MAX(t2.CNT-t1.CNT) DESC, t1.CodeId DESC, t2.Data DESC]. AS X

INNER JOIN

[SELECT t1.CodeID, t2.Data, MAX(t2.CNT-t1.CNT) AS massimo

FROM Test_Classifica AS t1 INNER JOIN Test_Classifica AS t2 ON t1.CodeId=t2.CodeId

WHERE t2.data = (SELECT MIN(t4.Data) from test_classifica as t4 WHERE t4.Data > t1.data and t4.CodeId=t1.CodeId)

GROUP BY t1.CodeID, t2.Data

ORDER BY MAX(t2.CNT-t1.CNT) DESC, t1.CodeId DESC, t2.Data DESC]. AS Y

ON (X.massimo >= Y.massimo) AND (X.Data= Y.Data)

WHERE X.Data IN (SELECT DISTINCT Data FROM Test_Classifica ORDER BY Data DESC)

ORDER BY Y.Data, count(Y.CodeId);

Ed il risultato è il seguente

CodeId	Data	classifica
4	29/04/07	1
3	29/04/07	2
2	29/04/07	3
1	29/04/07	4
1	30/04/07	1
3	30/04/07	2
4	30/04/07	3
2	30/04/07	4
1	02/05/07	1
2	03/05/07	1
4	06/05/07	2
3	06/05/07	2

Nei giorni 02/05/2007 e 03/05/2007 risultano solo le rilevazioni delle schede 1 e 2 che, quindi, risultano prime!

Nel giorno 06/04/2007 vi sono due pari merito.

Suggerimenti per miglioramenti?