Utwórz skalarną funkcję zdefiniowaną przez użytkownika (podgląd)

Dotyczy:✅ końcowego punktu analizy SQL i magazynu danych w usłudze Microsoft Fabric

Microsoft Fabric Data Warehouse wykorzystuje dwie techniki inlining do uruchamiania skalarnych funkcji definiowanych przez użytkownika (UDF) jako części rozproszonych zapytań:

Note

Skalarne funkcje UDF są obecnie funkcją w wersji zapoznawczej w Fabric Data Warehouse.

W tym artykule dowiesz się, jak przekształcić UDF nieliniowalny w funkcję inlineowalną lub stworzyć UDF nieinlineowalny.

Zrób nieliniowalny skalarny UDF inlineowalny

Zmiany w kodzie T-SQL potrzebne, aby skalarny UDF był inlineowalny, zależą od tego, która konstrukcja uczyni jego definicję nieliniowalną.

Poniższe przykłady pokazują, jak przekształcić wiele instrukcji RETURN, @@ROWCOUNT oraz funkcję zależną od czasu w funkcji UDF służącej do dostępu do danych.

Zamień wiele wyciągów RETURN

Następująca funkcja odczytuje typ klienta z tabeli i zawiera wiele RETURN instrukcji. Ponieważ funkcja uzyskuje dostęp do danych tabelowych, obowiązują skalarne wymagania dotyczące inlining UDF. Wiele RETURN instrukcji sprawia, że tej definicji nie można zainlinować.

Określ WITH INLINE = AUTO, aby utworzyć funkcję, gdy przygotowujesz się do jej przepisania:

CREATE OR ALTER FUNCTION dbo.GetDiscount (@CustomerID INT)
RETURNS DECIMAL(5, 2)
WITH INLINE = AUTO
AS
BEGIN
    DECLARE @CustomerType VARCHAR(20);

    SELECT @CustomerType = CustomerType
    FROM dbo.Customer
    WHERE CustomerID = @CustomerID;

    IF @CustomerType = 'Regular' RETURN 0.05;
    IF @CustomerType = 'Premium' RETURN 0.10;
    IF @CustomerType = 'VIP' RETURN 0.15;
    RETURN 0.01;
END;
GO

Sprawdź, czy funkcja jest inlineowalna

Widok katalogowy sys.sql_modules ujawnia dwie właściwości opisujące skalarną liniowalność UDF:

  • is_inlineable wskazuje, czy definicja funkcji jest liniowa. Wartość 1 oznacza możliwą do wstawienia inline, a 0 oznacza niemożliwą do wstawienia inline.
  • inline_eligibility_mask Identyfikuje, która technika inlining może przetwarzać definicję funkcji. Więcej informacji o wartościach można znaleźć w sekcji Sprawdź, czy skalarny UDF można włożyć (inline).

Użyj następującego zapytania, aby sprawdzić dbo.GetDiscount:

SELECT
    SCHEMA_NAME(o.schema_id) AS function_schema,
    o.name AS function_name,
    m.is_inlineable,
    m.inline_eligibility_mask
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
WHERE o.type = 'FN'
    AND SCHEMA_NAME(o.schema_id) = 'dbo'
    AND o.name = 'GetDiscount';
GO

Metadane opisują definicję funkcji. W przypadku początkowej definicji dbo.GetDiscount, is_inlineable ma wartość 0.

Przepisz funkcję, aby przypisać wynik zmiennej lokalnej i użyj jednego RETURN ostatecznego polecenia:

CREATE OR ALTER FUNCTION dbo.GetDiscount (@CustomerID INT)
RETURNS DECIMAL(5, 2)
AS
BEGIN
    DECLARE @Discount DECIMAL(5, 2) = 0.01;

    SELECT @Discount =
        CASE CustomerType
            WHEN 'Regular' THEN 0.05
            WHEN 'Premium' THEN 0.10
            WHEN 'VIP' THEN 0.15
            ELSE 0.01
        END
    FROM dbo.Customer
    WHERE CustomerID = @CustomerID;

    RETURN @Discount;
END;
GO

Zrewidowana funkcja ma jedną instrukcję RETURN i spełnia wymagania dotyczące wbudowywania skalarnej funkcji UDF. Ponownie uruchom zapytanie metadanych, aby potwierdzić, że is_inlineable jest .1

Note

Inlining w ExprBlock obsługuje wiele RETURN instrukcji. Ten przykład odczytuje dane tabelowe i dlatego wykorzystuje skalarne inlinowanie UDF, które wymaga pojedynczego RETURN polecenia.

Zastąp @@ROWCOUNT wartością początkową

