Início / dba / Perguntas / 63661
Accepted
Alexandre Neto
Asked: 2014-04-23 08:16:45 +0800 CST

No PostgreSQL, existe uma função agregada first() com segurança de tipo?

  • 772

Estou procurando uma first()função agregada.
Aqui encontrei algo que quase funciona:

CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement)
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
    SELECT $1;
$$;
 
-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
    sfunc    = public.first_agg,
    basetype = anyelement,
    stype    = anyelement
);

O problema é que quando uma varchar(n)coluna passa pela first()função, ela é convertida em simples varchar(sem modificador de tamanho). Tentando retornar a consulta em uma função as RETURNS SETOF anyelement, recebo o seguinte erro:

ERROR: structure of query does not match function result type Estado de
SQL:42804
Detalhe:Returned type character varying does not match expected type character varying(40) in column 2.
Contexto:PL/pgSQL function vsr_table_at_time(anyelement,timestamp without time zone) line 31 at RETURN QUERY

Na mesma página wiki há um link para uma versão C da função que substituiria a acima. Não sei como instalá-lo, mas gostaria de saber se esta versão poderia resolver meu problema.

Enquanto isso, existe uma maneira de alterar a função acima para que ela retorne exatamente o mesmo tipo da coluna de entrada?

postgresql functions

4 respostas

  1. Best Answer

    DISTINCT ON()

    Apenas como uma nota lateral, é exatamente isso DISTINCT ON()que (não deve ser confundido com DISTINCT)

    SELECT DISTINCT ON ( expression [, ...] ) mantém apenas a primeira linha de cada conjunto de linhas em que as expressões fornecidas são avaliadas como iguais . As DISTINCT ONexpressões são interpretadas usando as mesmas regras de ORDER BY(veja acima). Observe que a "primeira linha" de cada conjunto é imprevisível, a menos que ORDER BYseja usada para garantir que a linha desejada apareça primeiro. Por exemplo

    Então, se você fosse escrever,

    SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

    É efetivamente

    SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

    Em que leva o primeiro z. Existem duas diferenças importantes,

    1. Você também pode selecionar outras colunas sem custo de agregação adicional.

      SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

    2. Porque não há GROUP BYvocê não pode usar agregados (reais) com ele.

      CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

    Não se esqueçaORDER BY

    Além disso, embora eu não tenha colocado em negrito, agora vou

    Observe que a "primeira linha" de cada conjunto é imprevisível, a menos que ORDER BY seja usado para garantir que a linha desejada apareça primeiro. Por exemplo

    Sempre use um ORDER BYcomDISTINCT ON

    Usando uma função agregada de conjunto ordenado

    Imagino que muitas pessoas estejam procurando por Funções Agregadas de Conjuntofirst_value Ordenado . Só queria jogar isso lá fora. Ficaria assim, se a função existisse:

    SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

    Mas, infelizmente, você pode fazer isso.

    SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;
    • 35

  2. Yay, eu descobri uma maneira fácil com o seu caso usando alguns recursos do PostgreSQL 9.4+

    Vejamos este exemplo:

    select  (array_agg(val ORDER BY i))[1] as first_value_orderby_i,
    (array_agg(val ORDER BY i DESC))[1] as last_value_orderby_i,
    (array_agg(val))[1] as last_value_all,
    (array_agg(val))[array_length(array_agg(val),1)] as last_value_all
   FROM (
        SELECT i, random() as val
        FROM generate_series(1,100) s(i)
        ORDER BY random()
    ) tmp_tbl

    Espero que ajude no seu caso.

    • 11

  3. Não é uma resposta direta à sua pergunta, mas você deve tentar a first_valuefunção de janela. Funciona assim:

    CREATE TABLE test (
    id SERIAL NOT NULL PRIMARY KEY,
    cat TEXT,
    value VARCHAR(2)
    date TIMESTAMP WITH TIME ZONE

    );

    Então, se você quiser o primeiro item em cada cat(categoria), você consultará assim:

    SELECT
    cat,
    first_value(date) OVER (PARTITION BY cat ORDER BY date)
FROM
    test;

    ou:

    SELECT
    cat,
    first_value(date) OVER w
FROM
    test
WINDOW w AS (PARTITION BY cat ORDER BY date);
    • 7

  4. Tenho certeza de que a pergunta não é mais real para o iniciador do tópico, mas para quem procura solução em 2021, há um truque simples - array_agg

    por exemplo :

    SELECT (array_agg(t.f0) FILTER (WHERE t.f0 IS NOT NULL))[1] agg FROM t GROUP BY t.f1

    • -1

relate perguntas