Ruota su più colonne usando Tablefunc

Qualcuno ha tablefunc usato tablefunc per ruotare su più variabili invece di usare solo il nome della riga ? Le note della documentazione :

Le colonne “extra” dovrebbero essere le stesse per tutte le righe con lo stesso valore row_name.

Non sono sicuro di come farlo senza combinare le colonne su cui voglio girare (che dubito fortemente mi darà la velocità di cui ho bisogno). Un modo ansible per farlo sarebbe quello di rendere l’ quadro numerica e aggiungerla al local come millisecondi, ma questo sembra un modo tremolante di procedere.

Ho modificato i dati utilizzati in risposta a questa domanda: Query a campi incrociati di PostgreSQL .

  CREATE TEMP TABLE t4 ( timeof timestamp ,entity character ,status integer ,ct integer); INSERT INTO t4 VALUES ('2012-01-01', 'a', 1, 1) ,('2012-01-01', 'a', 0, 2) ,('2012-01-02', 'b', 1, 3) ,('2012-01-02', 'c', 0, 4); SELECT * FROM crosstab( 'SELECT timeof, entity, status, ct FROM t4 ORDER BY 1,2,3' ,$$VALUES (1::text), (0::text)$$) AS ct ("Section" timestamp, "Attribute" character, "1" int, "0" int); 

Ritorna:

  Sezione |  Attributo |  1 |  0
 --------------------------- + ----------- + --- + ---
  2012-01-01 00:00:00 |  a |  1 |  2
  2012-01-02 00:00:00 |  b |  3 |  4

Quindi, come afferma la documentazione, si presuppone che la colonna aggiuntiva “Attributo” sia la stessa per ogni nome di riga noto come “Sezione”. Pertanto, riporta b per la seconda riga anche se “entity” ha anche un valore “c” per quel valore “timeof”.

Uscita desiderata:

 Section | Attribute | 1 | 0 --------------------------+-----------+---+--- 2012-01-01 00:00:00 | a | 1 | 2 2012-01-02 00:00:00 | b | 3 | 2012-01-02 00:00:00 | c | | 4 

Qualche idea o riferimento?

Un po ‘più di background: ho potenzialmente bisogno di fare questo per miliardi di righe e sto testando la memorizzazione di questi dati in formati lunghi e ampi e vedendo se posso usare tablefunc per passare dal formato lungo al formato più efficiente rispetto alle normali funzioni di aggregazione .
Avrò circa 100 misurazioni effettuate ogni minuto per circa 300 quadro. Spesso, dovremo confrontare le diverse misurazioni fatte per un dato secondo per una data quadro, quindi dovremo andare al formato ampio molto spesso. Inoltre, le misurazioni effettuate su una particolare quadro sono altamente variabili.

EDIT: ho trovato una risorsa su questo: http://www.postgresonline.com/journal/categories/24-tablefunc .

Il problema con la tua query è che b e c condividono lo stesso timestamp 2012-01-02 00:00:00 , e tu hai il tempo della colonna timestamp prima nella tua query, quindi – anche se hai aggiunto grassetto enfasi – b e c sono solo colonne extra che rientrano nello stesso gruppo 2012-01-02 00:00:00 . Solo il primo ( b ) viene restituito da (citando il manuale) :

La colonna row_name deve essere la prima. Le colonne category e value devono essere le ultime due colonne, in questo ordine. Qualsiasi colonna tra row_name e category viene considerata come “extra”. Le colonne “extra” dovrebbero essere le stesse per tutte le righe con lo stesso valore row_name .

Grassetto enfasi mio.
Basta ripristinare l’ordine delle prime due colonne per rendere l’ entity il nome della riga e funziona come desiderato:

 SELECT * FROM crosstab( 'SELECT entity, timeof, status, ct FROM t4 ORDER BY 1' ,'VALUES (1), (0)') AS ct ( "Attribute" character ,"Section" timestamp ,"status_1" int ,"status_0" int); 

entity deve essere unica, ovviamente.

reiterare

  • row_name prima
  • (facoltativo) colonne successive
  • category (come definito dal secondo parametro) e value ultimo .

Le colonne aggiuntive vengono riempite dalla prima riga di ogni partizione row_name . I valori delle altre righe vengono ignorati, c’è solo una colonna per nome row_name da riempire. In genere quelli sarebbero uguali per ogni riga di uno row_name , ma row_name da te.

