ROW_NUMBER সহ PostgreSQL-এ পৃষ্ঠাঙ্কন ব্যাখ্যা করা হয়েছে

হোম » পাইথন » ROW_NUMBER সহ PostgreSQL-এ পৃষ্ঠাঙ্কন ব্যাখ্যা করা হয়েছে

PostgreSQL-এ বৃহৎ ফলাফল সেট পৃষ্ঠাঙ্কন করা প্রথম নজরে সহজ মনে হতে পারে, কিন্তু এটি দক্ষতার সাথে এবং সঠিকভাবে করা - বিশেষ করে যখন আপনার অর্ডারিং কলামে টাই থাকে - কেবল একটি কোয়েরিতে LIMIT/OFFSET চাপানোর চেয়ে আরও কিছু বেশি কিছু প্রয়োজন। উইন্ডো ফাংশন ROW_NUMBER() এই সমস্যা সমাধানের জন্য আপনার কাছে থাকা সবচেয়ে বহুমুখী সরঞ্জামগুলির মধ্যে একটি, একই সাথে র‍্যাঙ্কিং, টপ-এন কোয়েরি বা ডুপ্লিকেট সনাক্তকরণের মতো অতিরিক্ত বিশ্লেষণাত্মক ব্যবহারের ক্ষেত্রেও এটি আনলক করে।

এই নির্দেশিকাটি কীভাবে ব্যবহার করবেন তার গভীরে ডুব দেয় ROW_NUMBER() PostgreSQL-এ পৃষ্ঠাঙ্কনের জন্য, এটি আসলে কীভাবে কাজ করে, অন্যান্য র‍্যাঙ্কিং ফাংশন থেকে এটি কীভাবে আলাদা, পারফরম্যান্সের কী প্রভাব আশা করা যায় এবং অন্যান্য প্রধান ডাটাবেস ইঞ্জিনগুলি একই ধরণের প্যাটার্নের সাথে কীভাবে আচরণ করে। আমরা বাস্তব জগতের জটিল পরিস্থিতিগুলিও দেখব, যেমন কার্সার-ভিত্তিক পৃষ্ঠাকরণ, যখন সাজানোর কলামে ডুপ্লিকেট থাকে এবং কীভাবে একত্রিত করা যায় ROW_NUMBER() পরিষ্কার, উৎপাদন-প্রস্তুত SQL-এর জন্য CTE, জয়েন এবং সাবকোয়েরি সহ।

PostgreSQL ROW_NUMBER() উইন্ডো ফাংশন আসলে কী করে

এর মাঝখানে, ROW_NUMBER() একটি উইন্ডো ফাংশন যা ফলাফল সেটের প্রতিটি সারিতে একটি অনন্য, ক্রমিক পূর্ণসংখ্যা নির্ধারণ করে, যা 1 থেকে শুরু হয় এবং ফাঁক ছাড়াই 1 দ্বারা বৃদ্ধি পায়। আপনি উইন্ডোটি কীভাবে সংজ্ঞায়িত করেন তার উপর নির্ভর করে এই সংখ্যাটি সম্পূর্ণ ফলাফলে প্রয়োগ করা যেতে পারে অথবা প্রতিটি সারির গ্রুপের জন্য পুনরায় চালু করা যেতে পারে।

PostgreSQL-এর জেনেরিক সিনট্যাক্সটি এরকম দেখাচ্ছে:

ROW_NUMBER() OVER ( [PARTITION BY partition_expression] ORDER BY order_expression )

ভেতরে দুটি অংশ OVER সারি সংখ্যা কীভাবে বরাদ্দ করা হবে তা ক্লজ নিয়ন্ত্রণ করে: PARTITION BY (ঐচ্ছিক) ফলাফল সেটকে স্বাধীন গোষ্ঠীতে বিভক্ত করে, এবং ORDER BY (বাধ্যতামূলক) প্রতিটি পার্টিশনের মধ্যে ক্রম নির্ধারণ করে যা নির্ধারণ করে যে কোন সারি কোন সংখ্যা পাবে।

যদি তুমি বাদ দাও PARTITION BY, ফাংশনটি সম্পূর্ণ ফলাফল সেটকে একটি একক পার্টিশন হিসেবে বিবেচনা করে এবং নির্দিষ্ট ক্রম অনুসারে সমস্ত সারি সংখ্যা করে। একবার আপনি যোগ করলে PARTITION BY, প্রতিটি পার্টিশনে সারি সংখ্যাকরণ 1 থেকে পুনরায় শুরু হয়, যা প্রতি-বিভাগের র‍্যাঙ্কিং, প্রতি-গ্রুপের শীর্ষ-N-কোয়েরি এবং গোষ্ঠীবদ্ধ ডিডুপ্লিকেশনের জন্য অত্যন্ত কার্যকর।

