Pivot sur Plusieurs Colonnes à l'aide de Tablefunc

Quelqu'un a utilisé tablefunc pivoter sur plusieurs variables, par opposition à l'utilisation d' rang nom? Les notes de documentation:

Les colonnes "supplémentaires" sont censés être les mêmes pour toutes les lignes avec la
même row_name valeur.

Je ne suis pas sûr de la façon de le faire sans en combinant les colonnes que je veux pivot (ce dont je doute fortement de me donnera la vitesse j'ai besoin). Une façon possible de le faire serait de faire de l'entité numérique et l'ajouter à la localt comme le nombre de millisecondes, mais cela semble être un fragile façon de procéder.

J'ai édité les données utilisées dans une réponse à cette question: PostgreSQL Requête Analyse Croisée.

 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);

Retourne:

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

De sorte que les états de documentation, la supplémentaire colonne aka "Attribut" est supposé être le même pour chaque rang nom aka "Section". Ainsi, les rapports de b pour la deuxième ligne, même si le "entité" a également un 'c' de la valeur pour que " timeof valeur.

De Sortie Souhaité:

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

Des pensées ou des références?

Un fond un peu plus: j'ai éventuellement besoin de faire cela pour milliards de lignes et je suis en essais hors de stocker ces données en long et en large de formats et de voir si je peux utiliser tablefunc pour aller de long à l'échelle de format plus efficacement qu'avec des régulière des fonctions d'agrégation.

Je vais avoir environ 100 mesures effectuées toutes les minutes environ 300 entités. Souvent, nous avons besoin de comparer les différentes mesures effectuées pour une deuxième pour une entité donnée, donc nous avons besoin d'aller à grand format très souvent. Aussi, les mesures effectuées sur une entité donnée sont très variables.

EDIT: j'ai trouvé une ressource sur ce: http://www.postgresonline.com/journal/categories/24-tablefunc.

+1 Belle question avec un (très apprécié) de travail, cas de test, ce qui démontre clairement le problème. Seule la version de PostgreSQL est manquant. Je suppose actuelle, la version 9.2. Mais la solution est la même pour les deux dernières versions majeures.
La solution est valable que pour ce problème, et je veux dire seulement "ce problème" car il n'est pas valable pour les tableaux croisés dynamiques sur deux index de colonne. Donc, le titre n'est pas vrai, ou la réponse devrait être le deuxième (id=réponse-15559942)

OriginalL'auteur ideamotor | 2013-03-14

  1. 11

    Le problème avec votre requête, c'est que b et c partager le même horodatage 2012-01-02 00:00:00, et vous avez la timestamp colonne timeof en premier dans votre requête, même si vous avez ajouté gras de mettre l'accent sur b et c sont juste des colonnes supplémentaires qui tombent dans le même groupe 2012-01-02 00:00:00. Seul le premier (b) est retourné depuis (citant le manuel):

    La row_name colonne doit être la première. Le category et value les colonnes doivent être les deux dernières colonnes, dans l'ordre. Toutes les colonnes entre row_name et category sont traités comme des "extra". Les colonnes "supplémentaires" sont devrait être la même pour toutes les lignes avec le même row_name valeur.

    Gras c'est moi qui souligne.

    Juste inverser l'ordre des deux premières colonnes de faire entity le rang nom et il fonctionne comme vous le souhaitez:

    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 doit être unique, bien sûr.

    Réitérer

    • row_name première
    • (facultatif) extra colonnes prochaine
    • category (tel que défini par le deuxième paramètre) et value dernier.

    Colonnes supplémentaires sont remplis à partir de la première ligne de chaque row_name partition. Les valeurs des autres lignes sont ignorées, il y a une seule colonne par row_name à remplir. Généralement ceux qui serait le même pour chaque ligne d'un row_name, mais c'est à vous de voir.

    Pour les différents d'installation dans votre réponse:

    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  -- ??!!

    Pas étonnant que les requêtes dans votre test effectuer terriblement. Votre configuration de test a 14M lignes et vous processus tous d'entre eux avant de les jeter à la plupart de loin avec LIMIT 1000. Pour une réduction de l'ensemble de résultats d'ajouter OÙ les conditions ou une LIMITE à la requête source!

    Plus, le tableau vous travaillez avec est inutilement coûteux. - Je générer un substitut à la ligne nom de dense_rank() à la place.

    db<>violon ici - avec un test plus simple de configuration et de moins de lignes.

    Hey Erwin, je vous remercie très beaucoup pour la réponse. Je vais aller de l'avant et de vous accorder un crédit, mais je pense que j'ai laissé dans une certaine ambiguïté dans mon enquête. Je vais poster en arrière quand j'ai le temps.

    OriginalL'auteur Erwin Brandstetter

  2. 11

    Dans ma question de départ, j'aurais utilisé ce pour mon exemple de données:

    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);

    Avec ce que j'ai à pivot sur les deux timeof et de l'entité. Depuis tablefunc utilise uniquement une colonne pour les faire pivoter, vous avez besoin de trouver une façon de remplir les deux dimensions dans cette colonne. (http://www.postgresonline.com/journal/categories/24-tablefunc). Je suis allé avec le tableau, tout comme l'exemple dans ce lien.

    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, j'ai essayé à l'aide d'un tableau de caractères et jusqu'à présent, on dirait que c'est plus rapide pour ma configuration; 9.2.3 Postgresql.

    C'est le résultat et la sortie désirée.

    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

    Je suis curieux de voir comment cette effectue sur une plus vaste ensemble de données et fera rapport à une date ultérieure.

    Très intéressante. Je pense que ce doit être marqué comme réponse valable. Le premier maintenant stackoverflow.com/questions/15415446/... pas de répondre à la question, car des entités avec des valeurs dans le même temps, sera ignoré.

    OriginalL'auteur ideamotor

  3. 1

    Ok, alors j'ai couru sur une table de plus en plus de mon cas d'utilisation. Soit je suis en train de faire mal ou de tableau croisé n'est pas adapté pour mon utilisation.

    J'ai d'abord fait quelques données similaires:

    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;

    Ensuite, j'ai couru le tableau croisé code une couple de fois:

    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

    L'obtention de cette sur la troisième tentative:

    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

    Ensuite, j'ai couru la solution standard d'un couple de fois:

    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

    L'obtention de cette sur la troisième tentative:

    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

    Donc, pour mon cas, il semble que le tableau croisé est pas une solution. Et ce n'est qu'un jour, quand j'aurai plusieurs années. En fait, je vais sans doute aller avec les grand format (non normalisé) tables, malgré le fait que les mesures pour les entités est variable, et de nouveaux sont mis en place, mais je ne vais pas entrer dans les ici.

    Voici certains de mes paramètres à l'aide de Postgres 9.2.3:

    name                    setting
max_connections             100
shared_buffers          2097152
effective_cache_size    6291456
maintenance_work_mem    1048576
work_mem                 262144
    Juste trébuché à travers le présent. N'a jamais eu d'avis. J'ai ajouté une solution à ma réponse. Trop tard maintenant, mais j'ai été un lien vers cette question et je ne voulais pas laisser passer ça sans réponse.

    OriginalL'auteur ideamotor