Newsgroup: it.comp.lang.visual-basic
Titolo: [OT] Trasponi di excel ma via SQL e StoreProcedure

Salve a tutti,
	scusate l'OT.
     Ho una serie di tabelle che nel tempo sono variate passando prima 
da soli 10 campi con nomi campo standard e poi nel tempo sono aumentate 
di numero di campi. L'aggiunta dei campi non crea tanti problemi ma la 
diminuzione si visto che ci potrebbe essere questa possibilità. 
Oltretutto la modifica frequente(almeno una volta l'anno) delle label 
sul campo, mi ha fatto pensare che forse è il caso di riprogettare il DB 
prevedendo queste modifiche. Ho quindi creato una tabella Campi(id, 
nome_campo, label) e una tabella struttura dove ricreo dinamicamente la 
struttura della tabella. Questo mi consente di creare tabelle sempre 
dinamiche. Di contro però, e non è poco lo so, pecca in lettura!!! Se 
dovessi accedere al database mi metterei le mani nei capelli xche non 
esiste una tabella reale. Percui ho pensato di creare una serie di viste 
(o anche finte tabelle) di queste tabelle dinamiche dove "trasporre" la 
struttura della tabella per farla diventare una cosa statica e 
permettere agli utenti o ai tool che accedono al db di capire cosa 
stanno leggendo.
Ma esiste una maniera per scrivere una vista in modo da usare il 
trasponi tipo quello di excel? Ho visto su google e mi pare che nessuno 
ha dato una soluzione al problema. Voi che dite?
Grazie!

 soluzione al problema. Voi che dite?
Io uso una stored procedure nostra, che però ha preso spunto, ai tempi 
dei tempi, da una procedura che a sua volta usa massicciamente il 
Dynamic Sql. Trovi qualcosa sul web cercando CROSSTAB.
Anche l'operatore Pivot di SqlServer 2005 può fornire alcune soluzioni 
interessanti.
Alberto
-- 
Heinrich Zille: Ognuno crede che l'altro sia come se stesso, 
dimenticando che al mondo esistono anche delle persone oneste.

 Dynamic Sql. Trovi qualcosa sul web cercando CROSSTAB.
Io uso Postgresql ultima versione... dici che posso risolvere?

Scriveva Peca's lunedì, 16/02/2009:

Ovviamente la sintassi dovrà essere opportunamente modificata.
Inoltre non so come si comporti Postgres relativamente al Dynamic Sql.
Però i concetti di fondo sono gli stessi, cerca crosstab e studia.
ciao
Alberto
-- 
Ho spiegato a mio nipote cos'è il compromesso: se per le vacanze tua 
madre vuole andare al mare e tuo padre invece in montagna, il 
compromesso è che si va al mare, ma il papà può portarsi gli sci.

 Però i concetti di fondo sono gli stessi, cerca crosstab e studia.
Ho trovato questo esempio:
http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.html
è in inglese ma non è un problema... credo sia chiaro e inizio a 
studiarlo. La possibilità di fare il crosstab via SQL è un gran sollievo 
visto che fino a oggi ho sempre ripiegato su VB6. Per il momento quindi 
posso continuare a stutturare il database e quando dovò creare le viste 
in crosstab vedrò di mettere in pratica quello che c'è scritto.
Intanto ti ringrazio molto per l'aiuto.
Ciao.

 ha dato una soluzione al problema. Voi che dite?
Noi usiamo lo stesso approccio presso i clienti, poichè ogni cliente ha
la possibilità di creare più tabelle con più campi.
La soluzione adottata è quella di creare un dizionario dei campi (dove
ci sono tutti i campi che possono essere utilizzati per creare una tabella).
A questo punto creiamo l'anagrafica delle tabelle (diciamo una tabella
che contiene l'elenco delle tabelle create dall'utente).
Quindi viene così:
[CAMPI]1:n----<ElencoTabUtenteCampi>---1:n[ElencoTabelleUtente]
Ogni campo può essere assegnato a una o più tabelle e ogni tabella può
avere uno o più campi.
A questo punto ti fai una routine (decidi tu se farla in T-SQL o in
qualsiasi altro linguaggio) che fa una CREATE TABLE con Nometabella
preso dall'ElencoTabUtente e i relativi campi presi dalla tabella CAMPI.
Nicola

 preso dall'ElencoTabUtente e i relativi campi presi dalla tabella CAMPI.
