کشف سنگهای پنهان: توابع پنجره PostgreSQL که ممکن است درباره آنها ندانید

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

توابع پنجره چیست؟

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

در PostgreSQL از توابع پنجره همراه با استفاده می شود OVER() بند، که محدوده یا “پنجره” سطرها را برای محاسبه تعریف می کند.

حقایق کمتر شناخته شده در مورد توابع پنجره PostgreSQL

1. قاب های پنجره انعطاف پذیر

ممکن است با قاب پنجره کشویی پیش فرض که به صورت تعریف شده است آشنا باشید ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. با این حال، PostgreSQL چندین گزینه دیگر را برای تعریف فریم های پنجره ارائه می دهد، مانند RANGE و GROUPS. این به شما اجازه می دهد تا قاب های پنجره سفارشی را مطابق با نیازهای خاص خود ایجاد کنید.

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

SELECT value, AVG(value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
FROM your_table;

2. توابع چند پنجره در یک پرس و جو

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

مثال زیر را در نظر بگیرید، که هم کل و هم میانگین جاری فروش را محاسبه می کند:

SELECT date, sale_amount,
       SUM(sale_amount) OVER (ORDER BY date) AS running_total,
       AVG(sale_amount) OVER (ORDER BY date) AS running_average
FROM sales;

3. پارتیشن بندی با توابع پنجره

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

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

SELECT product_id, date, sale_amount,
       SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY date) AS product_running_total
FROM sales;

4. مصالح سفارشی در توابع پنجره

PostgreSQL به شما امکان می دهد از توابع جمع سفارشی در توابع پنجره استفاده کنید. این بدان معنی است که شما می توانید توابع انبوه خود را ایجاد کنید و از آنها در محاسبات پنجره خود استفاده کنید.

برای مثال، ممکن است بخواهید میانه متحرک یک مجموعه داده را محاسبه کنید:

CREATE AGGREGATE median(anyelement) (
  SFUNC=array_append,
  STYPE=anyarray,
  FINALFUNC=array_median
);

SELECT value, median(value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
FROM your_table;

5. از توابع پنجره با DISTINCT استفاده کنید

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

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

SELECT date,
       COUNT(DISTINCT customer_id) OVER (ORDER BY date) AS unique_customers
FROM sales;

نتیجه

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

پرس و جو مبارک!