بازیابی آخرین مقدار غیر NULL در SQL Server

بازیابی آخرین مقدار غیر NULL در رکوردهای SQL Server: راهنما و روش‌ها

هنگام کار با داده‌ها در SQL Server، سناریوهای زیادی پیش می‌آید که نیاز به یافتن آخرین مقدار غیر NULL برای یک مجموعه خاص از رکوردها دارید. این کار می‌تواند در گزارش‌گیری، تحلیل داده‌ها یا حتی فرآیندهای ETL اهمیت زیادی داشته باشد. این مقاله به بررسی روش‌های مختلف برای بازیابی آخرین مقدار غیر NULL از داده‌ها در SQL Server می‌پردازد.

برای شروع، یک جدول نمونه ایجاد می‌کنیم و چند رکورد در آن درج می‌کنیم تا بتوانیم سناریوهای مختلف را آزمایش کنیم. این داده‌ها به ما کمک می‌کنند تا عملکرد و نتیجه هر روش را به وضوح ببینیم و بهترین رویکرد را برای نیازهای بازیابی داده خود انتخاب کنیم.

CREATE TABLE Employee
(
    ID INT IDENTITY(1,1),
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary DECIMAL(10,2),
    Department VARCHAR(50)
);

INSERT INTO Employee (FirstName, LastName, Salary, Department) VALUES
('John', 'Doe', 60000.00, 'IT'),
('Jane', 'Smith', 75000.00, 'HR'),
('Peter', 'Jones', 50000.00, 'IT'),
('Alice', 'Williams', 80000.00, 'HR'),
('Robert', 'Brown', 65000.00, 'IT'),
('Sarah', 'Miller', 90000.00, 'Finance');

INSERT INTO Employee (FirstName, LastName, Department) VALUES
('David', 'Garcia', 'IT'); -- Salary is NULL

INSERT INTO Employee (FirstName, LastName, Salary, Department) VALUES
('Emily', 'Davis', 70000.00, 'HR');

INSERT INTO Employee (FirstName, LastName, Department) VALUES
('Michael', 'Wilson', 'Finance'); -- Salary is NULL

INSERT INTO Employee (FirstName, LastName, Salary, Department) VALUES
('Olivia', 'Martinez', 82000.00, 'IT');

INSERT INTO Employee (FirstName, LastName, Salary, Department) VALUES
('William', 'Taylor', 63000.00, 'HR');
GO

SELECT * FROM Employee;

با استفاده از این داده‌های نمونه، هدف ما یافتن آخرین مقدار حقوق (Salary) غیر NULL برای هر دپارتمان است. این کار می‌تواند پیچیده باشد زیرا نیاز به گروه‌بندی و ترتیب‌بندی مناسب برای شناسایی “آخرین” رکورد دارد.

استفاده از CURSOR برای یافتن آخرین مقدار غیر NULL

یکی از راه‌های اولیه (و اغلب غیربهینه) برای حل این مسئله، استفاده از `CURSOR` است. اگرچه `CURSOR` می‌تواند وظایف ردیف به ردیف را انجام دهد، اما به دلیل سربار عملکردی بالا معمولاً برای مجموعه داده‌های بزرگ توصیه نمی‌شود. در اینجا نحوه استفاده از `CURSOR` برای یافتن آخرین مقدار غیر NULL برای ستون `Salary` نمایش داده شده است.

DECLARE @EmployeeID INT;
DECLARE @FirstName VARCHAR(50);
DECLARE @LastName VARCHAR(50);
DECLARE @Salary DECIMAL(10,2);
DECLARE @Department VARCHAR(50);
DECLARE @LastNonNullSalary DECIMAL(10,2);
DECLARE @LastNonNullDepartment VARCHAR(50);

DECLARE @Result TABLE
(
    Department VARCHAR(50),
    LastNonNullSalary DECIMAL(10,2)
);

DECLARE EmployeeCursor CURSOR FOR
SELECT ID, FirstName, LastName, Salary, Department
FROM Employee
ORDER BY Department, ID; -- Ordering by ID to define "last" within a department

OPEN EmployeeCursor;

FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName, @Salary, @Department;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @LastNonNullDepartment IS NULL OR @LastNonNullDepartment <> @Department
    BEGIN
        -- New department, reset last non-null salary
        SET @LastNonNullSalary = NULL;
        SET @LastNonNullDepartment = @Department;
    END

    IF @Salary IS NOT NULL
    BEGIN
        SET @LastNonNullSalary = @Salary;
    END

    -- If this is the last record for a department or the last record overall,
    -- insert the current @LastNonNullSalary into the result table.
    -- This part is tricky with CURSOR and usually requires a peek-ahead or another pass.
    -- A simpler approach for the end of a group would be to store all and then process.
    -- For demonstration, let's just show the logic for updating.
    -- A true "last per group" with CURSOR would involve more complex logic or a final aggregation.

    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName, @Salary, @Department;
END

CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

-- This CURSOR example is simplified and primarily shows row-by-row processing.
-- To get the LAST non-null per group, more advanced logic or other methods are preferred.
-- The actual output for "last non-null per group" is better achieved with set-based operations.

-- For a set-based approach, which is more SQL-idiomatic and performant,
-- consider the methods below.

استفاده از CTE و ROW_NUMBER()

یکی از روش‌های کارآمدتر و ست‌محور (set-based) برای یافتن آخرین مقدار غیر NULL، استفاده از Common Table Expression (CTE) به همراه تابع پنجره‌ای `ROW_NUMBER()` است. این روش به شما امکان می‌دهد تا ردیف‌ها را در گروه‌های مختلف ترتیب‌بندی کرده و سپس با استفاده از شماره ردیف‌ها، رکوردهای مورد نظر را فیلتر کنید.

WITH RankedEmployees AS (
    SELECT
        ID,
        FirstName,
        LastName,
        Salary,
        Department,
        ROW_NUMBER() OVER (PARTITION BY Department ORDER BY ID DESC) AS rn -- Order by ID DESC to get "last"
    FROM Employee
    WHERE Salary IS NOT NULL
),
LastNonNullSalaries AS (
    SELECT
        Department,
        Salary AS LastNonNullSalary
    FROM RankedEmployees
    WHERE rn = 1
)
SELECT
    e.Department,
    e.ID,
    e.FirstName,
    e.LastName,
    e.Salary,
    lns.LastNonNullSalary AS LastDepartmentNonNullSalary
FROM Employee e
LEFT JOIN LastNonNullSalaries lns ON e.Department = lns.Department
ORDER BY e.Department, e.ID;

این کوئری ابتدا کارمندان با حقوق غیر NULL را مرتب‌سازی می‌کند، سپس بر اساس `Department` پارتیشن‌بندی کرده و `ROW_NUMBER()` را به ترتیب نزولی `ID` اعمال می‌کند تا آخرین رکورد (با توجه به `ID` بالا) با حقوق غیر NULL را در هر دپارتمان مشخص کند. سپس با استفاده از این رتبه‌بندی، آخرین حقوق غیر NULL برای هر دپارتمان بازیابی می‌شود و به جدول اصلی پیوند زده می‌شود.

استفاده از OUTER APPLY

استفاده از `OUTER APPLY` نیز راه حلی قدرتمند برای این سناریو است. `OUTER APPLY` مانند یک JOIN عمل می‌کند اما به شما امکان می‌دهد که برای هر ردیف از ورودی سمت چپ، یک تابع یا یک کوئری مستقل (معمولاً با `TOP 1`) را اجرا کنید. این روش برای یافتن “آخرین” مقادیر در گروه‌ها بسیار مفید است.

SELECT
    e.ID,
    e.FirstName,
    e.LastName,
    e.Salary,
    e.Department,
    oa.LastNonNullSalary
FROM
    Employee e
OUTER APPLY
    (
        SELECT TOP 1 sub.Salary AS LastNonNullSalary
        FROM Employee sub
        WHERE sub.Department = e.Department AND sub.Salary IS NOT NULL
        ORDER BY sub.ID DESC -- Assuming ID defines "last"
    ) oa
ORDER BY e.Department, e.ID;

