Newsgroup: it.comp.os.linux.sys
Titolo: Aiuto con query MySql

Ciao a tutti!
Ho un db che contiene lo stato di connessione a diversi PC in questo
formato...
ID | USER | DATA | ORA | PC | STATO
67, pippo, 2010-08-04, 10:16:18, PC1, login
68, pluto, 2010-08-04, 11:29:15, PC2, login
69, pippo, 2010-08-04, 11:23:24, PC1, logout
70, paperino, 2010-08-04, 11:15:36, PC3, login
Premesso che più che le operazioni veramente di base con MySQL non ho
mai fatto...
mi servirebbe poter ricavare chi in questo momento è connesso e il
relativo pc, in questo caso quindi
-pluto PC2
-paperino PC3
La logica è quella di "mostra l' utente che ha lo stato=login ma che
non ha record seguenti in cui lo stato=logout tenendo presente che si
deve trattare sempre dello stesso PC"
A parole sembra facile, ma non so da che parte iniziare e purtroppo mi
manca il tempo per leggermi un intero manuale di SQL (e ciò mi
rattrista)...
C'è qualcuno che mastica SQL e che mi da na mano?
Grazie!

 C'è qualcuno che mastica SQL e che mi da na mano?
-- 
Wikipedia Admin: "wikipedia non è un blog"
Iosif Stalin: "i compagni hanno scambiato il partito per un circolo di
discussione"

 deve trattare sempre dello stesso PC"
l'ideale sarebbe modificare la tabella
- id | user | pc | login | logout
  select user,pc,login from tabella where logout is null;
una sola select non ti basta:
- select count(*) from tabella where user="?????" and pc="????";
- select + from tabella where user="????" and pc="????" desc limit 1;
con la prima select si contano le righe presenti, se il numero è pari
significa che ad ogni login corrisponde un logout;
con "desc limit 1" della seconda select viene visualizzata l'ultima
riga
del risultato della select;
e questo per ogni coppia user+pc
oppure:
- create temporary table temp
  ( user varchar(10), pc varchar(10), stato varchar(10) );
per ogni coppia utente,pc devi fare una insert del tipo
- insert into temp (user,pc,stato)
        select user,pc,stato from tabella
            where user="name" and pc="pc" desc limit 1;
al termine
- select * from temp wher stato="login"
- drop table temp;
oppure ti crei una procedura...
e quà mi fermo perchè:
- non sono così esperto
- me sà tanto che...
  (solo i prof sono così...**)
ciao
michele
** come dite CL|UL di DB?
    CL|UL = prof

-paperino PC3
Ci sono varie possibilità, e alcune dipendono dalla versione di MySQL.
Inoltre, il fatto che data e ora siano separati complica le cose... se
avessi avuto un unico campo datetime sarebbe stato meglio.
Se la tabella non e' uno storico, ma solo uno snapshot degli stati dei
vari PC, ovvero c'e' un indice tipo
	CREATE UNIQUE INDEX tbl_ndx ON tbl(user,pc,stato);
	INSERT INTO tbl (user,data,ora,pc,stato) VALUES ...
		ON DUPLICATE KEY UPDATE
			data=VALUES(data),
			ora=VALUES(ora);