Per la diversa configurazione nella risposta :

 SELECT localt, entity , msrmnt01, msrmnt02, msrmnt03, msrmnt04, msrmnt05 -- , more? FROM crosstab( 'SELECT dense_rank() OVER (ORDER BY localt, entity)::int AS row_name , localt, entity -- additional columns , msrmnt, val FROM test -- WHERE ??? -- instead of LIMIT at the end ORDER BY localt, entity, msrmnt -- LIMIT ???' -- instead of LIMIT at the end , $$SELECT generate_series(1,5)$$) -- more? AS ct (row_name int, localt timestamp, entity int , msrmnt01 float8, msrmnt02 float8, msrmnt03 float8, msrmnt04 float8, msrmnt05 float8 -- , more? ) LIMIT 1000 -- ??!! 

Non c’è da meravigliarsi se le query nel tuo test si comportano in modo terribile. La tua configurazione di prova ha 14M righe e tu le elabori tutte prima di buttarne via la maggior parte con LIMIT 1000 . Per un risultato ridotto imposta le condizioni WHERE o LIMIT alla query di origine!

Inoltre, l’array con cui lavori è inutilmente costoso. Genero invece un nome di riga surrogato con dense_rank ().

db <> fiddle qui – con una configurazione di prova più semplice e meno righe.

Nella mia domanda iniziale avrei dovuto usarlo per i miei dati di esempio:

 CREATE TEMP TABLE t4 ( timeof date ,entity integer ,status integer ,ct integer); INSERT INTO t4 VALUES ('2012-01-01', 1, 1, 1) ,('2012-01-01', 1, 0, 2) ,('2012-01-01', 3, 0, 3) ,('2012-01-02', 2, 1, 4) ,('2012-01-02', 3, 1, 5) ,('2012-01-02', 3, 0, 6); 