র‍্যাঙ্কিং ফাংশনের বিপরীতে যেমন RANK() or DENSE_RANK(), ROW_NUMBER() বন্ধনকে সম্পূর্ণরূপে উপেক্ষা করে এবং কখনও কোনও সংখ্যার পুনরাবৃত্তি করে না, এমনকি যখন সারিগুলির বাছাই কলামে অভিন্ন মান থাকে। এটি এটিকে নির্ধারক পৃষ্ঠাঙ্কন এবং সুনির্দিষ্ট সারি কাটার জন্য আদর্শ করে তোলে।

অন্তর্দৃষ্টি তৈরির জন্য মৌলিক ROW_NUMBER() উদাহরণ

ব্যবহারের পূর্বে ROW_NUMBER() পৃষ্ঠাঙ্কনের জন্য, এটি সহজ উদাহরণগুলিতে এটিকে কার্যকরভাবে দেখতে সাহায্য করে, যেখানে লক্ষ্য হল কেবল নিয়ন্ত্রিত উপায়ে সারি সংখ্যা করা। একটা টেবিল কল্পনা করো। employees কলাম সহ id, name, department এবং salary.

বেতনের ক্রমহ্রাসমান ক্রম অনুসারে সাজানো একটি বিশ্বব্যাপী সারি নম্বর নির্ধারণ করতে, আপনি লিখতে পারেন:

SELECT
id,
name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

এই কোয়েরিটি সর্বোচ্চ থেকে সর্বনিম্ন বেতন অনুসারে সাজানো সমস্ত কর্মচারীকে ফেরত দেয়, যার সাথে row_num = 1 সবচেয়ে বেশি বেতন পাওয়া কর্মচারীর জন্য, 2 দ্বিতীয়টির জন্য, এবং আরও অনেক কিছু, কোনও ফাঁক ছাড়াই এবং কোনও পুনরাবৃত্তিমূলক মান ছাড়াই। বেতনের বন্ধন ইচ্ছামত ভেঙে দেওয়া হবে যদি না আপনি ORDER BY অতিরিক্ত কলাম সহ।

পরিবর্তে যদি আপনার প্রতিটি বিভাগের ভিতরে পুনরায় চালু করার জন্য সারি সংখ্যায়নের প্রয়োজন হয়, তাহলে আপনি একত্রিত করবেন PARTITION BY সঙ্গে ORDER BY:

SELECT
id,
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

এখানে, প্রতিটি বিভাগের নিজস্ব 1, 2, 3, … ক্রম থাকে, যাতে আপনি পরে ফিল্টার করে সহজেই "প্রতিটি বিভাগের সর্বোচ্চ উপার্জনকারী" খুঁজে পেতে পারেন row_num = 1 একটি বাইরের কোয়েরি বা CTE-তে। এই প্যাটার্নটি অনেক টপ-এন-পার-গ্রুপ কোয়েরির মেরুদণ্ড।

ফিল্টারিং লজিক থেকে নম্বরিং লজিককে পরিষ্কারভাবে আলাদা করার জন্য, উইন্ডো ফাংশনটিকে একটি CTE বা সাবকোয়েরিতে মোড়ানো এবং তারপর বাইরের SELECT-এ জেনারেট করা সারি সংখ্যাগুলি ফিল্টার করা সাধারণ। এটি বিশেষভাবে গুরুত্বপূর্ণ কারণ উইন্ডো ফাংশনগুলি সরাসরি ব্যবহার করা যায় না WHERE একই SELECT এর ধারা যা তাদের সংজ্ঞায়িত করে।

ক্লাসিক পৃষ্ঠা-ভিত্তিক পৃষ্ঠাকরণের জন্য ROW_NUMBER() ব্যবহার করা হচ্ছে

পৃষ্ঠাঙ্কন করার সবচেয়ে সহজ উপায় ROW_NUMBER() PostgreSQL-এ প্রতিটি সারির জন্য সারি সূচক গণনা করা হয়, এবং তারপর আপনার পছন্দসই পৃষ্ঠার সাথে সঙ্গতিপূর্ণ একটি সংখ্যাসূচক পরিসর জিজ্ঞাসা করা হয়। এটি কখনও কখনও বিকল্প হিসেবে ব্যবহৃত হয় OFFSET/LIMIT, এবং SQL সার্ভার বা Oracle থেকে পৃষ্ঠাঙ্কন কোড পোর্ট করার সময়ও সুন্দরভাবে কাজ করে।

ধরুন আপনি আকার সহ ফলাফলের একটি পৃষ্ঠা চান @PageSize এবং পৃষ্ঠা নম্বর @PageNumber (০-ভিত্তিক সূচক)। সাধারণ T-SQL কোয়েরিটি দেখতে এরকম:

SELECT PK_ID, Truco, Descripcion
FROM (
SELECT
PK_ID,
Truco,
Descripcion,
ROW_NUMBER() OVER (ORDER BY Truco, PK_ID) AS RowNumber
FROM TrucosInformaticos
) AS Trucos
WHERE RowNumber BETWEEN (@PageSize * @PageNumber) + 1
AND @PageSize * (@PageNumber + 1);

