Jak wygenerować przestawne połączenie krzyżowe, gdy wynikowa definicja tabeli jest nieznana?

Biorąc pod uwagę dwie tabele z nieokreśloną liczbą wierszy z nazwą i wartością, w jaki sposób wyświetlać funkcję obrotową CROSS JOINfunkcji nad ich wartościami.

CREATE TEMP TABLE foo AS
SELECT x::text AS name, x::int
FROM generate_series(1,10) AS t(x);

CREATE TEMP TABLE bar AS
SELECT x::text AS name, x::int
FROM generate_series(1,5) AS t(x);

Na przykład, jeśli tą funkcją byłoby mnożenie, jak wygenerowałbym tabelę (mnożenie) taką jak ta poniżej,

Wszystkie te (arg1,arg2,result)wiersze można wygenerować za pomocą

SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x AS result
FROM foo
CROSS JOIN bar; 

Więc to jest tylko kwestia prezentacji, chciałbym, aby to również działało z niestandardową nazwą - nazwą, która nie jest po prostu argumentem CASTedytowanym w tekście, ale ustawionym w tabeli,

CREATE TEMP TABLE foo AS
SELECT chr(x+64) AS name, x::int
FROM generate_series(1,10) AS t(x);

CREATE TEMP TABLE bar AS
SELECT chr(x+72) AS name, x::int
FROM generate_series(1,5) AS t(x);

Myślę, że byłoby to łatwe do wykonania dzięki CROSSTAB zdolnemu do dynamicznego typu powrotu.

SELECT * FROM crosstab(
  '
    SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x
    FROM foo
    CROSS JOIN bar
  ', 'SELECT DISTINCT name FROM bar'
) AS **MAGIC**

