شش مفهوم کلیدی برای تسلط بر Window Functions در SQL Server

نوشته شده توسط: تیم فنی نیک آموز

۲۰ دی ۱۴۰۰

زمان مطالعه: 17 دقیقه

مقدمه

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

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

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

پیش‌نیازها

اگر می‌خواهید همراه با مقاله، پیاده‌سازی هم انجام دهید، موارد زیر را باید حتما نصب کنید

داکر برای اجرای postgres
pgcli برای اتصال به postgres

در ترمینال خود یک کانتینر داکر Postgres را راه‌اندازی کرده و وارد آن شوید.

docker run --name pg_local -p 5432:5432 \
-e POSTGRES_USER=start_data_engineer -e POSTGRES_PASSWORD=password \
-e POSTGRES_DB=window -d postgres:12.2
pgcli -h localhost -p 5432 -U start_data_engineer window
# the password is password, enter when prompted

یک جدول با نام clickstream ایجاد کنید و داده‌های ساختگی در آن وارد کنید.

drop table if exists clickstream;
create table clickstream (
eventId varchar(40),
userId int,
sessionId int,
actionType varchar(8),
datetimeCreated timestamp
);
INSERT INTO clickstream(eventId, userId, sessionId, actionType, datetimeCreated )
VALUES
('۶e598ae5-3fb1-476d-9787-175c34dcfeff',1 ,1000,'click','2020-11-25 12:40:00'),
('0c66cf8c-0c00-495b-9386-28bc103364da',1 ,1000,'login','2020-11-25 12:00:00'),
('۵۸c021ad-fcc8-4284-a079-8df0d51601a5',1 ,1000,'click','2020-11-25 12:10:00'),
('۸۵eef2be-1701-4f7c-a4f0-7fa7808eaad1',1 ,1001,'buy', '2020-11-22 18:00:00'),
('۰۸dd0940-177c-450a-8b3b-58d645b8993c',3 ,1010,'buy', '2020-11-20 01:00:00'),
('db839363-960d-4319-860d-2c9b34558994',10,1120,'click','2020-11-01 13:10:03'),
('۲c85e01d-1ed4-4ec6-a372-8ad85170a3c1',10,1121,'login','2020-11-03 18:00:00'),
('۵۱eec51c-7d97-47fa-8cb3-057af05d69ac',8 ,6, 'click','2020-11-10 10:45:53'),
('۵bbcbc71-da7a-4d75-98a9-2e9bfdb6f925',3 ,3002,'login','2020-11-14 10:00:00'),
('f3ee0c19-a8f9-4153-b34e-b631ba383fad',1 ,90, 'buy', '2020-11-17 07:00:00'),
('f458653c-0dca-4a59-b423-dc2af92548b0',2 ,2000,'buy', '2020-11-20 01:00:00'),
('fd03f14d-d580-4fad-a6f1-447b8f19b689',2 ,2000,'click','2020-11-20 00:00:00');

توجه داشته باشید ما از سِشِن کاربر برای نشان دادن زمانی استفاده می‌کنیم که کاربر وارد سیستم می‌شود تا زمانی که از سیستم خارج شود. id سِشِن بین رویداد ورود و خروج کاربر مربوطه یکسان خواهد بود.

شش مفهوم کلیدی

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

۱) چه موقع از توابع پنجره استفاده کنیم؟

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

ردیف‌ها را بر اساس ستون یا ستون‌های خاصی در هر پارتیشن در جدول رتبه‌بندی کنید.
مقادیر عددی درون هر پارتیشن را بر اساس دسته‌های صدتایی در باکت‌ها برچسب‌گذاری کنید.
اولین (دومین یا آخرین) رویداد را در یک پارتیشن خاص شناسایی کنید.
محاسبه میانگین یا میانه.

استفاده‌های عمومی از توابع پنجره زمانی است که:

برای انجام یک محاسبه روی مجموعه‌ای از ردیف‌ها (که توسط ستون‌های پارتیشن مشخص شده‌اند) در شرایطی که حفظ نتیجه در سطح ردیف لازم باشد. اگر از group by استفاده کنیم، باید از توابع تجمعی در هر ستونی که بخشی از group by نیست استفاده کنیم.
نیاز به انجام محاسبات بر اساس یک rolling window داشته باشیم.

جدول clickstream شامل رویدادهای ورود، کلیک، خرید، فروش، بازگشت و خروج است. کوئری زیر رویدادها را در هر userId و sessionId بر اساس زمان ایجاد رویداد (datetimeCreated) مرتب می‌کند.

select eventId,
userId,
sessionId,
actionType,
datetimeCreated,
ROW_NUMBER() OVER(
PARTITION BY userId,
sessionId
ORDER BY datetimeCreated DESC
) as eventOrder
from clickstream;

۲) Partition By

عبارت Partition By ستون یا ستون‌هایی را که ردیف‌های مرتبط را گروه‌بندی می‌کنند، تعریف می‌کند. به این مجموعه از ردیف‌ها پارتیشن می‌گویند. در مورد مثال این مقاله، طبق شکل زیر، بر اساس userId و sessionId که ردیف‌ها را گروه‌بندی می‌کنیم، پارتیشن‌بندی می‌کنیم.

