Newsgroup: it.comp.appl.access
Titolo: Una query complicata

Ho 1 tabella (due campi) di 4005 Ambi
e una tabella (6 campi, sestine di enalotto)  di 12851 estrazioni.
 
Debbo fare 4005 * 12851 = 50 386 905 confronti per determinare per
ogni ambo quante occorrenze si sono verificate nelle 12851 estrazioni.
 
Non sono in grado di determinare una query che mi dia tali risultati.
 
Ho provato con due query ma impiego poco meno di 10 minuti, mi sembra
un'esagerazione.
 
Qualche mago in circolazione?
 
Bruno

 
 
questa la ho vista altrove ;-)
 
me lo ero comunque segnato per provarlo con MS SQL Server, quando avrò 
tempo. Ho un'idea in testa da sviluppare per vedere se riduce i tempi, ma 
devo mettermi alla tastiera per provarla
 
-- 
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........


puoi caricare online il db delle estrazioni e degli ambi?
 
BFS

BFS wrote on 11-11-20 :

Eccolo qua:
https://1drv.ms/u/s!AvTaMfd5-b2oz1JLxMA4-h8Is2rl?e=V7asL2
 
 
Bruno

Il 10/11/2020 20:17, Bruno Campanini ha scritto:

7 secondi ti va bene come tempo?
 
BFS

on 11-11-20, BFS supposed :

Ca benissimo!
Rimandami il mio file con la tua procedura.
 
Bruno


sorry avevo interpretato male :-(
ma mi sfugge una cosa
perchè devi ricalcolare tutto ogni volta?
noi fai prima  ad ogni nuova estrazione ad aumentare di +1 tutti i 
possibili ambi di quella singola estrazione nel tuo archivio dove hai 
gia i risultati?
 
 
ad ogni modo su sqlserver ci metto 3 minuti e mezzo per calcolare tutte 
le frequenze
 
 
 
 
 
 
 
 

 frequenze
 
Perché non mi mandi la tus stringa SQL?
Io in Access non riesco a far andare l'EXISTS che dovrebbe essere
la chiave per risolvere il problema cun un'unica query.
 
Bruno


da sql server
mi sono creato una view
 
CREATE VIEW [dbo].[ChiaviRicerca]
AS
SELECT     '-' + CONVERT(nvarchar(2), S1) + '-' + CONVERT(nvarchar(2), 
S2) + '-' + CONVERT(nvarchar(2), S3) + '-' + CONVERT(nvarchar(2), S4) + 
'-' + CONVERT(nvarchar(2), S5) + '-' + CONVERT(nvarchar(2), S6)
                       + '-' AS Chiave
FROM         dbo.Lotto_Sestine
 
GO
 
 
che in pratica mi crea una sorta di stringa di ricerca per ogni estrazione
 
 
esempio se l'estrazione è
 
2 9 56 72 80 90
 
la stringa è
-2-9-56-72-80-90
 
questo mi permette di capire se ho fatto ambo semplicemente facendo una 
select su tale view mettendo come condizione che i due estratti 
appartengano a quella stringa
 
esempio se ho estratto 56 e 90  mi bata cercare le due stringhe -56-  e 
-90- nelle stringhe chiave
in access uso instr, in sql server chardindex
 
 
la mia sql per avere la frequenza degli ambi
 
SELECT lotto_ambi.A1, lotto_ambi.A2, (SELECT Count(chiave) as frequenza
FROM chiaviricerca
WHERE charindex('-' + convert(nvarchar(2),a1) + '-',chiave)>0 and 
charindex('-' + convert(nvarchar(2),a2) +'-',chiave)>0)
FROM lotto_ambi;
 
 
 
3 minuti e mezzo per avere la frequenza di tutti i possibili ambi
 
 
notare che se facessi solo i top 100...esempio per vedere le frequenze 
di tutti gli ambi con il primo estratto = 1
 
SELECT top 100 lotto_ambi.A1, lotto_ambi.A2, (SELECT Count(chiave) as 
frequenza
FROM chiaviricerca
WHERE charindex('-' + convert(nvarchar(2),a1) + '-',chiave)>0 and 
charindex('-' + convert(nvarchar(2),a2) +'-',chiave)>0)
FROM lotto_ambi;
 
ci metto 6 secondi
 
 
secondo me si può migliorare di molto seguendo due strade
una caricare l'archivio estrazioni in una matrice e lavorare su quella, 
per fare i conteggi, sfruttando cosi la velocità della ram
 
seconda lavorare su dati parziali
tipo cercare tutte le estrazioni con il primo estratto, e solo sulle 
righe ottenute cercare quelle con anche il secondo estratto
 
ciao
BFS
 
 
 
 
 
 
 
 
 
 

BFS brought next idea :

Lavorare sulle due matrici Lotto_Sestine e Lotto_Ambi in VBA l'ho
già fatti in Excel e qualcuno, non io, ha ottenuto la frequenza
di tutti i 4005 ambi su 12851 estrazioni in 20 secondi.
 
Io pensavo di utilizzare Access con un'unica query usando la
clausola EXISTS:
 
SELECT Lotto_Sestine.S1, Lotto_Sestine.S2, Lotto_Sestine.S3, 
Lotto_Sestine.S4, Lotto_Sestine.S5
FROM Lotto_Sestine
WHERE (((Exists (SELECT A1,A2 FROM Lotto_Ambi Where S1=A1 AND S2=A2)
OR S1)=TRUE));
 
sostituendo via VBA i valori A1, A2, ma è una lungagna infinita...
 
Non mi sai eseguire la tua procedura da 3 minuti in formato
Access VBA sul mio database?
 
Bruno

 OR S1)=TRUE));
 
