Consulta de referência cruzada do PostgreSQL

Alguém sabe como criar consultas cruzadas no PostgreSQL?
Por exemplo, tenho a seguinte tabela:

Section Status Count A Active 1 A Inactive 2 B Active 4 B Inactive 5 

Gostaria que a consulta retornasse a seguinte crosstab:

 Section Active Inactive A 1 2 B 4 5 

Isso é possível?

Instale o tablefunc módulo adicional uma vez por database, que fornece a function crosstab() . Desde o Postgres 9.1, você pode usar o CREATE EXTENSION para isso:

 CREATE EXTENSION IF NOT EXISTS tablefunc; 

Caso de teste aprimorado

 CREATE TABLE tbl ( section text , status text , ct integer -- "count" is a reserved word in standard SQL ); INSERT INTO tbl VALUES ('A', 'Active', 1), ('A', 'Inactive', 2) , ('B', 'Active', 4), ('B', 'Inactive', 5) , ('C', 'Inactive', 7); -- ('C', 'Active') is missing 

Forma simples – não adequada para atributos ausentes

crosstab(text) com 1 parâmetro de input:

 SELECT * FROM crosstab( 'SELECT section, status, ct FROM tbl ORDER BY 1,2' -- needs to be "ORDER BY 1,2" here ) AS ct ("Section" text, "Active" int, "Inactive" int); 

Retorna:

  Seção |  Ativo |  Inativo
 --------- + -------- + ----------
  A |  1 |  2
  B |  4 |  5
  C |  7 |  - !!
  • Não há necessidade de transmitir e renomear.
  • Observe o resultado incorreto para C : o valor 7 é preenchido para a primeira coluna. Às vezes, esse comportamento é desejável, mas não para este caso de uso.
  • O formulário simples também é limitado a exatamente três colunas na consulta de input fornecida: row_name , category , value . Não há espaço para colunas extras como na alternativa de 2 parâmetros abaixo.

Forma segura

crosstab(text, text) com 2 parâmetros de input:

 SELECT * FROM crosstab( 'SELECT section, status, ct FROM tbl ORDER BY 1,2' -- could also just be "ORDER BY 1" here , $$VALUES ('Active'::text), ('Inactive')$$ ) AS ct ("Section" text, "Active" int, "Inactive" int); 

Retorna:

  Seção |  Ativo |  Inativo
 --------- + -------- + ----------
  A |  1 |  2
  B |  4 |  5
  C |  |  7 - !!
  • Observe o resultado correto para C

  • O segundo parâmetro pode ser qualquer consulta que retorne uma linha por atributo que corresponda à ordem da definição de coluna no final. Muitas vezes você desejará consultar atributos distintos da tabela subjacente da seguinte forma:

     'SELECT DISTINCT attribute FROM tbl ORDER BY 1' 

    Isso está no manual.

    Como você tem que soletrar todas as colunas em uma lista de definição de colunas de qualquer maneira (exceto para as variantes crosstab N () predefinição da crosstab N () ), normalmente é mais eficiente fornecer uma lista curta em uma expressão VALUES como demonstrado:

     $$VALUES ('Active'::text), ('Inactive')$$) 

    Ou (não no manual):

     $$SELECT unnest('{Active,Inactive}'::text[])$$ -- short syntax for long lists 
  • Eu usei cotação em dólar para facilitar a cotação.

  • Você pode até mesmo gerar colunas com diferentes tipos de dados com crosstab(text, text) – desde que a representação de texto da coluna de valor seja uma input válida para o tipo de destino. Desta forma você pode ter atributos de diferentes tipos e saída de text , date , numeric , etc. para os respectivos atributos. Há um exemplo de código no final da tabulação crosstab(text, text) do capítulo crosstab(text, text) no manual .

db <> toca aqui

Exemplos avançados

  • Pivô em várias colunas usando o Tablefunc – também demonstrando as “colunas extras” mencionadas

  • Alternativa dinâmica para dinamizar com CASE e GROUP BY

\crosstabview no psql

O Postgres 9.6 adicionou este meta-comando ao seu terminal interativo psql padrão. Você pode executar a consulta que você usaria como primeiro parâmetro crosstab() e alimentá-lo para \crosstabview (imediatamente ou na próxima etapa). Gostar:

 db=> SELECT section, status, ct FROM tbl \crosstabview 

Resultado semelhante ao acima, mas é um recurso de representação no lado do cliente exclusivamente. As linhas de input são tratadas de maneira ligeiramente diferente, portanto, ORDER BY não é necessário. Detalhes para \crosstabview no manual. Existem mais exemplos de código na parte inferior dessa página.