Io per fortuna ho una tabella statica e da quella collego una tab con i 
campi dinamici... diciamo che nella tab statica ho per forza quei campi 
e chiaramente per forza chiamati in quel modo. Poi da lì variano altri 
campi che sono legati a questa tab. Il tutto è fatto per garantire le 
chiavi. La tab dinamica invece deve essere necessariamente una Vista 
inquanto devono vederla più persone anche nello stesso momento e deve 
"refreshare" le sue colonne spesso. Non credo che esista una funzione da 
sostituire a "IF EXIST..." che fa tipo "IF MODIFIED" da intendersi 
dall'ultima visualizzazione è cambiata la struttura allora droppa e 
ricrea la tabella.
L'idea del crosstab non sembra male. Devo solo verificare se 
nell'interrogazione è veloce o va in timeout prima la vista e poi 
un'eventuale tool o web che la interroga. Ho tabelle da 21000 record in 
aumento con circa 20 colonne a testa (frutto di aggiunte e colonne non + 
usate). Se le metto dinamiche saranno a oggi 21000*20=420000 record da 
interrogare in crosstab. Devo verificare se è poi così performante la 
struttura.

 struttura.
Se ben indicizzato, 420.000 record fanno ridere ad un database moderno.
Vai tranquillo anche di crosstab.
Stamattina ho lanciato diverse query in join 1.300.000 x 1.300.000 
(cioè un join di una tabella con se stessa, su chiavi non indicizzate 
cluster), e il server non se ne accorgeva neppure.
Ciao
Alberto
-- 
Karl Marx: Uno scrittore è produttivo non nella misura in cui produce 
idee, ma nella misura in cui arricchisce l'editore che pubblica le sue 
opere.

 L'idea del crosstab non sembra male. 
Attenzione, noi non lavoriamo in crosstab.
Noi le tabelle le creiamo fisicamente nel database sulla base delle
scelte effettuate dall'utente.
Il nostro utente ha un pannello dove può censire i nuovi campi e un
altro pannello dove può selezionare i campi ed aggiungerli ad una tabella.
Ovviamente questi pannelli danno solo una simulazione di quello che
verrà fuori.
Solo al momento del "salva" la tabella viene creata fisicamente nel
database.
Questo ci assicura le migliori prestazioni e la massima flessibilità
(stiamo parlando di tabelle utente da milionate di record... solo per
dirne una, presso un nostro cliente c'è una tabella che giornalmente
riceve quasi 30 milioni di record).
Nicola

 riceve quasi 30 milioni di record).
Può funzionare, anche se a me non piace eccessivamente.
E' evidente che le performance saranno elevate, però occorre scrivere 
le query che vadano sulle tabelle.
Se usi * come elenco campi, hai mille altri problemi (fra cui anche il 
fatto che le performance degradano decisamente).
L'utente può creare nuove colonne, ma anche nuove tabelle. Per me è un 
record da aggiungere in una tabella di testata crosstab, per te è una 
tabella da creare fisicamente (poco male, comunque).
La storicizzazione è un altro punto dolente. Io posso oggi eliminare 
colonne da una tabella 'virtuale' (intendo con questa la tabella 
risultato del crosstab), semplicemente marcando come eliminato il 
record della struttura. Ma i record coi valori restano. Se domattina 
rimetto la colonna nella struttura, le colonne riappaiono valorizzate.
Infine, meraviglia delle meraviglie, posso avere tabelle virtuali 
diverse, con colonne prese da altre tabella virtuali, posso cioè 
comporre tabelle crosstab prelevando colonne da una o dall'altra 
tabella, semplicemente spostando le strutture, a caldo. Inserisco un 
dato in una tabella e questo dato appare anche in un'altra tabella, 
senza dover inserire trigger.
Ovvio che tutto questo si paga con una complessità 'significativa' 
delle procedure di estrazione dei dati.
Ma avere una form che consente di gestire qualunque tabella virtuale, e 
poter cambiare a caldo la struttura dei dati sottostanti (ad esempio 
sul click su una combo cambia la struttura del datagrid, ristrutturando 
la crosstab per una visualizzazione diversa) è una certa soddisfazione.
Comunque è una scelta, per certe situazioni ha un senso, per altre no.
Io mi sto divertendo parecchio, vedere queste cose girare è uno 
spettacolo.
Ciao
Alberto
-- 
Henry David Thoreau: L'uomo è tanto più ricco quanto meno costano i 
suoi piaceri.

 Io mi sto divertendo parecchio, vedere queste cose girare è uno spettacolo.
Ah si, quoto in pieno.
Noi necessitiamo di una bassa storicizzazione (massimo 3 o 4 periodi) e
lavoriamo su dati calcolati giornalmente (al massimo mensilmente).
L'aggiunta o l'eliminazione di un campo implica necessariamente che
venga ricreata l'intera tabella perchè vuol dire che i dati che arrivano
dagli estrattori sono cambiati.
E' questo fondamentalmente il motivo della nostra scelta.
Nicola

Nicola Ottomano ha spiegato il 17/02/2009 :

