محاسبه میانگین (Moving Average) متحرک در SQL Server با توابع پنجره ای TSQL

محاسبه میانگین متحرک در SQL Server با توابع پنجره‌ای T-SQL: راهنمایی جامع

میانگین متحرک (Moving Average) یک ابزار آماری و تحلیل تکنیکال برای ایجاد یک سری از میانگین‌ها برای زیرمجموعه‌های مختلف از یک مجموعه داده کامل است. این میانگین‌ها برای صاف کردن نوسانات داده‌ها در یک دوره زمانی مشخص استفاده می‌شوند و به این ترتیب روند یا ترندهای بلندمدت آسان‌تر قابل مشاهده خواهند بود. میانگین متحرک در تحلیل بازارهای مالی، تحلیل عملکرد کسب‌وکار و بسیاری دیگر از حوزه‌های تحلیل داده کاربرد گسترده‌ای دارد.

توابع پنجره‌ای (Windowing Functions) در SQL Server ابزار قدرتمندی برای انجام محاسبات روی مجموعه‌ای از ردیف‌های مرتبط با ردیف فعلی هستند. این توابع به شما امکان می‌دهند تا بدون نیاز به کوئری‌های پیچیده و ساب‌کوئری‌ها، میانگین متحرک را به شکلی کارآمد و با عملکرد بالا محاسبه کنید. در این مقاله، نحوه استفاده از توابع پنجره‌ای T-SQL برای محاسبه انواع میانگین متحرک را بررسی خواهیم کرد.

تنظیم داده‌های نمونه

برای شروع، به مجموعه‌ای از داده‌های نمونه نیاز داریم تا بتوانیم محاسبات میانگین متحرک را روی آن‌ها انجام دهیم. کوئری T-SQL زیر را برای ایجاد و پر کردن یک جدول با داده‌های ساده اجرا کنید:

IF OBJECT_ID('dbo.MovingAverageSample') IS NOT NULL
DROP TABLE dbo.MovingAverageSample;

CREATE TABLE dbo.MovingAverageSample
(
    [Date] DATE PRIMARY KEY,
    [Value] DECIMAL(10,2)
);

INSERT INTO dbo.MovingAverageSample ([Date], [Value]) VALUES
('2023-01-01', 10.00),
('2023-01-02', 12.00),
('2023-01-03', 11.50),
('2023-01-04', 13.00),
('2023-01-05', 12.50),
('2023-01-06', 14.00),
('2023-01-07', 13.50),
('2023-01-08', 15.00),
('2023-01-09', 14.50),
('2023-01-10', 16.00);

SELECT * FROM dbo.MovingAverageSample ORDER BY [Date];

این کد یک جدول به نام `MovingAverageSample` با دو ستون `Date` و `Value` ایجاد می‌کند و ۱۰ ردیف داده را در آن وارد می‌کند. این داده‌ها شامل تاریخ‌های متوالی و مقادیر مرتبط هستند که برای نشان دادن روند تغییرات مناسب‌اند.

محاسبه میانگین متحرک با استفاده از AVG() و OVER()

ساده‌ترین راه برای محاسبه میانگین متحرک در SQL Server، استفاده از تابع تجمیعی `AVG()` همراه با بند `OVER()` است. بند `OVER()` یک “پنجره” یا “فریم” را برای تابع تعریف می‌کند که تعیین می‌کند کدام ردیف‌ها باید در محاسبه تابع گنجانده شوند.

ساده‌ترین شکل بند `OVER` تنها شامل یک بند `ORDER BY` است که ترتیب ردیف‌ها را در پنجره تعیین می‌کند. این کار به SQL Server می‌گوید که ردیف‌ها را بر اساس ستون `Date` مرتب کند تا میانگین به ترتیب زمانی محاسبه شود.

(ORDER BY Date)

با استفاده از این ساختار، می‌توانیم اولین نسخه از میانگین متحرک را محاسبه کنیم. کوئری زیر میانگین کل مقادیر تا تاریخ فعلی را محاسبه می‌کند و در واقع یک میانگین متحرک تجمیعی (cumulative moving average) است.

SELECT
    [Date],
    [Value],
    AVG([Value]) OVER (ORDER BY [Date]) AS CumulativeMovingAverage
FROM
    dbo.MovingAverageSample
ORDER BY
    [Date];

خروجی این کوئری برای هر تاریخ، میانگین تمام مقادیری را که تا آن تاریخ ثبت شده‌اند، نشان می‌دهد. این یک راه عالی برای مشاهده چگونگی تغییر میانگین کلی داده‌ها در طول زمان است، اما هنوز یک میانگین متحرک “واقعی” با پنجره‌ای با اندازه ثابت نیست.

درک ORDER BY، ROWS و RANGE

برای ایجاد یک میانگین متحرک واقعی، باید اندازه پنجره را به طور صریح تعریف کنیم. این کار با استفاده از بندهای `ROWS` یا `RANGE` در داخل بند `OVER()` انجام می‌شود. این بندها به شما امکان می‌دهند تا دقیقاً مشخص کنید چند ردیف قبل یا بعد از ردیف فعلی باید در محاسبه پنجره لحاظ شوند.

رایج‌ترین شکل میانگین متحرک، میانگین متحرک ساده (Simple Moving Average) است که ردیف فعلی و تعداد مشخصی از ردیف‌های پیشین را در نظر می‌گیرد. برای محاسبه میانگین متحرک تجمیعی که شامل تمام ردیف‌ها از ابتدا تا ردیف فعلی است، از عبارت زیر استفاده می‌کنیم:

(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

این عبارت به SQL Server دستور می‌دهد که پنجره را از ابتدای مجموعه داده (UNBOUNDED PRECEDING) تا ردیف فعلی (CURRENT ROW) گسترش دهد. کوئری زیر این مفهوم را پیاده‌سازی می‌کند:

SELECT
    [Date],
    [Value],
    AVG([Value]) OVER (ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeMovingAverageFull
FROM
    dbo.MovingAverageSample
ORDER BY
    [Date];

خروجی این کوئری دقیقاً همانند مثال قبلی است، زیرا وقتی تنها `ORDER BY` را مشخص می‌کنید، SQL Server به طور پیش‌فرض `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` را اعمال می‌کند، که در صورت نبود ردیف‌های با مقادیر مرتب‌سازی یکسان، همانند `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` عمل می‌کند.

برای محاسبه یک میانگین متحرک سه روزه که شامل ردیف فعلی و دو ردیف قبلی (مجموعاً سه ردیف) می‌شود، از عبارت زیر استفاده می‌کنیم:

(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

این عبارت به SQL Server می‌گوید که پنجره را برای هر ردیف، شامل ردیف فعلی و دو ردیف بلافاصله قبل از آن تعریف کند. کوئری مربوطه به این صورت است:

SELECT
    [Date],
    [Value],
    AVG([Value]) OVER (ORDER BY [Date] ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ThreeDayMovingAverage
FROM
    dbo.MovingAverageSample
ORDER BY
    [Date];

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

همچنین می‌توان پنجره را طوری تعریف کرد که ردیف‌های بعدی را نیز شامل شود. برای مثال، برای محاسبه یک میانگین متحرک سه روزه مرکزی که شامل یک ردیف قبل، ردیف فعلی و یک ردیف بعد از آن باشد، از عبارت زیر استفاده می‌کنیم:

(ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

این کار برای داده‌هایی که نیاز به صاف‌سازی متقارن دارند، مانند داده‌های سنسور، می‌تواند مفید باشد. کوئری زیر این نوع میانگین متحرک را پیاده‌سازی می‌کند:

SELECT
    [Date],
    [Value],
    AVG([Value]) OVER (ORDER BY [Date] ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS CenteredThreeDayMovingAverage
FROM
    dbo.MovingAverageSample
ORDER BY
    [Date];

در این حالت، میانگین برای روز اول تنها شامل دو ردیف (روز اول و دوم) و برای روز آخر نیز شامل دو ردیف (روز نهم و دهم) خواهد بود، در حالی که برای روزهای میانی، سه ردیف را شامل می‌شود. این رویکرد به ویژه برای تحلیل روندهایی که نیاز به نگاه به آینده و گذشته دارند، مناسب است.

تفاوت بین `ROWS` و `RANGE` در نحوه برخورد با ردیف‌های دارای مقادیر یکسان در ستون `ORDER BY` است. `ROWS` تعداد فیزیکی ردیف‌ها را می‌شمارد، در حالی که `RANGE` بر اساس مقدار ستون `ORDER BY` کار می‌کند. به عنوان مثال، اگر چندین ردیف با تاریخ یکسان داشته باشید، `ROWS` هر یک از آنها را به عنوان یک ردیف جداگانه در نظر می‌گیرد، اما `RANGE` همه آنها را همزمان به عنوان بخشی از یک “محدوده” در نظر می‌گیرد. معمولاً برای میانگین متحرک مبتنی بر زمان، `ROWS` ترجیح داده می‌شود مگر اینکه نیاز خاصی به گروه بندی بر اساس مقادیر یکسان داشته باشید.

برای درک بهتر `RANGE`، در اینجا مثالی از `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` آورده شده است. این ساختار در شرایطی که ممکن است مقادیر `Date` تکراری داشته باشید و می‌خواهید همه ردیف‌ها با همان مقدار `Date` به عنوان بخشی از “ردیف فعلی” در نظر گرفته شوند، مفید است:

(RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

SELECT
    [Date],
    [Value],
    AVG([Value]) OVER (ORDER BY [Date] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeMovingAverageRange
FROM
    dbo.MovingAverageSample
ORDER BY
    [Date];

در مثال ما که `Date` یک کلید اصلی است و مقادیر تکراری ندارد، خروجی این کوئری با `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` یکسان خواهد بود. اما اگر ردیف‌هایی با تاریخ‌های یکسان وجود داشت، تفاوت بین `ROWS` و `RANGE` آشکار می‌شد.

ملاحظات عملکرد

استفاده از توابع پنجره‌ای برای محاسبه میانگین متحرک به طور کلی عملکرد بسیار بهتری نسبت به رویکردهای قدیمی با استفاده از ساب‌کوئری‌های همبسته (correlated subqueries) یا جوین‌های خودکار (self-joins) دارد. SQL Server می‌تواند این عملیات را بهینه‌تر اجرا کند. با این حال، هنگام کار با مجموعه داده‌های بسیار بزرگ، اطمینان از وجود ایندکس‌های مناسب بر روی ستون‌های استفاده شده در بند `ORDER BY` (مانند `Date` در مثال ما) بسیار مهم است. ایندکس‌ها به موتور SQL Server کمک می‌کنند تا ردیف‌ها را به سرعت مرتب و دسترسی پیدا کند، که به طور قابل توجهی زمان اجرای کوئری را بهبود می‌بخشد.