Использование кросс-таблицы в запросе с составным ключом (несколько столбцов)

Question

Я недавно переключился с SQL Server на PostgreSQL и пытался найти эквивалент функции pivot. Я не могу получить желаемый результат, используя кросс-таблицу, которую я смог достичь с помощью SQL Server.

Пример данных.

CREATE TABLE loc
AS
  SELECT location, sub_location, step, amount
  FROM ( VALUES
    ( 100 , '100_A', 'step_1', 2 ),
    ( 100 , '100_A', 'step_2', 7 ),
    ( 100 , '100_A', 'step_3', 6 ),
    ( 100 , '100_B', 'step_1', 5 ),
    ( 100 , '100_B', 'step_2', 8 ),
    ( 100 , '100_B', 'step_3', 9 )
  ) AS t(location, sub_location, step, amount);

Я пытаюсь добиться этого ниже набора результатов.

Location    Sub_location    Step_1  Step_2  Step_3
--------    ------------    ------  ------  ------
100         100_A           2       7       6
100         100_B           5       8       9

Я мог бы легко добиться этого MS SQL. И мой перекрестный запрос,

Select * from crosstab
    (
     'select location, sub_location, step, amount from loc',
     'select distinct step from loc'
    )
    as final_result(location varchar,sub_location varchar, step_1 int, step_2 int, step_3 int);

Я вижу только один ряд вместо двух. В любом случае, чтобы преодолеть это ограничение в postgres.

Answer 1

Использование ARRAY для решения проблемы составного ключа

Я думаю, что реальная проблема в том, что вы sub_location является частью вашего основного идентификатора (имени) для целей кросс. И не то, что кросс-таблица вызывает дополнительный столбец.

Ожидается, что "дополнительные" столбцы будут одинаковыми для всех строк с одинаковым значением row_name.

Таким образом, по сути, составные ключи, образующие имя, должны быть сериализованы пользователем. Вы все еще можете заставить эту работу сериализовать в SQL ARRAY типа text[], с помощью ARRAY[location, sub_location]::text[],

SELECT *
FROM crosstab(
  $$ SELECT ARRAY[location, sub_location]::text[], step, amount FROM loc ORDER BY 1, 2, 3; $$,
  $$ SELECT DISTINCT step FROM loc ORDER BY 1; $$
) AS t(location text[], step_1 int, step_2 int, step_3 int );

  location   | step_1 | step_2 | step_3 
-------------+--------+--------+--------
 {100,100_A} |      2 |      7 |      6
 {100,100_B} |      5 |      8 |      9
(2 rows)

Использование вашего подкласса с фактическим местоположением в нем

Теперь, поскольку в вашем конкретном случае данные о местонахождении содержат данные о местоположении, мы можем сделать это еще короче, изменив порядок. У меня не будет под-местоположения, сохраненного в таблице с 100_, но мы можем использовать это здесь. Чтобы было ясно, это не будет работать, если location: 100, sublocation: 'A' это то, как я бы его хранил.

SELECT *
FROM crosstab(
  $$ SELECT sub_location, location, step, amount FROM loc ORDER BY 1, 2, 3; $$,
  $$ SELECT DISTINCT step FROM loc ORDER BY 1; $$
) AS t(sub_location text, location int, step_1 int, step_2 int, step_3 int );
 sub_location | location | step_1 | step_2 | step_3 
--------------+----------+--------+--------+--------
 100_A        |      100 |      2 |      7 |      6
 100_B        |      100 |      5 |      8 |      9
(2 rows)

Это исключает сложность вызова ARRAY хоть.

Упрощение для вашего случая использования

Мы также можем просто удалить `location в этой точке или изменить порядок в родительском запросе.

SELECT *
FROM crosstab(
  $$ SELECT sub_location, step, amount FROM loc ORDER BY 1, 2, 3; $$,
  $$ SELECT DISTINCT step FROM loc ORDER BY 1; $$
) AS t(location_full text, step_1 int, step_2 int, step_3 int );

 location_full | step_1 | step_2 | step_3 
---------------+--------+--------+--------
 100_A         |      2 |      7 |      6
 100_B         |      5 |      8 |      9
(2 rows)

Не уверен, какой метод выше работает лучше для вас. Не забудь CREATE EXTENSION tablefunc; Конечно, это абсолютно субъективно, проще ли это, чем версия без кросс-таблицы.