Uma função de janela realiza cálculos em um conjunto de linhas da tabela que estão de alguma forma relacionadas à linha corrente. Isso é comparável ao tipo de cálculo que pode ser feito por uma função de agregação. Entretanto, as funções de janela não fazem com que as linhas sejam agrupadas em uma única linha de saída, como faria uma chamada a uma função de agregação padrão. Em vez disso, cada linha mantém a sua própria identidade. Nos bastidores, a função de janela consegue acessar outras linhas, além da linha corrente do resultado da consulta.

Abaixo está um exemplo mostrando como comparar o salário de cada funcionário com o salário médio de seu departamento:

SELECT nome_dep, num_emp, salario,
       avg(salario) OVER (PARTITION BY nome_dep)
    FROM salario_emp;

    nome_dep     | num_emp | salario |          avg
-----------------+---------+---------+-----------------------
 desenvolvimento |      11 |    5200 | 5020.0000000000000000
 desenvolvimento |       7 |    4200 | 5020.0000000000000000
 desenvolvimento |       9 |    4500 | 5020.0000000000000000
 desenvolvimento |       8 |    6000 | 5020.0000000000000000
 desenvolvimento |      10 |    5200 | 5020.0000000000000000
 pessoal         |       5 |    3500 | 3700.0000000000000000
 pessoal         |       2 |    3900 | 3700.0000000000000000
 vendas          |       3 |    4800 | 4866.6666666666666667
 vendas          |       1 |    5000 | 4866.6666666666666667
 vendas          |       4 |    4800 | 4866.6666666666666667
(10 linhas)

As três primeiras colunas da saída vêm diretamente da tabela salario_emp, e há uma linha na saída para cada linha da tabela. A quarta coluna mostra a média calculada sobre todas as linhas da tabela possuindo o mesmo valor de nome_dep da linha corrente. (Na verdade, essa função avg é a mesma função de agregação avg não-janela, mas a cláusula OVER faz com que ela seja tratada como função de janela e computada no quadro da janela).

A chamada de uma função de janela sempre contém a cláusula OVER logo após o nome e o(s) argumento(s) da função de janela. Isso é o que distingue sintaticamente uma função de janela de uma função normal, ou agregação não-janela. A cláusula OVER determina exatamente como as linhas da consulta vão ser divididas para o processamento pela função de janela. A cláusula PARTITION BY dentro de OVER divide as linhas em grupos, ou partições, que compartilham os mesmos valores da(s) expressão(ões) na cláusula PARTITION BY. Para cada linha, a função de janela é calculada sobre as linhas que se encontram na mesma partição que a linha corrente.

Você também pode controlar a ordem pela qual as linhas são processadas pelas funções de janela usando a cláusula ORDER BY dentro da cláusula OVER. (A janela processada pelo ORDER BY não precisa corresponder à ordem em que as linhas serão exibidas.) Aqui está um exemplo:

SELECT nome_dep, num_emp, salario,
       row_number() OVER (PARTITION BY nome_dep
                    ORDER BY salario DESC)
FROM salario_emp;

    nome_dep     | num_emp | salario | row_number
-----------------+---------+---------+------------
 desenvolvimento |       8 |    6000 |          1
 desenvolvimento |      10 |    5200 |          2
 desenvolvimento |      11 |    5200 |          3
 desenvolvimento |       9 |    4500 |          4
 desenvolvimento |       7 |    4200 |          5
 pessoal         |       2 |    3900 |          1
 pessoal         |       5 |    3500 |          2
 vendas          |       1 |    5000 |          1
 vendas          |       4 |    4800 |          2
 vendas          |       3 |    4800 |          3
(10 linhas)

Como pode ser visto acima, a função de janela row_number atribui números sequenciais às linhas de cada partição, na ordem definida pela cláusula ORDER BY (com as linhas ligadas numeradas em uma ordem não especificada). A função row_number não requer nenhum parâmetro explícito, porque seu comportamento é inteiramente determinado pela cláusula OVER.

