Bagikan melalui

ANY_VALUE (Transact-SQL)

Berlaku untuk:Gudang di Microsoft Fabric

Fungsi mengembalikan ANY_VALUE nilai apa pun (tidakNULL jika memungkinkan) dari sekelompok baris. Anda dapat menggunakannya sebagai fungsi agregat dan fungsi jendela (analitik):

  • Penggunaan agregat: Mengembalikan nilai arbitrer dari seluruh grup.
  • Penggunaan jendela: Beroperasi di atas bingkai jendela yang ditentukan dan mengembalikan nilai arbitrer dari seluruh jendela.

Konvensi sintaks Transact-SQL

Sintaksis

Sintaks fungsi agregasi:

ANY_VALUE ( [ ALL | DISTINCT ] expression )

Sintaks fungsi analitik:

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

Argumen

SEMUA

Menerapkan fungsi agregat ke semua nilai. ALL adalah opsi default, hanya bermakna, dan hanya tersedia untuk kompatibilitas ISO.

NYATA

DISTINCT tidak bermakna dengan ANY_VALUE, dan hanya tersedia untuk kompatibilitas ISO.

expression

Nilai yang akan dikembalikan. Salah satu nilai dapat dikembalikan sebagai hasilnya, tetapi nilai dilewati NULL jika memungkinkan.

Klausa OVER

partition_by_clause membagi tataan hasil yang dihasilkan oleh FROM klausul menjadi partisi, dan fungsi diterapkan ke setiap partisi.

Jika Anda tidak menentukan klausul ini, fungsi memperlakukan semua baris hasil kueri yang ditetapkan sebagai satu grup.

order_by_clause menentukan urutan data sebelum fungsi diterapkan. Jika Anda menentukan partition_by_clause, itu menentukan urutan data dalam partisi. order_by_clause tidak diperlukan.

Untuk informasi selengkapnya, lihat SELECT - OVER clause (Transact-SQL).

Tipe pengembalian

Mengembalikan nilai dengan jenis yang sama dengan ekspresi.

Komentar

ANY_VALUE bersifat nondeterministik. Untuk informasi selengkapnya, lihat Fungsi deterministik dan nondeterministik. Tidak seperti FIRST_VALUE atau LAST_VALUE, ANY_VALUE tidak memberikan urutan deterministik. Ini dirancang untuk kasus di mana nilai yang tepat tidak penting untuk logika kueri.

Fungsi mencoba mengembalikan non-nilaiNULL jika memungkinkan dan mengembalikan NULL nilai hanya jika semua nilai adalah NULL.

Skenario penggunaan

Kasus penggunaan umum adalah ANY_VALUE ketika Anda perlu menyertakan kolom non-kunci dalam kumpulan hasil yang dikelompokkan menurut kolom kunci. Misalnya, jika Anda mengelompokkan baris menurut StoreID, Anda dapat menggunakan ANY_VALUE untuk mengembalikan nilai untuk kolom seperti nama toko, alamat, atau atribut deskriptif lainnya tanpa menambahkannya ke GROUP BY klausul atau menggunakan fungsi yang lebih mahal seperti MAX, , MINFIRST_VALUE, atau LAST_VALUE untuk menyertakannya dalam proyeksi. Pendekatan ini menyederhanakan desain kueri, meningkatkan keterbacaan, dan meningkatkan performa karena kueri SQL tidak perlu melakukan pengelompokan yang tidak perlu pada kolom deskriptif. Akibatnya, agregasi Anda tetap ringkas, lebih mudah dipertahankan, dan lebih efisien.

Examples

A. Mengambil nilai non-NULL apa pun

Kueri sederhana ini menunjukkan bagaimana ANY_VALUE bisa mengembalikan nilai non-NULL arbitrer dari sekumpulan nilai:

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

Fungsi mengabaikan NULL nilai dan mengembalikan salah satu nilai nonNULL -(terkadang 2, kadang-kadang 7) dengan cara yang tidakdeterministik.

B. Kolom deskriptif proyek

Kueri ini meringkas total penjualan per toko dengan bergabung FactSales dengan DimStore, mengelompokkan pada StoreKey, dan mengambil detail penyimpanan kunci menggunakan 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;

Dengan menerapkan ANY_VALUE fungsi , Anda dapat menyertakan kolom nongrouping (seperti StoreName, StoreStatusStoreDescription, StoreFaxStorePhone, ZipCode, AddressLine1, dan AddressLine2) tanpa mencantumkannya dalam GROUP BY klausul .

C. Batalkan pivot nilai dari baris ke kolom

Tabel FactSales berisi satu baris per item baris, di mana OrderKey mengidentifikasi pesanan. Untuk setiap pesanan, atribut seperti OrderDate, DeliveryDate, CustomerKey, dan StoreKey diulang di semua baris milik yang sama OrderKey. Sebaliknya, ProductKey bervariasi menurut item baris, dengan satu produk per LineNumber.

Kueri berikut mempivot FactSales baris sehingga masing-masing OrderKey adalah satu baris. Ini menyimpan atribut tingkat pesanan bersama dan membuat kolom terpisah (ProductKey0, , ProductKey1...) untuk produk yang terkait dengan setiap nomor baris. Fungsi ANY_VALUE ini digunakan untuk memilih nilai perwakilan dari setiap grup, sementara ekspresi kondisional mengekstrak produk untuk setiap item baris tertentu.

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;

Dengan menggunakan ANY_VALUE fungsi , Anda menghindari penempatan OrderDate, , CustomerKeyDeliveryDate, dan StoreKey dalam GROUP BY klausul . Fungsi menyederhanakan ANY_VALUE kueri dan dapat meningkatkan performa karena hanya satu kolom (OrderKey) yang digunakan dalam GROUP BY klausa. Pola ANY_VALUE + CASE WHEN mengekstrak yang sesuai ProductKey untuk setiap item baris dan mengembalikannya sebagai kolom terpisah. Dalam praktiknya, pola ini menghasilkan pivot terprogram dari kunci produk (alternatif untuk operator tradisional UNPIVOT ).

D. Nilai acak per dua partisi kolom

Anda membuat laporan detail tingkat penjualan dengan indikator kinerja utama harian (KPI) per toko. Dalam laporan, Anda perlu mengembalikan partisi per (StoreKey, DateKey) yang tidak SalesOrderNumber ada aturan bisnis untuk memilih .SalesOrderNumber Tidak ada persyaratan untuk memilih urutan paling awal, terbaru, atau terbesar per baris dalam laporan. Misalnya, antarmuka pengguna menunjukkan "urutan referensi untuk hari penyimpanan" di samping setiap baris sehingga analis dapat dengan cepat melompat ke pesanan dari pasangan (toko, hari).

Tujuannya adalah untuk mengembalikan satu konsisten SalesOrderNumber per (toko, hari).

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;

Jika Anda mengganti ANY_VALUE(fs.SalesOrderNumber) ekspresi dengan fs.SalesOrderNumber referensi kolom, label bervariasi baris demi baris; Anda kehilangan perilaku "satu label yang konsisten per (toko, hari)".

Konten terkait

  • Last updated on