Non l'ho precisato ma il WHERE, qui abbreviato per esemplificazione,
comprende 14 OR essendo 15 le combinazioni C6,2.
 
Bruno


su access ci mette 5 minuti
 
crei una vista chiamata chiavi
 
SELECT '-' & [s1] & '-' & [s2] & '-' & [s3] & '-' & [s4] & '-' & [s5] & 
'-' & [s6] & '-' AS Chiave
FROM Lotto_Sestine;
 
 
query creazione tabella con coppia ambi e frequenza
 
SELECT (SELECT Count(chiave) AS Espr1
FROM chiavi
WHERE (((InStr(1,[chiave],'-' & [lotto_ambi].[A1] & '-'))>0) AND 
((InStr(1,[chiave],'-' & [lotto_ambi].[A2] & '-'))>0))) AS freq, 
lotto_ambi.A2, lotto_ambi.A1 INTO Risultati
FROM lotto_ambi;
 
 
se la soluzione tramite matrici in memoria è cosi veloce perchè non 
segui quella strada?
da cosa deriva l'obbligo di usare una query?
 
 
ciao
BFS

 in pratica mi crea una sorta di stringa di ricerca per ogni estrazione
 
...che era poi l'idea grossomodo che avevo in testa :)
 
perņ pensavo anche di generare una tabella monocampo (alfa, 4 caratteri) 
dove creare tutti gli ambi delle 12000 estrazioni per poi contare le 
frequenze
 
prendendo il tuo esempio
 
2 9 56 72 80 90
 
genero 
 
0209
0256
0272
0280
0290
0956
0972
0980
0990
5672
5680
5690
7280
7290
8090
 
metto tutti insieme, ottengo 12.000*15 = 180.000 record
 
quanto impiega?
 
oppure aggiungere un campo longint e incrementare il contatore se l'ambo 
esiste gią


BFS explained :

Sto cercando, ma ormai sono rassegnato, di fare il tutto con una
singola query, senza dover trasformare una tabella 6 x 12851
numerica in una mococampo stringa sempre lunga 12851.
 
