در این نوشته هدف اصلی، درک مفاهیم اساسی window function ها و اعمال آنها در گردش کار SQL است. window function ها چیزی بیش از FOPO (Function Over PartitionBy OrderBy) نیستند. در اینجا یک طرح کلی از آنچه در این مقاله پوشش داده خواهد شد، آمده است.
اگر درک خوبی از GROUP BY دارید، می توانید مفهوم window function ها به راحتی درک کنید. به طور خلاصه، GROUP BY یک جدول را به ردیف های کمتری متراکم می کند یا ردیف های منحصر به فرد را خروجی می گیرد. هنگام گروه بندی بر اساس یک ستون، کوئری SQL یک سطر را برای هر مقدار متمایز یافت شده تحت آن ستون خروجی می دهد. در گروه بندی چندین ستونه، خروجی از ترکیبات منحصر به فرد ستون های مشخص شده (با / بدون) برخی aggregate function ها تشکیل می شود. در اینجا مثالی آورده شده است.
در این جدول product_orders، هر order_id به تمام آیتم های محصول (product_name) خریداری شده توسط مشتری پیوست می شود.
اکنون، بیایید نگاهی عمیق‌تر به مکانیزم window function ها بیندازیم.
بر اساس لول رفتار مورد نیاز برای transform دیتا، نیازی به استفاده از همه کامپوننت ها نیست، به جز OVER. (به یاد داشته باشید: عبارت OVER باعث فراخوانی window function ها می شود و همچنین aggregate function ها را قادر می سازد تا به window function ها تبدیل شوند.) در اینجا موارد استفاده وجود دارد.
روش OVER () بدون تعریف PARTITION BY (و/یا) ORDER BY ، تابع را در کل مجموعه داده اعمال می کند. این رویکرد به خوبی با aggregate function ها جفت می شود. یکپارچگی جدول یا مجموعه داده حفظ می شود در حالی که یک تابع برای محاسبه یک مقدار با استفاده از تمام ردیف ها اعمال می شود.
در این مثال، سه aggregate function با عبارت OVER جفت می‌شوند تا سه window function ایجاد کنند که با total_amount، total_count و total_mean نمایش داده می‌شوند. برای مثال total_amount تمام مقادیر زیر ستون amount را جمع می‌کند و مجموع جدید در هر ردیف ظاهر می‌شود. همین منطق برای total_count و total_mean صدق می کند. این setup به محاسبه درصد کل و ایجاد نسبت‌هایی در برابر مقدار کل یا آماری مانند میانگین کمک می‌کند.
SELECT t1.*, SUM(t1.amount) OVER () AS total_amount, COUNT(*) OVER () AS total_count, AVG(t1.amount) OVER () AS total_mean FROM orders t1;
در نتیجه، مقادیر window function ها در ایجاد معیارهای استاندارد برای مقایسه آسان در میان مجموعه داده مفید هستند. اولین بلوک خاکستری برجسته، مقادیر کل را در مثال ارائه شده در زیر نشان می دهد، در حالی که بلوک دوم حاوی معیارهایی است که داده ها را توصیف می کند.
SELECT t1.order_id, t1.order_date, -- aggregate functions + OVER clause = window functions SUM(t1.amount) OVER () AS total_amount, ROUND(AVG(t1.amount) OVER (), 2) AS avg_order_size, SUM(t1.num_product_items) OVER () AS total_product_items, COUNT(*) OVER () AS total_count, -- create ratios and % of total using values from window functions ROUND(1.0 * t1.amount / SUM(t1.amount) OVER (), 4) AS percent_amount, ROUND(1.0 * t1.num_product_items / SUM(t1.num_product_items) OVER (), 4) AS ercent_product_items FROM orders t1;
همانطور که قبلاً اشاره کردم، PARTITION BY مشابه GROUP BY است. مانند GROUP BY، با تعریف ستون یا ستون ها PARTITION BY شروع کنید. هنگامی که زیرگروه ها تعریف شدند، عبارت OVER تابع را فراخوانی یا فعال می کند تا منطق خود را در هر زیرگروه اجرا کند. برخلاف GROUP BY، بند PARTITION BY جدول را فشرده نمی کند. در عوض، یکپارچگی جدول را حفظ می کند در حالی که خروجی را به عنوان یک ستون اضافه می کند.
SELECT user_id, order_id, product_name, amount, NTILE(3) OVER (PARTITION BY order_id) AS ntile_by_order_id, FIRST_VALUE(amount) OVER (PARTITION BY order_id) AS first_amount_value_by_order_id, COUNT(*) OVER (PARTITION BY order_id) AS count_by_order_id, SUM(amount) OVER (PARTITION BY order_id) AS sum_by_order_id, ROUND(AVG(amount) OVER (PARTITION BY order_id), 4) AS avg_amount_by_order_id FROM product_orders;
برای مثال، با استفاده از جدول product_orders، عبارت PARTITION BY روی ستون order_id است. علاوه بر این، تعداد زیر گروه ها با تعداد منحصر به فرد order_id تعریف می شود. در این مورد، آن عدد، سه است.
در NTILE : NTILE(3) برابر با تعداد bin ها است و PARTITION BY زیر گروه ها (یا پارتیشن ها) را تعیین می کند که در آن هر زیر گروه به سه bin تقسیم می شود.
در FIRST_VALUE: تابع اولین مقدار یا ردیف را در زیرگروه تعریف شده را خروجی می دهد.
در COUNT: تعداد ردیف های هر زیر گروه تعریف شده را می شمارد.
در SUM: مقادیر (مثلاً amount) را در هر زیرگروه جمع می کند.
در AVG: میانگین هر زیرگروه تعریف شده را محاسبه می کند.
در خروجی، بلوک های خاکستری مشخص شده زیر زیرگروه های تعریف شده را با عبارت PARTITION BY و ایجاد ستون های جدید توسط window function ها را نشان می دهند.
عبارت ORDER BY هنگام رتبه بندی یا مرتب سازی داده ها استفاده می شود. این می تواند یک عبارت مستقل در window function ها باشد یا با عبارت PARTITION BY جفت شود. گاهی اوقات، فقط استفاده از عبارت ORDER BY مناسب تر خواهد بود، که امکان رتبه بندی یا ترتیب کل مجموعه داده را فراهم می کند. به عنوان مثال:
SELECT order_id, amount, num_product_items, -- ranking by num_product_items ROW_NUMBER() OVER (ORDER BY num_product_items) AS row_num_by_items, RANK() OVER (ORDER BY num_product_items) AS rank_by_items, DENSE_RANK() OVER (ORDER BY num_product_items) AS dense_rank_by_items, -- ranking by amount ROW_NUMBER() OVER (ORDER BY amount) AS row_num_by_amount, RANK() OVER (ORDER BY amount) AS rank_by_amount, DENSE_RANK() OVER (ORDER BY amount) AS dense_rank_by_amount FROM orders;
استفاده از عبارت ORDER BY در توابع رتبه بندی، view مرتبی را بر اساس مقادیر یافت شده در ستون num_product_items و amount ایجاد می کند. تفاوت بین توابع رتبه بندی در ناحیه پررنگ نارنجی زیر نشان داده شده است.
یادآوری: عبارت ORDER BY به ترتیب صعودی (ASC) تنظیم شده است. برای نزولی، باید با DESC نشان داده شود (به عنوان مثال، ORDER BY amount DESC).
پس از بررسی مکانیزم PARTITION BY و ORDER BY به طور جداگانه، جفت کردن این دو کامپوننت مرحله بعدی است. PARTITION BY بر اساس ستون یا مجموعه ای از ستون های انتخابی، زیر گروه ها (یا پارتیشن ها) را ایجاد می کند. ORDER BY داده ها را به ترتیب صعودی یا نزولی سازماندهی می کند. من جفت شدن این مؤلفه ها را با استفاده از جدول product_orders نشان خواهم داد.
در این مثال، من از 9 window function برای نشان دادن جفت شدن PARTITION BY و ORDER BY استفاده می کنم. در این مرحله، اگر درک کاملی از هر کامپوننت دارید، این باید ساده باشد. شرح هر window function ای برای Postgres در اینجا موجود است.
در این کوئری SQL، تمام window function ها از order_id در عبارت PARTITION BY استفاده می کنند. تعداد order_id های یکتا برابر با تعداد زیر گروه ها است و ستون ORDER BY داده های هر زیرگروه را مرتب می کند.
SELECT user_id, order_id, product_name, amount, -- rank items within the order by amount ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY amount DESC) AS row_num, -- rank items within the order by amount and highlight same values with same rank DENSE_RANK() OVER (PARTITION BY order_id ORDER BY amount DESC) AS dense_rnk, -- understand the percentile ranking of items in terms of amount within the order PERCENT_RANK() OVER (PARTITION BY order_id ORDER BY amount) AS percent_rnk, -- what is the first product_name within the order based on items being ordered by price ascending? FIRST_VALUE(product_name) OVER (PARTITION BY order_id ORDER BY amount RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_val, -- what is the last product_name within the order based on items being ordered by price ascending? LAST_VALUE(product_name) OVER (PARTITION BY order_id ORDER BY amount RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val, -- create three bins within the order by bucketing items based on amount ascending NTILE(3) OVER (PARTITION BY order_id ORDER BY amount) AS ntile_bin, -- what is the second product_name within the order ordered by amount? NTH_VALUE(product_name, 2) OVER (PARTITION BY order_id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS nth_val, -- grab the amount of an item that immediately precedes the current item within the order ordered by amount LAG(amount) OVER (PARTITION BY order_id ORDER BY amount) AS lag_val, -- grab the amount of an item that immediately following the current item within the order ordered by amount LEAD(amount) OVER (PARTITION BY order_id ORDER BY amount) AS lead_val FROM product_orders;
یکی از مواردی که قبلاً مورد بحث قرار نگرفت، استفاده از عبارت «RANGE BETWEEN UNBOUNDED PRECEDING UNBUNDED AND UNBOUNDED FOLLOWING» در توابع FIRST_VALUE()، LAST_VALUE() و NTH_VALUE() است. این عبارت برای تعریف ردیف اول و آخر زیرگروه مورد نیاز است. هیچ چیز خاصی در مورد این عبارت وجود ندارد، به جز اینکه باید برای تنظیم frame توابع پنجره ای یا window function ها شامل شود.
نکته کلیدی این است که توابع پنجره تلفیقی از مفاهیم SQL موجود هستند که با هم ترکیب شده اند تا روشی متفاوت برای برش داده ها ایجاد کنند. استفاده از توابع پنجره ای مزایای مشخصی دارد و به شما امکان می دهد تا به سرعت داده های transform شده را در مقابل صرف کردن زمان برای ایجاد توابع سفارشی خود، خروجی بگیرید.
اگر قبلاً Postgres را روی رایانه خود نصب کرده اید، لطفاً این دستورات CREATE TABLE و INSERT را اجرا کنید و پرس و جوهای SQL را که قبلاً به اشتراک گذاشته ام را اجرا کنید.
DROP TABLE IF EXISTS orders; CREATE TABLE orders ( order_id serial primary key, order_date timestamp, user_id integer, amount numeric, num_product_items integer ); INSERT INTO orders VALUES (134,'2017-12-03 14:18:12',1,19.99,1); INSERT INTO orders VALUES (145,'2018-01-05 12:33:00',1,54.56,2); INSERT INTO orders VALUES (156,'2018-01-18 21:34:59',1,36.78,3); INSERT INTO orders VALUES (167,'2018-03-07 09:35:34',1,24.56,2); INSERT INTO orders VALUES (234,'2018-03-12 09:45:56',2,63.96,4); INSERT INTO orders VALUES (245,'2018-04-01 10:45:56',2,456.87,6); INSERT INTO orders VALUES (256,'2018-05-04 18:23:14',2,122.45,3); INSERT INTO orders VALUES (267,'2018-08-11 21:59:59',2,34.56,1); INSERT INTO orders VALUES (345,'2016-06-12 18:45:01',3,136.08,5); INSERT INTO orders VALUES (356,'2017-06-10 09:51:14',3,165.67,4); INSERT INTO orders VALUES (367,'2018-06-07 22:54:04',3,156.76,3); DROP TABLE IF EXISTS product_orders; CREATE TABLE product_orders ( user_id integer, order_date timestamp, order_id integer, product_name varchar, amount numeric ); INSERT INTO product_orders VALUES (1,'2017-11-12 00:11:45',123,'dvd_movie',19.99); INSERT INTO product_orders VALUES (1,'2017-11-12 00:11:45',123,'tennis_balls',12.99); INSERT INTO product_orders VALUES (1,'2017-11-12 00:11:45',123,'bestseller_book',28.56); INSERT INTO product_orders VALUES (1,'2017-11-12 00:11:45',123,'jacket_winter',112.99); INSERT INTO product_orders VALUES (1,'2017-11-12 00:11:45',123,'sweater_winter',49.99); INSERT INTO product_orders VALUES (2,'2018-03-12 09:45:56',234,'socks_spring',9.99); INSERT INTO product_orders VALUES (2,'2018-03-12 09:45:56',234,'hoodie_jacket',16.99); INSERT INTO product_orders VALUES (2,'2018-03-12 09:45:56',234,'blue_jeans',24.99); INSERT INTO product_orders VALUES (2,'2018-03-12 09:45:56',234,'baseball_hat',11.99); INSERT INTO product_orders VALUES (3,'2016-06-12 18:45:01',345,'pc_game_2',24.99); INSERT INTO product_orders VALUES (3,'2016-06-12 18:45:01',345,'pc_game_3',45.99); INSERT INTO product_orders VALUES (3,'2016-06-12 18:45:01',345,'console_controller',35.12); INSERT INTO product_orders VALUES (3,'2016-06-12 18:45:01',345,'surge_protector',12.99); INSERT INTO product_orders VALUES (3,'2016-06-12 18:45:01',345,'desk_lamp',16.99);
