Кросс-таблица PostgreSQL с тремя столбцами со значениями, суммированными из одного столбца

Я новичок в SQL и пытался сделать перекрестную таблицу в Postgres. Я бы сделал это в Excel, но у меня есть база данных, содержащая около 3,5 миллионов строк, 20 000 различных значений кода, 7 категорий в cat и значения переменных от 1 до 100. В коде может быть только несколько из 7 категорий.

Excel не может обрабатывать количество строк, так что это SQL.

Мои данные в форме

code   |  cat        |   value |
--------------------------------
abc123 |   1         |    4    |
abc234 |   2         |    6    |
abc345 |   1         |    1    |
abc123 |   3         |    2    |
abc123 |   6         |    12   |

с кодом и котом в виде текста, значением в виде целого числа, хранящегося в таблице Postgres.

Я хотел бы выполнить кросс-таблицу по коду и кошке с суммой значений. Я бы хотел, чтобы в ответе отображался ноль вместо «нуль», но если бы «нуль» был бы более простым запросом, тогда это было бы хорошо.

Итак, результат, который я хотел бы,

code   |   'cat=0' | 'cat=1' | 'cat=2' | 'cat=3' | 'cat=4' | 'cat=5' | 'cat=6'|
abc123 |    25     |  0      |  3      |  500    | 250     | 42      |  0     |
abc234 |     0     |  100    |  0      |   10    |  5      |  0      |   25   |
abc345 |    1000   |   0     |  0      |    0    |  0      |  0      |   0    |

Я искал файлы справки Postgres и другие форумы; самым близким был вопрос SO PostgreSQL Crosstab Query, но я не мог понять, как суммировать значения из третьего столбца.

Будем очень благодарны любой помощи.


postgresql sql crosstab
person Siraj    schedule 15.01.2012    source источник
comment
У вас есть что-нибудь до сих пор? ;)   -  person plaes    schedule 15.01.2012
comment
просто разочарование .. перепробовал много примеров со многих сайтов. даже делая простую кросс-таблицу, я получаю ошибку 42601 .. несоответствие типов   -  person Siraj    schedule 15.01.2012
comment
Да, но что вы пробовали до сих пор?   -  person plaes    schedule 15.01.2012

Ответы (2)


arrow_upward
9
arrow_downward

Я получил это, обновив свой код до следующего:

select * from crosstab(
'select code, cat, sum(value) as value
 from my_table 
 group by code, cat
 order by 1,2'
) as ct(code varchar(255),
    cat_0 bigint,
    cat_1 bigint, 
    cat_2 bigint, 
    cat_3 bigint, 
    cat_4 bigint, 
    cat_5 bigint, 
    cat_6 bigint)

Я смог определить правильный тип данных, запустив оператор select внутри кросс-таблицы и сопоставив мои типы данных as ct с типами данных, возвращенными запросом внутри кросс-таблицы.

person Kristen Hazard    schedule 03.01.2013
comment
Я заметил, что имена столбцов cat_XX могут быть случайными, postgres им не соответствует... :( - person Chris; 27.07.2016
comment
@Chris, если вам нужен порядок, вы можете попробовать это: SELECT * FROM crosstab(' your query ' ,$$VALUES ('cat_xx'::text), ('cat_xx2'::text) $$) - person Srinivas Rathikrindi; 22.08.2017
comment
Это сработало и для меня - person Yaakov Bressler; 14.03.2021

arrow_upward
3
arrow_downward

Пытаться:

select * from crosstab(
'select code, cat, sum(value) as value
 from my_table 
 group by code, cat
 order by 1,2'
) as ct(code text,
        cat_0 int,
        cat_1 int, 
        cat_2 int, 
        cat_3 int, 
        cat_4 int, 
        cat_5 int, 
        cat_6 int)
person Community    schedule 15.01.2012
comment
спасибо .. я пробовал это, но получаю эту ошибку .... ОШИБКА: недопустимый тип возвращаемого значения DETAIL: тип данных rowid SQL не соответствует возвращаемому типу данных rowid. ********** Ошибка ********** ОШИБКА: недопустимый тип возвращаемого значения Состояние SQL: 42601 Подробности: тип данных SQL rowid не соответствует возвращаемому типу данных rowid. - person Siraj; 16.01.2012
comment
пробовал с подсчетом (значением), используя текст, а не int после 'cat_0' и т. д., все равно заканчивается той же ошибкой: состояние sql: 42601 - person Siraj; 16.01.2012