Solo curiosità... che il problema non è nemmeno mio.
 
Bruno

 Solo curiosità... che il problema non è nemmeno mio.
 
"Ricerca Ambi 2 Query": 10 minuti
"Ricerca Ambi Matrici"  20 secondi
 
https://1drv.ms/u/s!AvTaMfd5-b2oz1JLxMA4-h8Is2rl?e=jVZttu
 
 
Bruno

Il 10/11/2020 20:17, Bruno Campanini ha scritto:

procederei in questo modo
 
step 1
ordinare la sestina dal n più basso al più alto
 
step2
ricavare gli ambi da ogni sestina n!/(k!(n-k)!)=15 combinazioni
 
uscira una tabella con 12851*15 righe
 
 
ma è semplice: query di aggregazione e per ogni ambo saprai quante volte 
si è ripetuto
 
Akery
 
-- 
Questa e-mail è stata controllata per individuare virus con Avast antivirus.
https://www.avast.com/antivirus
 
 

 Akery
 
Vorrei lo facessi tu sul file che ho inviato (i dati sono già
presenti) c'è solo da ordinare le sestine, etc.
 
Io con una query non ci sono riuscito.
Ce l'ho fatta con 2 query ma il tempo impiegato è 20 minuti.
Con VBA ho risolto in 20 secondi
 
Bruno

Qui 
https://www.dropbox.com/s/cgo5bh6lwb8q3ok/RicercaAmbi.rar?dl=0
 
trovi un esempio

Qui
 
 
https://www.dropbox.com/s/cgo5bh6lwb8q3ok/RicercaAmbi.rar?dl=0
 
 
trovi un esempio che dovrebbe svolgere l'operazione in circa 20 secondi.

 trovi un esempio che dovrebbe svolgere l'operazione in circa 20 secondi.
 
Da me bastano 5 secondi, velocissimo... complimenti.
 
L'idea era già stata suggerita da Akery, che prevedeva l'ordinamento
crescente da sinistra a destra delle sestine.
La condizione completa però è che anche gli ambi siano ordinati
in tal senso (Ackery l'avrà omessa avendo visto dal mio file
che già lo erano).
 
Comunque gli ambi vengono normalmente rappresentati in tale ordine,
le sestine dell'enalotto pure, mentre le cinquine del lotto no:
vengono esposte in ordine di estrazione.
 
Converrebbe pertanto ordinare ambi e sestine/cinquine prima di tutto.
Se provi a invertire un ambo (es. 1-2 --> 2-1) quello non viene
più individuato nelle sestine che si trovano ordinate inversamente.
 
Se tu provi a ordinare il tutto (anche se nella fattispecie cosa
non necessaria), io provo a eliminare quella tabella da 188715
record che contribuisce a portare il file a 3800 kb circa.
 
Bruno


premessa: perdonate le ridondanze ma sono solo un amatore del linguaggio 
non un professionista.
 
qui il file con l'aggiunta della sub che segue
 
https://1drv.ms/u/s!Ant7qlWuw9Z6gbcI6uLXvVbdK_5tmw?e=aLLadd
 
 
spero di aver compreso il problema
 
Option Compare Database
 
Sub comb()
 
 
Dim DBCorrente As DAO.Database
Dim orig, dest As DAO.Recordset
 
 
Set DBCorrente = CurrentDb
 
Set orig = DBCorrente.OpenRecordset("lotto_sestine", dbOpenDynaset)
Set dest = DBCorrente.OpenRecordset("ambiord", dbOpenDynaset)
 
