تجمع در GROUP BY در مقابل توابع پنجره با استفاده از OVER()

Summarize this content to 400 words in Persian Lang
SQL یک زبان فوق العاده همه کاره است که برای دستکاری و تجزیه و تحلیل داده ها ضروری است. یکی از قدرتمندترین ویژگی های SQL توانایی آن در انجام تجمیع است. با این حال، به عنوان مهندسان داده یا علاقه مندان، ما اغلب نیاز به تشخیص بین دو روش کلیدی برای تجمیع داریم: GROUP BY توابع بند و پنجره با استفاده از OVER() عبارت. در این مجموعه پست، ما عمیقاً به درک تفاوت های بین این دو تکنیک، ساختار آنها و نحوه استفاده از آنها برای ایجاد بینش تجاری ارزشمند خواهیم پرداخت.

این GROUP BY عبارت در SQL برای گروه بندی ردیف هایی که دارای مقادیر یکسان در ستون های مشخص شده هستند در ردیف های خلاصه استفاده می شود. این یک روش کلاسیک برای تجمیع داده ها است.

نحو:

SELECT
column1,
aggregate_function(column2)
FROM table
WHERE conditions
GROUP BY column1;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

مثال:

SELECT
SalesPersonID,
SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

در این مثال، کل فروش هر فروشنده با گروه بندی ردیف ها بر اساس محاسبه می شود SalesPersonID و جمع بندی SalesAmount برای هر گروه

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

نحو:

SELECT
column1,
aggregate_function(column2) OVER (PARTITION BY column1 ORDER BY column2) AS new_column
FROM table;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

مثال:

SELECT
SalesPersonID,
SalesAmount,
SUM(SalesAmount) OVER (PARTITION BY SalesPersonID) AS TotalSales
FROM Sales;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

اینجا، SUM(SalesAmount) OVER (PARTITION BY SalesPersonID) کل فروش را برای هر فروشنده محاسبه می کند و یک ستون جدید اضافه می کند TotalSales به هر ردیف مربوط به آن فروشنده.

بیایید ساختار دستور تابع پنجره را بشکنیم: FUNCTION() OVER (PARTITION BY column1 ORDER BY column2).

1. FUNCTION()

هر تابع استاندارد SQL aggregate (SUM، AVG، COUNT، MIN، MAX) یا توابع ویژه پنجره (ROW_NUMBER، RANK، DENSE_RANK، LAG، LEAD، FIRST_VALUE، LAST_VALUE).

2. OVER()

پنجره ای را که تابع روی آن کار می کند، تعریف می کند.

3. پارتیشن توسط ستون 1

داده ها را به پارتیشن تقسیم می کند. توابع به طور مستقل در هر پارتیشن عمل می کنند.

مثال:

SELECT
SalesPersonID,
SalesAmount,
SUM(SalesAmount) OVER (PARTITION BY SalesPersonID) AS TotalSales
FROM Sales;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

اینجا، PARTITION BY SalesPersonID یعنی که SUM(SalesAmount) برای هر فروشنده به طور جداگانه محاسبه می شود و یک ستون فروش کل بدون کاهش ردیف ها اضافه می شود.

4. سفارش بر اساس ستون2

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

مثال:

with table_sum as
(select
SalesPersonID,
sum(SalesAmount) as total_sales
from Sales group by 1)

select
SalesPersonId
, Rank() over( order by total_sales desc) as ranking
from table_sum;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

در این مثال، ORDER BY total_sales تضمین می کند که فروش بر اساس مجموع مرتب شده است تا فروش هر فروشنده رتبه بندی شود.

کاهش داده در مقابل حفظ داده

دسته بندی بر اساس: داده ها را با کاهش تعداد ردیف ها جمع می کند، که برای خلاصه کردن مجموعه داده ها ایده آل است.

توابع پنجره: تمام ردیف‌ها را حفظ می‌کند و برای تحلیل‌های دقیق سطح ردیف مناسب است.

زمینه و بینش

دسته بندی بر اساس: عالی برای تولید خلاصه ها و گزارش های سطح بالا.

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

برنامه های کاربردی تجاری

دسته بندی بر اساس: برای خلاصه های داشبورد، گزارش های فصلی و معیارهای مالی گروه بندی شده مفید است.

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

تجزیه و تحلیل طرح های پرس و جو: به طور منظم استفاده کنید EXPLAIN بررسی کنید که موتور پایگاه داده چگونه پرس و جوهای شما را اجرا می کند.

نمایه سازی کارآمد: همیشه ستون های مورد استفاده در گروه بندی و مرتب سازی را ایندکس کنید.

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

به طور خلاصه، هر دو GROUP BY و توابع پنجره اهداف منحصر به فردی را در تجزیه و تحلیل داده های SQL انجام می دهند. در حالی که GROUP BY برای خلاصه کردن داده‌ها و تولید گزارش‌های سطح بالا عالی است، توابع پنجره با حفظ جزئیات سطح ردیف و امکان محاسبات پیچیده روی مجموعه‌ای از ردیف‌های مرتبط، بینش عمیق‌تری را ارائه می‌کنند.

با تسلط بر هر دو تکنیک، مهندسان داده و تحلیلگران می توانند پتانسیل کامل داده های خود را باز کنند و بینشی ایجاد کنند که ارزش کسب و کار را افزایش می دهد و فرآیندهای تصمیم گیری را بهینه می کند. در پست بعدی، کاربردهای ساده توابع پنجره را برای انباشته‌های اولیه مانند موارد زیر بررسی خواهیم کرد AVG، MAX، MIN، و COUNT.

منتظر غواصی عمیق تر در قابلیت های SQL باشید! اگر این پست برای شما مفید بود، اشتراک گذاری و نظر را در زیر فراموش نکنید.

تصاویر از https://www.boardinfinity.com/blog/window-function-in-sql/

SELECT 
    column1,
    aggregate_function(column2)
FROM table
WHERE conditions
GROUP BY column1;

SELECT 
    SalesPersonID,
    SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID;

SELECT 
    column1,
    aggregate_function(column2) OVER (PARTITION BY column1 ORDER BY column2) AS new_column
FROM table;

SELECT 
    SalesPersonID, 
    SalesAmount,
    SUM(SalesAmount) OVER (PARTITION BY SalesPersonID) AS TotalSales
FROM Sales;

SELECT
    SalesPersonID,
    SalesAmount,
    SUM(SalesAmount) OVER (PARTITION BY SalesPersonID) AS TotalSales
FROM Sales;

with table_sum as 
(select 
SalesPersonID, 
sum(SalesAmount) as total_sales 
from Sales group by 1) 

select 
SalesPersonId
, Rank() over( order by total_sales desc) as ranking 
from table_sum;