allora puoi cavartela con una semplice LEFT JOIN:
	SELECT a.*,b.data AS logout_data,b.ora AS logout_ora
		FROM tbl AS a LEFT JOIN tbl AS b
		ON (a.user = b.user AND a.pc = b.pc
			AND a.stato = 'login'
			AND b.stato = 'logout'
			AND 
			((a.data < b.data) OR 
			 (a.data = b.data AND a.ora < b.ora));
che ti da' l'elenco di tutti i record di login, uniti "eventualmente"
(perche' e' una LEFT JOIN e non una JOIN) ai corrispondenti record di
logout tali che il logout sia avvenuto successivamente. Se questi non
esistono, logout_data e _ora risulteranno NULL, quindi puoi avere chi
e' loggato aggiungendo una limitazione
		WHERE b.ora IS NULL
Nel caso pero' che quella tabella sia uno storico, per ogni coppia di
tipo (user, pc) tu vuoi l'ultimo stato disponibile, e per questo devi
usare una sotto-select: anziche' "FROM tbl AS a" ti servirebbe quindi
	FROM ( SELECT pc, user, MAX(data), MAX(ora) FROM tbl
		WHERE stato = 'login' GROUP BY pc, user ) AS a
che pero' ti darebbe risultati sbagliati, perche' il massimo e' fatto
su due campi indipendenti (data e ora... hai le 9 del giorno 5 e le 5
del giorno 9, ti dara' un login alle 9 del giorno 9).
Le strade sono due:
	* al posto del campo data e ora metti un solo campo timestamp
	  e poi crei una "VIEW" che "veda" data e ora con le funzioni
	  di estrazione marcatempo di MySQL.
	  Questo accelera le queries di aggiornamento e consultazione
	  aggregata, che lavoreranno su un tipo dati nativo; e invece
	  rallentera' le query di consultazione semplice che dovranno
	  calcolarsi data e ora a partire dal timestamp (ma questo e'
	  un calcolo unario, molto veloce e molto ottimizzato).
	  Puoi anche fare un trucco sporco, rinominando la tabella in
	  tbl_orig, e creando una VIEW che si chiama come la tabella.
	  Se non hai INSERT strane, o se le puoi cambiare, nessuno si
	  accorgera' di niente: SELECT... FROM tbl continuera' sempre
	  a funzionare, e nessuno sapra' che data e ora non sono piu'
	  dei valori a loro stanti ma il frutto di un calcolo ]:-)
	* nella query aggregata la' sopra, usi
	     MAX(STR_TO_DATE(CONCAT(data,' ',ora),'%Y-%m-%d %H:%i')))
	  e naturalmente cosi' rallenti molto la query aggregata, che
	  deve farsi una paccata di CONCAT e STR_TO_DATE, pero' lasci
	  la tabella attuale come sta.