CurrentDb.Execute "DELETE * FROM [ambiord]"
 
 
Do Until orig.EOF
 
     dest.AddNew
     If orig!s1 < orig!s2 Then
 
     dest!A1 = orig!s1
     dest!A2 = orig!s2
     Else
 
     dest!A1 = orig!s2
     dest!A2 = orig!s1
     End If
     dest.Update
 
     dest.AddNew
     If orig!s1 < orig!s3 Then
 
     dest!A1 = orig!s1
     dest!A2 = orig!s3
     Else
 
     dest!A1 = orig!s3
     dest!A2 = orig!s1
     End If
     dest.Update
 
     dest.AddNew
     If orig!s1 < orig!s4 Then
 
     dest!A1 = orig!s1
     dest!A2 = orig!s4
     Else
 
     dest!A1 = orig!s4
     dest!A2 = orig!s1
     End If
     dest.Update
 
     dest.AddNew
     If orig!s1 < orig!s5 Then
 
     dest!A1 = orig!s1
     dest!A2 = orig!s5
     Else
 
     dest!A1 = orig!s5
     dest!A2 = orig!s1
     End If
     dest.Update
 
  dest.AddNew
     If orig!s1 < orig!s6 Then
 
     dest!A1 = orig!s1
     dest!A2 = orig!s6
     Else
 
     dest!A1 = orig!s6
     dest!A2 = orig!s1
     End If
     dest.Update
 
     dest.AddNew
     If orig!s2 < orig!s3 Then
 
     dest!A1 = orig!s2
     dest!A2 = orig!s3
     Else
 
     dest!A1 = orig!s3
     dest!A2 = orig!s2
     End If
     dest.Update
 
     dest.AddNew
     If orig!s2 < orig!s4 Then
 
     dest!A1 = orig!s2
     dest!A2 = orig!s4
     Else
 
     dest!A1 = orig!s4
     dest!A2 = orig!s2
     End If
     dest.Update
 
     dest.AddNew
     If orig!s2 < orig!s5 Then
 
     dest!A1 = orig!s2
     dest!A2 = orig!s5
     Else
 
     dest!A1 = orig!s5
     dest!A2 = orig!s2
     End If
     dest.Update
 
dest.AddNew
     If orig!s2 < orig!s6 Then
 
     dest!A1 = orig!s2
     dest!A2 = orig!s6
     Else
 
     dest!A1 = orig!s6
     dest!A2 = orig!s2
     End If
     dest.Update
 
     dest.AddNew
     If orig!s3 < orig!s4 Then
 
     dest!A1 = orig!s3
     dest!A2 = orig!s4
     Else
 
     dest!A1 = orig!s4
     dest!A2 = orig!s3
     End If
     dest.Update
 
     dest.AddNew
     If orig!s3 < orig!s5 Then
 
     dest!A1 = orig!s3
     dest!A2 = orig!s5
     Else
 
     dest!A1 = orig!s5
     dest!A2 = orig!s3
     End If
     dest.Update
 
dest.AddNew
     If orig!s3 < orig!s6 Then
 
     dest!A1 = orig!s3
     dest!A2 = orig!s6
     Else
 
     dest!A1 = orig!s6
     dest!A2 = orig!s3
     End If
     dest.Update
 
     dest.AddNew
     If orig!s4 < orig!s5 Then
 
     dest!A1 = orig!s4
     dest!A2 = orig!s5
     Else
 
     dest!A1 = orig!s5
     dest!A2 = orig!s4
     End If
     dest.Update
 
dest.AddNew
     If orig!s4 < orig!s6 Then
 
     dest!A1 = orig!s4
     dest!A2 = orig!s6
     Else
 
     dest!A1 = orig!s6
     dest!A2 = orig!s4
     End If
     dest.Update
 
dest.AddNew
     If orig!s5 < orig!s6 Then
 
     dest!A1 = orig!s5
     dest!A2 = orig!s6
     Else
 
     dest!A1 = orig!s6
     dest!A2 = orig!s5
     End If
     dest.Update
 
     orig.MoveNext
     Loop
 
     DoCmd.OpenQuery "frequenze"
 
 
End Sub


Akery formulated the question :