একই যুক্তি সরাসরি PostgreSQL-এ অনুবাদ করে - আপনি কেবল প্যারামিটার সিনট্যাক্সটি অভিযোজিত করতে পারেন এবং, যদি আপনি চান, এটি একটি সঞ্চিত পদ্ধতির পরিবর্তে একটি ফাংশনে মোড়ানো। সারমর্ম হল: গণনা ROW_NUMBER() একবার, তারপর আপনার পৃষ্ঠার সীমানার সাথে সামঞ্জস্যপূর্ণ একটি সাংখ্যিক ব্যবধানে সারিগুলি কেটে ফেলুন।

উদাহরণস্বরূপ, PostgreSQL-এ, একটি স্থির পৃষ্ঠার জন্য আপনি লিখতে পারেন:

WITH ranked_posts AS (
SELECT
id,
title,
ROW_NUMBER() OVER (ORDER BY title, id) AS row_num
FROM posts
)
SELECT id, title
FROM ranked_posts
WHERE row_num BETWEEN 11 AND 20;

এটি দ্বারা সংজ্ঞায়িত ক্রম অনুসারে ১১-২০ সারি প্রদান করে ORDER BY title, id, কার্যকরভাবে আপনাকে পৃষ্ঠার আকার ১০ সহ দ্বিতীয় পৃষ্ঠাটি প্রদান করে। প্লেইন OFFSET এর তুলনায় বড় সুবিধা হলো, সারি সংখ্যাগুলো স্পষ্ট এবং অতিরিক্ত লজিকের সাথে একত্রিত করা যেতে পারে, উদাহরণস্বরূপ, আবার যোগদান, ফিল্টারিং, অথবা আরও বিশ্লেষণ করা।

যখন সাজানোর কলামে ডুপ্লিকেট থাকে তখন কার্সার-স্টাইলের পৃষ্ঠাঙ্কন

অফসেট-ভিত্তিক পৃষ্ঠাঙ্কন সম্পর্কে যুক্তি করা সহজ, তবে এটি বড় টেবিলগুলিতে কর্মক্ষমতা সংক্রান্ত সমস্যা তৈরি করতে পারে এবং অনুরোধগুলির মধ্যে অন্তর্নিহিত ডেটা পরিবর্তন হলে ভঙ্গুর হয়ে ওঠে। কার্সার-ভিত্তিক পৃষ্ঠাঙ্কন (যাকে কীসেট পৃষ্ঠাঙ্কনও বলা হয়) এর লক্ষ্য হল একটি পরম অফসেটের পরিবর্তে শেষ দেখা আইটেমটিকে অ্যাঙ্কর হিসাবে ব্যবহার করে এটি সমাধান করা।

যখন আপনার সাজানো কলামে ডুপ্লিকেট মান থাকে তখন জিনিসগুলি জটিল হয়ে ওঠে। একটি স্কিমা বিবেচনা করুন যার সাথে posts এবং comments:

CREATE TABLE posts(
id uuid PRIMARY KEY,
title varchar(255) NOT NULL
);

CREATE TABLE comments(
id uuid PRIMARY KEY,
post_id uuid NOT NULL REFERENCES posts
);

কল্পনা করুন আপনি প্রথমে এমন একটি কোয়েরি তৈরি করেছেন যা পোস্টগুলিকে মন্তব্যের সংখ্যা অনুসারে অবরোহী ক্রমে সাজায়:

SELECT p.*, COUNT(c.id) AS comments_count
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count DESC;

কার্সার-ভিত্তিক পৃষ্ঠাঙ্কনের জন্য, কেউ একটি নির্দিষ্ট পর্যন্ত নির্বাচন করতে প্রলুব্ধ হতে পারে comments_count থ্রেশহোল্ড এবং তারপর একটি প্রয়োগ করুন LIMIT:

WITH cte AS (
SELECT p.*, COUNT(c.id) AS comments_count
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count DESC
)
SELECT *
FROM cte
WHERE comments_count <= (
SELECT comments_count FROM cte WHERE id = '00000000-0000-0000-0000-000000000003'
)
LIMIT 3;

সমস্যাটি তখন দেখা দেয় যখন একাধিক পোস্ট একই রকম পোস্ট শেয়ার করে। comments_count. যদি দুটি পোস্টের গণনা 2 হয় এবং আপনার কার্সার তাদের মধ্যে একটির দিকে নির্দেশ করে, তাহলে <= দ্বিতীয় পৃষ্ঠায় বাঁধা সারি দুটি অন্তর্ভুক্ত করে, যখন ব্যবহার করা হয় < একই গণনা সহ সমস্ত সারি এড়িয়ে যায় এবং অনেক দূরে লাফিয়ে যায়, যার ফলে আপনার প্রত্যাশিত কিছু পোস্ট মিস হয়ে যায়।