Con questo devo fare perno sul tempo e sull’ quadro. Poiché tablefunc utilizza solo una colonna per il pivoting, devi trovare un modo per inserire entrambe le dimensioni in quella colonna. ( http://www.postgresonline.com/journal/categories/24-tablefunc ). Sono andato con la matrice, proprio come nell’esempio in quel collegamento.

 SELECT (timestamp 'epoch' + row_name[1] * INTERVAL '1 second')::date as localt, row_name[2] As entity, status1, status0 FROM crosstab('SELECT ARRAY[extract(epoch from timeof), entity] as row_name, status, ct FROM t4 ORDER BY timeof, entity, status' ,$$VALUES (1::text), (0::text)$$) as ct (row_name integer[], status1 int, status0 int) 

FWIW, ho provato a usare un array di caratteri e finora sembra che questo sia più veloce per il mio setup; 9.2.3 Postgresql.

Questo è il risultato e l’output desiderato.

 localt | entity | status1 | status0 --------------------------+---------+-------- 2012-01-01 | 1 | 1 | 2 2012-01-01 | 3 | | 3 2012-01-02 | 2 | 4 | 2012-01-02 | 3 | 5 | 6 

Sono curioso di sapere come funziona su un set di dati molto più ampio e riferirò in un secondo momento.

Ok, quindi l’ho eseguito su un tavolo più vicino al mio caso d’uso. O sto sbagliando o crosstab non è adatto per il mio uso.

Per prima cosa ho fatto alcuni dati simili:

 CREATE TABLE public.test ( id serial primary key, msrmnt integer, entity integer, localt timestamp, val double precision ); CREATE INDEX ix_test_msrmnt ON public.test (msrmnt); CREATE INDEX ix_public_test_201201_entity ON public.test (entity); CREATE INDEX ix_public_test_201201_localt ON public.test (localt); insert into public.test (msrmnt, entity, localt, val) select * from( SELECT msrmnt, entity, localt, random() as val FROM generate_series('2012-01-01'::timestamp, '2012-01-01 23:59:00'::timestamp, interval '1 minutes') as localt join (select * FROM generate_series(1, 50, 1) as msrmnt) as msrmnt on 1=1 join (select * FROM generate_series(1, 200, 1) as entity) as entity on 1=1) as data; 

Quindi ho eseguito il codice crosstab un paio di volte:

 explain analyze SELECT (timestamp 'epoch' + row_name[1] * INTERVAL '1 second')::date As localt, row_name[2] as entity ,msrmnt01,msrmnt02,msrmnt03,msrmnt04,msrmnt05,msrmnt06,msrmnt07,msrmnt08,msrmnt09,msrmnt10 ,msrmnt11,msrmnt12,msrmnt13,msrmnt14,msrmnt15,msrmnt16,msrmnt17,msrmnt18,msrmnt19,msrmnt20 ,msrmnt21,msrmnt22,msrmnt23,msrmnt24,msrmnt25,msrmnt26,msrmnt27,msrmnt28,msrmnt29,msrmnt30 ,msrmnt31,msrmnt32,msrmnt33,msrmnt34,msrmnt35,msrmnt36,msrmnt37,msrmnt38,msrmnt39,msrmnt40 ,msrmnt41,msrmnt42,msrmnt43,msrmnt44,msrmnt45,msrmnt46,msrmnt47,msrmnt48,msrmnt49,msrmnt50 FROM crosstab('SELECT ARRAY[extract(epoch from localt), entity] as row_name, msrmnt, val FROM public.test ORDER BY localt, entity, msrmnt',$$VALUES ( 1::text),( 2::text),( 3::text),( 4::text),( 5::text),( 6::text),( 7::text),( 8::text),( 9::text),(10::text) ,(11::text),(12::text),(13::text),(14::text),(15::text),(16::text),(17::text),(18::text),(19::text),(20::text) ,(21::text),(22::text),(23::text),(24::text),(25::text),(26::text),(27::text),(28::text),(29::text),(30::text) ,(31::text),(32::text),(33::text),(34::text),(35::text),(36::text),(37::text),(38::text),(39::text),(40::text) ,(41::text),(42::text),(43::text),(44::text),(45::text),(46::text),(47::text),(48::text),(49::text),(50::text)$$) as ct (row_name integer[],msrmnt01 double precision, msrmnt02 double precision,msrmnt03 double precision, msrmnt04 double precision,msrmnt05 double precision, msrmnt06 double precision,msrmnt07 double precision, msrmnt08 double precision,msrmnt09 double precision, msrmnt10 double precision ,msrmnt11 double precision, msrmnt12 double precision,msrmnt13 double precision, msrmnt14 double precision,msrmnt15 double precision, msrmnt16 double precision,msrmnt17 double precision, msrmnt18 double precision,msrmnt19 double precision, msrmnt20 double precision ,msrmnt21 double precision, msrmnt22 double precision,msrmnt23 double precision, msrmnt24 double precision,msrmnt25 double precision, msrmnt26 double precision,msrmnt27 double precision, msrmnt28 double precision,msrmnt29 double precision, msrmnt30 double precision ,msrmnt31 double precision, msrmnt32 double precision,msrmnt33 double precision, msrmnt34 double precision,msrmnt35 double precision, msrmnt36 double precision,msrmnt37 double precision, msrmnt38 double precision,msrmnt39 double precision, msrmnt40 double precision ,msrmnt41 double precision, msrmnt42 double precision,msrmnt43 double precision, msrmnt44 double precision,msrmnt45 double precision, msrmnt46 double precision,msrmnt47 double precision, msrmnt48 double precision,msrmnt49 double precision, msrmnt50 double precision) limit 1000 

Ottenendo questo al terzo tentativo:

 QUERY PLAN Limit (cost=0.00..20.00 rows=1000 width=432) (actual time=110236.673..110237.667 rows=1000 loops=1) -> Function Scan on crosstab ct (cost=0.00..20.00 rows=1000 width=432) (actual time=110236.672..110237.598 rows=1000 loops=1) Total runtime: 110699.598 ms 

Quindi ho eseguito la soluzione standard un paio di volte:

 explain analyze select localt, entity, max(case when msrmnt = 1 then val else null end) as msrmnt01 ,max(case when msrmnt = 2 then val else null end) as msrmnt02 ,max(case when msrmnt = 3 then val else null end) as msrmnt03 ,max(case when msrmnt = 4 then val else null end) as msrmnt04 ,max(case when msrmnt = 5 then val else null end) as msrmnt05 ,max(case when msrmnt = 6 then val else null end) as msrmnt06 ,max(case when msrmnt = 7 then val else null end) as msrmnt07 ,max(case when msrmnt = 8 then val else null end) as msrmnt08 ,max(case when msrmnt = 9 then val else null end) as msrmnt09 ,max(case when msrmnt = 10 then val else null end) as msrmnt10 ,max(case when msrmnt = 11 then val else null end) as msrmnt11 ,max(case when msrmnt = 12 then val else null end) as msrmnt12 ,max(case when msrmnt = 13 then val else null end) as msrmnt13 ,max(case when msrmnt = 14 then val else null end) as msrmnt14 ,max(case when msrmnt = 15 then val else null end) as msrmnt15 ,max(case when msrmnt = 16 then val else null end) as msrmnt16 ,max(case when msrmnt = 17 then val else null end) as msrmnt17 ,max(case when msrmnt = 18 then val else null end) as msrmnt18 ,max(case when msrmnt = 19 then val else null end) as msrmnt19 ,max(case when msrmnt = 20 then val else null end) as msrmnt20 ,max(case when msrmnt = 21 then val else null end) as msrmnt21 ,max(case when msrmnt = 22 then val else null end) as msrmnt22 ,max(case when msrmnt = 23 then val else null end) as msrmnt23 ,max(case when msrmnt = 24 then val else null end) as msrmnt24 ,max(case when msrmnt = 25 then val else null end) as msrmnt25 ,max(case when msrmnt = 26 then val else null end) as msrmnt26 ,max(case when msrmnt = 27 then val else null end) as msrmnt27 ,max(case when msrmnt = 28 then val else null end) as msrmnt28 ,max(case when msrmnt = 29 then val else null end) as msrmnt29 ,max(case when msrmnt = 30 then val else null end) as msrmnt30 ,max(case when msrmnt = 31 then val else null end) as msrmnt31 ,max(case when msrmnt = 32 then val else null end) as msrmnt32 ,max(case when msrmnt = 33 then val else null end) as msrmnt33 ,max(case when msrmnt = 34 then val else null end) as msrmnt34 ,max(case when msrmnt = 35 then val else null end) as msrmnt35 ,max(case when msrmnt = 36 then val else null end) as msrmnt36 ,max(case when msrmnt = 37 then val else null end) as msrmnt37 ,max(case when msrmnt = 38 then val else null end) as msrmnt38 ,max(case when msrmnt = 39 then val else null end) as msrmnt39 ,max(case when msrmnt = 40 then val else null end) as msrmnt40 ,max(case when msrmnt = 41 then val else null end) as msrmnt41 ,max(case when msrmnt = 42 then val else null end) as msrmnt42 ,max(case when msrmnt = 43 then val else null end) as msrmnt43 ,max(case when msrmnt = 44 then val else null end) as msrmnt44 ,max(case when msrmnt = 45 then val else null end) as msrmnt45 ,max(case when msrmnt = 46 then val else null end) as msrmnt46 ,max(case when msrmnt = 47 then val else null end) as msrmnt47 ,max(case when msrmnt = 48 then val else null end) as msrmnt48 ,max(case when msrmnt = 49 then val else null end) as msrmnt49 ,max(case when msrmnt = 50 then val else null end) as msrmnt50 from sample group by localt, entity limit 1000 

Ottenendo questo al terzo tentativo:

 QUERY PLAN Limit (cost=2257339.69..2270224.77 rows=1000 width=24) (actual time=19795.984..20090.626 rows=1000 loops=1) -> GroupAggregate (cost=2257339.69..5968242.35 rows=288000 width=24) (actual time=19795.983..20090.496 rows=1000 loops=1) -> Sort (cost=2257339.69..2293339.91 rows=14400088 width=24) (actual time=19795.626..19808.820 rows=50001 loops=1) Sort Key: localt Sort Method: external merge Disk: 478568kB -> Seq Scan on sample (cost=0.00..249883.88 rows=14400088 width=24) (actual time=0.013..2245.247 rows=14400000 loops=1) Total runtime: 20197.565 ms 

Quindi, nel mio caso, sembra così lontano che la tabella incrociata non è una soluzione. E questo è solo un giorno in cui avrò più anni. In effetti, probabilmente dovrò andare con tabelle di grande formato (non normalizzate), nonostante il fatto che le misure siano fatte per le quadro è variabile e ne vengono introdotte di nuove, ma non ne parlerò qui.

Ecco alcune delle mie impostazioni che utilizzano Postgres 9.2.3:

 name setting max_connections 100 shared_buffers 2097152 effective_cache_size 6291456 maintenance_work_mem 1048576 work_mem 262144