Ale bez tego **MAGIC**rozumiem

ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM crosstab(

Dla porównania, używając powyższych przykładów z nazwami, jest to coś więcej niż to tablefunc, czego crosstab()chce.

SELECT * FROM crosstab(
  '
    SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x
    FROM foo
    CROSS JOIN bar
  '
) AS t(row int, i int, j int, k int, l int, m int);

Ale teraz wróciliśmy do przyjmowania założeń dotyczących zawartości i wielkości bartabeli w naszym przykładzie. Więc jeśli,

1. Tabele mają nieokreśloną długość,
2. Następnie połączenie krzyżowe reprezentuje sześcian o nieokreślonym wymiarze (z powodu powyższego),
3. Nazwy kategorii (język tabulatora) znajdują się w tabeli

Co możemy zrobić w PostgreSQL bez „listy definicji kolumn”, aby wygenerować tego rodzaju prezentację?

Prosta sprawa, statyczny SQL

Non-dynamiczne rozwiązanie z crosstab()tej prostej sprawy:

SELECT * FROM crosstab(
  'SELECT b.x, f.name, f.x * b.x AS prod
   FROM   foo f, bar b
   ORDER  BY 1, 2'
   ) AS ct (x int, "A" int, "B" int, "C" int, "D" int, "E" int
                 , "F" int, "G" int, "H" int, "I" int, "J" int);

Kolejne kolumny uporządkuję według foo.name, a nie foo.x. Oba są sortowane równolegle, ale to tylko prosta konfiguracja. Wybierz odpowiednią kolejność sortowania dla swojej skrzynki. Rzeczywista wartość drugiej kolumny nie ma znaczenia w tym zapytaniu (forma 1-parametrowa crosstab()).

Nie potrzebujemy nawet crosstab()z 2 parametrami, ponieważ z definicji nie brakuje brakujących wartości. Widzieć:

• Zapytanie krzyżowe PostgreSQL

(Naprawiono zapytanie w tabeli przestawnej w pytaniu, zastępując fooje barw późniejszej edycji. To także naprawia zapytanie, ale nadal działa z nazwami od foo.)

Nieznany typ zwrotu, dynamiczny SQL

Nazwy i typy kolumn nie mogą być dynamiczne. SQL wymaga znajomości liczby, nazw i typów wynikowych kolumn w czasie połączenia. Albo przez wyraźne oświadczenie, albo z informacji w katalogach systemowych (tak się dzieje SELECT * FROM tbl: Postgres sprawdza zarejestrowaną definicję tabeli).

Chcesz, aby Postgres wyprowadzał wynikowe kolumny z danych w tabeli użytkowników. Nie zdarzy się.

Tak czy inaczej, potrzebujesz dwóch podróży w obie strony na serwer. Utwórz kursor, a następnie przejdź przez niego. Lub utworzysz tabelę tymczasową, a następnie wybierzesz z niej. Lub zarejestruj typ i użyj go w połączeniu.

Lub po prostu wygeneruj zapytanie w jednym kroku i wykonaj je w następnym:

SELECT $$SELECT * FROM crosstab(
  'SELECT b.x, f.name, f.x * b.x AS prod
   FROM   foo f, bar b
   ORDER  BY 1, 2'
   ) AS ct (x int, $$
 || string_agg(quote_ident(name), ' int, ' ORDER BY name) || ' int)'
FROM   foo;

To generuje powyższe zapytanie, dynamicznie. Wykonaj to w następnym kroku.

Korzystam z notowań dolarowych ( $$), aby uprościć obsługę zagnieżdżonych cytatów. Widzieć:

• Wstaw tekst z pojedynczymi cudzysłowami w PostgreSQL

quote_ident() jest niezbędny, aby uciec w przeciwnym razie niedozwolonym nazwom kolumn (i ewentualnie obronić się przed wstrzyknięciem SQL).

Związane z:

• Konwertuje kolumny PostgreSQL na wiersze? Transponować?
• Dynamiczna alternatywa dla pivot z CASE i GROUP BY

Co możemy zrobić w PostgreSQL bez „listy definicji kolumn”, aby wygenerować tego rodzaju prezentację?

Jeśli ujmujesz to jako problem z prezentacją, możesz rozważyć funkcję prezentacji po zapytaniu.

psqlPochodzą nowsze wersje (9.6) \crosstabview, pokazujące wynik w postaci tabeli przestawnej bez wsparcia SQL (ponieważ SQL nie może tego wygenerować bezpośrednio, jak wspomniano w odpowiedzi @ Erwin: SQL wymaga znajomości liczby, nazw i typów wynikowych kolumn w czasie połączenia )

Na przykład pierwsze zapytanie daje:

SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x AS result
FROM foo
CROSS JOIN bar
\crosstabview

 arg1 | 1  | 2  | 3  | 4  | 5  
------+----+----+----+----+----
 1    |  1 |  2 |  3 |  4 |  5
 2    |  2 |  4 |  6 |  8 | 10
 3    |  3 |  6 |  9 | 12 | 15
 4    |  4 |  8 | 12 | 16 | 20
 5    |  5 | 10 | 15 | 20 | 25
 6    |  6 | 12 | 18 | 24 | 30
 7    |  7 | 14 | 21 | 28 | 35
 8    |  8 | 16 | 24 | 32 | 40
 9    |  9 | 18 | 27 | 36 | 45
 10   | 10 | 20 | 30 | 40 | 50
(10 rows)

Drugi przykład z nazwami kolumn ASCII podaje:

SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x
    FROM foo
    CROSS JOIN bar
  \crosstabview

 arg1 | I  | J  | K  | L  | M  
------+----+----+----+----+----
 A    |  1 |  2 |  3 |  4 |  5
 B    |  2 |  4 |  6 |  8 | 10
 C    |  3 |  6 |  9 | 12 | 15
 D    |  4 |  8 | 12 | 16 | 20
 E    |  5 | 10 | 15 | 20 | 25
 F    |  6 | 12 | 18 | 24 | 30
 G    |  7 | 14 | 21 | 28 | 35
 H    |  8 | 16 | 24 | 32 | 40
 I    |  9 | 18 | 27 | 36 | 45
 J    | 10 | 20 | 30 | 40 | 50
(10 rows)

Więcej informacji można znaleźć w instrukcji psql i https://wiki.postgresql.org/wiki/Crosstabview .

To nie jest ostateczne rozwiązanie

To jest moje najlepsze podejście do tej pory. Nadal trzeba przekonwertować końcową tablicę na kolumny.

Najpierw mam kartezjański produkt z obu stołów:

select foo.name xname, bar.name yname, (foo.x * bar.x)::text as val,
       ((row_number() over ()) - 1) / (select count(*)::integer from foo) as row
 from bar
     cross join foo
 order by bar.name, foo.name

Ale dodałem numer wiersza tylko w celu zidentyfikowania każdego wiersza pierwszej tabeli.

((row_number() over ()) - 1) / (select count(*)::integer from foo)

Następnie kupiłem wynik w tym formacie:

[Row name] [Array of values]


select col_name, values
from
(
select '' as col_name, array_agg(name) as values from foo
UNION
select fy.name as col_name,
    (select array_agg(t.val) as values
    from  
        (select foo.name xname, bar.name yname, (foo.x * bar.x)::text as val,
              ((row_number() over ()) - 1) / (select count(*)::integer from foo) as row
        from bar
           cross join foo
        order by bar.name, foo.name) t
    where t.row = fy.row)
from
    (select name, (row_number() over(order by name)) - 1 as row from bar) fy
) a
order by col_name;

+---+---------------------+
|   |      ABCDEFGHIJ     |
+---+---------------------+
| I |     12345678910     |
+---+---------------------+
| J |   2468101214161820  |
+---+---------------------+
| K |  36912151821242730  |
+---+---------------------+
| L |  481216202428323640 |
+---+---------------------+
| M | 5101520253035404550 |
+---+---------------------+ 

Konwertowanie go na ciąg rozdzielany przecinkami:

select col_name, values
from
(
select '' as col_name, array_to_string(array_agg(name),',') as values from foo
UNION
select fy.name as col_name,
    (select array_to_string(array_agg(t.val),',') as values
    from  
        (select foo.name xname, bar.name yname, (foo.x * bar.x)::text as val,
              ((row_number() over ()) - 1) / (select count(*)::integer from foo) as row
        from bar
           cross join foo
        order by bar.name, foo.name) t
    where t.row = fy.row)
from
    (select name, (row_number() over(order by name)) - 1 as row from bar) fy
) a
order by col_name;


+---+------------------------------+
|   | A,B,C,D,E,F,G,H,I,J          |
+---+------------------------------+
| I | 1,2,3,4,5,6,7,8,9,10         |
+---+------------------------------+
| J | 2,4,6,8,10,12,14,16,18,20    |
+---+------------------------------+
| K | 3,6,9,12,15,18,21,24,27,30   |
+---+------------------------------+
| L | 4,8,12,16,20,24,28,32,36,40  |
+---+------------------------------+
| M | 5,10,15,20,25,30,35,40,45,50 |
+---+------------------------------+

(Wypróbuj później: http://rextester.com/NBCYXA2183 )

Na marginesie, wydaje się, że SQL: 2016 uwzględni to dzięki funkcjom tabeli polimorficznej (ISO / IEC TR 19075-7: 2017)

Znalazłem link Co nowego w SQL: 2016, ale autor nie rozwija się tak bardzo.