কার্সার পেজিনেশনে একটি অ-অনন্য কী দ্বারা ক্রমবিন্যাসের এটি একটি ক্লাসিক লক্ষণ - যদি আপনার কার্সার শুধুমাত্র অ-অনন্য মান এনকোড করে তবে ডাটাবেস নির্দিষ্টভাবে ডেটাসেটটিকে "মাঝখানে" স্লাইস করতে পারে না। একটি কার্সার নিরাপদে সংজ্ঞায়িত করার জন্য আপনার একটি অনন্য এবং স্থিতিশীল ক্রম প্রয়োজন।

একটি সমাধান হল প্রাথমিক কী-এর সাথে মন্তব্য গণনা একত্রিত করে একটি সিন্থেটিক, অনন্য সাজানোর কী তৈরি করা, উদাহরণস্বরূপ কনক্যাটেনেশনের মাধ্যমে:

WITH cte AS (
SELECT
p.,
COUNT(c.id) AS comments_count,
CONCAT(COUNT(c.id), ':', p.id) AS comments_count_id
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count_id DESC
)
SELECT *
FROM cte
WHERE comments_count_id < (
SELECT comments_count_id
FROM cte
WHERE id = '00000000-0000-0000-0000-000000000003'
)
LIMIT 3;

একটি যৌগিক কী তৈরি করে যেমন '2:00000000-...-0003', আপনি অর্ডারিংটিকে সম্পূর্ণরূপে অনন্য করে তোলেন এবং নিরাপদে বলতে পারেন "আমাকে সারি দাও" comments_count_id "নোঙরের চেয়ে কম", কোনও অস্পষ্টতা ছাড়াই। এটি সর্বদা একই ধারণা, যার মধ্যে রয়েছে id আপনার ORDER BY টাই-ব্রেকার হিসেবে।

বাস্তবে, আপনাকে একটি স্ট্রিং-এর সাথে সংযুক্ত করতে হবে না; আপনি কেবল একাধিক কলাম ব্যবহার করতে পারেন ORDER BY এবং অ্যাপ্লিকেশন লেয়ারে আপনার কার্সার অবজেক্টে সেগুলিকে এনকোড করুন। ডাটাবেসের দিক থেকে গুরুত্বপূর্ণ অংশ হল মোট ক্রমটি অনন্য এবং কলগুলির মধ্যে পুনরুৎপাদনযোগ্য।

ROW_NUMBER() বনাম LIMIT এবং OFFSET সহ পৃষ্ঠাকরণ

PostgreSQL ক্লাসিক সমর্থন করে LIMIT এবং OFFSET সিনট্যাক্স তৈরি করা হয়েছে, এবং অনেক ছোট থেকে মাঝারি ফলাফল সেটের জন্য, এটি ব্যবহার করা পুরোপুরি ঠিক। আপনি কেবল কতগুলি সারি এড়িয়ে যেতে হবে এবং কতগুলি ফেরত দিতে হবে তা নির্দিষ্ট করুন।

তবে, OFFSET-ভিত্তিক পৃষ্ঠাঙ্কনের দুটি বড় ত্রুটি রয়েছে: কর্মক্ষমতা এবং স্থিতিশীলতা। As OFFSET বৃদ্ধি পায়, PostgreSQL-কে ফলাফল ফেরত দেওয়ার আগে পূর্ববর্তী সমস্ত সারি স্ক্যান করে বাতিল করতে হয়, যা বড় ডেটাসেটে ব্যয়বহুল হয়ে পড়ে। এবং যদি অনুরোধের মধ্যে ডেটা পরিবর্তন হয়, তাহলে পৃষ্ঠাগুলি "স্থানান্তর" করতে পারে এবং ডুপ্লিকেট দেখাতে পারে অথবা সারি এড়িয়ে যেতে পারে।

ব্যবহার ROW_NUMBER() পৃষ্ঠাঙ্কন আপনাকে সারি সূচকটি একবার বাস্তবায়িত করার এবং তারপর এটি পরিষ্কারভাবে কাটার একটি উপায় দেয়:

WITH numbered_products AS (
SELECT
product_id,
product_name,
price,
ROW_NUMBER() OVER (ORDER BY product_name) AS row_number
FROM products
)
SELECT product_id, product_name, price
FROM numbered_products
WHERE row_number BETWEEN 11 AND 20
ORDER BY product_name;

এই প্যাটার্নটি স্বজ্ঞাতভাবে পড়ে: এটি প্রথমে প্রতিটি পণ্যকে সাজানো তালিকায় তার অবস্থান নির্ধারণ করে, তারপর বাইরের কোয়েরিটি ১১ থেকে ২০ নম্বর সারি আনে। যতক্ষণ পর্যন্ত পৃষ্ঠাটি সম্পাদন এবং ব্যবহারের মধ্যে অন্তর্নিহিত ডেটা পরিবর্তিত না হয়, ততক্ষণ পর্যন্ত আপনি লজিক্যাল ক্রমের একটি স্থিতিশীল অংশ পাবেন।