Resposta relacionada no dba.SE por Daniel Vérité (autor do recurso psql):

  • Como faço para gerar um CROSS JOIN dynamic onde a definição da tabela resultante é desconhecida?


A resposta aceita anteriormente está desatualizada.

  • A variante da function crosstab(text, integer) está desatualizada. O segundo parâmetro integer é ignorado. Eu cito o manual atual :

    crosstab(text sql, int N)

    Versão obsoleta da crosstab(text) . O parâmetro N agora é ignorado, pois o número de colunas de valor é sempre determinado pela consulta de chamada

  • Fundição e renomeação desnecessárias.

  • Ele falhará se uma linha não tiver todos os atributos. Veja variante segura com dois parâmetros de input acima para lidar com atributos ausentes corretamente.

  • ORDER BY é obrigatório na forma de um parâmetro da crosstab() de crosstab() . O manual:

    Na prática, a consulta SQL sempre deve especificar ORDER BY 1,2 para garantir que as linhas de input sejam ordenadas corretamente

Você pode usar a function crosstab() do módulo adicional tablefunc – que você precisa instalar uma vez por database. Desde o PostgreSQL 9.1 você pode usar o CREATE EXTENSION para isso:

 CREATE EXTENSION tablefunc; 

No seu caso, acredito que seria algo parecido com isto:

 CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer); INSERT INTO t VALUES ('A', 'Active', 1); INSERT INTO t VALUES ('A', 'Inactive', 2); INSERT INTO t VALUES ('B', 'Active', 4); INSERT INTO t VALUES ('B', 'Inactive', 5); SELECT row_name AS Section, category_1::integer AS Active, category_2::integer AS Inactive FROM crosstab('select section::text, status, count::text from t',2) AS ct (row_name text, category_1 text, category_2 text); 
 SELECT section, SUM(CASE status WHEN 'Active' THEN count ELSE 0 END) AS active, SUM(CASE status WHEN 'Inactive' THEN count ELSE 0 END) AS inactive FROM t GROUP BY section 

Solução com agregação JSON:

 CREATE TEMP TABLE t ( section text , status text , ct integer -- don't use "count" as column name. ); INSERT INTO t VALUES ('A', 'Active', 1), ('A', 'Inactive', 2) , ('B', 'Active', 4), ('B', 'Inactive', 5) , ('C', 'Inactive', 7); SELECT section, (obj ->> 'Active')::int AS active, (obj ->> 'Inactive')::int AS inactive FROM (SELECT section, json_object_agg(status,ct) AS obj FROM t GROUP BY section )X 

Desculpe, isso não está completo, porque não posso testá-lo aqui, mas isso pode levá-lo à direção certa. Estou traduzindo de algo que eu uso e que faz uma consulta semelhante:

 select mt.section, mt1.count as Active, mt2.count as Inactive from mytable mt left join (select section, count from mytable where status='Active')mt1 on mt.section = mt1.section left join (select section, count from mytable where status='Inactive')mt2 on mt.section = mt2.section group by mt.section, mt1.count, mt2.count order by mt.section asc; 

O código do qual estou trabalhando é:

 select m.typeID, m1.highBid, m2.lowAsk, m1.highBid - m2.lowAsk as diff, 100*(m1.highBid - m2.lowAsk)/m2.lowAsk as diffPercent from mktTrades m left join (select typeID,MAX(price) as highBid from mktTrades where bid=1 group by typeID)m1 on m.typeID = m1.typeID left join (select typeID,MIN(price) as lowAsk from mktTrades where bid=0 group by typeID)m2 on m1.typeID = m2.typeID group by m.typeID, m1.highBid, m2.lowAsk order by diffPercent desc; 

que retornará um typeID, o lance de preço mais alto eo menor preço solicitado ea diferença entre os dois (uma diferença positiva significaria que algo poderia ser comprado por menos do que pode ser vendido).

Crosstab function de referência Crosstab está disponível sob a extensão tablefunc . Você terá que criar essa extensão uma vez para o database.

CREATE EXTENSION tablefunc ;

Você pode usar o código abaixo para criar uma tabela dinâmica usando a tabulação cruzada:

 create table test_Crosstab( section text, 
status text,
count numeric)
insert into test_Crosstab values ( 'A','Active',1)
,( 'A','Inactive',2)
,( 'B','Active',4)
,( 'B','Inactive',5) select * from crosstab(
'select section
,status
,count
from test_crosstab'
)as ctab ("Section" text,"Active" numeric,"Inactive" numeric)