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

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


postgresql crosstab composite-key
person SreenivasBR    schedule 14.04.2017    source источник
comment
Это первый раз, когда я отправляю вопрос, пожалуйста, не обращайте внимания на формат.   -  person SreenivasBR    schedule 14.04.2017
comment
Вы должны отметить это и попросить администраторов перейти к администраторам базы данных.   -  person Evan Carroll    schedule 14.04.2017

Ответы (1)


arrow_upward
0
arrow_downward

Использование 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.

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

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

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; Конечно, это абсолютно субъективно, легче ли это, чем версия без перекрестных таблиц.

person Evan Carroll    schedule 14.04.2017
comment
Большое спасибо @evan Кэролл Эван. Это имеет смысл и хорошее обучение. Я понимаю, что вы пытаетесь сказать о Sub_location. Это были просто придуманные данные для понимания концепции. Я был разработчиком MS Sql и только что вошел в мир postgresql. В будущем вы можете ожидать от меня новых вопросов. :) - person SreenivasBR; 14.04.2017