میانگین متحرک چیست و چگونه آن را در SQL محاسبه کنیم

آیا می خواهید عمیقاً در داده های سری زمانی غوطه ور شوید و روندهای بلندمدت را بررسی کنید؟آیا می خواهید بدانید میانگین متحرک چیست و چگونه آن را در SQL محاسبه کنید؟پس این مقاله برای تو است. من یک ویژگی قدرتمند در SQL به نام توابع پنجره را توضیح می دهم و نشان می دهم که چگونه می توانید میانگین متحرک را با استفاده از آنها محاسبه کنید.

بهترین راه برای یادگیری در مورد توابع پنجره SQL دوره تعاملی Window Functions در LeaSQL.com است. این شامل بیش از 200 تمرین عملی است که به شما کمک می کند تا در مهارت های SQL خود اعتماد کنید. و این دوره تنها یکی از بیش از 30 دوره SQL تعاملی در سطوح مختلف دشواری است که ما ارائه می دهیم. هم اکنون به صورت رایگان ثبت نام کنید!

میانگین متحرک چیست؟

میانگین متحرک یک تکنیک سری زمانی برای تجزیه و تحلیل و تعیین روند در داده ها است. که گاهی اوقات به نام‌های رولینگ، میانگین‌های غلتشی یا میانگین‌های در حال اجرا نامیده می‌شوند، به عنوان میانگین جریان و تعداد مشخصی از مقادیر بلافاصله قبل برای هر نقطه از زمان محاسبه می‌شوند. ایده اصلی بررسی نحوه رفتار این میانگین ها در طول زمان به جای بررسی رفتار نقاط داده اصلی یا خام است.

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

در زیر جدولی با نام stock_price است که در این مقاله از آن استفاده خواهیم کرد:

در این جدول دو ستون داریم (تاریخ و قیمت). این یک مجموعه داده سری زمانی است، زیرا هر قیمت سهام با یک نقطه زمانی خاص مرتبط است. یعنی قیمت هر روز در این جدول ذخیره می شود.

نمودار خطی این قیمت ها در طول زمان به صورت زیر است:

حتی قبل از انجام هر محاسباتی می‌توانیم چند چیز را از جدول و نمودار جمع‌آوری کنیم:

• قیمت‌های جدول سهام_قیمت ما برای دوره بین ۷ ژانویه ۲۰۲۰ تا ۳۰ ژوئن ۲۰۲۰ است.
• قیمت ها تقریباً از 1000 تا 1400 متغیر است (به محور y مراجعه کنید).
• نوساناتی در قیمت ها وجود دارد. چند سنبله در فوریه، چندین سنبله در ژوئن و غیره وجود دارد.

بیایید میانگین متحرک را برای قیمت ستون محاسبه کنیم و نمودار خط میانگین ها را تولید کنیم تا ببینیم چه اتفاقی می افتد. برای این مثال ، ما با میانگین حرکت سه روزه کار خواهیم کرد. برای انجام این کار ، ما میانگین قیمت سهام را از سه روز متوالی - روز مورد نظر و دو روز گذشته محاسبه می کنیم - سپس برای هر روز در مجموعه داده ها یکسان را تکرار می کنیم. این یک میانگین سه روزه در حال حرکت است ، زیرا ما به طور متوسط در طی یک دوره سه روزه هستیم.

در اینجا نحوه محاسبه میانگین سه روزه در 9 ژانویه 2020 آمده است:

برای 9 ژانویه 2020 ، میانگین حرکت سه روزه به عنوان میانگین قیمت از آن روز (1300) و دو روز قبل محاسبه می شود: 8 ژانویه (1300) و 7 ژانویه (1320). بنابراین ، میانگین متحرک برای 9 ژانویه 2020 میانگین این سه مقدار یا 1،306. 66 است که در تصویر بالا نشان داده شده است.

