ANY_VALUE (Transact-SQL)

Область применения:хранилище в Microsoft Fabric

Функция ANY_VALUE возвращает любое (NULL если возможное) значение из группы строк. Ее можно использовать как агрегатную функцию, так и функцию окна (аналитика):

• Статистическое использование: возвращает произвольное значение из всей группы.
• Использование окна: работает над определенным фреймом окна и возвращает произвольное значение из всего окна.

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис функции агрегирования:

ANY_VALUE ( [ ALL | DISTINCT ] expression )

Синтаксис функции аналитики:

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

Аргументы

ВСЕ

Применяет агрегатную функцию ко всем значениям. ALL — это параметр по умолчанию, только значимый параметр и доступен только для совместимости ISO.

УНИКАЛЬНЫЙ

DISTINCT не имеет значения и ANY_VALUEдоступен только для совместимости ISO.

выражение

Возвращаемое значение. Любое из значений может быть возвращено в качестве результата, но NULL значения пропускаются, если это возможно.

Предложение OVER

Partition_by_clause делит результирующий набор, созданный FROM предложением, на секции, и функция применяется к каждой секции.

Если это предложение не указано, функция обрабатывает все строки результирующих наборов запросов как одну группу.

order_by_clause определяет порядок данных перед применением функции. Если указать partition_by_clause, он определяет порядок данных в секции. Order_by_clause не требуется.

Дополнительные сведения см. в предложении SELECT - OVER (Transact-SQL).

Типы возвращаемых данных

Возвращает значение того же типа, что и выражение.

Замечания

Функция ANY_VALUE не детерминирована. Дополнительные сведения см. в разделе детерминированные и недетерминированные функции. В отличие FIRST_VALUE от или LAST_VALUE, ANY_VALUE не обеспечивает детерминированное упорядочение. Он предназначен для случаев, когда точное значение не важно для логики запроса.

Функция пытается вернуть значение, отличноеNULL от возможного, и возвращает NULL значение только в том случае, если все значения имеют значение NULL.

Сценарий использования

Распространенный вариант ANY_VALUE использования заключается в том, что необходимо включить неключевые столбцы в результирующий набор, сгруппированный по ключевому столбцу. Например, если вы группируете строки по, можно использовать ANY_VALUE для возврата значений для столбцовStoreID, таких как имя хранилища, адрес или другие описательные атрибуты, не добавляя их в GROUP BY предложение или используя более дорогие функции, например MAX, MINFIRST_VALUEили LAST_VALUE включать их в проекцию. Этот подход упрощает проектирование запросов, улучшает удобочитаемость и повышает производительность, так как SQL-запрос не должен выполнять ненужные группировки по описательным столбцам. В результате агрегирование остается кратким, проще поддерживать и эффективнее.

Примеры

А. Получение любого значения, отличного от NULL

В этом простом запросе показано, как ANY_VALUE возвращать произвольное значение, отличное от NULL, из набора значений:

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

Функция игнорирует NULL значения и возвращает одно из недетерминированныхNULL значений (иногда 2, иногда 7).

В. Описательные столбцы проекта

Этот запрос суммирует общий объем продаж для каждого магазина, присоединяясь FactSales к ним, DimStoreгруппируя StoreKeyи извлекая сведения о хранилище ключей с помощью 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;  

Применяя функциюANY_VALUE, можно включить негрупповые столбцы (напримерStoreName, , StoreDescription, StoreStatus, StorePhone, StoreFaxZipCodeAddressLine1и AddressLine2) без перечисления их в предложенииGROUP BY.

C. Отмена сводных значений из строк в столбцы

Таблица FactSales содержит одну строку для каждого элемента строки, где OrderKey определяется порядок. Для каждого порядка атрибуты, такие как OrderDate, CustomerKeyDeliveryDateи StoreKey повторяются во всех строках, принадлежащих одному и тому жеOrderKey. В отличие от этого, ProductKey зависит от элемента строки, с одним продуктом на LineNumberкаждый.

Следующий запрос сводит FactSales строки таким образом, чтобы каждая из них OrderKey была одной строкой. Он сохраняет общие атрибуты уровня заказа и создает отдельный столбец (ProductKey0, ProductKey1...) для продукта, связанного с каждым номером строки. Функция ANY_VALUE используется для выбора репрезентативного значения из каждой группы, а условные выражения извлекают продукт для каждого конкретного элемента строки.

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;

Используя функцию, вы избегаете ANY_VALUE размещения OrderDate, CustomerKeyDeliveryDateи StoreKey в предложенииGROUP BY. Функция ANY_VALUE упрощает запрос и может повысить производительность, так как в GROUP BY предложении используется только один столбец (OrderKey). Шаблон ANY_VALUE + CASE WHEN извлекает соответствующий ProductKey элемент строки и возвращает их в виде отдельных столбцов. На практике этот шаблон создает программную сводку ключей продукта (альтернативу традиционному UNPIVOT оператору).

Д. Случайное значение для двух секций столбцов

Вы создаете отчет о подробном уровне продаж с ежедневным ключевым показателем производительности (KPI) для каждого магазина. В отчете необходимо вернуть ту же SalesOrderNumber секцию (StoreKey, DateKey), где для выбора конкретного бизнес-правила нет бизнес-правила SalesOrderNumber. Нет необходимости выбирать самые ранние, последние или наибольшие заказы на строку в отчете. Например, в пользовательском интерфейсе отображается "ссылочный заказ для магазина" рядом с каждой строкой, чтобы аналитик может быстро перейти к заказу из пары (магазин, день).

Цель состоит в том, чтобы вернуть один согласованный SalesOrderNumber на (магазин, день).

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;

Если вы заменяете ANY_VALUE(fs.SalesOrderNumber) выражение fs.SalesOrderNumber ссылкой на столбец, метка зависит от строки; вы потеряете поведение "одна согласованная метка на (магазин, день)".

Связанный контент

• Агрегатные функции (Transact-SQL)
• Функции аналитики (Transact-SQL)
• ПРЕДЛОЖЕНИЕ SELECT — OVER (Transact-SQL)