Início / dba / Perguntas / 326486
Accepted
Leon
Asked: 2023-04-27 13:14:24 +0800 CST

Como selecionar corretamente várias colunas de um retorno de uma função no Postgresql?

  • 772

Tenho centenas de tabelas com a mesma estrutura em um banco de dados Postgresql-11 e preciso realizar uma mesma estatística em cada uma delas.

Como a lógica das estatísticas reais é muito complexa, para simplificar minha pergunta aqui, digamos que eu precise calcular MAX/MIN/MEAN/STDDEV de cada um.

Não quero editar SQLs individuais para cada tabela, uma a uma, pois, nesse caso, codificaria centenas de linhas de SQL muito semelhantes, com uma única diferença no nome da tabela.

Então usei o SQL dinâmico em uma função para fazer o cálculo de uma tabela por vez, assim:

DROP FUNCTION IF EXISTS get_features_for;
CREATE OR REPLACE FUNCTION get_features_for(
    IN  table_name VARCHAR,
    OUT result_ RECORD ) LANGUAGE 'plpgsql'
AS $func$

DECLARE sql_string VARCHAR :=
    format('
        SELECT ''%s'' table_name,     -- just for validating
            MAX(feature) max_feature,
            MIN(feature) min_feature,
            AVG(feature) avg_feature,
            STDDEV(feature) std_feature
        FROM "%s";',
        table_name, table_name );

BEGIN
  EXECUTE sql_string INTO result_;
END
$func$;

E então chame-o com todos os nomes de tabelas no banco de dados, dentro de uma consulta, assim:

SELECT tbs."table_name", get_features_for( tbs."table_name" )
FROM information_schema."tables" tbs
WHERE tbs.table_schema = 'public' AND tbs.table_type = 'BASE TABLE'
  AND tbs."table_name" LIKE 'analy%'
ORDER BY tbs."table_name";

Funciona, mas os resultados são semelhantes aos seguintes:

"table_name", "get_features_for"

"analy_001", "(analy_001,-3,2,0,2.5)"

"analy_002", "(analy_002,-3,2,-1,2)"

"analy_003", "(analy_003,-3,2,0,2)"

Você pode ver que todas as colunas de resultado foram agrupadas em uma única coluna de string e deixe-me NÃO usá -las convenientemente.

Eu quero que eles sejam semelhantes a:

table_name, max_feature, min_feature, avg_feature, std_feature

"analy_001", "analy_001", -3, 2, 0, 2,5

"analy_002", "analy_002", -3, 2, -1, 2

"analy_003", "analy_003", -3, 2, 0, 2

Como devo conseguir isso?

Aliás, estou usando o pgAdmin.

Obrigado!!!

postgresql

2 respostas

  1. Best Answer

    O tipo de retorno da função é um tipo composto. A maneira mais eficiente de obter as colunas individuais seria uma junção lateral:

    SELECT tbs.table_name, f.*
FROM information_schema.tables tbs
   CROSS JOIN LATERAL get_features_for(tbs.table_name) AS f
WHERE tbs.table_schema = 'public'
  AND tbs.table_type = 'BASE TABLE'
  AND tbs.table_name LIKE 'analy%'
ORDER BY tbs."table_name";

    Evite *e soletre os nomes das colunas reais em sua consulta real.

    • 1

  2. Você também pode fazer o seguinte - parece ter bastante desempenho (faz uso extensivo de funções de string rápida, sem regexes - todo o código abaixo está disponível no violino aqui ):

    CREATE TABLE test
(
  analysis TEXT NOT NULL
);

    Preencher:

    INSERT INTO test VALUES 
('"analy_001", "(analy_001,-3,2,0,2.5)"'),
('"analy_002", "(analy_002,-3,2,-1,2)"'),
('"analy_003", "(analy_003,-3,2,0,2)"');

    e então executamos:

    SELECT 
  SPLIT_PART(analysis, '", "(', 2)  -- do as much of the  work here as possible!
FROM test;

    Resultado:

    split_part
analy_001,-3,2,0,2.5)"
analy_002,-3,2,-1,2)"
analy_003,-3,2,0,2)"

    E então:

    SELECT
  REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', '')
FROM test_;

    Resultado:

    replace
analy_001,-3,2,0,2.5
analy_002,-3,2,-1,2
analy_003,-3,2,0,2

    Por fim, executamos:

    SELECT
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 1) AS table_name,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 2) AS min_f,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 3) AS max_f,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 4) AS avg_f,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 5) AS std_f
FROM
  test_;

    Resultado:

    table_name  min_f max_f avg_f std_f
 analy_001     -3     2     0   2.5
 analy_002     -3     2    -1     2
 analy_003     -3     2     0     2

    Claro, você terá que alimentar seus valores de seu SQL original, mas parece ter um bom desempenho:

    SET enable_seqscan = OFF;

    e então:

    EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING)
SELECT
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 1) AS table_name,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 2) AS min_feature,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 3) AS max_feature,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 4) AS avg_feature,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 5) AS std_feature
FROM
  test_;

    Resultado:

    QUERY PLAN
Bitmap Heap Scan on public.test_  (cost=15.29..89.89 rows=1360 width=160) (actual time=0.028..0.034 rows=3 loops=1)
  Output: split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 1), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 2), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 3), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 4), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 5)
  Heap Blocks: exact=1
  Buffers: shared hit=2
  ->  Bitmap Index Scan on t_ix  (cost=0.00..14.95 rows=1360 width=0) (actual time=0.013..0.013 rows=3 loops=1)
        Buffers: shared hit=1
Planning Time: 0.164 ms
Execution Time: 0.100 ms

    Um breve desvio sobre SET enable_seqscan = OFF;.

    • Na verdade, isso não desativa as verificações sequenciais de tabelas, apenas as torna muito caras - veja a discussão abaixo.

    • Não faça isso em sistemas de produção, ou pelo menos não faça globalmente. Você poderia, se e somente se entender completamente todas as consequências, fazê-lo caso a caso, consulta por consulta, mas isso não é recomendado. As dicas de consulta de hoje são os bugs de amanhã - use com cuidado.

    • A razão pela qual estou fazendo isso aqui é forçar o otimizador a escolher o índice em vez de uma varredura sequencial. Sem enable_seqscan = OFF, as tabelas de amostra muito pequenas aqui fariam com que o otimizador escolhesse automaticamente uma varredura sequencial. Com um grande número de registros em um sistema de produção, isso não deve ser um problema.

    Da documentação aqui :

    enable_seqscan (boolean)

    Ativa ou desativa o uso do planejador de consulta de tipos de plano de varredura sequencial. É impossível suprimir totalmente as varreduras sequenciais (*) , mas desativar essa variável desencoraja o planejador de usar uma se houver outros métodos disponíveis. O padrão é ativado.

    (*) Ênfase minha

    • 0

relate perguntas