যে বলেন, ROW_NUMBER()-ভিত্তিক পৃষ্ঠাঙ্কনও পারফরম্যান্সের জন্য একটি ভালো সমাধান নয়। ডাটাবেসকে এখনও সমস্ত যোগ্য সারি জুড়ে উইন্ডো ফাংশন মূল্যায়ন করতে হবে যাতে সংখ্যা নির্ধারণ করা যায়, তাই অত্যন্ত বড় টেবিলের জন্য, এটি একটি বৃহৎ OFFSET-এর মতোই ব্যয়বহুল হতে পারে। অতিরিক্ত ফিল্টারিংয়ের সাথে মিলিত হলে অথবা যখন আপনি বিশুদ্ধ পৃষ্ঠাঙ্কনের বাইরে নির্ধারক, সারি-সংখ্যা-ভিত্তিক যুক্তি চান তখন এটি উজ্জ্বল হয়।

ডাটাবেস ইঞ্জিন জুড়ে উইন্ডো-ফাংশন পৃষ্ঠাকরণ কীভাবে আচরণ করে

উইন্ডো ফাংশন যেমন ROW_NUMBER() স্ট্যান্ডার্ডাইজড SQL বৈশিষ্ট্য, কিন্তু প্রতিটি ডাটাবেস ইঞ্জিন পৃষ্ঠাঙ্কনের মতো প্যাটার্নের জন্য এগুলিকে আলাদাভাবে অপ্টিমাইজ করে। কিছু পণ্য "উইন্ডো ফাংশন সহ টপ-এন" কোয়েরিগুলি সনাক্ত করতে পারে এবং ইনডেক্স অ্যাক্সেস ব্যবহার করে তাড়াতাড়ি স্ক্যান করা বন্ধ করে দেয়; অন্যরা প্রতিবার চুপচাপ সম্পূর্ণ সেটটি প্রক্রিয়া করবে।

এই সাধারণ top-N / পৃষ্ঠাঙ্কন-শৈলীর কোয়েরিটি ব্যবহার করে বিবেচনা করুন ROW_NUMBER একটি ক্রমযুক্ত সূচকের উপরে একটি sales টেবিল:

SELECT *
FROM (
SELECT
sales.*,
ROW_NUMBER() OVER (ORDER BY sale_date DESC, sale_id DESC) AS rn
FROM sales
) AS tmp
WHERE rn BETWEEN 11 AND 20
ORDER BY sale_date DESC, sale_id DESC;

এখানে, ROW_NUMBER সংজ্ঞায়িত ক্রম অনুসারে সারি গণনা করে OVER ধারা, এবং বাইরের WHERE ফলাফলকে একটি নির্দিষ্ট পৃষ্ঠায় সীমাবদ্ধ করে (সারি ১১-২০)। এটি যুক্তিসঙ্গতভাবে একটি অফসেটের সাথে মিলিত একটি top-N কোয়েরির সমতুল্য।

উদাহরণস্বরূপ, ওরাকল স্টপ কন্ডিশন চিনতে সক্ষম এবং একটি সূচক ব্যবহার করতে পারে sale_date এবং sale_id "টপ-এন ইন পাইপলাইন" আচরণ বাস্তবায়ন করতে। এর বাস্তবায়ন পরিকল্পনা দেখাতে পারে WINDOW NOSORT STOPKEY, যা নির্দেশ করে যে ইঞ্জিনটির অতিরিক্ত সাজানোর প্রয়োজন নেই এবং অনুরোধ করা উইন্ডোর উপরের সীমানা তৈরি করার সাথে সাথে এটি বন্ধ হয়ে যাবে।

এই ধরণের অপ্টিমাইজেশনের জন্য সমর্থন সর্বজনীন নয়। PostgreSQL এর কিছু সংস্করণ এবং MySQL, MariaDB এবং Db2 এর মতো অন্যান্য ইঞ্জিনগুলি এই উইন্ডো-ফাংশন-ভিত্তিক প্যাটার্নগুলির শুরুতে সূচক স্ক্যান বন্ধ করে না, যার অর্থ তারা এখনও অনুরোধকৃত পৃষ্ঠাটি সরবরাহ করার জন্য কঠোরভাবে প্রয়োজনের চেয়ে বেশি সারি প্রক্রিয়া করে।

সাম্প্রতিক PostgreSQL সংস্করণগুলিতে (১৫+ এবং তার পরবর্তী) উইন্ডো-ফাংশন কর্মক্ষমতা এবং অপ্টিমাইজেশন উন্নত হয়েছে, তবে আচরণ এখনও প্রধান সংস্করণগুলির মধ্যে ভিন্ন হতে পারে। সর্বদা বাস্তবায়ন পরিকল্পনাগুলি পরীক্ষা করুন EXPLAIN (ANALYZE) আপনার ডাটাবেস ইনডেক্স ব্যবহার করে তাড়াতাড়ি থামাতে সক্ষম কিনা তা দেখার জন্য, অথবা এটি সম্পূর্ণ ফলাফল সেট স্ক্যান এবং সাজানোর কাজ করছে কিনা তা দেখার জন্য।

