Início / dba / Perguntas / 219990
Accepted
Parker
Asked: 2018-10-13 11:31:09 +0800 CST

Crie uma tabela verdade 2D a partir de "produto cruzado" de duas tabelas por meio da visualização ou função do PostgreSQL

  • 772

Eu tenho um método de trabalho baseado em Excel para criar uma tabela verdade de dois vetores exportados de um banco de dados PostgreSQL. O processo leva cerca de 4 horas para ser concluído devido a um grande número de operações VLOOKUPe COUNTIFS, portanto, estou procurando um método para implementar isso como uma visualização diretamente no banco de dados.

Os vetores de entrada são produzidos a partir de duas visualizações existentes em meu banco de dados, que não possuem chaves estrangeiras.

Para tornar essa pergunta e solução o mais genérica possível, criei um problema paralelo usando duas tabelas simples com dados de amostra para cobrir todos os casos possíveis:

CREATE TABLE group_membership
(
  member character varying(6) NOT NULL,
  group_name character varying(64) NOT NULL
);

INSERT INTO group_membership VALUES ('000001','A');
INSERT INTO group_membership VALUES ('000001','B');
INSERT INTO group_membership VALUES ('000001','B'); -- A value may occur more than once.
INSERT INTO group_membership VALUES ('000001','D'); -- A value may not necessarily have a corresponding row in the group table.
INSERT INTO group_membership VALUES ('000001','D');

INSERT INTO group_membership VALUES ('000002','B');
INSERT INTO group_membership VALUES ('000002','C');
INSERT INTO group_membership VALUES ('000002','E');

INSERT INTO group_membership VALUES ('000003','A');
INSERT INTO group_membership VALUES ('000003','C');

INSERT INTO group_membership VALUES ('000004','D');
INSERT INTO group_membership VALUES ('000004','E');

CREATE TABLE groups
(
  name character varying(64) NOT NULL
);

INSERT INTO groups VALUES ('A');
INSERT INTO groups VALUES ('B');
INSERT INTO groups VALUES ('C');
INSERT INTO groups VALUES ('C'); -- A value may occur more than once.
INSERT INTO groups VALUES ('Z');
-- 'D' and 'E' not present in this table

Não há relações entre essas duas tabelas.

Estou tentando construir uma visão que criará uma tabela de verdade binária (matriz) assim:

member A B C Z
000001 t t f f
000002 f t t f
000003 t f t f
000004 f f f f

Onde a primeira coluna são os membros distintos da group_membershiptabela, e as colunas subsequentes mostram a presença ou ausência de memberapenas nos grupos definidos na grouptabela. A tabela resultante deve ser apenas booleana ( TRUEse o membro aparecer em uma tupla com o grupo pelo menos uma vez, FALSEcaso contrário).

Por exemplo, algumas "células" específicas na tabela acima estariam em conformidade com o seguinte:

SELECT COUNT(*) > 0 AS value FROM group_membership WHERE group_name='A' AND member='000001';
 value
-------
 t
(1 row)

SELECT COUNT(*) > 0 AS value FROM group_membership WHERE group_name='Z' AND member='000001';
 value
-------
 f
(1 row)

E para criar a segunda coluna (a coluna 'A'):

SELECT COUNT(*) > 0 AS A FROM group_membership WHERE group_name='A' AND member='000001'
 UNION ALL
SELECT COUNT(*) > 0 AS A FROM group_membership WHERE group_name='A' AND member='000002'
 UNION ALL
SELECT COUNT(*) > 0 AS A FROM group_membership WHERE group_name='A' AND member='000003'
 UNION ALL
SELECT COUNT(*) > 0 AS A FROM group_membership WHERE group_name='A' AND member='000004'
;

Ainda melhor seria algo assim ( 1e 0em vez de TRUEe FALSE):

member A B C Z
000001 1 1 0 0
000002 0 1 1 0
000003 1 0 1 0
000004 0 0 0 0

Onde a consulta para cada uma das "células" individuais pode ser da forma:

SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM group_membership WHERE group_name='A' AND member='000001';

Minha group_membershiptabela tem cerca de 50.000 linhas e minha grouptabela tem cerca de 200 linhas.

Nota: Se você fizer algo como o seguinte para ignorar grupos que não são comuns entre as duas tabelas, você acabará eliminando linhas como 000004nos conjuntos de resultados de exemplo acima, que não é o que estou procurando (membro 000004e grupo Zdevem ser presente no conjunto de resultados):

SELECT * FROM group_membership WHERE group_name IN (SELECT DISTINCT(name) FROM groups);

Como uma primeira tentativa de resolver esse problema, estou procurando criar um FUNCTIONque dependa de uma recursiva JOINsobre a grouptabela para construir a tabela de resultados.

