ANY_VALUE (Transact-SQL)

Aplica-se a:Warehouse no Microsoft Fabric

A ANY_VALUE função devolve qualquer valor (não,NULL se possível) de um grupo de linhas. Pode usá-lo tanto como função agregada como função janela (analítica):

• Uso agregado: Devolve um valor arbitrário de todo o grupo.
• Utilização da janela: Opera sobre uma moldura de janela definida e devolve um valor arbitrário de toda a janela.

Transact-SQL convenções de sintaxe

Sintaxe

Sintaxe da função de agregação:

ANY_VALUE ( [ ALL | DISTINCT ] expression )

Sintaxe da função analítica:

ANY_VALUE ( [ ALL | DISTINCT ] expression) OVER ( [ <partition_by_clause> ] [ <order_by_clause> ] )

Argumentos

ALL

Aplica a função de agregação a todos os valores. ALL é a opção predefinida, a única significativa, e está disponível apenas para compatibilidade ISO.

DISTINTO

DISTINCT não faz sentido com ANY_VALUEa , e está disponível apenas para compatibilidade ISO.

expressão

O valor a ser devolvido. Qualquer um dos valores pode ser devolvido como resultado, mas os NULL valores são omitidos se possível.

Cláusula OVER

O partition_by_clause divide o conjunto de resultados produzido pela FROM cláusula em partições, e a função é aplicada a cada partição.

Se não especificares esta cláusula, a função trata todas as linhas do conjunto de resultados da consulta como um único grupo.

O order_by_clause determina a ordem dos dados antes que a função seja aplicada. Se especificar partition_by_clause, determina a ordem dos dados na partição. A order_by_clause não é obrigatória.

Para mais informações, consulte a cláusula SELECT - OVER (Transact-SQL).

Tipos de devolução

Devolve um valor do mesmo tipo que a expressão.

Observações

ANY_VALUE é não determinística. Para obter mais informações, consulte Funções determinísticas e não determinísticas. Ao contrário de FIRST_VALUE ou LAST_VALUE, ANY_VALUE não fornece ordenação determinística. Foi concebido para casos em que o valor exato não é importante para a lógica da consulta.

A função tenta devolver um não-valorNULL quando possível e só devolve NULL valor se todos os valores forem NULL.

Caso de utilização

Um caso ANY_VALUE de uso comum é quando é necessário incluir colunas não-chave num conjunto de resultados agrupadas por uma coluna chave. Por exemplo, se agrupar linhas por StoreID, pode usar ANY_VALUE para devolver valores de colunas como nome de armazenar, endereço ou outros atributos descritivos sem os adicionar à GROUP BY cláusula ou usar funções mais caras como MAX, MIN, FIRST_VALUE, ou LAST_VALUE incluí-los na projeção. Esta abordagem simplifica o design das consultas, melhora a legibilidade e o desempenho porque a consulta SQL não precisa de realizar agrupamentos desnecessários nas colunas descritivas. Como resultado, a sua agregação mantém-se concisa, mais fácil de manter e mais eficiente.

Exemplos

A. Recuperar qualquer valor não NULL

Esta consulta simples demonstra como ANY_VALUE pode devolver um valor arbitrário não NULL a partir de um conjunto de valores:

SELECT ANY_VALUE(v)
FROM (VALUES (NULL), (NULL), (NULL), (NULL), (2), (NULL), (NULL), (7), (NULL), (NULL)) AS t(v);

A função ignora NULL valores e devolve um dos não-valoresNULL (por vezes 2, por vezes 7) de forma não determinista.

B. Colunas descritivas do projeto

Esta consulta resume as vendas totais por loja juntando FactSales a DimStore, agrupando em StoreKey, e recuperando detalhes chave da loja usando ANY_VALUE.

USE ContosoDW;  
GO  
SELECT
    fs.StoreKey,
    ANY_VALUE(ds.StoreName)        AS StoreName,
    ANY_VALUE(ds.StoreDescription) AS StoreDescription,
    ANY_VALUE(ds.Status)           AS StoreStatus,
    ANY_VALUE(ds.Phone)            AS StorePhone,
    ANY_VALUE(ds.Fax)              AS StoreFax,
    ANY_VALUE(ds.ZipCode)          AS ZipCode,
    ANY_VALUE(ds.AddressLine1)     AS AddressLine1,
    ANY_VALUE(ds.AddressLine2)     AS AddressLine2,
    SUM(fs.UnitPrice * fs.SalesQuantity) AS SalesAmount
FROM dbo.FactSales AS fs
LEFT JOIN dbo.DimStore AS ds
    ON ds.StoreKey = fs.StoreKey