در این کوئری، برای هر کارمند در جدول `Employee`، یک کوئری فرعی با `TOP 1` اجرا می‌شود که آخرین حقوق غیر NULL را برای دپارتمان مربوطه پیدا می‌کند. `OUTER APPLY` تضمین می‌کند که حتی اگر هیچ حقوق غیر NULLی برای یک دپارتمان وجود نداشته باشد، رکوردهای کارمند اصلی همچنان نمایش داده شوند و `LastNonNullSalary` برای آن‌ها NULL باشد.

استفاده از توابع پنجره‌ای LAST_VALUE و FIRST_VALUE (SQL Server 2012 به بعد)

با معرفی توابع پنجره‌ای در SQL Server 2012، می‌توان از `LAST_VALUE` و `FIRST_VALUE` برای حل این مشکل استفاده کرد. این توابع به شما اجازه می‌دهند تا مقدار یک ستون را از اولین یا آخرین ردیف در یک پنجره (partition) خاص بازیابی کنید.

ساختار کلی تابع `LAST_VALUE` به صورت زیر است:

LAST_VALUE ( scalar_expression ) OVER ( [ PARTITION BY partition_expression, … ] ORDER BY order_by_expression [ ASC | DESC ] )

ساختار کلی تابع `FIRST_VALUE` به صورت زیر است:

FIRST_VALUE ( scalar_expression ) OVER ( [ PARTITION BY partition_expression, … ] ORDER BY order_by_expression [ ASC | DESC ] [ ROWS BETWEEN frame_bound AND frame_bound ] )

برای یافتن آخرین مقدار غیر NULL با `LAST_VALUE`، باید یک روش هوشمندانه برای فیلتر کردن مقادیر NULL و ترتیب‌بندی صحیح استفاده کنیم. در اینجا یک رویکرد با `LAST_VALUE` نشان داده شده است:

SELECT
    ID,
    FirstName,
    LastName,
    Salary,
    Department,
    LAST_VALUE(Salary) IGNORE NULLS OVER (
        PARTITION BY Department
        ORDER BY ID
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS LastNonNullSalaryPerGroup -- This requires SQL Server 2022+ for IGNORE NULLS
FROM
    Employee
ORDER BY Department, ID;

**توجه:** کلیدواژه `IGNORE NULLS` برای `LAST_VALUE` در SQL Server 2022 معرفی شد. برای نسخه‌های قدیمی‌تر (مانند SQL Server 2012-2019)، باید از روش‌های جایگزین استفاده کرد که در ادامه به آن می‌پردازیم.

استفاده از FIRST_VALUE با ROWS BETWEEN (برای نسخه‌های قبل از SQL Server 2022 و رویکرد جایگزین)

در نسخه‌های SQL Server قبل از 2022 که `IGNORE NULLS` در `LAST_VALUE` پشتیبانی نمی‌شود، می‌توانیم با کمی خلاقیت از `FIRST_VALUE` استفاده کنیم. با مرتب‌سازی به صورت نزولی و انتخاب `FIRST_VALUE`، عملاً “آخرین” مقدار را پیدا می‌کنیم. همچنین، `ROWS BETWEEN` نقش مهمی در تعریف پنجره ایفا می‌کند.

SELECT
    ID,
    FirstName,
    LastName,
    Salary,
    Department,
    FIRST_VALUE(Salary) OVER (
        PARTITION BY Department
        ORDER BY
            CASE WHEN Salary IS NOT NULL THEN 0 ELSE 1 END, -- Prioritize non-NULLs
            ID DESC                                          -- Then order by ID descending
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- Look at all rows in the partition
    ) AS LastNonNullSalaryPerGroup
FROM
    Employee
ORDER BY Department, ID;

این کوئری از `FIRST_VALUE` استفاده می‌کند، اما با یک ترفند در `ORDER BY`، ابتدا مقادیر غیر NULL را اولویت‌بندی کرده و سپس بر اساس `ID` به صورت نزولی مرتب می‌کند تا “آخرین” مقدار غیر NULL را در صدر هر پارتیشن قرار دهد. `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` تضمین می‌کند که تابع به تمام ردیف‌های داخل هر گروه نگاه کند. این روش یک جایگزین قدرتمند و انعطاف‌پذیر برای یافتن آخرین مقدار غیر NULL در نسخه‌های مختلف SQL Server است.