As linhas consideradas por uma função de janela são aquelas da “tabela virtual” produzida pela cláusula FROM da consulta filtrada por suas cláusulas WHERE, GROUP BY e HAVING, se houver. Por exemplo, uma linha rejeitada por não atender à condição WHERE não é vista por nenhuma função de janela. Uma consulta pode conter várias funções de janela que dividem os dados de diferentes maneiras por meio de diferentes cláusulas OVER, mas todas agem na mesma coleção de linhas definida por esta tabela virtual.

Já foi visto que ORDER BY pode ser omitido quando a ordem das linhas não tem importância. Também é possível omitir PARTITION BY e, nesse caso, haverá uma única partição contendo todas as linhas.

Há outro conceito importante associado às funções de janela: para cada linha há um conjunto de linhas dentro de sua partição chamado de quadro da janela (window frame). Algumas funções de janela atuam apenas nas linhas do quadro da janela, em vez de toda a partição. Por padrão, se uma cláusula ORDER BY for especificada, o quadro da janela contém todas as linhas desde o início da partição até a linha corrente, assim como todas as linhas seguintes que sejam iguais à linha corrente, segundo a cláusula ORDER BY. Quando ORDER BY é omitido, o quadro padrão contém todas as linhas da partição. ^[12] Aqui está um exemplo usando sum:

SELECT salario,
       sum(salario) OVER ()
    FROM salario_emp;

 salario |  sum
---------+-------
    5200 | 47100
    5000 | 47100
    3500 | 47100
    4800 | 47100
    3900 | 47100
    4200 | 47100
    4500 | 47100
    4800 | 47100
    6000 | 47100
    5200 | 47100
(10 linhas)

No exemplo acima, como não há ORDER BY na cláusula OVER, o quadro da janela é o mesmo da partição, que por falta de PARTITION BY é a tabela inteira; em outras palavras, cada soma é calculada sobre toda a tabela, portanto obtemos o mesmo resultado para todas as linhas da saída. Mas se adicionarmos uma cláusula ORDER BY, obteremos resultados muito diferentes:

SELECT salario,
       sum(salario) OVER (ORDER BY salario)
    FROM salario_emp;

 salario |  sum
---------+-------
    3500 |  3500
    3900 |  7400
    4200 | 11600
    4500 | 16100
    4800 | 25700
    4800 | 25700
    5000 | 30700
    5200 | 41100
    5200 | 41100
    6000 | 47100
(10 linhas)

Aqui a soma é calculada a partir do primeiro salário (ou seja, o mais baixo) até o salário corrente, incluindo todos os valores duplicados do salário corrente (observe os resultados para os salários duplicados).

As funções de janela só são permitidas na lista do SELECT e na cláusula ORDER BY da consulta. Elas são proibidas nos outros lugares, como nas cláusulas GROUP BY, HAVING e WHERE. Isso acontece, porque são executadas após o processamento dessas cláusulas. Além disso, as funções de janela são executadas após as funções de agregação que não são de janela, significando ser válido incluir uma chamada de função de agregação nos argumentos de uma função de janela, mas não vice-versa.

Se houver necessidade de filtrar ou agrupar linhas após a execução dos cálculos das funções da janela, pode ser usada uma subconsulta. Por exemplo:

SELECT nome_dep, num_emp, salario, data_adm
FROM
  (SELECT nome_dep, num_emp, salario, data_adm,
      row_number() OVER (PARTITION BY nome_dep
                   ORDER BY salario DESC, num_emp) AS pos
     FROM salario_emp
  ) AS ss
WHERE pos < 3;

A consulta acima mostra apenas as linhas da consulta interna que possuem row_number menor que 3. (ou seja, as duas primeiras linhas para cada departamento).

Quando uma consulta envolve várias funções de janela, é possível escrever cada uma delas com uma cláusula OVER separada, mas isso leva a duplicidades, ficando propenso a erros se o mesmo comportamento de janela for desejado para várias funções. Em vez disso, cada comportamento de janela pode ser associado a um nome em uma cláusula WINDOW e, depois, referenciado em OVER. Por exemplo:

SELECT sum(salario) OVER w,
       avg(salario) OVER w
  FROM salario_emp
  WINDOW w AS (PARTITION BY nome_dep
               ORDER BY salario DESC);

Mais detalhes sobre as funções da janela podem ser encontrados em Seção 4.2.8, Seção 9.22, Seção 7.2.5, e na página de referência do comando SELECT.