適用対象:Microsoft Fabric の
ウェアハウス
ANY_VALUE関数は、行のグループから任意の (可能な場合はNULLではない) 値を返します。 集計関数とウィンドウ (分析) 関数の両方として使用できます。
- 集計使用法: グループ全体から任意の値を返します。
- ウィンドウの使用法: 定義されたウィンドウ フレームを操作し、ウィンドウ全体から任意の値を返します。
構文
集計関数の構文:
ANY_VALUE ( [ ALL | DISTINCT ] expression )
分析関数の構文:
ANY_VALUE ( [ ALL | DISTINCT ] expression) OVER ( [ <partition_by_clause> ] [ <order_by_clause> ] )
引数
全て
すべての値にこの集計関数を適用します。 ALL は既定の意味のあるオプションであり、ISO 互換性でのみ使用できます。
独特
DISTINCT は ANY_VALUEでは意味を持たず、ISO 互換でのみ使用できます。
expression
返される値。 結果として任意の値を返すことができますが、可能な場合、 NULL 値はスキップされます。
OVER 句
partition_by_clauseでは、FROM句によって生成された結果セットがパーティションに分割され、関数が各パーティションに適用されます。
この句を指定しない場合、関数はクエリ結果セットのすべての行を 1 つのグループとして扱います。
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の一般的なユース ケースは、キー列でグループ化された結果セットにキー以外の列を含める必要がある場合です。 たとえば、 StoreIDで行をグループ化する場合は、 ANY_VALUE を使用して、 GROUP BY 句に追加したり、 MAX、 MIN、 FIRST_VALUE、 LAST_VALUE などのより高価な関数を使用したりせずに、ストア名、住所、その他の記述属性などの列の値を返して、それらをプロジェクションに含めることができます。 この方法では、SQL クエリで説明列に対して不要なグループ化を実行する必要がないため、クエリの設計が簡略化され、読みやすさが向上し、パフォーマンスが向上します。 その結果、集計は簡潔で、保守が容易になり、効率が向上します。
例示
A. 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) のいずれかを返します。
B. プロジェクトの説明列
このクエリでは、 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、StoreFax、ZipCode、AddressLine1、AddressLine2など) をGROUP BY句に一覧表示せずに含めることができます。
C. 行から列への値のピボット解除
FactSales テーブルには、行項目ごとに 1 行が含まれています。ここで、OrderKeyは注文を識別します。 順序ごとに、 OrderDate、 DeliveryDate、 CustomerKey、 StoreKey などの属性が、同じ OrderKeyに属するすべての行で繰り返されます。 一方、 ProductKey は品目によって異なり、 LineNumberごとに 1 つの製品があります。
次のクエリでは、各OrderKeyが 1 つの行になるように、FactSales行をピボットします。 共有注文レベルの属性を保持し、各行番号に関連付けられている製品の個別の列 (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関数を使用すると、GROUP BY句にOrderDate、DeliveryDate、CustomerKey、およびStoreKeyを配置しないようにします。
ANY_VALUE関数はクエリを簡略化し、GROUP BY句で 1 つの列 (OrderKey) のみが使用されるため、パフォーマンスを向上させることができます。
ANY_VALUE
+
CASE WHEN パターンは、各行項目の適切なProductKeyを抽出し、それらを個別の列として返します。 実際には、このパターンでは、プロダクト キーのプログラムによるピボットが生成されます (従来の UNPIVOT 演算子の代替手段)。
D. 2 列パーティションあたりのランダム値
店舗ごとに日次主要業績評価指標 (KPI) を含む販売レベルの詳細レポートを作成しています。 レポートでは、特定のSalesOrderNumberを選択するビジネス ルールが存在しない (StoreKey、DateKey) パーティションごとに同じSalesOrderNumberを返す必要があります。 レポート内の行ごとに最も早い、最新、または最大の順序を選択する必要はありません。 たとえば、ユーザー インターフェイスでは各行の横に "店舗日の参照注文" が表示されるため、アナリストは (店舗、日) ペアから注文にすばやくジャンプできます。
意図は、(ストア、日) ごとに 1 つの一貫した 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列参照に置き換えた場合、ラベルは行ごとに異なります。"1 つの一貫性のあるラベル (ストア、日)" の動作は失われます。