Però, vedi, noi ad esempio lavoriamo con sistemi di acquisizione 
automatica di dati, da macchinari, sensori, ecc...
La macchina tal dei tali mi dice la temperatura di un pezzo, ad 
esempio. E io lo archivio fra le rilevazioni effettuate.
Adesso arriva una macchina nuova, che mi dice la temperatura e anche 
l'umidità del pezzo.
Ovvio che non posso cambiare la struttura della tabella, aggiungendo la 
colonna 'umidità'. Questo comporterebbe la rottura totale degli indici, 
dovrei inserire un valore di default per il passato, andrei a occupare 
gigabyte di spazio per niente, solo per inserire qualche smilionata di 
'celle' vuote. Oltretutto, magari su cento rilevatori che ho 
nell'impianto, solo uno mi rileva l'umidità, gli altri 99 insistono a 
rilevare solo la temperatura. E se domattina uno degli altri 99 smette 
di rilevare la temperatura e mi rileva il colore del pezzo, dovrei 
aggiungere una terza colonna alla tabella.
Allora aggiungo, nella struttura della tabella virtuale, il fatto che 
esista la colonna umidità e la colonna colore.
Chi inserisce solo la temperatura, scrive questo dato e punto. Chi 
inserisce anche l'umidità, inserisce un secondo record con il valore di 
umidità. Chi inserisce il colore, inserisce questo record.
In fase di estrazione (e solo in questa fase), l'umidità viene 
'affiancata' alla temperatura, cioè visualizzata su una medesima riga, 
quando è un dato disponibile, altrimenti al client arriva un NULL (o un 
valore di coalesce, ad esempio '').
Ciao
Alberto
-- 
Carlo Rubbia: Siamo su un treno che va a trecento chilometri all'ora, 
non sappiamo dove ci sta portando e, soprattutto, ci siamo accorti che 
non c'è il macchinista.

 valore di coalesce, ad esempio '').
Quoto in pieno ed è proprio questo il mio problema. E se tu hai risolto 
con il crosstab mi metto all'opera per studiare il sistema per adattarlo 
alle mie necessità.
Solo una cosa. Tu ha parlato di procedure che fai girare. Questo 
significa che hai creato delle procedure nel tuo database oppure le fai 
girare in programmi esterni? Io vorrei crearle nel mio database e 
restituirle probabilmente come viste o in qualsiasi altra forma 
restituisca la funzione crosstab. Tu hai avuto mai modo di testare le 
prestazioni della funzione all'interno o all'esterno del database?
Grazie ancora x l'aiuto.

 l'umidità del pezzo.
[CUT]
Un'altra domanda... Tu in questa risposta porti ad esempio un sensore 
che aquisisce la temperatura(ad esempio). Mi viene da chiederti se 
parlando di sensore e acquisizione significa che tu i dati del sensore 
stesso li hai archiviati da qualche parte altrimenti l'operatore che 
inserisce la temp non sa a quale sensore fare riferimento. Quindi da 
qualche parte avrai una tabella sensore o strumenti che immagazzina i 
dati del sensore. Altrimenti come fai a rispettare la chiave che lega 
l'elenco sensori con i dati acquisiti?
Grazie.

 all'interno o all'esterno del database?
Non c'è paragone. Quando SqlServer esegue una qualunque cosa sui dati, 
prima di tutto deve determinare il miglior piano di esecuzione e le 
opportune ottimizzazioni. Questo processo richiede tempo. Se invochi 
una query dall'esterno, per SqlServer è una cosa nuova che deve 
analizzare e on the fly decide come eseguirla. Se la incapsuli in una 
stored procedure o in una vista, SqlServer determina il miglior piano 
una volta per tutte, e alla seconda esecuzione è un fulmine, anche 
perchè al termine dell'esecuzione SqlServer non butta via le 
informazioni che ha messo in cache in ram, fintanto che ha ancora Ram 
disponibile. Quindi, alla seconda esecuzione, spesso tutto avviene in 
ram senza accesso ai dischi. Fai un po' te...
Alberto
-- 
Thomas Fuller: Ci sono persone che sono state considerate coraggiose 
perchè avevano troppa paura per scappare.

Peca's ha usato la sua tastiera per scrivere :

E' evidente che le 'sorgenti' di informazione sono tabellate.
Se il sensore è accessibile ad esempio in lan, o in seriale, o in altro 
modo, ci sarà un servizio NT che su richiesta o su timer va ad 
interrogare il sensore, riceve il dato rilevato, e lo invia al dbms 
taggando il dato con l'id della sorgente (il sensore). Il dbms lo 
archivia in una tabella raw, che poi viene spazzolata da procedure di 
consuntivazione, validazione, accorpamento, ecc..., per generare le 
tabelle storiche con la granularità desiderata.
Se il dato viene invece inserito da un operatore, al posto del servizio 
NT in background ci sarà un client, da qualche parte, dove l'operatore 
scrive il valore rilevato, e il client invierà il dato al dbms 
invocando la stessa stored procedure invocata dal servizio NT di prima, 
semplicemente taggando il dato in modo da indicare da dove arriva...
Alberto
-- 
Friedrich Nietzsche: Un po' di salute ogni tanto è il miglior rimedio 
per l'ammalato.