[Access] Estrarre una query da una query a campi incrociati

[fiorella.fortunato]

Credo che sia una limitazione di Access. Io uso la versione 2000.

Ho questo codice perfettamente funzionante. Mi permette di vedere quante pagine sono statre visitate ora x ora nella stessa giornata a seconda dell'IP del visitatore. Nell'esempio sto trattando il 17/06/2006.

Codice: Seleziona tutto

TRANSFORM Count(tabellaoraria.PathDir) AS ConteggioDiPathDir
SELECT tabellaoraria.IPAddress, min(idlog) AS minimolog,Count(tabellaoraria.PathDir) AS Totalepagine
FROM (select * from log where day(datanavigazione)=17 and month(datanavigazione)=6 and year(datanavigazione)=2006) AS tabellaoraria
GROUP BY tabellaoraria.IPAddress
PIVOT Format([DataNavigazione],"h") In ('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23');

Legenda:
- Datanavigazione = data e ora in cui è stata visitata la pagina
- Pathdir = Percorso e nome pagina visitata
- IDLog = numero progressivo che attribuito alle varie righe di log
- IPAddress = IP con il quale si naviga nelle pagine del sito

La query mi estrae i dati corretti ma gli IP me li mette ovviamente in ordine alfabetico. Io vorrei che gli IP fossero ordinati per l'ordine corretto di visita effettuato sul sito. E' per questo motivo che ho inserito il campo"minimo", ma non sono riuscita a fare in modo che i dati della query fossero estratti ordinati per quel campo. Anche provando ad inserire degli ORDER BY non riesco ad ottenere il risultato voluto.