Atualização: A FUNCTIONrequer uma RETURNS TABLEdefinição, que parece não ser uma solução viável, dado o número variável de colunas no conjunto de resultados. Algumas ideias adicionais que tenho são criar uma função que execute uma série de UNIONs em uma dimensão e, em seguida, seja agrupada com uma visualização que execute um UNIONsobre um crosstab()dos resultados sobreSELECT DISTINCT(name) FROM groups ORDER BY name ASC;

postgresql pivot

2 respostas

  1. Best Answer

    Parece que você basicamente quer isso, sem escrever isso:

    SELECT member
      ,bool_or(group_name='A')::int as "A"
      ,bool_or(group_name='B')::int as "B"
      ,bool_or(group_name='C')::int as "C"
      ,bool_or(group_name='Z')::int as "Z" 
  FROM group_membership
  GROUP BY member
  ORDER BY member;

    O Postgres não está estruturado para facilitar as tabelas dinâmicas.

     CREATE or replace FUNCTION prepare() returns void language plpgsql as $F$
 BEGIN
     execute (
          WITH g AS ( select name from groups group by name order by name)
          SELECT
          $$
          create or replace function pg_temp.pivot () 
              RETURNS TABLE ( member text,
          $$ || string_agg( quote_ident( name )||' INT',',') || $$ 
          ) LANGUAGE SQL AS $X$
              SELECT member
    $$ || string_agg( ',bool_or(group_name=' || quote_literal( name ) ||
    ')::int AS '|| quote_ident( name ),e'\n') || $$
                 FROM group_membership
                 GROUP BY member
                 ORDER BY member; $X$; $$
    FROM g ) ;
END;
$F$;


select prepare();
select * from pg_temp.pivot();

 member | A | B | C | Z
--------+---+---+---+---
 000001 | 1 | 1 | 0 | 0
 000002 | 0 | 1 | 1 | 0
 000003 | 1 | 0 | 1 | 0
 000004 | 0 | 0 | 0 | 0
(4 rows)

    aqui eu uso o SQL para formar a consulta acima como uma função temporária e, em seguida, puxo os fluxos de resultado disso

    na função principal eu uso uma subseleção que me permite usar um CTE o que significa que posso ordenar os nomes das colunas.

    Eu poderia ter criado uma visão temporária dentro da função principal, mas não pensei nisso até agora.

    Estou supondo que os valores em group_name não são maiores que 64 octetos, varchar(64)não impõe isso - o tipo namesim e provavelmente é mais adequado para essa tarefa.

    • 3

  2. Aqui está uma função para gerar o valor de qualquer célula (estou usando textem vez de intpara evitar conflitos de tipo ao mesclar os cabeçalhos posteriormente):

    CREATE OR REPLACE FUNCTION is_group_member(mname text, gname text)
RETURNS text
AS $$
  SELECT CASE WHEN COUNT(*) > 0 THEN '1' ELSE '0' END FROM group_membership WHERE group_name=gname AND member=mname
$$ LANGUAGE SQL STABLE;

SELECT is_group_member('000001','A');
SELECT is_group_member('000001','Z');

    Com a função acima, podemos fazer:

    SELECT DISTINCT(name) AS name,is_group_member('000001',name) FROM groups ORDER BY name ASC;
 name | is_group_member
------+-----------------
 A    | 1
 B    | 1
 C    | 0
 Z    | 0
(4 rows)

    E podemos converter a consulta acima para retornar um array ordenado:

    SELECT array_agg(is_member) AS membership FROM (SELECT DISTINCT(name) AS name,is_group_member('000001',name) AS is_member FROM groups ORDER BY name ASC) g;

 membership
------------
 {1,1,0,0}
(1 row)

    No entanto, eu realmente não quero os colchetes lá, então usarei string_agg:

    SELECT string_agg(is_member,',') AS membership FROM (SELECT DISTINCT(name) AS name,is_group_member('000001',name) AS is_member FROM groups ORDER BY name ASC) g;

    Convertendo a consulta acima em uma função:

    CREATE OR REPLACE FUNCTION group_memberships(mname text)
RETURNS text
AS $$
  SELECT string_agg(is_member,',') AS membership FROM (SELECT DISTINCT(name) AS name,is_group_member(mname,name) AS is_member FROM groups ORDER BY name ASC) g
$$ LANGUAGE SQL STABLE;

SELECT group_memberships('000001');

 group_memberships
-------------------
 1,1,0,0
(1 row)

    E, em seguida, invocando a função em uma consulta:

    SELECT DISTINCT(member),group_memberships(member) FROM group_membership ORDER BY member;
 member | group_memberships
--------+-------------------
 000001 | 1,1,0,0
 000002 | 0,1,1,0
 000003 | 1,0,1,0
 000004 | 0,0,0,0