۳) Order By

در یک پارتیشن، می‌توانیم به‌صورت اختیاری ترتیب ردیف‌ها را با استفاده از عبارت Order By تعریف کنیم. در اینجا ما ردیف‌های درون یک پارتیشن را به‌صورت نزولی بر اساس زمان ایجاد رویداد (datetimeCreated) مرتب می‌کنیم.

۴) تابع

توابع برای ردیف‌های داخل یک پارتیشن اعمال خواهند شد. توابع پنجره به پایگاه داده بستگی دارد و برای هر پایگاه داده، ممکن است با یک پایگاه داده دیگر متفاوت باشد.
در مثال زیر، از ROW_NUMBER برای دریافت ترتیب ایجاد رویدادها در هر سِشِن کاربر استفاده می‌کنیم.

۵) Lead و Lag

از اینها می‌توان برای انجام محاسبات بر اساس داده‌های سایر ردیف‌ها استفاده کرد. Lead و Lag به ترتیب برای دسترسی به داده‌های ردیف‌های بعد یا قبل از ردیف فعلی استفاده می‌شوند. ردیف‌ها را می‌توان با استفاده از order by مرتب کرد.
Lead و Lag می‌توانند برای محاسبه اختلاف زمانی بین رویدادها برای یک سشن کاربر معین (پارتیشن) استفاده شوند. در مثال زیر، ما از Lead و Lag استفاده می‌کنیم تا زمانی را که رویدادهای بعدی و قبلی در یک سشن کاربر رخ می‌دهند را به دست آوریم.

select eventId,
userId,
sessionId,
actionType,
datetimeCreated,
LEAD(datetimeCreated, 1) OVER(
PARTITION BY userId,
sessionId
ORDER BY datetimeCreated
) as nextEventTime,
LAG(datetimeCreated, 1) OVER(
PARTITION BY userId,
sessionId
ORDER BY datetimeCreated
) as prevEventTime
from clickstream;

۶) Rolling Window

می‌توانیم از توابع پنجره بدون به کار بردن PARTITION BY برای شبیه‌سازی یکRolling Window در تمام ردیف‌ها استفاده کنیم. فرض کنید می‌خواهیم تعداد رویدادهای خرید را در ۵ رویداد آخر در بین همه کاربران، به استثنای رویداد فعلی، پیدا کنیم، کوئری زیر را برای این منظور استفاده می‌کنیم:

select eventId,
userId,
sessionId,
actionType,
datetimeCreated,
SUM(
CASE
WHEN actionType = 'buy' THEN 1
ELSE 0
END
) OVER(
ORDER BY datetimeCreated DESC ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
) as num_purchases
from clickstream;

از تصویر بالا می‌بینید که پنجره از ۵ ردیف PRECEDING شروع شده و قبل از ردیف فعلی که یک ردیف PRECEDING است متوقف می‌شود. num_purchases برای هر ردیف همان‌طور که در مجموعه نتایج بالا مشاهده می‌شود محاسبه می‌شود.
در مثال بعدی قصد داریم یک کوئری بنویسیم که بررسی کند آیا یکی از رویدادهای فعلی، قبلی یا بعدی، یک رویداد خرید بوده است یا خیر؟

select eventId,
userId,
sessionId,
actionType,
datetimeCreated,
MAX(
CASE
WHEN actionType = 'buy' THEN 1
ELSE 0
END
) OVER(
ORDER BY datetimeCreated DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) as neighborBuy
from clickstream;

همچنین می‌توانید از این محدوده‌های پنجره سفارشی، در یک پارتیشن خاص نیز استفاده کنید.

کارایی توابع پنجره

توابع پنجره ممکن است پر هزینه باشند. از EXPLAIN قبل از کوئری استفاده کنید تا Query Plan را نشان دهد. برای مثال، اگر می‌خواهید فقط ردیفی را با آخرین رویداد دریافت کنید. ممکن است استفاده از تکنیک دیگری مانند Group By که در زیر نشان داده شده است، مناسب‌تر باشد.

EXPLAIN
select *
from (
select userId,
sessionId,
datetimeCreated,
ROW_NUMBER() OVER(
PARTITION BY userId,
sessionId
ORDER BY datetimeCreated DESC
) as eventOrder
from clickstream
) as t
where t.eventOrder = 1;

EXPLAIN
select userId,
sessionId,
max(datetimeCreated) as datetimeCreated
from clickstream
group by userId,
sessionId;
\q -- exit pgcli

هزینه نشان داده شده در کادر قرمز در تصاویر بالا نشان دهنده هزینه کوئری است (واحدهای هزینه دلخواه هستند، اما به طور معمول هزینه واکشی از صفحه دیسک مورد نظر قرار می‌گیرند). هرچه هزینه کمتر باشد اجرای کوئری سریع‌تر می‌شود. EXPLAIN ANALYZE را برای اجرای کوئری و همچنین دریافت زمان واقعی صرف شده، امتحان کنید.
پس از پایان کار برای توقف کانتینر داکر برای Postgres از دستورات زیر استفاده کنید:

# Stop Postgres docker container
docker stop pg_local
docker rm pg_local

نتیجه گیری

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