GROUP BY
    fs.StoreKey;  

Ao aplicar a ANY_VALUE função, pode incluir colunas que não agrupam (como StoreName, StoreDescription, StoreStatus, StorePhone, StoreFax, ZipCode, AddressLine1, , e AddressLine2) sem as listar na GROUP BY cláusula.

C. Valores de despivô de linhas para colunas

A FactSales tabela contém uma linha por item de linha, onde OrderKey identifica a ordem. Para cada ordem, atributos como OrderDate, DeliveryDate, CustomerKey, e StoreKey são repetidos em todas as linhas pertencentes à mesma OrderKey. Em contraste, ProductKey varia consoante o artigo, com um produto por LineNumber.

A consulta seguinte pivota as FactSales linhas para que cada OrderKey uma seja uma única linha. Mantém os atributos partilhados ao nível da ordem e cria uma coluna separada (ProductKey0, ProductKey1, ...) para o produto associado a cada número de linha. A ANY_VALUE função é usada para escolher um valor representativo de cada grupo, enquanto as expressões condicionais extraem o produto para cada item específico.

SELECT
    OrderKey,
    -- Projecting groups that are same within the group.
    ANY_VALUE(OrderDate)      AS OrderDate,
    ANY_VALUE(DeliveryDate)   AS DeliveryDate,
    ANY_VALUE(CustomerKey)    AS CustomerKey,
    ANY_VALUE(StoreKey)       AS StoreKey,
    -- Unpivoted values returned as multiple columns per row
    ANY_VALUE(IIF(LineNumber = 0, ProductKey, NULL)) AS ProductKey0,
    ANY_VALUE(IIF(LineNumber = 1, ProductKey, NULL)) AS ProductKey1,
    ANY_VALUE(IIF(LineNumber = 2, ProductKey, NULL)) AS ProductKey2,
    ANY_VALUE(IIF(LineNumber = 3, ProductKey, NULL)) AS ProductKey3,
    ANY_VALUE(IIF(LineNumber = 4, ProductKey, NULL)) AS ProductKey4,
    ANY_VALUE(IIF(LineNumber = 5, ProductKey, NULL)) AS ProductKey5,
    ANY_VALUE(IIF(LineNumber = 6, ProductKey, NULL)) AS ProductKey6
FROM dbo.FactSales
GROUP BY
    OrderKey;

Ao usar a ANY_VALUE função, evita colocar OrderDate, DeliveryDate, CustomerKey, e StoreKey na GROUP BY cláusula. A ANY_VALUE função simplifica a consulta e pode melhorar o desempenho porque apenas uma única coluna (OrderKey) é usada na GROUP BY cláusula. O ANY_VALUE + CASE WHEN padrão extrai o apropriado ProductKey para cada item e devolve-os como colunas separadas. Na prática, este padrão produz um pivot programático das chaves de produto (uma alternativa ao operador tradicional UNPIVOT ).

D. Valor aleatório por partição de duas colunas

Está a produzir um relatório detalhado ao nível das vendas com um indicador diário de desempenho (KPI) por loja. No relatório, é necessário devolver o mesmo SalesOrderNumber por partição (StoreKey, DateKey) onde não existe regra de negócio para escolher um determinado SalesOrderNumber. Não há necessidade de escolher a ordem mais antiga, mais recente ou a maior por linha no relatório. Por exemplo, a interface de utilizador mostra "uma encomenda de referência para o dia da loja" ao lado de cada linha, para que um analista possa rapidamente saltar para uma encomenda do par (loja, dia).

A intenção é devolver um consistente SalesOrderNumber por (loja, dia).

USE ContosoDW;
GO
SELECT
    fs.DateKey,
    fs.StoreKey,

    -- Window KPI: total sales per Store-Day (keeps row-level output)
    SUM(fs.UnitPrice * fs.SalesQuantity)
      OVER (PARTITION BY fs.StoreKey, dd.DateKey) AS DailySales,

    -- Partition label with no preferred ordering: any one order from that Store-Day
    ANY_VALUE(fs.SalesOrderNumber)
      OVER (PARTITION BY fs.StoreKey, dd.DateKey) AS SampleOrderNumber

FROM dbo.FactSales AS fs;

Se substituíres a ANY_VALUE(fs.SalesOrderNumber) expressão por fs.SalesOrderNumber referência de coluna, o rótulo varia linha a linha; perdes o comportamento de "uma etiqueta consistente por (armazenar, dia)".

Conteúdo relacionado

• Funções agregadas (Transact-SQL)
• Funções analíticas (Transact-SQL)
• Cláusula SELECT - OVER (Transact-SQL)