sql: combinare e trasformare tabelle

[ZeroUno]

Salve.

Ho un database mysql con una tabella che ha questi tre campi:
timestamp
name
values

la chiave primaria è timestamp,name

cioè per ogni timestamp ci possono essere più valori ognuno con nome diverso.

Visto che il numero di nomi è limitato io vorrei costruire, a partire da quella, una del tipo

timestamp,nome1,nome2,value

per esempio

timestamp;name;values
1234;X;10
1234;Y;11
1235;X;12
1235;Y;13
1236;X;14
1236;Y;15

diventerebbe

timestamp;X;Y
1234;10;11
1235;12;13
1236;14;15


Questo in tempo reale, cioè a "query time" perchè la tabella originale è popolata in continuazione ogni 5 minuti.

Pensavo di fare questo a livello di query invece che dal programma.

E' possibile?



Inoltre (applicato sempre sulle stessa tabella, ma sono due problemi diversi) dal momento che questa tabella può arrivare ad avere molte righe, tale tabella è suddivisa in più tabelle con gli stessi campi e del tutto uguali, ma ognuna mantiene solo qualche ora di dati, poi ne viene fatta un'altra. La media è di 2 milioni di righe l'una (c.a. 60MB)

Quando devo prelevare i dati da un arco temporale che scavalca quell'orario devo fare la query su più tabelle e poi unire i dati.

E' possibile anche quì far fare una unica query in cui gli dico di prendere i dati da una concatenazione di più tabelle?
Ho visto la direttiva 'UNION' che sembra fare al caso mio, ma mi chiedo se conviene fare
select * from tabella1 where condizione
union
select * from tabella2 where condizione
o, visto che la condizione è la stessa,
select * from tabella1
union
select * from tabella2
e dare questa in pasto ad una where (ma non so come si fa).

notare che a volte aggiungo anche formule invece di * e/o funzioni di aggregazione con group by. Per le prime posso operare su singola tabella e dopo fare la union, per la group by talvolta devo farlo su range che potrebbero abbracciare le due tabelle, tipo sum(value)...group by name o qualcosa di simile, da applicare sull'intero dataset.

Non escludo poi il mischiare le due problematiche (compattare le tabelle come spiegato al primo punto e combinarle come al secondo o viceversa).

a tutto potrebbe unirsi qualche inner join con altre tabelle.


Sto pretendendo troppo?

p.s.: preferisco un accesso readonly al db, quindi escluderei la creazione di store procedure ecc..., visto che il database è popolato da software di terze parti.

[ZeroUno]

wow... sono riuscito a combinare!!!

a tutto - per capire la query che ho fatto - va aggiunto che il popolamento delle tabelle viene effettuato in tempi totali di diversi secondi, per cui il primo valore può essere caricato alle 00:00:00 e l'ultimo magari alle 00:00:30, ma va considerato come lo stesso timestamp (il caricamento avviene ogni 5 minuti)

Codice: Seleziona tutto

select * from
(
select
  tt1.ts,tt1.value as v_6588,tt2.value as v_6602
from 
(
     select variable,round(timestamp/60)*60 as ts,value from cache_group_0_1441929600 where variable=6588
  union
     select variable,round(timestamp/60)*60 as ts,value from cache_group_0_1441951200 where variable=6588
) as tt1
inner join
(
     select variable,round(timestamp/60)*60 as ts,value from cache_group_0_1441929600 where variable=6602
  union
     select variable,round(timestamp/60)*60 as ts,value from cache_group_0_1441951200 where variable=6602
) as tt2
on tt1.ts=tt2.ts
) as combined
where ts>1441964580

ora vi chiedo se si può ottimizzare visto che le variabili da combinare (quelle che prima ho chiamato il campo 'name') sono tante.
In verità sono più di 20.000 ma non ne utilizzerò più di 10 in contemporanea credo.

Stavo pensando di fare un altro database volatile (forse sqlite così ne posso fare semplicemente uno per ogni tipologia) di appoggio per la presentazione dei risultati (che comunque dovrò esportare in csv) che mi faccia da cache, da popolare in modalità batch invece che realtime, in quanto poi sulla tabella finale è possibile che debba fare aggregazioni, somme ecc ecc..

[targzeta]

Codice: Seleziona tutto

SELECT * FROM test;
+-----------+------+-------+
| timestamp | name | value |
+-----------+------+-------+
|      1234 | X    |    10 |
|      1234 | Y    |    11 |
|      1235 | X    |    12 |
|      1235 | Y    |    13 |
|      1236 | X    |    14 |
|      1236 | Y    |    15 |
+-----------+------+-------+
6 rows in set (0.00 sec)

Premesso che:

• i valori siano interi;
• la chiave è (timestamp, name);

questo è un trucchetto che ti permette di fare quello che vuoi:

Codice: Seleziona tutto

SELECT timestamp, SUM(X) AS X, SUM(Y) AS Y FROM (SELECT timestamp, value AS X, 0 AS Y FROM test WHERE name='X' UNION SELECT timestamp, 0 AS X, value AS Y FROM test WHERE name='Y') AS _ GROUP BY timestamp;
+-----------+------+------+
| timestamp | X    | Y    |
+-----------+------+------+
|      1234 |   10 |   11 |
|      1235 |   12 |   13 |
|      1236 |   14 |   15 |
+-----------+------+------+
3 rows in set (0.01 sec)

La prima tabella tira fuori solo i valori delle X mentre per le Y tira fuori degli 0, la seconda fa il contrario, X=0 e Y al loro valore. La tabella intermedia sarebbe:

Codice: Seleziona tutto

SELECT timestamp, value AS X, 0 AS Y FROM test WHERE name='X' UNION SELECT timestamp, 0 AS X, value AS Y FROM test WHERE name='Y';
+-----------+------+------+
| timestamp | X    | Y    |
+-----------+------+------+
|      1234 |   10 |    0 |
|      1235 |   12 |    0 |
|      1236 |   14 |    0 |
|      1234 |    0 |   11 |
|      1235 |    0 |   13 |
|      1236 |    0 |   15 |
+-----------+------+------+
6 rows in set (0.00 sec)

quindi raggruppando per timestamp e sommando i valori, ottieni quello che vuoi...credo .

Emanuele