FIRST_VALUE e LAST_VALUE no PostgreSQL: primeiro e ultimo valor por particao e a armadilha do frame

FIRST_VALUE e LAST_VALUE sao funcoes de janela que retornam o valor da primeira ou da ultima linha de uma janela, sem colapsar o resultado em uma unica linha. Elas respondem a perguntas como "qual foi o primeiro pedido deste usuario" ou "quem ganha mais neste departamento", mantendo cada linha original no lugar.

Sintaxe e exemplo basico

Ambas as funcoes operam sobre OVER (...) com um ORDER BY obrigatorio dentro da janela; sem ele, "primeira" e "ultima" nao significam nada. PARTITION BY define os grupos dentro dos quais a janela e calculada.

SELECT
    id,
    dept,
    name,
    salary,
    FIRST_VALUE(name) OVER (
        PARTITION BY dept
        ORDER BY salary DESC
    ) AS top_earner
FROM employees;

Aqui, para cada funcionario, a coluna top_earner contem o nome da pessoa mais bem paga do seu departamento. Alguns comportamentos que vale conhecer:

• O resultado e um escalar de uma linha especifica da janela, nao um agregado. Seu tipo coincide com o do argumento.
• FIRST_VALUE funciona "como esperado" com o frame padrao, porque o inicio da janela esta sempre fixado na primeira linha.
• LAST_VALUE com esse mesmo frame padrao e traicoeira, como se ve abaixo.

A armadilha do frame de LAST_VALUE

O erro mais comum: escrever LAST_VALUE e obter a linha atual em vez da ultima. A causa e o frame padrao da janela, que e RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. A janela cresce do inicio da particao ate a linha atual, entao sua "ultima" linha e sempre a atual.

-- WRONG: returns the current row's score, not the partition's last
SELECT
    id,
    team_id,
    score,
    LAST_VALUE(score) OVER (
        PARTITION BY team_id
        ORDER BY score DESC
    ) AS wrong_last
FROM scores;

Para que LAST_VALUE enxergue toda a particao, amplie o frame de forma explicita ate as duas bordas da janela:

SELECT
    id,
    team_id,
    score,
    LAST_VALUE(score) OVER (
        PARTITION BY team_id
        ORDER BY score DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_score
FROM scores;

Pegadinha: FIRST_VALUE e imune a isso porque a borda esquerda do frame e sempre UNBOUNDED PRECEDING. Mas no momento em que voce adiciona um ORDER BY a janela, o frame padrao corta a borda direita para CURRENT ROW, e e exatamente isso que quebra o LAST_VALUE. Na duvida, declare o frame.

Primeiro e ultimo em uma unica consulta

Muitas vezes voce precisa das duas bordas ao mesmo tempo, por exemplo o primeiro e o ultimo pedido de um usuario por data. Para nao repetir uma definicao de janela longa, extraia-a para uma clausula WINDOW:

SELECT DISTINCT
    user_id,
    FIRST_VALUE(amount) OVER w AS first_order_amount,
    LAST_VALUE(amount)  OVER w AS last_order_amount
FROM orders
WINDOW w AS (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

DISTINCT colapsa as linhas identicas da particao em uma so. Repare que ambas as chamadas compartilham o mesmo frame w, entao FIRST_VALUE pega o pedido mais antigo e LAST_VALUE o mais recente.

Quando algo mais simples encaixa

FIRST_VALUE e LAST_VALUE brilham quando voce precisa manter todas as linhas e anexar um valor de borda a cada uma. Mas se voce so quer uma linha por grupo, outras construcoes costumam ser mais enxutas:

• No PostgreSQL, DISTINCT ON (user_id) ... ORDER BY user_id, created_at retorna a primeira linha de um grupo sem nenhuma janela.
• Para um agregado por grupo, um simples MAX(salary) / MIN(salary) com GROUP BY dept ja basta.
• NTH_VALUE(amount, 2) OVER w pega a N-esima linha da janela e tambem precisa de um frame amplo.

Diferencas no MySQL e no ClickHouse

O MySQL 8+ tem FIRST_VALUE e LAST_VALUE e elas se comportam de forma identica, incluindo a mesma armadilha do frame padrao; amplie a janela com ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING exatamente do mesmo jeito. O MySQL 5.7 nao tem funcoes de janela, entao voce as emula com subconsultas ou GROUP_CONCAT.

-- MySQL 8: same frame trap, same fix
SELECT
    team_id,
    score,
    LAST_VALUE(score) OVER (
        PARTITION BY team_id
        ORDER BY score DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_score
FROM scores;

O ClickHouse tambem as suporta, mas argMax(name, salary) e argMin(name, salary) costumam ser mais praticas: retornam o valor de uma coluna na linha com o maximo ou o minimo de outra, sem frame explicito. Quando voce precisa especificamente de um resultado por linha que preserve todas as linhas, a forma de janela com um frame amplo continua sendo a mais portavel.