E poi c'e' il problema dei login e logout nello stesso minuto, che e'
un discreto bigoncio di vermi pure quello (e no, non basta aggiungere
':01' e '%s' nel concat per lo str_to_date ai login - anche se magari
puo' aiutare parecchio. Dipende quale dura di piu', login o logout, e
dal momento in cui viene registrato).
La query finale nel caso orridiore è
	SELECT a.*,b.logout,TIMEDIFF(b.logout, a.login) AS duration
	  FROM ( SELECT pc, user, MAX(STR_TO_DATE(CONCAT(data,'
',ora),'%Y-%m-%d %H:%i')) AS login
	    FROM storico WHERE stato = 'login' GROUP BY pc, user ) AS a
	LEFT JOIN ( SELECT pc, user, MAX(STR_TO_DATE(CONCAT(data,'
',ora),'%Y-%m-%d %H:%i')) AS logout
	    FROM storico WHERE stato = 'logout' GROUP BY pc, user ) AS b
	ON (a.pc = b.pc AND a.user = b.user AND a.login <= b.logout)
	[ WHERE b.logout IS NULL ]
	;
(be', nel caso di b.logout IS NULL, di TIMEDIFF te ne fai poco).
===
Test case, se non ho fatto casino (e ci sta, ci sta, ci sta):
CREATE TABLE storico (id integer not null primary key auto_increment, pc
varchar(16), data varchar(10), ora varchar(8), user varchar(8), stato
varchar(6));
INSERT INTO storico (pc,data,ora,user,stato) VALUES
('alpher','2010-09-01','08:17','alfio','login'),('alpher','2010-09-01','21:15','alfio','logout'),
('alpher','2010-09-01','21:15','alfio','login'),('alpher','2010-09-01','21:15','alfio','logout'),
('bayter','2010-09-01','08:17','bruno','login'),('bayter','2010-09-01','21:15','bruno','logout'),
('bayter','2010-09-01','21:15','bruno','login'),('bayter','2010-09-01','21:15','bruno','logout'),
('gammer','2010-09-01','21:15','chawker','login'),('gammer','2010-09-02','08:19','chawker','logout'),
('kapper','2010-09-01','19:00','chawker','login');
SELECT a.*,b.logout,DATE_DIFF(b.logout, a.login) AS duration FROM
	( SELECT pc, user, MAX(STR_TO_DATE(CONCAT(data,' ',ora),'%Y-%m-%d
%H:%i')) AS login
		FROM storico WHERE stato = 'login' GROUP BY pc, user ) AS
a
	LEFT JOIN
	( SELECT pc, user, MAX(STR_TO_DATE(CONCAT(data,' ',ora),'%Y-%m-%d
%H:%i')) AS logout
		FROM storico WHERE stato = 'logout' GROUP BY pc, user ) AS
b
	ON (a.pc = b.pc AND a.user = b.user AND a.login <= b.logout);
	WHERE logout IS NULL;
+--------+---------+---------------------+---------------------+----------+
| pc     | user    | login               | logout              | duration |
+--------+---------+---------------------+---------------------+----------+
| alpher | alfio   | 2010-09-01 21:15:00 | 2010-09-01 21:15:00 | 00:00:00 |
| bayter | bruno   | 2010-09-01 21:15:00 | 2010-09-01 21:15:00 | 00:00:00 |
| gammer | chawker | 2010-09-01 21:15:00 | 2010-09-02 08:19:00 | 11:04:00 |
| kapper | chawker | 2010-09-01 19:00:00 | NULL                | NULL     |
+--------+---------+---------------------+---------------------+----------+
4 rows in set (0.00 sec)
Detto tutto questo, e aggiunto che tenere 'stato' come campo di testo
parlante e' inefficiente abbestia, in alternativa ci sono i comandi
	who
e
	last
da shell Linux ;-)
Leonardo
-- 
Enough, enough!  But, stranger, ere we part,
Glancing farewell to each nefarious bier,
This warning would I beg you take to heart:
There is an end to e'en the worst career!

[ot] posso chiederti che programmi usi per la gestione dei newsgroup? io 
non riesco a gestire l'indentazione del testo :!

 io non riesco a gestire l'indentazione del testo :!
se è per questo non riesci nemmeno a guardare gli header delle news.
Forte Agent 5.00/32.1171 
(il buon Leonardo è diventato un winzozzer? non ci posso credere).


io non riesco a gestire l'indentazione del testo :!
Neanche io. Dopo aver cercato inutilmente per molto tempo un programma che
facesse le cose come le volevo io, senza ovviamente dovermi impiccare (che
so: "Copia il testo, lo incolli, lo mandi in pipe a par, poi..."), mi sono
scocciato e ho deciso di fare a mano.


(il buon Leonardo è diventato un winzozzer? non ci posso credere).
Sempre usato Forte Agent, sono un tradizionalista[1].
Poi, effettivamente, uso Windows XP come GUI di Linux :-D
Leonardo
[1] suona meglio di "pigro"


...
Complimenti davvero, io non so quanto tempo tu abbia potuto passare
tra il ragionare e lo scrivere tutto ciò, ma credo sia uno dei post
più lunghi che fin' ora abbia mai visto...
Tornando alla questione, ora devo cercare di interpretare al meglio
quanto tu hai scritto, e magari adattarmi le diverse soluzioni...
Purtroppo ci ho pensato dopo che sarebbe stato meglio avere un ' unica
riga contenente le diverse sessioni dell' utente, ed ora credo sia
troppo tardi poiche questo db è importato da un altra applicazione
adattata allo scopo...

adattata allo scopo...
Be'. Intanto nessuno ti obbliga a tenerlo in quel formato li'. Puoi,
per esempio, avere una tabella di appoggio e metterci dentro ad ogni
import (mica li farai ogni tre per due, no?) i dati grezzi, e poi li
elabori in batch popolando la tabella vera e ottimizzata.
(Poche ottimizzazioni, poi. Una TLB per gli utenti e una per i PC, e
una fusione di data e ora in datetime, e conversione di login/logout
in tipo enumerato, anzi, se hai solo login/logout, booleano proprio;
una riga ti occupa metà spazio e va cento volte più veloce).
E poi, a seconda di cosa ci devi fare, magari non è neanche MySQL il
mezzo più adatto. Una delle cose veramente ganze di Linux è la linea
di comando e i tool tipo sed, grep, awk (o Perl, se proprio uno deve
andare in guerra (se contro i Borg, Python: 'import starfleet')).
Si possono ideare soluzioni al limite del criminoso: per esempio uno
potrebbe avere dei file LNK generati al volo, uno per ogni PC, e, in
un'altra cartella, uno per ogni utente (va visto...!), e modificarli
con il nome dell'utente, e l'iconcina di PC acceso/spento. Condividi
la cartella (in read only) col presidente-arcangelo e quello si puo'
vedere a colpo d'occhio lo stato dei terminali... (OKAY! OKAY! Ve la
passo! Uffa, per una volta che avevo trovato della robba buona...).
Leonardo e le UI molto non convenzionali