Non capisco bene il tuo codice per la creazione dei 188715 ambi...
ma funziona bene.
La query comunque dà 52 numeri che non c'entrano coi corretti 4005
seguenti.
Ti allego il file che comprende le varie mie e vostre fasi che
conducono al risultato finale.
C'è chi ci ha messo l'idea dei 188715 (tu), chi ha scritto la
query giusta (C Federici) e chi ha fatto qualche ritocco (io).
 
Se guardi il codice che sta dietro alla soluzione 3", vedi com'é
semplice creare i 188715 ambi.
Se poi esamini la query qFrequenze scritta da FC capisce la logica
della sua fattura: io non l'ho capita.
 
Bruno


Dimenticavo il file:
 
https://1drv.ms/u/s!AvTaMfd5-b2oz1JLxMA4-h8Is2rl?e=Dzfvkp
 
 
Bruno

 Bruno
si può "riattivare" il link?
 
Grazie

robertoz...@gmail.com
 explained on 21-11-20 :

Eccolo qua:
https://1drv.ms/u/s!AvTaMfd5-b2oz1pMmFgppq19biHJ?e=7jPqhZ
 
 
Se trovi una soluzione migliore (più veloce) aggiungi un tasto
alla form e rispediscimi il file.
 
Se ti piace lavorar di query, ne ho un'altra più robusta che non
riesco a passare ad Excel...
 
Bruno

 riesco a passare ad Excel... 
 
qui non ho capito il senso

 qui non ho capito il senso
 
Questa è una query di Access, l'ho abbreviata per semplicità.
[1_GEN] e [2_FEB] sono due tabelle con 10 record 6 campi da F1 a F6.
 
SELECT [1_GEN].F1,
Sum([1_GEN].[F2]+[2_FEB].[F2]) AS FF2,
Sum([1_GEN].[F3]+[2_FEB].[F4]) AS FF3,
Sum([1_GEN].[F4]+[2_FEB].[F4]) AS FF4,
Sum([1_GEN].[F5]+[2_FEB].[F5]) AS FF5,
Sum([1_GEN].[F6]+[2_FEB].[F6]) AS FF6,
[FF2]+[FF3]+[FF4]+[FF5]+[FF6] AS S
FROM 1_GEN
INNER JOIN 2_FEB ON [1_GEN].F1 = [2_FEB].F1
GROUP BY [1_GEN].F1;
 
 
In excel ho gli stessi dati in [GEN$A1:A10] e [FEB$A1:A10]
(GEN e FEB sono i fogli, A1:A10 sono i range in ambedue i fogli).
Per usare la query in Excel normalmente si fa la seguente
trasformazione, l'ho fatta tante volte e funziona.
Qui però ho errore nella 5 righe "Sum(..." che non so risolvere!
 
SQL = "SELECT [GEN$A1:A10].F1,"
SQL = SQL & "Sum([GEN$A1:A10].F2 + [FEB$A1:A10].F2) AS FF2, "
SQL = SQL & "Sum([GEN$A1:A10].F3 + [FEB$A1:A10].F3) AS FF3,"
SQL = SQL & "Sum([GEN$A1:A10].F4 + [FEB$A1:A10].F4) AS FF4,"
SQL = SQL & "Sum([GEN$A1:A10].F5 + [FEB$A1:A10].F3) AS FF5,"
SQL = SQL & "Sum([GEN$A1:A10].F6 + [FEB$A1:A10].F4) AS FF6,"
SQL = SQL & "FF2+FF3+FF4+FF5+FF6 AS S "
SQL = SQL & "FROM [GEN$A1:A10] "
SQL = SQL & "INNER JOIN [FEB$A1:A10] ON [GEN$A1:A10].F1 = 
[FEB$A1:A10].F1 "
SQL = SQL & "GROUP BY [GEN$A1:A10].F1;"
 
Buona la formulazione SQL, poi si esegue tutto con:
 
Set RS = xlDB.OpenRecordset(SQL, dbOpenDynaset)
Destination(2, 1).CopyFromRecordset RS
 
Bruno