Transposer les lignes et les colonnes (un.k.un. pivot) uniquement avec un minimum de COUNT()?

CASE

Si votre cas est simple, comme l'a démontré, un CASE déclaration:

SELECT year
     , sum(CASE WHEN animal = 'kittens' THEN price END) AS kittens
     , sum(CASE WHEN animal = 'puppies' THEN price END) AS puppies
FROM  (
   SELECT year, animal, avg(price) AS price
   FROM   tab_test
   GROUP  BY year, animal
   HAVING count(*) > 2
   ) t
GROUP  BY year
ORDER  BY year;

N'a pas d'importance si vous utilisez sum(), max() ou min() comme fonction d'agrégation dans la requête externe. Ils ont tous la même valeur dans ce cas.

SQL Violon

crosstab()

Avec plus de catégories, il sera plus simple avec un crosstab() requête. Cela devrait également être plus rapide pour les grandes tables.

Vous devez installer le module supplémentaire tablefunc (une fois par base de données). Depuis Postgres 9.1, c'est aussi simple que:

CREATE EXTENSION tablefunc;

Détails dans cette réponse:

• PostgreSQL Requête Analyse Croisée

SELECT * FROM crosstab(
      'SELECT year, animal, avg(price) AS price
       FROM   tab_test
       GROUP  BY animal, year
       HAVING count(*) > 2
       ORDER  BY 1,2'

      ,$$VALUES ('kittens'::text), ('puppies')$$)
AS ct ("year" text, "kittens" numeric, "puppies" numeric);

Pas sqlfiddle pour celui-ci car le site ne permet pas de modules supplémentaires.

De référence

Pour vérifier mes demandes, j'ai couru un rapide test avec près de réel données dans ma petite base de données de test. PostgreSQL 9.1.6. Test avec EXPLAIN ANALYZE, mieux de 10:

Configuration de Test avec 10020 lignes:

CREATE TABLE tab_test (year int, animal text, price numeric);

-- years with lots of rows
INSERT INTO tab_test
SELECT 2000 + ((g + random() * 300))::int/1000 
     , CASE WHEN (g + (random() * 1.5)::int) %2 = 0 THEN 'kittens' ELSE 'puppies' END
     , (random() * 200)::numeric
FROM   generate_series(1,10000) g;

-- .. and some years with only few rows to include cases with count < 3
INSERT INTO tab_test
SELECT 2010 + ((g + random() * 10))::int/2
     , CASE WHEN (g + (random() * 1.5)::int) %2 = 0 THEN 'kittens' ELSE 'puppies' END
     , (random() * 200)::numeric
FROM   generate_series(1,20) g;

Résultats:

@bluefeet

Total runtime: 95.401 ms

@wildplasser (des résultats différents, comprend des lignes avec count <= 3)

Total runtime: 64.497 ms

@Andreiy (+ ORDER BY)

& @Erwin1 - CASE (à la fois effectuer sur le même)

Total runtime: 39.105 ms

@Erwin2 - crosstab()

Total runtime: 17.644 ms

Largement proportionnel (mais peu importe), les résultats avec seulement 20 lignes. Seul @wildplasser du CCE a plus de frais généraux et des pointes un peu.

Avec plus d'une poignée de lignes, crosstab() rapidement prend la tête.
@Andreiy de la requête effectue sur la même que ma version simplifiée, la fonction d'agrégation à l'extérieur de la SELECT (min(), max(), sum()) ne fait pas de différence mesurable (juste deux lignes par groupe).

Tout comme prévu, pas de surprises, prendre ma configuration et l'essayer @home.

OriginalL'auteur Erwin Brandstetter

CREATE TABLE pussyriot(year INTEGER NOT NULL
        , animal varchar
        , price integer
        );

INSERT INTO pussyriot(year , animal , price ) VALUES
 (2000, 'kittens', 79)
, (2000, 'kittens', 93)
...
, (2007, 'puppies', 81)
, (2007, 'puppies', 38)
        ;

-- a self join is a poor man's pivot:
WITH cal AS ( -- generate calendar file
        SELECT generate_series(MIN(pr.year) , MAX(pr.year)) AS year
        FROM pussyriot pr
        )
, fur AS (
        SELECT distinct year, animal, AVG(price) AS price
        FROM pussyriot
        GROUP BY year, animal
        -- UPDATE: added next line
        HAVING COUNT(*) >= 3
        )
SELECT cal.year
        , pussy.price AS price_of_the_pussy
        , puppy.price AS price_of_the_puppy
FROM cal
LEFT JOIN fur pussy ON pussy.year=cal.year AND pussy.animal='kittens'
LEFT JOIN fur puppy ON puppy.year=cal.year AND puppy.animal='puppies'
        ;

OriginalL'auteur wildplasser