ROW_NUMBER() কে DISTINCT, CTE এবং সাবকোয়েরির সাথে একত্রিত করা

ব্যবহার করার সময় একটি সাধারণ গটচা ROW_NUMBER() এর পাশাপাশি DISTINCT হল যে উইন্ডো ফাংশনটি ডুপ্লিকেট এলিমিনেশন ধাপের আগে মূল্যায়ন করা হয়। এর ফলে বিভ্রান্তিকর ফলাফল দেখা দিতে পারে যেখানে দৃশ্যত সদৃশ মানগুলি এখনও স্বতন্ত্র সারি সংখ্যা পায়।

উদাহরণস্বরূপ, যদি আপনি একটি থেকে পৃথক মূল্য গণনা করার চেষ্টা করেন products একটি একক কোয়েরি সহ টেবিল যেমন:

SELECT DISTINCT price,
ROW_NUMBER() OVER (ORDER BY price) AS rn
FROM products;

একই রকম একাধিক সারি দেখে আপনি অবাক হতে পারেন price কিন্তু ভিন্ন rn মান, কারণ উইন্ডো ফাংশনটি আগে সমস্ত সারিতে চলত DISTINCT চূড়ান্ত প্রক্ষেপণ থেকে ডুপ্লিকেটগুলি সরানো হয়েছে।

এটি পরিচালনা করার শক্তিশালী উপায় হল প্রথমে স্বতন্ত্র মানগুলি বাস্তবায়িত করা (একটি CTE বা সাবকোয়েরির মাধ্যমে), তারপর প্রয়োগ করা ROW_NUMBER() তার উপরে:

WITH prices AS (
SELECT DISTINCT price
FROM products
)
SELECT price,
ROW_NUMBER() OVER (ORDER BY price) AS rn
FROM prices;

বিকল্পভাবে, আপনি সরাসরি একটি সাবকোয়েরি ব্যবহার করতে পারেন FROM ধারা, একই প্রভাব অর্জন করে। গুরুত্বপূর্ণ ধারণা হল স্পষ্টভাবে সিদ্ধান্ত নেওয়া যে কোন ফলাফল সেটটি "উইন্ডো" যেখানে ROW_NUMBER() কাজ করা উচিত, এবং যদি আপনার অনন্যতার প্রয়োজন হয়, তাহলে প্রথমে সেই সেটটি তৈরি করুন।

"তৃতীয় সর্বোচ্চ মূল্যে পণ্যটি পান" অথবা "সারি সংখ্যা দিয়ে স্বতন্ত্র মূল্য গণনা করুন এবং তারপর একটি নির্দিষ্ট মূল্য বেছে নিন" এর মতো পৃষ্ঠাঙ্কন কাজের জন্য এই প্যাটার্নটি অত্যন্ত কার্যকর। আপনি প্রথমে অনন্য সাজানো দাম পেতে পারেন ROW_NUMBER() এবং তারপর আপনার পছন্দের নির্দিষ্ট র‍্যাঙ্কে যোগদান করুন বা ফিল্টার করুন।

র‍্যাঙ্কিং, টপ-এন এবং ডুপ্লিকেট অপসারণের জন্য ROW_NUMBER()

যদিও আমাদের লক্ষ্য পৃষ্ঠাঙ্কন, তবে এটি উল্লেখ না করা অপচয় হবে ROW_NUMBER() ডেটা র‍্যাঙ্কিং এবং ডিডুপ্লিকেট করার জন্য এটি একটি দুর্দান্ত হাতিয়ার। পৃষ্ঠাঙ্কনের জন্য আপনি যে একই ধরণের প্যাটার্ন ব্যবহার করেন তার অনেকগুলি র‍্যাঙ্কিং লজিকের দ্বিগুণ।

প্রতিটি বিভাগে শীর্ষ N রেকর্ড পেতে, আপনি বিভাগ অনুসারে ভাগ করতে পারেন এবং মূল্য হ্রাসের মতো একটি মেট্রিক দ্বারা সারিগুলিকে র‍্যাঙ্ক করতে পারেন:

WITH ranked_products AS (
SELECT
product_id,
product_name,
category_id,
price,
ROW_NUMBER() OVER (
PARTITION BY category_id
ORDER BY price DESC
) AS rank
FROM products
)
SELECT product_id, product_name, category_id, price
FROM ranked_products
WHERE rank <= 2;

এটি প্রতিটি বিভাগের দুটি সবচেয়ে ব্যয়বহুল পণ্য ফিরিয়ে দেয়। তারপর আপনি একটিতে যোগ দিতে পারেন categories টেবিল ব্যবহার করে USING (category_id) অথবা মানুষের পঠনযোগ্য নাম প্রদর্শনের জন্য একটি স্পষ্ট সংযোগ।