میانگین متحرک به همان روش برای هر یک از تاریخ های باقیمانده محاسبه می شود ، در مجموع سه قیمت سهام از تاریخ مورد نظر و دو روز گذشته سپس تقسیم می شود که در مجموع 3. برای 30 ژوئن ، میانگین حرکت سه روزه 1،070 استمیانگین قیمت ها از تاریخ 30 ژوئن (1،067) ، 29 ژوئن (1،067) و 28 ژوئن (1،076).

اگر نقاط داده اصلی و میانگین متحرک را در یک نمودار خط ترسیم کنیم ، موارد زیر را دریافت می کنیم:

خط قرمز نشان دهنده میانگین متحرک است و خط آبی نقاط داده اصلی را نشان می دهد. ممکن است متوجه شوید که خط قرمز صاف تر است و سنبله هایی در خط آبی دیده نمی شود. این هموار سازی هدف اصلی تکنیک متوسط در حال حرکت است؟ -؟ برای حذف نویز از داده ها استفاده می شود. با سر و صدای کمتری ، روندهای واقعی در داده های سری زمانی آسان تر است.

ما در این مثال میانگین حرکت سه روزه را محاسبه کرده ایم. با این حال ، ما می توانیم آن را در هر دوره ای که می خواهیم محاسبه کنیم ، مانند میانگین حرکت هفت روزه ، میانگین حرکت ده روزه و غیره.

چرا و از کجا میانگین حرکت استفاده می شود

میانگین های متحرک به طور گسترده در تجارت مالی و فنی مانند تجزیه و تحلیل قیمت سهام مورد استفاده قرار می گیرند تا روندهای کوتاه مدت و بلند مدت را بررسی کنند. اگر قیمت سهام بالاتر از میانگین متحرک باشد ، ما یک صعود داریم. اگر در زیر بماند ، معامله گران می گویند که ما در حال نزولی هستیم. سیگنالهایی مانند صعودها و نزول ها هنگام تصمیم گیری در مورد خرید یا فروش سهام ، به معامله گران اطلاع می دهند.

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

• تجزیه و تحلیل فروش: میانگین حرکت نوسانات صاف و سنبله در فروش هفتگی یا روزانه.: میانگین های حرکت در حال حرکت نشان می دهد که چگونه تعداد موارد تأیید شده با گذشت زمان تغییر می کند.
• تجزیه و تحلیل ترافیک وب: میانگین حرکت به ما کمک می کند تا روندهای بلند مدت در تعداد بازدیدها و نماهای صفحه را ببینیم.
• امور مالی شخصی: میانگین حرکت به طور میانگین کمک می کند تا روند هزینه های ما را نشان دهد (به عنوان مثال ، با نگاهی به میانگین ده روزه طی 2 سال گذشته).

میانگین حرکت در SQL

اکنون که می دانید میانگین متحرک چیست ، بیایید ببینیم چگونه آن را محاسبه کنیم. در SQL ، با توابع پنجره ، یک ویژگی خاص SQL که به شما امکان می دهد تجمع را در ردیف ها انجام دهید ، آسان است.

در حالی که شبیه به گروه توسط گروه است ، توابع پنجره هنگام نمایش نتیجه ، تمام ردیف ها را نگه می دارد. هیچ سقوط ردیف وجود ندارد. در عوض ، با توابع پنجره ، یک قاب یا "پنجره" از ردیف های یک اندازه معین را در اطراف ردیف فعلی تعریف می کنیم و سپس محاسبات را در آن پنجره انجام می دهیم. بنابراین ، یک جمع برای هر ردیف در یک جدول انجام می شود. هر ردیف پنجره خاص خود را دارد که محاسبه آن انجام می شود.

در زیر نحوه حرکت ستون از مثال ما در SQL محاسبه می شود:

برای توضیح کد با جزئیات:

• ما از یک تابع پنجره استفاده می کنیم که با یک بند بیش از حد مشخص شده است. همانطور که قبلاً توضیح داده شد ، ردیف ها از بین نمی روند و هر ردیف دارای پنجره خاص خود است که یک محاسبه بر روی آن انجام می شود.
• اندازه پنجره در مثال ما سه است. برای هر ردیف معین ، خود را ردیف و دو ردیف قبلی می گیریم و میانگین قیمت آن را از آن سه ردیف محاسبه می کنیم. این با کلمه کلیدی ردیف در بیانیه مشخص شده است: ردیف های بین 2 ردیف قبلی و فعلی. این بیانیه می گوید ، برای هر سطر در جدول ، چیزی به عنوان تجمع جریان و دو ردیف قبلی محاسبه می شود. این بدان معنی است که میانگین متحرک برای هر سطر به عنوان میانگین قیمت از روز معین و دو روز گذشته محاسبه می شود.
• ما برای هر روز یک قاب پنجره متفاوت داریم. در زیر ، می توانید تصویری از قاب پنجره ای را که برای ردیف مربوط به 9 ژانویه (به رنگ سبز) و قاب پنجره ای که برای ردیف مربوط به 27 ژوئن (به رنگ آبی) استفاده می شود ، مشاهده کنید:
• این مهم است که داده ها در خرما هیچ شکافی نداشته باشند. برای هر روز ، ما باید میانگین قیمت ها را از آن روز و دو روز قبل محاسبه کنیم. اگر تاریخ های از دست رفته در داده ها وجود داشته باشد ، این تجزیه و تحلیل معنی نخواهد داشت.
• ترتیب توسط کلمه کلیدی در داخل بند Over ، ترتیب ردیف هایی را که باید میانگین متحرک محاسبه شود ، تعریف می کند. در مثال ما ، ردیف ها ابتدا توسط ستون تاریخ مرتب می شوند ، سپس قاب پنجره تعریف می شود و محاسبه انجام می شود.
• برای این مثال ، ما در بند Over از پارتیشن توسط کلمه کلیدی استفاده نمی کنیم. تقسیم توسط گروه ها به بخش های منطقی توسط برخی از دسته ها ، اما ما در اینجا ردیف ها را به این ترتیب گروه بندی نمی کنیم. در واقع ، کل مجموعه داده ما فقط یک پارتیشن بزرگ است. بعداً در این مقاله ، نمونه ای با یک پارتیشن توسط.

بنابراین ، اکنون می دانید که چگونه می توانید میانگین های متحرک را در SQL محاسبه کنید! از کد SQL فوق می توان در بسیاری از سناریوهای تجاری دیگر استفاده کرد. فقط باید جدول و نام ستون را جایگزین کنید و تعداد ردیف هایی را که می خواهید میانگین آنها را محاسبه کنید تنظیم کنید. هر چیز دیگری می تواند یکسان باشد.

توابع پنجره یک نحو خاص دارند و برای آشنایی با استفاده ، مدتی طول می کشد و تمرین می کند. برای یادگیری و تمرین بیشتر ، دوره توابع پنجره را در LeaSql.com توصیه می کنم. این با تمرینات زیادی تعاملی است و به شما فرصتی می دهد تا سریعاً مواد جدید را تمرین کنید و یاد بگیرید ، زیرا با انجام این کار یاد می گیرید! برای کسب اطلاعات بیشتر در مورد محتوای دوره و توابع پنجره ، می توانید مقاله SQL ماه را بخوانید: توابع پنجره. در همان پلتفرم ، می توانید مقالات بسیار خوبی در مورد زمان استفاده از توابع پنجره و کشف چند نمونه نیز پیدا کنید.

حرکت به طور متوسط در تعداد مشخصی از روزها

در مثال قبلی ، ما میانگین حرکت سه روزه را محاسبه کردیم. شما می توانید میانگین های دیگر را نیز محاسبه کنید ، و هر تعداد از مقادیر قبلی را که دوست دارید در نظر بگیرید. هرچه تعداد مقادیر قبلی بیشتر باشد ، منحنی ما نرم تر خواهد بود. هرچه تعداد ردیف های مورد استفاده برای محاسبه میانگین ها کوچکتر باشد ، نمودار میانگین متحرک به مقادیر اصلی نزدیک تر می شود. تفاوت زیادی بین ، به عنوان مثال ، میانگین حرکت دو روزه و 30 روزه وجود دارد.

