راهنمای محاسبه Mode در TSQL SQL Server

محاسبه مُد (Mode) در T-SQL با داده‌های SQL Server: راهنمای کامل و بهینه

در علم آمار، مُد (Mode) به مقداری گفته می‌شود که بیشترین تکرار را در یک مجموعه داده دارد. بر خلاف میانگین (Mean) و میانه (Median)، مُد را می‌توان برای داده‌های عددی و همچنین داده‌های طبقه‌بندی شده (Categorical Data) به کار برد. یک مجموعه داده ممکن است دارای یک مُد (تک‌مُدی)، چندین مُد (چندمُدی) یا اصلاً هیچ مُدی نباشد. در SQL Server، محاسبه مُد به سادگی محاسبه میانگین یا میانه نیست، زیرا تابع داخلی مانند AVG() یا MEDIAN() برای آن وجود ندارد. این راهنما به شما کمک می‌کند تا روش‌های مختلف محاسبه مُد در T-SQL را بیاموزید و داده‌های خود را بهتر تحلیل کنید.

برای شروع، یک مجموعه داده نمونه ایجاد می‌کنیم. یک متغیر جدولی به نام @T ساخته و آن را با مقادیر صحیح پر می‌کنیم تا بتوانیم محاسبه مُد در SQL Server را تمرین کنیم:


DECLARE @T TABLE (col INT);

INSERT INTO @T (col) VALUES
(1), (1), (2), (3), (3), (3), (4), (5), (5);

در این مجموعه داده نمونه، عدد ‘3’ سه بار تکرار شده و اعداد ‘1’ و ‘5’ هر کدام دو بار تکرار شده‌اند. بنابراین، مُد این مجموعه داده ‘3’ است.

محاسبه مُد با استفاده از COUNT و ROW_NUMBER

یکی از رایج‌ترین روش‌ها برای محاسبه مُد در T-SQL، استفاده ترکیبی از GROUP BY، COUNT() و ROW_NUMBER() (یا TOP WITH TIES) است. این رویکرد ابتدا تعداد تکرار هر مقدار منحصر به فرد را شمارش می‌کند، سپس آن‌ها را بر اساس فراوانی‌شان رتبه‌بندی می‌کند. این روش برای یافتن مُد در SQL Server بسیار کاربردی است.

برای محاسبه مُد با استفاده از TOP 1 WITH TIES و GROUP BY، می‌توانید از کد زیر استفاده کنید:


SELECT TOP 1 WITH TIES
T.col
FROM
@T AS T
GROUP BY
T.col
ORDER BY
COUNT(*) DESC;


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

به‌عنوان جایگزین، می‌توانید همین نتیجه را با استفاده از ROW_NUMBER() برای رتبه‌بندی فراوانی‌ها به دست آورید. این رویکرد کنترل بیشتری به شما می‌دهد، به‌خصوص اگر بخواهید با موارد چندمُدی (Ties) به شکل متفاوتی برخورد کنید. این یک روش قدرتمند برای تحلیل فراوانی داده‌ها در T-SQL است.


WITH RankedCounts AS
(
SELECT
T.col,
COUNT(*) AS Freq,
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
FROM
@T AS T
GROUP BY
T.col
)
SELECT
R.col
FROM
RankedCounts AS R
WHERE
R.rn = 1;


این کوئری تنها یک مُد را برمی‌گرداند، حتی اگر چندین مقدار با بالاترین فراوانی وجود داشته باشد (بر اساس ترتیب ROW_NUMBER()). اگر می‌خواهید همه مُدهای ممکن را داشته باشید، باید از RANK() یا DENSE_RANK() به جای ROW_NUMBER() استفاده کنید و شرط WHERE را بر روی رتبه 1 قرار دهید. این نکته برای بهینه‌سازی کوئری‌های SQL Server در موارد چندمُدی مهم است.

محاسبه مُد با استفاده از Common Table Expression (CTE)

استفاده از عبارت جدول مشترک (CTE – Common Table Expression) می‌تواند کوئری را خواناتر و سازمان‌یافته‌تر کند، به‌ویژه زمانی که با مراحل متعدد سروکار دارید. این روش اساساً یک راه ساختاریافته برای اعمال منطق GROUP BY و ROW_NUMBER() است که به بهبود خوانایی و نگهداری کدهای T-SQL کمک می‌کند.

در اینجا مثالی از محاسبه مُد با CTE در SQL Server آورده شده است:


WITH ModeCalculation AS
(
SELECT
T.col,
COUNT(*) AS Frequency
FROM
@T AS T
GROUP BY
T.col
),
RankedMode AS
(
SELECT
M.col,
M.Frequency,
ROW_NUMBER() OVER (ORDER BY M.Frequency DESC, M.col ASC) AS RowNum
FROM
ModeCalculation AS M
)
SELECT
R.col
FROM
RankedMode AS R
WHERE
R.RowNum = 1;


این کوئری CTE ابتدا فراوانی هر مقدار را محاسبه کرده و سپس آن‌ها را رتبه‌بندی می‌کند تا مقدار با بالاترین فراوانی را پیدا کند. افزودن M.col ASC به ORDER BY در ROW_NUMBER() باعث می‌شود در صورت تساوی فراوانی‌ها، مقدار کوچکتر به عنوان مُد انتخاب شود. این روش برای مدیریت داده‌های پیچیده و یافتن مُد بسیار مؤثر است.

بررسی توابع PERCENTILE_CONT در SQL Server 2022

SQL Server 2022 توابع جدید درصد تقریبی (Approximate Percentile Functions) از جمله APPROX_PERCENTILE_CONT و APPROX_PERCENTILE_DISC را معرفی کرده است. اگرچه این توابع در درجه اول برای محاسبه درصدی‌ها (Percentiles) طراحی شده‌اند، اما PERCENTILE_CONT(0.5) به طور خاص برای محاسبه میانه (Median) استفاده می‌شود. در توزیع‌های داده خاص و کاملاً متقارن، ممکن است میانه و مُد یکسان باشند، اما به طور کلی این تابع برای محاسبه مستقیم مُد نیست.

با این حال، اشاره به این توابع در بحث تحلیل داده در SQL Server مفید است، زیرا می‌توانند برای بررسی ویژگی‌های دیگر توزیع داده‌ها در کنار مُد به کار روند. کد زیر میانه را محاسبه می‌کند:


SELECT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY T.col) OVER () AS MedianValue
FROM
@T AS T;


مهم است بدانید که خروجی این کوئری، میانه مجموعه داده است نه مُد آن. برای مجموعه داده نمونه ما (1, 1, 2, 3, 3, 3, 4, 5, 5)، میانه ‘3’ و مُد نیز ‘3’ است، اما این یک اتفاق تصادفی در این مثال خاص است و همیشه صادق نیست. استفاده از توابع درصد تقریبی می‌تواند در پردازش مجموعه‌داده‌های بسیار بزرگ به لحاظ عملکردی مفید باشد، اما برای یافتن دقیق مُد، روش‌های مبتنی بر COUNT و GROUP BY قابل اطمینان‌تر هستند.