Как трансформировать таблицу в PostgreSQL?

Question

[HelloDarknessMyOldFried]

У меня есть таблица подобного типа:

+-----------+----------+------------------------+
| week_no   | ticker   | ratio                  |
|-----------+----------+------------------------|
| 39.0      | PBRT     | 0.33333333333333333333 |
| 38.0      | ADM      | 0.50000000000000000000 |
| 40.0      | TSCO     | 1.00000000000000000000 |
| 39.0      | AAL      | 0.09090909090909090909 |
+-----------+----------+------------------------+

Мне нужно трансформировать таблицу в подобный формат:

+-----------+------------------------+------------------------+------------------------+------------------------+
| week_no   | adm                    | pbrt                   | aal                    | tsco                   |
|-----------+------------------------+------------------------+------------------------+------------------------|
| 38.0      | 0.50000000000000000000 | <null>                 | <null>                 | <null>                 |
| 39.0      | <null>                 | 0.33333333333333333333 | 0.09090909090909090909 | <null>                 |
| 40.0      | <null>                 | <null>                 | <null>                 | 1.00000000000000000000 |
+-----------+------------------------+------------------------+------------------------+------------------------+

Чтобы каждый тикер стал отдельным столбцом, а недели были строками.

Я использую такой скрипт:

SELECT *   
       FROM crosstab(   
           'select week_no, ticker, ratio   
           from rejected_ratio_per_ticker_and_week_for_crosstab   
           order by 1,2')   
       AS ct(week_no double precision, ADM numeric, PBRT numeric, AAL numeric, TSCO numeric);

Но на выходе я получаю это:

+-----------+------------------------+------------------------+--------+--------+
| week_no   | adm                    | pbrt                   | aal    | tsco   |
|-----------+------------------------+------------------------+--------+--------|
| 38.0      | 0.50000000000000000000 | <null>                 | <null> | <null> |
| 39.0      | 0.09090909090909090909 | 0.33333333333333333333 | <null> | <null> |
| 40.0      | 1.00000000000000000000 | <null>                 | <null> | <null> |
+-----------+------------------------+------------------------+--------+--------+

Как мне поступить? Я первый раз использую crosstab и не совсем понимаю логику его ORDER BY.

Спасибо за помощь!

Answer 1

[alexalexes]

Это не трансформация, а подготовка подходящей выборки.
Структуру таблицы вы же не меняете, только форму получения данных?

select t1.week_no,
(select max(t2.ratio) from table t2 where t2.week_no = t1.week_no and t2.ticker = t1.ticker and t1.ticker = 'ADM') adm,
(select max(t2.ratio) from table t2 where t2.week_no = t1.week_no and t2.ticker = t1.ticker and t1.ticker = 'PBRT') pbrt,
(select max(t2.ratio) from table t2 where t2.week_no = t1.week_no and t2.ticker = t1.ticker and t1.ticker = 'ALL') aal,
(select max(t2.ratio) from table t2 where t2.week_no = t1.week_no and t2.ticker = t1.ticker and t1.ticker = 'TSCO') tsco
from table t1

PS: max взят, чтобы выводить единственное значение свойства и не возникала ошибка, если будут дубликаты записей по одной неделе, можно использовать Limit в подзапросах или другой критерий, например, row_number.
PPS: Можно сделать представление (view) на основе этой выборки, если нужно использовать результат как таблицу.

Comments

[HelloDarknessMyOldFried]

Мне не совсем понятно, откуда берется t2, так как на входе я имею только одну таблицу:

+-----------+----------+------------------------+
| week_no   | ticker   | ratio                  |
|-----------+----------+------------------------|
| 39.0      | PBRT     | 0.33333333333333333333 |
| 38.0      | ADM      | 0.50000000000000000000 |
| 40.0      | TSCO     | 1.00000000000000000000 |
| 39.0      | AAL      | 0.09090909090909090909 |
+-----------+----------+------------------------+