Ho fatto in modo che Access tramite la creazione guidata delle query mi estraesse i dati nell'ordine esatto dalla query a campi incorciati sopra indicata e salvata ma era solo una prova (funzionante tra l'altro) per poter creare un SQL più complesso. E' proprio qui che ho avuto il problema.
Infatti ho avuto immediatamente una segnalazione di errore quando ho digitato ed eseguito il seguente codice (al posto dei 3 puntini c'è il resto del codice sopra indicato):

Codice: Seleziona tutto

select *
from (TRANSFORM Count(tabellaoraria.PathDir) AS ... )
order by minimolog

E' come se l'SQL non fosse in grado di accettare il comando TRANSFORM.

Sbaglio qualcosa? E' una limitazione di Access? Si può ovviare in qualche modo all'eventuale limitazione?

Grazie a chi mi risponderà e soprattutto a chi saprà aiutarmi nel trovare la soluzione al mio problema.

[fiorella.fortunato]

Scusatemi, ma nel ho scritto involontariamente OUTLOOK al posto di ACCESS. Se potete correggere grazie.

[archimede]

Non sono un esperto di crosstab queries né sono sicuro di aver capito cosa vuoi fare esattamente (né ho capito perché usi due SELECT).

Comunque, se la prima query funziona dovrebbe bastare salvarla e poi fare SELECT * FROM mia_query ORDER BY minimolog. O no?

Alessandro

[archimede]

Secondo me puoi provare a semplificare/modificare la tua query:

Codice: Seleziona tutto

TRANSFORM Count(*) AS ConteggioDiIP
SELECT IPAddress FROM log WHERE Day(datanavigazione)=17 and Month(datanavigazione)=6 and Year(datanavigazione)=2006
GROUP BY IPAddress ORDER BY IPAddress
PIVOT Format([DataNavigazione],"h") In ('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23');

HTH.

Alessandro

[fiorella.fortunato]

Codice: Seleziona tutto

TRANSFORM Count(*) AS ConteggioDiIP
SELECT IPAddress,min(idlog) as minimolog FROM log WHERE Day(datanavigazione)=17 and Month(datanavigazione)=6 and Year(datanavigazione)=2006
GROUP BY IPAddress
PIVOT Format([DataNavigazione],"h") In ('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23');

Bene, ma... Come puoi vedere sopra ho modificato leggermente il tuo codice.
Il tuo codice semplifica sicuramente la mia query ma anche la tua continua ad ordinare i dati per IP anche togliendo l'"ORDER BY IPAddress" che risulta in questo caso superfluo. La mia aggiunta del campo "minimolog" sul tuo codice mi serve perchè ho bisogno di ordinare la tabella per quel campo cosa che attualmente non riesco a fare in modo diretto.
Se faccio una SELECT diretta sulla query ottengo un messaggio di errore perchè Access non riconosce il comando TRANSFORM.
Io voglio proprio evitare di salvare la query e fare una seconda estrazione dalla query salvata in quanto quest'ultima la devo proiettare all'interno di una pagina asp.

[archimede]

Avevo capito male. Ma non puoi fare

Codice: Seleziona tutto

TRANSFORM Count(*) AS ConteggioDiIP
SELECT IPAddress FROM log WHERE Day(datanavigazione)=17 and Month(datanavigazione)=6 and Year(datanavigazione)=2006
GROUP BY IPAddress ORDER BY min(idlog)
PIVOT Format([DataNavigazione],"h") In ('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23');

?

Alessandro

[archimede]

Io voglio proprio evitare di salvare la query e fare una seconda estrazione dalla query salvata in quanto quest'ultima la devo proiettare all'interno di una pagina asp.

Questa non l'ho capita.

Alessandro

[fiorella.fortunato]

Per l'ultima query come l'hai impostata tu farò un controllo domani. Purtroppo adesso mi manca l'mdb di prova sul quale lavorare.
In effetti è un tentativo che non ho pensato di applicare e spero vivamente che funzioni.

Per l'ultima frase in effetti (e chiedo venia) mi sono espressa male.
La creazione di una query a campi incrociati prevede che l'enunciato SQL sia TRANSFORM... PIVOT... ecc.
In Access non è possibile (almeno per mia esperienza attuale) effettuare una SELECT diretta (magari per effettuare una JOIN) tipo SELECT * FROM (TRANSFORM... PIVOT... ecc...). Attualmente devo prima salvare la prima query a campi incrociati dentro l'mdb e poi creare una seconda query che effettua una SELECT * FROM dalla prima query. Cosa che voglio proprio evitare.
Grazie della disponibilità Archimede e spero di essere stata più chiara

[fiorella.fortunato]

Ho ovviato al mio problema di recupero del file mdb...
Utilizzando la tua ultima query ottengo la segnalazione di errore:
"Impossibile avere una funzione di aggregazione nella clausola ORDER BY (min(idlog))".

Utilizzando il mio ultimo SQL postato
se dopo il GROUP BY utilizzo l'ORDER BY minimolog appare un prompt;
se dopo il GROUP BY utilizzo l'ORDER BY (min(idlog)) ottengo l'errore che appare usando la tua query;
se dopo il GROUP BY utilizzo l'ORDER BY log.idlog ottengo l'errore "Impossibile eseguire una query che non include l'espressione 'log.idlog' specificata come parte di una funzione di aggregazione".

Purtroppo non ci siamo

[archimede]

Utilizzando la tua ultima query ottengo la segnalazione di errore:
"Impossibile avere una funzione di aggregazione nella clausola ORDER BY (min(idlog))".

Strano, giacché in una query normale (senza TRANSFORM... PIVOT...) a me non dà problemi. Puoi provare:

Codice: Seleziona tutto

TRANSFORM Count(*) AS ConteggioDiIP
SELECT IPAddress, min(idlog) FROM log WHERE Day(datanavigazione)=17 and Month(datanavigazione)=6 and Year(datanavigazione)=2006
GROUP BY IPAddress ORDER BY 2
PIVOT Format([DataNavigazione],"h") In ('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23');

Attualmente devo prima salvare la prima query a campi incrociati dentro l'mdb e poi creare una seconda query che effettua una SELECT * FROM dalla prima query. Cosa che voglio proprio evitare.

E perché lo vuoi evitare? Problemi di performance?

Alessandro

[fiorella.fortunato]

Ti mostro il risultato della tua query senza "l'ORDER BY 2" e con... La query non ha fatto quasi una piega...
Rimango sempre più basita... come ha ordinato la tabella?

Codice: Seleziona tutto

IPAddress      Expr1003   IPAddress      Expr1003
151.24.19.107  92127      82.52.64.82    92211
212.239.13.34  92271      212.239.13.34  92271
62.211.139.138 92129      62.211.139.138 92129
65.54.188.100  92091      65.54.188.100  92091
65.54.188.101  92329      65.54.188.101  92329
65.54.188.98   92356      65.54.188.98   92356
65.54.188.99   92210      65.54.188.99   92210
66.249.65.5    92094      66.249.65.5    92094
80.104.45.82   92178      80.104.45.82   92178
80.180.62.146  92206      80.180.62.146  92206
81.208.74.182  92301      81.208.74.182  92301
82.184.255.106 92092      151.24.19.107  92127
82.50.62.163   92209      82.50.62.163   92209
82.52.64.82    92211      85.32.208.209  92177
82.52.68.211   92176      82.52.68.211   92176
82.52.89.28    92204      82.52.89.28    92204
82.54.142.124  92327      82.54.142.124  92327
82.54.156.177  92330      82.54.156.177  92330
82.54.159.95   92328      82.54.159.95   92328
82.58.100.3    92095      82.58.100.3    92095
82.58.86.77    92227      82.58.86.77    92227
82.60.5.51     92357      82.60.5.51     92357
82.60.90.42    92214      82.60.90.42    92214
85.18.59.242   92136      85.18.59.242   92136
85.32.208.209  92177      82.184.255.106 92092

Il perchè non voglio creare due tabelle è presto spiegato.
Sono solita a creare una query in una pagina asp che usa Access.

Di solito prima testo i miei SQL "complessi" in access e se funzionanti li proietto nell'ASP.

Per esempio prendendo gli stessi log creo una tabella dove giorno x giorno so quanti utenti mi hanno visitato e quante pagine hanno visto in tutto e (perchè no?) anche la media di pagine visitate per utente... Tabella complessa ma risultato velocissimo (circa 3 sec)

Codice: Seleziona tutto

SELECT tabella1.datanav, tabella1.pagine, tabella2.utenti, cint(tabella1.pagine / tabella2.utenti) as intero
FROM (SELECT DISTINCT Datanav, count(datanav) AS pagine
FROM (SELECT DateValue([DataNavigazione]) AS DataNav
FROM log where month([DataNavigazione])=" & mese & " AND year([DataNavigazione])=" & anno & ") AS tabellapagine
GROUP BY datanav)  AS tabella1, (SELECT Datanav, count(datanav) AS utenti
FROM (SELECT DISTINCT tabellaip.DataNav, tabellaip.ip
FROM (SELECT DateValue([DataNavigazione]) AS DataNav,ipaddress as ip
FROM log where month([DataNavigazione])=" & mese & " AND year([DataNavigazione])=" & anno & ") AS tabellaip
GROUP BY tabellaip.DataNav, tabellaip.ip) AS tabellautenti
GROUP BY datanav) AS tabella2
WHERE tabella1.datanav= tabella2.datanav

SQL ancora più complessa ma abbastanza veloce (circa 6 secondi) se voglio controllare (in ordine di visita) se un certo ip ha visitato l'home page o no:

Codice: Seleziona tutto

SELECT *
FROM [select datanav,ip,min(minimo) as logminimo,sum(defaultasp) as logdefaultasp
FROM (SELECT *
FROM (SELECT *
FROM (SELECT DISTINCT tabellaip.DataNav, tabellaip.ip,min(tabellaip.idlog) as minimo, count(tabellaip.pathdir)/count(tabellaip.pathdir) as defaultasp
FROM (SELECT DateValue(DataNavigazione) AS DataNav,ipaddress as ip,idlog, pathdir
FROM log where month(DataNavigazione)=6 AND year(DataNavigazione)=2006) AS tabellaip
WHERE (((Day(tabellaip.DataNav))=17) AND ((Month(tabellaip.DataNav))=6) AND ((Year(tabellaip.DataNav))=2006) and pathdir like '*default.asp*')
GROUP BY tabellaip.DataNav, tabellaip.ip)  AS ipgiornalieri
ORDER BY ipgiornalieri.minimo)
UNION
SELECT *
FROM (SELECT *
FROM (SELECT DISTINCT tabellaip1.DataNav, tabellaip1.ip, min(tabellaip1.idlog) as minimo, -count(tabellaip1.pathdir)/count(tabellaip1.pathdir) as defaultasp
FROM (SELECT DateValue(DataNavigazione) AS DataNav,ipaddress as ip,idlog, pathdir
FROM log where month(DataNavigazione)=6 AND year(DataNavigazione)=2006) AS tabellaip1
WHERE (((Day(tabellaip1.DataNav))=17) AND ((Month(tabellaip1.DataNav))=6) AND ((Year(tabellaip1.DataNav))=2006) and pathdir not like '*default.asp*')
GROUP BY tabellaip1.DataNav, tabellaip1.ip)  AS ipgiornalieri1
ORDER BY ipgiornalieri1.minimo))
GROUP BY datanav,ip]. AS listacli
ORDER BY listacli.logminimo;

tra l'altro con questa query avrei voluto effettuare una JOIN con la dannata query che sto cercando di realizzare.
I due "megaSQL" sono già funzionanti sul sito e se solo riuscissi ad utilizzare la tabella avrei messo la ciliegina sulla torta...

[archimede]

Non so perché l'ordinamento non funziona: forse con la GROUP BY Access lo ignora, oppure non supporta correttamente l'ORDER BY su una funzione di aggregazione. Però non ottieni lo stesso risultato ordinando per datanavigazione? Oppure cosa succede se SALVI la query nel db (dando un nome alla colonna, ovviamente) senza l'ORDER BY e poi fai una SELECT * FROM query ORDER BY minimolog?

Riguardo all'altra questione (e poi la smetto) continuo a non capire. Il bello delle query dentro al db è che "rimuovono" la complessità dalle tue pagine. Quale vantaggio ti dà avere una SELECT di 20 righe in una pagina ASP invece di un semplice SELECT * FROM query_nel_db? Io preferisco di gran lunga il secondo approccio: codice più chiaro e maggiormente "portabile".

Alessandro

[archimede]

Però non ottieni lo stesso risultato ordinando per datanavigazione?

Ok, non ero completamente sveglio.

Alessandro

[fiorella.fortunato]

Riguardo all'altra questione (e poi la smetto) continuo a non capire. Il bello delle query dentro al db è che "rimuovono" la complessità dalle tue pagine. Quale vantaggio ti dà avere una SELECT di 20 righe in una pagina ASP invece di un semplice SELECT * FROM query_nel_db? Io preferisco di gran lunga il secondo approccio: codice più chiaro e maggiormente "portabile".

Ti do in parte ragione se usi una query in un mdb stand-alone che puoi aprire con il programma Access.
Nel mio caso sto utilizzando un mdb che si trova su un host al quale ho bisogno di accedere tramite una connessione ADODB. Per cui penso sia più performante creare una query diretta nelle istruzioni ASP anzichè utilizzare una query main che richiama delle sottoquery all'interno di una pagina web ma non è detto che io abbia ragione... e poi comunque ammetto che non so come si faccia a passare dei parametri da una query ad un'altra già memorizzata nel mdb (giorno, mese, anno)... magari è un aspetto che affronterò più avanti...

Ieri notte ho trovato una soluzione che cercherò comunque di ottimizzare ma qui forse rischio di andare OT perchè inizio sto parlando di programmazione pagine web con l'utilizzo del DB di Access: in pratica estraggo una prima tabella da questa stringa

Codice: Seleziona tutto

strSqlListaIP = "SELECT * FROM (select datanav,ip,min(minimo) as logminimo,sum(defaultasp) as logdefaultasp,sum(totpagine) as totalepagine FROM (SELECT * FROM (SELECT * FROM (SELECT DISTINCT tabellaip.DataNav, tabellaip.ip,min(tabellaip.idlog) as minimo, count(tabellaip.pathdir)/count(tabellaip.pathdir) as defaultasp,count(tabellaip.pathdir) as totpagine FROM (SELECT DateValue(DataNavigazione) AS DataNav,ipaddress as ip,idlog, pathdir FROM log where month(DataNavigazione)=" & request("mese") & " AND year(DataNavigazione)=" & request("anno") & ") AS tabellaip WHERE ( datevalue(tabellaip.DataNav)='" & request("day") & "' and pathdir like '%default.asp%') GROUP BY tabellaip.DataNav, tabellaip.ip)  AS ipgiornalieri ORDER BY ipgiornalieri.minimo) UNION SELECT * FROM (SELECT * FROM (SELECT DISTINCT tabellaip1.DataNav, tabellaip1.ip, min(tabellaip1.idlog) as minimo, -count(tabellaip1.pathdir)/count(tabellaip1.pathdir) as defaultasp,count(tabellaip1.pathdir) as totpagine FROM (SELECT DateValue(DataNavigazione) AS DataNav,ipaddress as ip,idlog, pathdir FROM log where month(DataNavigazione)=6 AND year(DataNavigazione)=2006) AS tabellaip1 WHERE ( datevalue(tabellaip1.DataNav)='" & request("day") & "' and pathdir not like '%default.asp%') GROUP BY tabellaip1.DataNav, tabellaip1.ip)  AS ipgiornalieri1 ORDER BY ipgiornalieri1.minimo)) GROUP BY datanav,ip) AS listacli ORDER BY listacli.logminimo"
set rsListaIP = Server.CreateObject("ADODB.Recordset")
rsListaIP.open strSqlListaIP, db, 3, 3


Estraggo in questo modo la data di navigazione (datanav), tutti gli IP che hanno visitato in una certa data il sito (IP), il primo progressivo nel contatore dei log in base al quale faccio l'ordinamento della query(logminimo), controllo della visita al default.asp (logdefaultasp) e numero totale di pasite visitate durante il giorno (totalepagine).

Faccio anche una seconda estrazione (quella che purtroppo non riesco ad ordinare come vorrei):

Codice: Seleziona tutto

strSqlListaOr = "TRANSFORM Count(*) AS ConteggioDiIDlog SELECT IPAddress,min(idlog) as minimolog FROM log WHERE DateValue(datanavigazione)='" & request("day") &"' GROUP BY IPAddress PIVOT Format([DataNavigazione],'h') In ('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23')"
set rsListaOr = Server.CreateObject("ADODB.Recordset")
rsListaOr.open strSqlListaOr, db, 3, 3


Qui estraggo gli IP (IPAddress), (minimolog) come nell'estrazione della prima query e, ora x ora, tutte le navigazioni effettuate dall'IP (0,1,2,3...).

Ora le due tabelle hanno lo stesso numero di righe ma hanno ognuna un ordinamento differente. Se fossi riuscita a creare una JOIN ad hoc con un'unica istruzione SQL avrei potuto avere una tabella completa (magari riducendo i tempi di esecuzione della query). Nella situazione da me adottata devo purtroppo:
a - con un ciclo DO-LOOP leggere riga per riga i dati della tabella estratta dalla prima query
b - con un altro ciclo DO LOOP, nidificato dentro al ciclo DO-LOOP cui sopra, leggere riga per riga i dati della tabella estratta dalla seconda query e quando trovo la corrispondenza della prima tabella con la seconda (IP=IPAddress) pubblico il risultato dentro una <table></table>.
Questa mia soluzione al momento può bastare perchè nel giro di 5-6 secondi al massimo (con un numero di utenti giornalieri inferiore a 100) riesco a costruire la pagina web con il dettaglio delle navigazioni in una certa data (request("day")). Prima di ieri sera la creazione della pagina web durava minimo 2-3 minuti!!!

Anche se non sono riuscita ad ottenere ciò che avrei voluto, lo scambio di idee come vedi mi è servito parecchio. Grazie Archimede!

[fiorella.fortunato]

il codice che segue sostituisce quello del mio post precedente in quanto mi sono accorta che dava anomalie quando visualizzavo una data con anni e mesi diversi dal Giugno 2006. Ciao

Codice: Seleziona tutto

strSqlListaIP = "SELECT * FROM (select datanav,ip,min(minimo) as logminimo,sum(defaultasp) as logdefaultasp,sum(totpagine) as totalepagine FROM (SELECT * FROM (SELECT * FROM (SELECT DISTINCT tabellaip.DataNav, tabellaip.ip,min(tabellaip.idlog) as minimo, count(tabellaip.pathdir)/count(tabellaip.pathdir) as defaultasp,count(tabellaip.pathdir) as totpagine FROM (SELECT DateValue(DataNavigazione) AS DataNav,ipaddress as ip,idlog, pathdir FROM log where month(DataNavigazione)=" & request("mese") & " AND year(DataNavigazione)=" & request("anno") & ") AS tabellaip WHERE ( datevalue(tabellaip.DataNav)='" & request("day") & "' and pathdir like '%default.asp%') GROUP BY tabellaip.DataNav, tabellaip.ip)  AS ipgiornalieri ORDER BY ipgiornalieri.minimo) UNION SELECT * FROM (SELECT * FROM (SELECT DISTINCT tabellaip1.DataNav, tabellaip1.ip, min(tabellaip1.idlog) as minimo, -count(tabellaip1.pathdir)/count(tabellaip1.pathdir) as defaultasp,count(tabellaip1.pathdir) as totpagine FROM (SELECT DateValue(DataNavigazione) AS DataNav,ipaddress as ip,idlog, pathdir FROM log where month(DataNavigazione)=" & request("mese") & " AND year(DataNavigazione)=" & request("anno") & ") AS tabellaip1 WHERE ( datevalue(tabellaip1.DataNav)='" & request("day") & "' and pathdir not like '%default.asp%') GROUP BY tabellaip1.DataNav, tabellaip1.ip)  AS ipgiornalieri1 ORDER BY ipgiornalieri1.minimo)) GROUP BY datanav,ip) AS listacli ORDER BY listacli.logminimo"


[archimede]

penso sia più performante creare una query diretta nelle istruzioni ASP anzichè utilizzare una query main che richiama delle sottoquery all'interno di una pagina web ma non è detto che io abbia ragione... e poi comunque ammetto che non so come si faccia a passare dei parametri da una query ad un'altra già memorizzata nel mdb (giorno, mese, anno)...

Neppure io sono in possesso di dati certi circa le prestazioni di una query nel db rispetto a una query passata tramite ADO (ma se dovessi scommettere direi che non può essere più lenta).

Quanto al passaggio dei parametri, questo temo non sia possibile. Devi costruire una query che ritorna tutti i records e mettere i parametri nella WHERE.

Infine mi permetto di osservare che, pur non essendo affatto entrato nel merito, le tue query mi lasciano un po' perplesso. Raramente mi sono imbattuto in mostri di SELECT annidate come queste: magari è giusto così, ma non invidio chi dovesse avere la sventura di metterci mano. Potrebbe essere un esercizio stimolante sapere come sono strutturate le tabelle e che dati esattamente vuoi estrarre, ma sto andando OT.

Alessandro

[fiorella.fortunato]

Infine mi permetto di osservare che, pur non essendo affatto entrato nel merito, le tue query mi lasciano un po' perplesso. Raramente mi sono imbattuto in mostri di SELECT annidate come queste: magari è giusto così, ma non invidio chi dovesse avere la sventura di metterci mano. Potrebbe essere un esercizio stimolante sapere come sono strutturate le tabelle e che dati esattamente vuoi estrarre, ma sto andando OT.

La necessità di creare questi megaSQL è dovuta anche dal fatto che se dovessi salvare le sottoquery nell'mdb del sito dovrei bloccare gli accessi al sito, scaricare l'mdb, salvarci sopra le sottoquery, salvare nel sito l'mdb e riattivare gli accessi al sito. E il sito rimarrebbe non disponibile per minuti/ore.
L'SQL fatto in quel modo viene creato, eseguito e distrutto al momento mediante i comandi ASP.
Comunque stai sicuro, ogni mia pagina asp, ogni mio SQL, ogni cosa che riguarda il modo di programmare è comunque sempre documentato su carta con tanto di spiegazioni ed eventuali considerazioni come ogni buon programmatore dovrebbe fare e come ormai faccio da svariati anni.
Ciao e ancora grazie per la tua disponibilità che ho apprezzato moltissimo.