Categorias:: Sintaxe e uso da função de janela (classificação)

FIRST_VALUE¶

Retorna o primeiro valor dentro de um grupo ordenado de valores.

Consulte também:: LAST_VALUE , NTH_VALUE

Sintaxe¶

FIRST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ]
  OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2>  [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ <window_frame> ] )

Para uma sintaxe window_frame detalhada, consulte Sintaxe e uso da função de janela.

Argumentos¶

expr

A expressão que determina o valor de retorno.

expr1

A expressão pela qual as linhas são particionadas. Você pode especificar uma única expressão ou uma lista de expressões separadas por vírgula. Por exemplo:

PARTITION BY column_1, column_2

expr2

A expressão pela qual se ordenam as linhas. Você pode especificar uma única expressão ou uma lista de expressões separadas por vírgula. Por exemplo:

ORDER BY column_3, column_4

{ IGNORE | RESPECT } NULLS

Se você deve ignorar ou respeitar os valores de NULL quando um expr contiver valores de NULL:

IGNORE NULLS retorna o primeiro valor diferente de NULL.
RESPECT NULLS retorna um valor NULL se for o primeiro valor na expressão.

Padrão: RESPECT NULLS

Notas de uso¶

Esta função é uma função relacionada à classificação, portanto, deve especificar uma janela. Uma cláusula de janela consiste nas seguintes subcláusulas:
- Subcláusula PARTITION BY expr1 (opcional).
- Subcláusula ORDER BY expr2 (obrigatória). Para obter detalhes sobre outras opções de ordenação compatíveis (ordem de classificação, ordenação dos valores NULL e assim por diante), consulte a documentação da cláusula ORDER BY, que segue as mesmas regras.
- Subcláusula window_frame (opcional).
A ordem das linhas em uma janela (e portanto o resultado da consulta) é totalmente determinística somente se as chaves na cláusula ORDER BY tornarem cada linha única. Considere o seguinte exemplo:
```
... OVER (PARTITION BY p ORDER BY o COLLATE 'lower') ...
```
O resultado da consulta pode variar se qualquer partição contiver valores da coluna o que sejam idênticos, ou seriam idênticos em uma comparação que não diferencia maiúsculas e minúsculas.
A cláusula ORDER BY dentro da cláusula OVER controla a ordem das linhas somente dentro da janela, não a ordem das linhas na saída de toda a consulta. Para controlar a ordem de saída, use uma cláusula ORDER BY separada no nível mais externo da consulta.

O window_frame opcional especifica o subconjunto de linhas dentro da janela para as quais a função é calculada. Se nenhum window_frame for especificado, o padrão é a janela inteira:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Observe que isso se desvia da norma ANSI, que especifica o seguinte padrão para quadros de janelas:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Para uma sintaxe window_frame detalhada, consulte Sintaxe e uso da função de janela.

Exemplos¶

Este exemplo mostra uma consulta que usa a função FIRST_VALUE para encontrar o item de menu mais barato em cada categoria. A consulta contém duas cláusulas ORDER BY: uma para controlar a ordem das linhas em cada partição e outra para classificar a saída da consulta completa. Para criar e carregar a tabela utilizada neste exemplo, consulte Criação e carregamento da tabela menu_items.

SELECT menu_category, menu_item_name, menu_price_usd,
       FIRST_VALUE(menu_item_name) OVER (PARTITION BY menu_category ORDER BY menu_price_usd) AS cheapest_item
  FROM menu_items
  WHERE menu_category IN ('Beverage', 'Dessert', 'Snack')
  ORDER BY menu_category, menu_price_usd
  LIMIT 12;

+---------------+--------------------+----------------+---------------+
| MENU_CATEGORY | MENU_ITEM_NAME     | MENU_PRICE_USD | CHEAPEST_ITEM |
|---------------+--------------------+----------------+---------------|
| Beverage      | Bottled Water      |           2.00 | Bottled Water |
| Beverage      | Iced Tea           |           3.00 | Bottled Water |
| Beverage      | Bottled Soda       |           3.00 | Bottled Water |
| Beverage      | Lemonade           |           3.50 | Bottled Water |
| Dessert       | Popsicle           |           3.00 | Popsicle      |
| Dessert       | Ice Cream Sandwich |           4.00 | Popsicle      |
| Dessert       | Mango Sticky Rice  |           5.00 | Popsicle      |
| Dessert       | Sugar Cone         |           6.00 | Popsicle      |
| Dessert       | Waffle Cone        |           6.00 | Popsicle      |
| Dessert       | Two Scoop Bowl     |           7.00 | Popsicle      |
| Snack         | Spring Mix Salad   |           6.00 | Fried Pickles |
| Snack         | Fried Pickles      |           6.00 | Fried Pickles |
+---------------+--------------------+----------------+---------------+