ডুপ্লিকেট অপসারণের জন্য, ROW_NUMBER() সঙ্গে প্রায়শই সংমিশ্রণে ব্যবহৃত হয় PARTITION BY প্রতিটি গ্রুপের প্রথম ঘটনা ছাড়া বাকি সকলকে সদৃশ হিসেবে চিহ্নিত করতে। একটি সহজ টেবিল বিবেচনা করুন:

CREATE TABLE items (
id INT,
name VARCHAR
);

ধরুন আপনি বেশ কয়েকটি ডুপ্লিকেট নাম সন্নিবেশ করান এবং প্রতিটি নামের জন্য সর্বনিম্ন আইডি রেখে অতিরিক্ত কপিগুলি সরাতে চান। আপনি প্রথমে ডুপ্লিকেট সনাক্ত করতে পারেন:

SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_number
FROM items
ORDER BY id;

যেকোনো সারি row_number > 1 এটি একটি ডুপ্লিকেট। তারপর আপনি একটি CTE এবং একটি ব্যবহার করতে পারেন DELETE তাদের অপসারণের বিবৃতি:

DELETE FROM items
WHERE id IN (
WITH ranked_items AS (
SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_number
FROM items
)
SELECT id
FROM ranked_items
WHERE row_number > 1
);

এটি চালানোর পরে, থেকে নির্বাচন করা items শুধুমাত্র স্বতন্ত্র নাম দেখাবে, প্রতি মানের জন্য একটি প্রতিনিধিত্বমূলক সারি রাখা হবে। এটি একটি পরিষ্কার, ঘোষণামূলক উপায় যার মাধ্যমে আপনি কোন সারিটি সংরক্ষণ করবেন তা নিয়ন্ত্রণ করার সময় ডি-ডুপ্লিকেট করা যায়।

পৃষ্ঠাঙ্কন পরিস্থিতিতে ROW_NUMBER() বনাম RANK() বনাম DENSE_RANK()

PostgreSQL বেশ কয়েকটি র‍্যাঙ্কিং উইন্ডো ফাংশন অফার করে: ROW_NUMBER(), RANK(), এবং DENSE_RANK(). যদিও সকলেই ক্রমানুসারে সংখ্যা নির্ধারণ করে, সাজানো কলামে টাই থাকলে তারা ভিন্নভাবে আচরণ করে।

গুরুত্বপূর্ণ পার্থক্যগুলি হল:

• ROW_NUMBER() প্রতিটি সারিতে সর্বদা একটি অনন্য পূর্ণসংখ্যা নির্ধারণ করে, এমনকি যখন টাই হয়; সংখ্যাগুলি কঠোরভাবে ক্রমানুসারে (1, 2, 3, 4, …)।
• RANK() অভিন্ন মানগুলিকে একই র‍্যাঙ্ক দেয় কিন্তু টাইয়ের পরে সংখ্যাগুলি এড়িয়ে যায় (উদাহরণস্বরূপ, 1, 2, 2, 4 - র‍্যাঙ্ক 3 অনুপস্থিত)।
• DENSE_RANK() টাইয়ের জন্যও একই র‍্যাঙ্ক দেয় কিন্তু সংখ্যাগুলি এড়িয়ে যায় না (1, 2, 2, 3)।

পৃষ্ঠাঙ্কনের জন্য, ROW_NUMBER() সাধারণত সবচেয়ে নিরাপদ পছন্দ কারণ এটি প্রতি সংখ্যার জন্য ঠিক একটি সারি নিশ্চিত করে, যা স্বাভাবিকভাবেই পৃষ্ঠার রেঞ্জ যেমন 1-10, 11-20, ইত্যাদির সাথে ম্যাপ করে। আপনি যদি ব্যবহার RANK() or DENSE_RANK(), তাহলে টাইয়ের কারণে আপনার এমন পৃষ্ঠাগুলি আসতে পারে যেখানে প্রত্যাশার চেয়ে কম বা বেশি সারি থাকবে।

অন্যদিকে, প্রতিযোগিতার ফলাফলের মতো ব্যবহারের ক্ষেত্রে রিপোর্ট করার জন্য যেখানে বাঁধা মানগুলি একই অবস্থান ভাগ করে নিতে হবে, RANK() or DENSE_RANK() উদ্দেশ্যকে আরও ভালোভাবে উপস্থাপন করে ROW_NUMBER(). আপনি এখনও সেই ফলাফলগুলিকে পৃষ্ঠাঙ্কিত করতে পারেন, তবে আপনাকে সচেতন থাকতে হবে যে "অবস্থান" আর সরাসরি ভৌত ​​সারি সংখ্যার সাথে মিলে না।

ব্যবহারিক টিপস, অসুবিধা এবং কর্মক্ষমতা বিবেচনা

ব্যবহার করার সময় ROW_NUMBER() পৃষ্ঠাঙ্কন এবং বিশ্লেষণের জন্য, কিছু ভালো অভ্যাস আপনাকে সূক্ষ্ম ত্রুটি এবং অপ্রয়োজনীয় কর্মক্ষমতা মাথাব্যথা থেকে রক্ষা করবে। তাদের বেশিরভাগই স্পষ্ট এবং নিয়তিবাদী।