قبل از ایجاد تصویری برای اثبات این موضوع ، بیایید هر دو میانگین متحرک را محاسبه کنیم:

و اکنون می توانیم نحوه تفاوت این دو خط را تصور کنیم:

خط سبز نشان دهنده میانگین متحرک 30 روزه است (30 قیمت سهام در محاسبه هر میانگین استفاده می شود) و خط قرمز نشان دهنده میانگین دو روزه است. خط قرمز تقریباً شبیه خط آبی نقاط داده اصلی است. خط سبز صاف تر و دیدن روندهای بلند مدت آسان تر است.

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

مثال: میانگین حرکت هفت روزه موارد covid

حال بیایید کمی بیشتر تمرین کنیم تا آنچه را که تاکنون آموخته ایم ، یادآوری کنیم. Covid-19 هنوز در زندگی ما بسیار واقعی است ، بنابراین ما میانگین حرکت هفت روزه را برای تعداد کل موارد تأیید شده توسط کشور محاسبه خواهیم کرد. تعداد موارد تأیید شده برای هر روز برای هر کشور در جدول با نام تأیید شده_Covid ، در ستون تأیید شده_ روز ذخیره می شود:

برای محاسبه میانگین حرکت هفت روزه تعداد موارد تأیید شده ، می توانیم موارد زیر را انجام دهیم:

شاید متوجه شده باشید که این کد دقیقاً شبیه به روشی است که ما برای میانگین حرکت قیمت سهام نوشتیم. یک تفاوت اساسی در این است که ما در اینجا پارتیشن هایی داریم؟-؟ هر کشور یک پارتیشن است ، زیرا ما میانگین هفت روزه را به طور جداگانه برای هر کشور محاسبه می کنیم. در غیر این صورت ، فقط جدول و نام ستون متفاوت است. به راحتی می توانید این کد را با هر سناریوی تجاری دیگر تطبیق دهید.

پس از اجرای این کد و محاسبه میانگین متحرک ، می توانیم نمودار را با خطی که روند کشور خاص را نشان می دهد ، از شروع همه گیر تا ماه ژوئیه تولید کنیم. در زیر نمودار کشور کرواسی قرار دارد:

باز هم ، می بینیم که چگونه میانگین متحرک در مقایسه با نقاط داده اصلی ، سنبله ها و نوسانات را کاهش می دهد و باعث کاهش سنبله ها و نوسانات می شود.

برای به دست آوردن بینش قدرتمند از داده های COVID-19 می توانید از سایر توابع پنجره استفاده کنید. اگر علاقه مند به اطلاعات بیشتر هستید ، مقاله اخیر ما را در مورد نحوه تجزیه و تحلیل داده های COVID-19 با استفاده از توابع پنجره بررسی کنید.

میانگین حرکت و عملکردهای پنجره در SQL

در این مقاله ، ما دیدیم که چگونه می توانید میانگین های متحرک را در SQL با استفاده از توابع پنجره محاسبه کنید. این میانگین به ما کمک می کند تا با کاهش میزان سر و صدا ، روند واقعی را بهتر ببینیم.

اگر می خواهید در مورد توابع پنجره تمرین کنید و اطلاعات بیشتری کسب کنید ، من دوره تعاملی آنلاین را در توابع پنجره ای که توسط LeaSql.com منتشر شده است توصیه می کنم. این دوره شامل بیش از 200 تمرین است ، بنابراین شما مطمئناً مهارت تازه آموخته شده خود را عملی خواهید کرد. و به من اعتماد کنید - وقتی صحبت از SQL و مهارت های برنامه نویسی می شود ، تمرین مهم است - شما با انجام این کار یاد می گیرید!