Następująca funkcja UDF dostępu do danych używa elementu @@ROWCOUNT, aby określić, czy jej zapytanie znalazło klienta. @@ROWCOUNT nie jest obsługiwany przez wbudowywanie skalarnej funkcji UDF, więc definicja nie może być wbudowana:

CREATE OR ALTER FUNCTION dbo.CustomerExists (@CustomerID INT)
RETURNS BIT
WITH INLINE = AUTO
AS
BEGIN
    DECLARE @CustomerExists BIT = 1;

    SELECT @CustomerExists = 1
    FROM dbo.Customer
    WHERE CustomerID = @CustomerID;

    IF @@ROWCOUNT = 0 SET @CustomerExists = 0;

    RETURN @CustomerExists;
END;
GO

Zainicjalizuj zmienną wartością wyniku dla przypadku braku pasującego wiersza. Element SELECT zmienia wartość tylko wtedy, gdy zostanie znaleziony wiersz, co eliminuje potrzebę sprawdzania @@ROWCOUNT:

CREATE OR ALTER FUNCTION dbo.CustomerExists (@CustomerID INT)
RETURNS BIT
AS
BEGIN
    DECLARE @CustomerExists BIT = 0;

    SELECT @CustomerExists = 1
    FROM dbo.Customer
    WHERE CustomerID = @CustomerID;

    RETURN @CustomerExists;
END;
GO

Uruchom zapytanie metadanych z poprzedniego przykładu z CustomerExists nazwą funkcji. Początkowa definicja ma is_inlineable = 0, a zmieniona definicja ma .is_inlineable = 1

Przekaż wartość zależną od czasu do funkcji UDF uzyskującej dostęp do danych

Następująca funkcja łączy dostęp do tabeli z .GETUTCDATE() Dostęp do tabel wymaga skalarnego inliningu UDF, gdzie funkcja zależna od czasu w ciele UDF sprawia, że definicja nie jest inlineowalna:

CREATE OR ALTER FUNCTION dbo.GetCustomerTenureDays (@CustomerID INT)
RETURNS INT
WITH INLINE = AUTO
AS
BEGIN
    DECLARE @MemberSince DATE;

    SELECT @MemberSince = MemberSince
    FROM dbo.Customer
    WHERE CustomerID = @CustomerID;

    RETURN DATEDIFF(DAY, @MemberSince, GETUTCDATE());
END;
GO

Zamiast tego przekaż aktualną datę i godzinę do funkcji jako parametr:

CREATE OR ALTER FUNCTION dbo.GetCustomerTenureDays
(
    @CustomerID INT,
    @AsOfDate DATETIME2(6)
)
RETURNS INT
AS
BEGIN
    DECLARE @MemberSince DATE;

    SELECT @MemberSince = MemberSince
    FROM dbo.Customer
    WHERE CustomerID = @CustomerID;

    RETURN DATEDIFF(DAY, @MemberSince, @AsOfDate);
END;
GO

Oblicz GETUTCDATE() w zapytaniu wywołującym i przekaż jego wartość do funkcji UDF:

SELECT
    CustomerID,
    CustomerName,
    dbo.GetCustomerTenureDays(CustomerID, GETUTCDATE()) AS TenureDays
FROM dbo.Customer
ORDER BY CustomerID;
GO

Zrób zapytanie inlineowalne

Wymagania dotyczące kształtu zapytania w tej sekcji mają zastosowanie, gdy przynajmniej jeden UDF w zapytaniu opiera się na skalarnym inliningu UDF. Gdy każdy UDF w zapytaniu jest obsługiwany przez inlining w ExprBlock, te kształty zapytań działają bez powtórzeń pokazanych tutaj.

Poniższe przykłady wykorzystują wersję z dostępem do danych z dbo.GetDiscount poprzedniej sekcji.

Zastąp wspólne wyrażenie tabelaryczne

Zapytanie używające wspólnego wyrażenia tabelowego (CTE) nie może inlineować funkcji zależnej od skalarnego inliningu UDF. Następujące zapytanie nie jest obsługiwane:

WITH CustomerDiscounts AS
(
    SELECT
        CustomerID,
        CustomerName,
        dbo.GetDiscount(CustomerID) AS DiscountRate
    FROM dbo.Customer
)
SELECT CustomerID, CustomerName, DiscountRate
FROM CustomerDiscounts;
GO

Materializuj wynik w tabeli tymczasowej, a następnie zapytaj tabelę tymczasową:

DROP TABLE IF EXISTS #CustomerDiscounts;
GO