সর্বদা একটি স্পষ্ট সংজ্ঞা দিন ORDER BY ভিতরে OVER() দফা। এটি ছাড়া, PostgreSQL উইন্ডো ফাংশনের উদ্দেশ্যে যেকোনো ক্রমে সারি ফেরত দিতে পারে, এবং অন্তর্নিহিত ডেটা একই রকম হলেও আপনার সারি সংখ্যাগুলি এক্সিকিউশনের মধ্যে পরিবর্তিত হতে পারে।

যখনই সম্ভব, শেষে একটি অনন্য কলাম (প্রায়শই প্রাথমিক কী) অন্তর্ভুক্ত করুন ORDER BY তালিকা। এটি ক্রমকে একটি সম্পূর্ণ ক্রমে পরিণত করে এবং টাইয়ের সাথে অস্পষ্টতা এড়ায়, যা কার্সার-ভিত্তিক পৃষ্ঠাঙ্কন এবং পূর্বাভাসযোগ্য শীর্ষ-N ফলাফলের জন্য অত্যন্ত গুরুত্বপূর্ণ।

সরাসরি উইন্ডো ফাংশন ব্যবহার করার আশা করবেন না WHERE একই SELECT এর ধারা। পরিবর্তে, এগুলিকে একটি CTE বা সাবকোয়েরিতে মুড়িয়ে বাইরের কোয়েরিতে প্রাপ্ত কলামে ফিল্টার করুন। এই প্যাটার্নটি সহজ, পুনঃব্যবহারযোগ্য এবং আপনার SQL পঠনযোগ্য রাখে।

পৃষ্ঠাঙ্কন করার সময়, যখনই ব্যবহারিক হবে, সূচীবদ্ধ কলামে ক্রম নির্ধারণ করতে পছন্দ করুন। উভয় ORDER BY এবং ROW_NUMBER() বাছাই বা সূচক স্ক্যানের উপর নির্ভর করুন; সঠিক সূচককরণ বড় টেবিলে মিলিসেকেন্ড এবং সেকেন্ডের মধ্যে পার্থক্য করতে পারে।

একত্রিত করার সময় সাবধান থাকুন PARTITION BY কিছু ইঞ্জিনে পৃষ্ঠাঙ্কন সহ। কিছু ডাটাবেস পণ্য এবং সংস্করণে, ভিউ বা সাবকোয়েরির ভিতরে পার্টিশন করা উইন্ডো ফাংশন ব্যবহার করলে অন্যথায় উপলব্ধ স্টপকি অপ্টিমাইজেশনগুলি অক্ষম করা যেতে পারে, যার ফলে ইঞ্জিনটি প্রয়োজনের চেয়ে বেশি সারি প্রক্রিয়া করতে পারে। বাস্তবসম্মত ডেটা এবং রিডিং কোয়েরি প্ল্যানগুলির সাথে পরীক্ষা করা অপরিহার্য।

খুব বড় ডেটাসেট এবং অত্যন্ত গতিশীল ডেটার জন্য, একত্রিত করার কথা বিবেচনা করুন ROW_NUMBER() ব্যবহারকারী-মুখী শেষ বিন্দুগুলির জন্য কার্সার-ভিত্তিক কীসেট পৃষ্ঠাঙ্কন সহ "অ্যাডমিন-স্টাইল" ভিউগুলির জন্য পৃষ্ঠাঙ্কন। এইভাবে আপনি আপনার API বা UI-তে নির্ধারক টুলিং কোয়েরি এবং দক্ষ, স্থিতিশীল নেভিগেশন উভয়ই পাবেন।

সামগ্রিকভাবে দেখা গেলে, ROW_NUMBER() এটি কেবল একটি পৃষ্ঠাঙ্কন কৌশল নয়: এটি একটি শক্তিশালী বিশ্লেষণাত্মক বিল্ডিং ব্লক যা আপনাকে একই অন্তর্নিহিত যুক্তি ব্যবহার করে PostgreSQL এবং অন্যান্য প্রধান SQL ইঞ্জিনগুলিতে ফলাফল সেট সংখ্যা, র‍্যাঙ্ক, স্লাইস এবং পরিষ্কার করতে দেয়। এটি আয়ত্ত করা - এর সাথে একটি দৃঢ় বোধগম্যতা OVER(), PARTITION BY, এবং এর পার্থক্যগুলি RANK() এবং DENSE_RANK() - বাস্তব-বিশ্বের অ্যাপ্লিকেশনগুলিতে দক্ষ পৃষ্ঠাঙ্কন, শীর্ষ-N কোয়েরি এবং শক্তিশালী ডিডুপ্লিকেশনের জন্য আপনাকে একটি অত্যন্ত নমনীয় টুলকিট দেয়।