(4 rows)

    Os resultados acima são exatamente o que estou procurando, embora eu sinta que isso poderia ser melhorado das seguintes maneiras:

    • simplificar para usar menos funções ou até mesmo recolher em uma única consulta
    • adicione uma primeira linha contendo a matriz ordenada de nomes de grupos
    • expanda os resultados em uma tabela (como nesta resposta ou possivelmente nesta resposta )

    É claro que, em retrospecto, os nomes dos meus grupos não podem ser usados ​​como nomes de colunas porque maiúsculas/minúsculas, caracteres especiais, espaços em branco etc.

    Então, mantendo a abordagem baseada em array, tentarei obter a próxima melhor coisa.

    Obtendo uma linha apenas dos nomes de grupos ordenados:

    SELECT '' AS member,string_agg(DISTINCT(name),',' ORDER BY name ASC) AS group_memberships FROM groups;

 member | group_memberships
--------+-------------------
        | A,B,C,Z
(1 row)

    Então, posso criar uma visão que une os conjuntos de resultados para produzir a matriz completa:

    CREATE VIEW group_memberships AS
  SELECT '' AS member,string_agg(DISTINCT(name),',' ORDER BY name ASC) AS group_memberships FROM groups
   UNION
  SELECT DISTINCT(member),group_memberships(member) FROM group_membership ORDER BY member
;

 member | group_memberships
--------+-------------------
        | A,B,C,Z
 000001 | 1,1,0,0
 000002 | 0,1,1,0
 000003 | 1,0,1,0
 000004 | 0,0,0,0
(5 rows)

    Com esta solução, não há tabelas temporárias nem visualizações materializadas. A exibição produz resultados em um formulário que é fácil de importar para o Excel, então isso funciona para meus propósitos. Eu gostaria de resolver esse problema com menos funções (ou mesmo sem funções).

    Exportando na linha de comando, posso soltar os cabeçalhos do conjunto de resultados:

    C:\temp>C:\Progra~1\PostgreSQL\9.6\bin\psql.exe -U user -d testdb -c "\copy (SELECT * FROM group_memberships) to 'group_membership.csv' WITH (FORMAT CSV, HEADER FALSE);"

    Produz o seguinte arquivo:

    "","A,B,C,Z"
000001,"1,1,0,0"
000002,"0,1,1,0"
000003,"1,0,1,0"
000004,"0,0,0,0"

    Isso não é perfeito devido à citação, mas é próximo o suficiente para importar para o Excel após uma pesquisa/substituição mínima em um editor de texto.

    Para chegar um pouco mais perto do arquivo de saída desejado:

    DROP VIEW group_memberships;
DROP FUNCTION group_memberships(text);
DROP FUNCTION is_group_member(text,text);

CREATE OR REPLACE FUNCTION is_group_member(mname text, gname text)
RETURNS text
AS $$
  SELECT CASE WHEN COUNT(*) > 0 THEN '"1"' ELSE '"0"' END FROM group_membership WHERE group_name=gname AND member=mname
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION group_memberships(mname text)
RETURNS text
AS $$
  SELECT '"' || mname || '"' || ',' || string_agg(is_member,',') AS membership FROM (SELECT DISTINCT(name) AS name,is_group_member(mname,name) AS is_member FROM groups ORDER BY name ASC) g
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE VIEW group_memberships AS
  SELECT '' AS member, '""' || ',"' || string_agg(DISTINCT(name),'","' ORDER BY name ASC) || '"' AS group_memberships FROM groups
   UNION
  SELECT DISTINCT(member),group_memberships(member) FROM group_membership ORDER BY member
;

SELECT group_memberships FROM group_memberships;

    group_memberships
--------------------------
 "","A","B","C","Z"
 "000001","1","1","0","0"
 "000002","0","1","1","0"
 "000003","1","0","1","0"
 "000004","0","0","0","0"
(5 rows)

    E executando:

    C:\temp>C:\Progra~1\PostgreSQL\9.6\bin\psql.exe -U user -d testdb -c "\copy (SELECT group_memberships FROM group_memberships) to 'group_membership.csv' WITH (FORMAT CSV, HEADER FALSE);"

    Produz o seguinte arquivo:

    """"",""A"",""B"",""C"",""Z"""
"""000001"",""1"",""1"",""0"",""0"""
"""000002"",""0"",""1"",""1"",""0"""
"""000003"",""1"",""0"",""1"",""0"""
"""000004"",""0"",""0"",""0"",""0"""

    Incrementalmente mais perto, mas ainda não perfeito. No entanto, duas pesquisas/substituições de ""to "produzem:

    "","A","B","C","Z"
"000001","1","1","0","0"
"000002","0","1","1","0"
"000003","1","0","1","0"
"000004","0","0","0","0"

    Que importa diretamente para o Excel. Essa abordagem pode causar problemas se houver aspas duplas no grupo ou nos nomes dos membros, portanto, se alguém tiver uma solução melhor para as aspas, gostaria de ouvi-la.

    • 1

relate perguntas