CREATE TABLE #CustomerDiscounts
(
    CustomerID INT NOT NULL,
    CustomerName VARCHAR(100) NOT NULL,
    DiscountRate DECIMAL(5, 2) NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN);
GO

INSERT INTO #CustomerDiscounts
(
    CustomerID,
    CustomerName,
    DiscountRate
)
SELECT
    CustomerID,
    CustomerName,
    dbo.GetDiscount(CustomerID)
FROM dbo.Customer;
GO

SELECT CustomerID, CustomerName, DiscountRate
FROM #CustomerDiscounts;
GO

Przenieś wywołanie UDF poza GRUPĘ BY

Następujące zapytanie wywołuje UDF bezpośrednio w klauzuli GROUP BY i nie jest obsługiwane dla funkcji zależnej od skalarnego inliningu UDF:

SELECT
    dbo.GetDiscount(CustomerID) AS DiscountRate,
    COUNT(*) AS CustomerCount
FROM dbo.Customer
GROUP BY dbo.GetDiscount(CustomerID);
GO

Oblicz wynik UDF w tabeli pochodnej, a następnie grupuj wynik w zewnętrznym zapytaniu:

SELECT
    DiscountRate,
    COUNT(*) AS CustomerCount
FROM
(
    SELECT dbo.GetDiscount(CustomerID) AS DiscountRate
    FROM dbo.Customer
) AS CustomerDiscounts
GROUP BY DiscountRate;
GO

Użyj aliasu kolumny w klauzuli ORDER BY

Unikaj bezpośredniego wywoływania w klauzuli ORDER BY funkcji UDF zależnej od wbudowywania skalarnej funkcji UDF. Wyświetl wynik tylko raz i posortuj według aliasu kolumny lub jej numeru porządkowego:

SELECT
    CustomerID,
    CustomerName,
    dbo.GetDiscount(CustomerID) AS DiscountRate
FROM dbo.Customer
ORDER BY DiscountRate;
GO

Utwórz skalarną funkcję UDF niepodlegającą inline'owaniu

Możesz potrzebować funkcji do samodzielnego obliczenia, nawet jeśli jej definicja nie może być włożona. Na przykład aplikacja lub raport może raz wywołać UDF, aby obliczyć metrykę biznesową.

Następująca funkcja odczytuje przychody od początku roku z tabeli klientów, a następnie używa pętli WHILE do dodawania średnich miesięcznych przychodów za każdy pozostały miesiąc. Ponieważ funkcja uzyskuje dostęp do danych tabelowych, opiera się na skalarnym inliningu UDF, który nie obsługuje WHILE. Definicja funkcji jest zatem nieliniowana.

Użyj WITH INLINE = AUTO do umożliwienia utworzenia funkcji, mimo że jej definicja nie jest inlineowalna:

CREATE OR ALTER FUNCTION dbo.GetProjectedAnnualRevenue (@MonthsElapsed INT)
RETURNS DECIMAL(18, 2)
WITH INLINE = AUTO
AS
BEGIN
    DECLARE @YearToDateRevenue DECIMAL(38, 6);
    DECLARE @ProjectedRevenue DECIMAL(38, 6);
    DECLARE @MonthNumber INT = @MonthsElapsed + 1;

    IF @MonthsElapsed BETWEEN 1 AND 12
    BEGIN
        SELECT @YearToDateRevenue = SUM(YearToDateRevenue)
        FROM dbo.Customer;

        SET @ProjectedRevenue = @YearToDateRevenue;

        WHILE @MonthNumber <= 12
        BEGIN
            SET @ProjectedRevenue =
                @ProjectedRevenue + (@YearToDateRevenue / @MonthsElapsed);
            SET @MonthNumber = @MonthNumber + 1;
        END;
    END;

    RETURN @ProjectedRevenue;
END;
GO

Użyj osobnego połączenia, aby raz obliczyć metrykę dla dashboardu:

DECLARE @ProjectedAnnualRevenue DECIMAL(18, 2);

SET @ProjectedAnnualRevenue =
    dbo.GetProjectedAnnualRevenue(MONTH(GETUTCDATE()));

SELECT @ProjectedAnnualRevenue AS ProjectedAnnualRevenue;
GO

Zapytanie zwraca prognozowany roczny przychód na podstawie średniego miesięcznego przychodu do końca bieżącego miesiąca UTC.

INLINE = AUTO pozwala na utworzenie tej funkcji, ale nie czyni funkcji nieinlineowalnej do użycia w rozproszonym zapytaniu. Na przykład nie można użyć dbo.GetProjectedAnnualRevenue w zapytaniu SELECT ... FROM dbo.Customer.

Treści powiązane

  • Last updated on