O exemplo a seguir também usa a tabela menu_items para comparar três funções relacionadas: FIRST_VALUE, NTH_VALUE e LAST_VALUE:

A consulta cria um quadro de janela deslizante com três linhas de largura, que contém:
- A linha que precede a linha atual.
- A linha atual.
- A linha que segue a linha atual.
A 2 na chamada NTH_VALUE(menu_price_usd, 2) especifica a segunda linha no quadro da janela (que, neste caso, é também a linha atual).
Quando a linha atual é a primeira linha no quadro da janela, não há linha anterior para referência, portanto FIRST_VALUE retorna um NULL para aquela linha.
Às vezes, os limites da estrutura se estendem além das linhas em uma partição, mas as linhas inexistentes não são incluídas nos cálculos da função de janela. Por exemplo, quando a linha atual é a primeira linha da partição e o quadro de janela é ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, não há nenhuma linha anterior a ser referenciada, portanto, a função FIRST_VALUE retorna o valor da primeira linha da partição.
Os resultados nunca são iguais para todas as três funções, considerando os dados da tabela. Essas funções selecionam os valores first, last ou nth para cada linha do quadro, e a seleção de valores se aplica separadamente a cada partição.

SELECT menu_category, menu_item_name, menu_price_usd,
       FIRST_VALUE(menu_price_usd) OVER (PARTITION BY menu_category ORDER BY menu_price_usd
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS first_val,
       NTH_VALUE(menu_price_usd, 2) OVER (PARTITION BY menu_category ORDER BY menu_price_usd
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS nth_val,
       LAST_VALUE(menu_price_usd) OVER (PARTITION BY menu_category ORDER BY menu_price_usd
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS last_val
  FROM menu_items
  WHERE menu_category = 'Dessert'
  ORDER BY menu_price_usd;

+---------------+--------------------+----------------+-----------+---------+----------+
| MENU_CATEGORY | MENU_ITEM_NAME     | MENU_PRICE_USD | FIRST_VAL | NTH_VAL | LAST_VAL |
|---------------+--------------------+----------------+-----------+---------+----------|
| Dessert       | Popsicle           |           3.00 |      3.00 |    4.00 |     4.00 |
| Dessert       | Ice Cream Sandwich |           4.00 |      3.00 |    4.00 |     5.00 |
| Dessert       | Mango Sticky Rice  |           5.00 |      4.00 |    5.00 |     6.00 |
| Dessert       | Sugar Cone         |           6.00 |      6.00 |    6.00 |     7.00 |
| Dessert       | Waffle Cone        |           6.00 |      5.00 |    6.00 |     6.00 |
| Dessert       | Two Scoop Bowl     |           7.00 |      6.00 |    7.00 |     7.00 |
+---------------+--------------------+----------------+-----------+---------+----------+

Este exemplo demonstra a diferença entre IGNORE NULLS e RESPECT NULLS. Os dados de amostra incluem linhas em que o valor de custo é NULL. Com o comportamento RESPECT NULLS padrão, se a primeira linha na partição ordenada tiver um valor NULL, FIRST_VALUE retornará NULL. Com IGNORE NULLS, FIRST_VALUE ignora valores NULL e retorna o primeiro valor diferente de NULL.

SELECT item_name, item_cost, item_price,
       FIRST_VALUE(item_cost) RESPECT NULLS
         OVER (ORDER BY item_price) AS first_cost_respect,
       FIRST_VALUE(item_cost) IGNORE NULLS
         OVER (ORDER BY item_price) AS first_cost_ignore
  FROM VALUES
    ('Pretzel', NULL, 3.00),
    ('Corn Dog', NULL, 4.00),
    ('Hot Dog', 1.50, 5.00),
    ('Sandwich', 2.50, 6.00)
  AS menu(item_name, item_cost, item_price)
  ORDER BY item_price;

+-----------+-----------+------------+--------------------+-------------------+
| ITEM_NAME | ITEM_COST | ITEM_PRICE | FIRST_COST_RESPECT | FIRST_COST_IGNORE |
|-----------+-----------+------------+--------------------+-------------------|
| Pretzel   |      NULL |       3.00 |               NULL |              1.50 |
| Corn Dog  |      NULL |       4.00 |               NULL |              1.50 |
| Hot Dog   |      1.50 |       5.00 |               NULL |              1.50 |
| Sandwich  |      2.50 |       6.00 |               NULL |              1.50 |
+-----------+-----------+------------+--------------------+-------------------+