Transponieren von Zeilen und Spalten (eine.k.ein. pivot) nur mit einem minimum COUNT()?

CASE

Wenn Ihr Fall ist so einfach, wie gezeigt, eine CASE Anweisung tun:

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;

Egal, ob Sie sum(), max() oder min() als Aggregat-Funktion in der äußeren Abfrage. Sie alle führen den gleichen Wert in diesem Fall.

SQL Fiddle

crosstab()

Mit mehr Kategorien wird es einfacher sein mit einem crosstab() Abfrage. Dies sollte auch schneller für größere Tabellen.

Zu installieren, müssen Sie das zusätzliche Modul tablefunc (einmal pro Datenbank). Da Postgres 9.1, das so einfach wie:

CREATE EXTENSION tablefunc;

Details in diesem Zusammenhang zu beantworten:

• PostgreSQL Kreuztabelle Abfrage

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

Kein sqlfiddle für diesen einen, weil die Website nicht ermöglichen zusätzliche Module.

Benchmark

Zu überprüfen, meine Forderungen, ich lief ein schnelles benchmark mit der Nähe zu den realen Daten in meinem kleinen test-Datenbank. PostgreSQL 9.1.6. Test mit EXPLAIN ANALYZE am besten von 10:

Test-setup mit 10020 Zeilen:

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;

Ergebnisse:

@bluefeet

Gesamt-Laufzeit: 95.401 ms

@wildplasser (unterschiedliche Ergebnisse, enthält Zeilen mit count <= 3)

Gesamt-Laufzeit: 64.497 ms

@Andreiy (+ ORDER BY)

& @Erwin1 - CASE (führen beide zu den gleichen)

Gesamt-Laufzeit: 39.105 ms

@Erwin2 - crosstab()

Gesamt-Laufzeit: 17.644 ms

Weitgehend proportional (aber irrelevante) Ergebnisse mit nur 20 Zeilen. Nur @wildplasser CTE hat mehr Verwaltungsaufwand und-Spitzen ein wenig.

Mit mehr als eine Handvoll Zeilen, crosstab() schnell nimmt führen.
@Andreiy die Abfrage ausführt, ungefähr das gleiche wie meine vereinfachte version, die Aggregat-Funktion in der äußeren SELECT (min(), max(), sum()) macht keinen messbaren Unterschied (nur zwei Zeilen pro Gruppe).

Alles wie erwartet, keine überraschungen, nimm mein setup und versuchen Sie es @home.

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