Dynamiczna alternatywa dla Pivota z CASE I GROUP BY

Jeśli nie zainstalowałeś dodatkowego modułu tablefunc, uruchom to polecenie raz w bazie danych:

CREATE EXTENSION tablefunc;

Odpowiedź na pytanie

Bardzo podstawowe rozwiązanie crosstab dla Twojego przypadku:

SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- more columns?

Specjalna trudność oto, że nie ma kategorii (cat) w tabeli zasadniczej. Dla podstawowej 1-parametrowej postaci {[51] } możemy po prostu podać atrapę kolumny z atrapą wartości służącą jako kategoria. Wartość jest i tak ignorowane.

Jest to jeden z rzadkich przypadków , gdzie drugi parametr dla funkcji crosstab() jest niepotrzebny , ponieważ wszystkie wartości NULL pojawiają się tylko w wiszących kolumnach po prawej stronie według definicji tego problemu. A kolejność może być określona przez wartość .

Gdybyśmy mieli rzeczywistą kategorię kolumnę z nazwami określającymi kolejność wartości w wyniku, potrzebowalibyśmy 2-parametrowej postaci z crosstab(). Proszę. Generuję kolumnę kategorii przy pomocy funkcji windowrow_number(), do bazy crosstab() na:

SELECT * FROM crosstab(
   $$
   SELECT bar, val, feh
   FROM  (
      SELECT *, 'val' || row_number() OVER (PARTITION BY bar ORDER BY feh) AS val
      FROM tbl_org
      ) x
   ORDER BY 1, 2
   $$
 , $$VALUES ('val1'), ('val2'), ('val3')$$         -- more columns?
) AS ct (bar text, val1 int, val2 int, val3 int);  -- more columns?

Reszta jest dość prosta. Znajdź więcej wyjaśnień i linków w tych ściśle powiązanych odpowiedziach.

podstawy:
_{przeczytaj to najpierw, jeśli nie znasz funkcji crosstab()!}

• PostgreSQL Crosstab Query

zaawansowane:

• Pivot na wielu kolumnach za pomocą Tablefunc
• połączenie tabeli i dziennika zmian w widoku w PostgreSQL

Właściwa konfiguracja testu

W ten sposób powinieneś dostarczyć test case na początek:

CREATE TEMP TABLE tbl_org (id int, feh int, bar text);
INSERT INTO tbl_org (id, feh, bar) VALUES
   (1, 10, 'A')
 , (2, 20, 'A')
 , (3,  3, 'B')
 , (4,  4, 'B')
 , (5,  5, 'C')
 , (6,  6, 'D')
 , (7,  7, 'D')
 , (8,  8, 'D');

Dynamiczny crosstab?

Niezbyt dynamiczne , Jeszcze, jak @ Clodoaldo skomentował . Dynamiczne typy zwrotów są trudne do osiągnięcia dzięki plpgsql. Ale istnieją sposoby obejścia tego - z pewnymi ograniczeniami .

Aby nie komplikować reszty, demonstruję z prostsze przypadek testowy:

CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
   ('A', 'val1', 10)
 , ('A', 'val2', 20)
 , ('B', 'val1', 3)
 , ('B', 'val2', 4)
 , ('C', 'val1', 5)
 , ('D', 'val3', 8)
 , ('D', 'val1', 6)
 , ('D', 'val2', 7);

Wywołanie:

SELECT * FROM crosstab('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2')
AS ct (row_name text, val1 int, val2 int, val3 int);

Zwraca:

 row_name | val1 | val2 | val3
----------+------+------+------
 A        | 10   | 20   |
 B        |  3   |  4   |
 C        |  5   |      |
 D        |  6   |  7   |  8

Wbudowana funkcja modułu tablefunc

Moduł tablefunc zapewnia prostą infrastrukturę dla ogólnych wywołań crosstab() bez dostarczania listy definicji kolumn. Szereg funkcji napisanych w C (zazwyczaj bardzo szybko):

crosstabN()

crosstab1() - crosstab4() są predefiniowane. Jedna drobna uwaga: wymagają i zwracają wszystkie text. Więc musimy rzucić nasze integer wartości. Ale upraszcza wywołanie:

SELECT * FROM crosstab4('SELECT row_name, attrib, val::text  -- cast!
                         FROM tbl ORDER BY 1,2')

Wynik:

 row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
 A        | 10         | 20         |            |
 B        | 3          | 4          |            |
 C        | 5          |            |            |
 D        | 6          | 7          | 8          |

Custom crosstab() function

Dla więcej kolumn lub inne typy danych, tworzymy własny typ złożony i funkcja (raz).
"Type": "content"]}

CREATE TYPE tablefunc_crosstab_int_5 AS (
  row_name text, val1 int, val2 int, val3 int, val4 int, val5 int);

Funkcja:

CREATE OR REPLACE FUNCTION crosstab_int_5(text)
  RETURNS SETOF tablefunc_crosstab_int_5
AS '$libdir/tablefunc', 'crosstab' LANGUAGE c STABLE STRICT;

Wywołanie:

SELECT * FROM crosstab_int_5('SELECT row_name, attrib, val   -- no cast!
                              FROM tbl ORDER BY 1,2');

Wynik:

 row_name | val1 | val2 | val3 | val4 | val5
----------+------+------+------+------+------
 A        |   10 |   20 |      |      |
 B        |    3 |    4 |      |      |
 C        |    5 |      |      |      |
 D        |    6 |    7 |    8 |      |

jedna polimorficzna, dynamiczna funkcja dla wszystkich

to wykracza poza to, co obejmuje moduł tablefunc.
Do dynamicznego typu zwracanego używam typu polimorficznego z techniką opisaną w tej pokrewnej odpowiedzi:

• Refactor a funkcja PL / pgSQL zwracająca wyjście różnych SELECT queries

Forma 1-parametrowa:

CREATE OR REPLACE FUNCTION crosstab_n(_qry text, _rowtype anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
   (SELECT format('SELECT * FROM crosstab(%L) t(%s)'
                , _qry
                , string_agg(quote_ident(attname) || ' ' || atttypid::regtype
                           , ', ' ORDER BY attnum))
    FROM   pg_attribute
    WHERE  attrelid = pg_typeof(_rowtype)::text::regclass
    AND    attnum > 0
    AND    NOT attisdropped);
END
$func$  LANGUAGE plpgsql;

Przeciążenie tym wariantem dla postaci 2-parametrowej:

CREATE OR REPLACE FUNCTION crosstab_n(_qry text, _cat_qry text, _rowtype anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
   (SELECT format('SELECT * FROM crosstab(%L, %L) t(%s)'
                , _qry, _cat_qry
                , string_agg(quote_ident(attname) || ' ' || atttypid::regtype
                           , ', ' ORDER BY attnum))
    FROM   pg_attribute
    WHERE  attrelid = pg_typeof(_rowtype)::text::regclass
    AND    attnum > 0
    AND    NOT attisdropped);
END
$func$  LANGUAGE plpgsql;

pg_typeof(_rowtype)::text::regclass: dla każdego zdefiniowanego przez użytkownika typu kompozytowego zdefiniowany jest typ wiersza, dzięki czemu atrybuty (kolumny) są wymienione w katalogu systemowympg_attribute. Szybki pas, aby go zdobyć: rzuć zarejestrowany Typ (regtype) na text i rzuć ten text na regclass.

Tworzenie typów złożonych raz:

Musisz zdefiniować po każdym zwracanym typie:

CREATE TYPE tablefunc_crosstab_int_3 AS (
    row_name text, val1 int, val2 int, val3 int);

CREATE TYPE tablefunc_crosstab_int_4 AS (
    row_name text, val1 int, val2 int, val3 int, val4 int);

...

Dla wywołań ad-hoc, możesz również utworzyć tymczasową tabelę z tym samym (tymczasowym) efektem:

CREATE TEMP TABLE temp_xtype7 AS (
    row_name text, x1 int, x2 int, x3 int, x4 int, x5 int, x6 int, x7 int);

Lub użyj typu istniejącej tabeli, widoku lub widoku zmaterializowanego, jeśli jest dostępny.

Call

Użycie powyższych typów wiersza:

Forma 1-parametru (brak brakujących wartości):

SELECT * FROM crosstab_n(
   'SELECT row_name, attrib, val FROM tbl ORDER BY 1,2'
 , NULL::tablefunc_crosstab_int_3);

Forma 2-parametrowa (niektórych wartości może brakować):

SELECT * FROM crosstab_n(
   'SELECT row_name, attrib, val FROM tbl ORDER BY 1'
 , $$VALUES ('val1'), ('val2'), ('val3')$$
 , NULL::tablefunc_crosstab_int_3);

To jedna funkcja działa dla wszystkich typów zwrotów, podczas gdy framework crosstabN() dostarczany przez moduł tablefunc wymaga osobnej funkcji dla każdego z nich.
Jeśli nazwałeś swoje typy w kolejności, jak pokazano powyżej, musisz tylko zastąpić pogrubioną liczbę. Aby znaleźć maksymalną liczbę kategorii w tabeli bazowej:

SELECT max(count(*)) OVER () FROM tbl  -- returns 3
GROUP  BY row_name
LIMIT  1;

To jest tak dynamiczne, jak to się robi, jeśli chcesz pojedynczych kolumn. Tablice jak pokazane przez @ Clocoaldo lub prostą reprezentację tekstu lub wynik zawinięte w typ dokumentu, taki jak json lub hstore, mogą działać dynamicznie dla dowolnej liczby kategorii.

Zastrzeżenie:
Zawsze jest to potencjalnie niebezpieczne, gdy dane wejściowe użytkownika są konwertowane na kod. Upewnij się, że nie można tego użyć do SQL injection. Nie Akceptuj danych wejściowych od niezaufanych użytkowników (bezpośrednio).

Wywołanie oryginalnego pytania:

SELECT * FROM crosstab_n('SELECT bar, 1, feh FROM tbl_org ORDER BY 1,2'
                       , NULL::tablefunc_crosstab_int_3);