Window function — ROW_NUMBER, RANK, LAG আর running total

১ · সারি না হারিয়ে হিসাব — window function-এর মূল ধারণা

পর্ব ১৮-এ (GROUP BY আর HAVING) দেখেছেন, GROUP BY সারিগুলোকে দলে ভাগ করে প্রতিটি দল থেকে একটি ফল-সারি দেয়। তাতে একটি দাম দিতে হয়: দলের ভেতরের সারিগুলো হারিয়ে যায়। এখন একটি সাধারণ প্রশ্ন ভাবুন — “প্রতিজন শিক্ষার্থীর নাম, তাঁর CGPA, আর তাঁর বিভাগের গড় CGPA পাশাপাশি দেখাও।” এখানে নামও চাই (প্রতিটি সারি), গড়ও চাই (দলের হিসাব)। GROUP BY দিলে নাম হারায়; না দিলে গড় পাওয়া যায় না।

এই সমস্যার সমাধান window function. এটি একটি aggregate বা বিশেষ ফাংশন, যা প্রতিটি সারির জন্য আলাদা করে হিসাব করে, কিন্তু হিসাবের সময় সেই সারির আশেপাশের কিছু সারি — তার “জানালা” (window) — দেখে। ফল-সারির সংখ্যা কমে না। চেনার উপায় একটিই: ফাংশনের পরে OVER শব্দ।

নিচের ডায়াগ্রামে db-uni-র আটজন শিক্ষার্থীর ওপর দুই পথ পাশাপাশি দেখুন। (students টেবিলের কলাম: student_id, name, dept_id — 1 = CSE, 2 = EEE, 3 = BBA, batch আর cgpa. Fahim Karim-এর cgpa NULL, Habib Ullah-র dept_id NULL.)

 name           │ dept │ cgpa
 Arif Hossain   │ 1    │ 3.75
 Bristy Saha    │ 1    │ 3.40
 Chayon Roy     │ 2    │ 2.95
 Dilruba Khatun │ 1    │ 3.90
 Emon Mia       │ 3    │ 3.10
 Fahim Karim    │ 2    │ NULL
 Gulshan Ara    │ 1    │ 3.55
 Habib Ullah    │ NULL │ 2.80

SELECT dept_id, AVG(cgpa) FROM students
GROUP BY dept_id;

 dept │ AVG(cgpa)
 NULL │ 2.8
 1    │ 3.65
 2    │ 2.95
 3    │ 3.1

নামগুলো কোথায়? দলে গুটিয়ে গেছে —
এক সারিতে চারটি নাম বসানো যায় না

[dept NULL]  Habib 2.80
[dept 1   ]  Dilruba 3.90 │ Arif 3.75 │ Gulshan 3.55 │ Bristy 3.40
[dept 2   ]  Chayon 2.95 │ Fahim NULL
[dept 3   ]  Emon 3.10

প্রতিটি ভাগকে বলে partition —
সারিগুলো নিজের জায়গায় থাকে

 name           │ dept │ cgpa │ dept_avg
 Habib Ullah    │ NULL │ 2.80 │ 2.8
 Dilruba Khatun │ 1    │ 3.90 │ 3.65
 Arif Hossain   │ 1    │ 3.75 │ 3.65
 Gulshan Ara    │ 1    │ 3.55 │ 3.65
 Bristy Saha    │ 1    │ 3.40 │ 3.65
 Chayon Roy     │ 2    │ 2.95 │ 2.95
 Fahim Karim    │ 2    │ NULL │ 2.95
 Emon Mia       │ 3    │ 3.10 │ 3.1

 name           │ cgpa │ dept_avg │ diff
 Dilruba Khatun │ 3.90 │ 3.65     │ +0.25
 Arif Hossain   │ 3.75 │ 3.65     │ +0.10
 Gulshan Ara    │ 3.55 │ 3.65     │ −0.10
 Bristy Saha    │ 3.40 │ 3.65     │ −0.25
 Fahim Karim    │ NULL │ 2.95     │ NULL

GROUP BY দিয়ে এটি পেতে লাগত
আলাদা কোয়েরি আর একটি JOIN

এবার নিজে চালান — প্রথমে GROUP BY, তারপর একই হিসাব window function দিয়ে:

-- পথ ১: GROUP BY — প্রতি বিভাগে একটি সারি
SELECT dept_id, ROUND(AVG(cgpa), 2) AS dept_avg
FROM students GROUP BY dept_id;

-- পথ ২: window — প্রতিটি শিক্ষার্থী, পাশে বিভাগের গড়
SELECT name, dept_id, cgpa,
       ROUND(AVG(cgpa) OVER (PARTITION BY dept_id), 2)        AS dept_avg,
       ROUND(cgpa - AVG(cgpa) OVER (PARTITION BY dept_id), 2) AS diff
FROM students
ORDER BY dept_id, cgpa DESC;

২ · OVER-এর তিনটি অংশ — OVER (), PARTITION BY আর ORDER BY

প্রতিটি window function-এর গঠন একই: ফাংশন(…) OVER ( [PARTITION BY …] [ORDER BY …] [frame] ). বন্ধনীর ভেতরের তিনটি অংশই ঐচ্ছিক, আর প্রতিটি একটি আলাদা প্রশ্নের উত্তর দেয়:

সবচেয়ে সরল রূপ OVER () — খালি বন্ধনী। তখন পুরো ফল একটি জানালা, তাই COUNT(*) OVER () প্রতিটি সারিতে মোট সারির সংখ্যা বসায়। পরের কোয়েরিতে তিনটি রূপ একসাথে: সব অর্ডারের সংখ্যা, গ্রাহকের নিজের অর্ডারের সংখ্যা, আর গ্রাহকের কততম অর্ডার। db-shop-এর orders টেবিলে দশটি অর্ডার: order_id, customer_id, order_date (তারিখ, 'YYYY-MM-DD' লেখা হিসেবে) আর status.

SELECT order_id, customer_id, order_date,
       COUNT(*)     OVER ()                         AS all_orders,
       COUNT(*)     OVER (PARTITION BY customer_id) AS cust_orders,
       ROW_NUMBER() OVER (PARTITION BY customer_id
                          ORDER BY order_date)      AS nth
FROM orders
ORDER BY customer_id, order_date;

৩ · Ranking — ROW_NUMBER, RANK আর DENSE_RANK

মেধাতালিকা বানাতে গেলেই প্রথম প্রশ্ন: দুজনের নম্বর সমান হলে কী হবে? SQL-এ তিনটি ranking function, আর তিনটির পার্থক্য শুধু টাই (tie)-তে:

• ROW_NUMBER() — সবসময় 1, 2, 3, 4 … ; টাই হলেও আলাদা নম্বর, আর কে আগে পাবে তা অনির্দিষ্ট।
• RANK() — সমান মানে সমান অবস্থান, তারপর ফাঁক: 1, 2, 2, 4. খেলাধুলার প্রচলিত নিয়ম — দুজন দ্বিতীয় হলে কেউ তৃতীয় হন না।
• DENSE_RANK() — সমান মানে সমান অবস্থান, কিন্তু ফাঁক নেই: 1, 2, 2, 3. “কততম আলাদা মান” — যেমন “দ্বিতীয় সর্বোচ্চ বেতন”।

db-uni-র CGPA-তে কোনো টাই নেই, তাই এই অংশের এডিটরে (data-setup দিয়ে) একজন নতুন CSE শিক্ষার্থী যোগ করা হয়েছে: Ishita Das (2303), batch 2023, CGPA 3.75 — ঠিক Arif-এর সমান। এবার CSE বিভাগের মেধাক্রম তিন ফাংশনে পাশাপাশি:

সূত্র মনে রাখার সহজ উপায়: RANK = (আমার চেয়ে ভালো মানের সারির সংখ্যা) + 1; DENSE_RANK = (আমার চেয়ে ভালো আলাদা মানের সংখ্যা) + 1. Gulshan-এর চেয়ে ভালো সারি তিনটি (3.90, 3.75, 3.75), তাই RANK 4; কিন্তু আলাদা মান দুটি (3.90, 3.75), তাই DENSE_RANK 3. ফলে শেষ সারিতে RANK সবসময় ≤ সারির সংখ্যা, আর DENSE_RANK-এর সর্বোচ্চ মান = আলাদা মানের সংখ্যা।

Ranking-এর সাথে আরও তিনটি “অবস্থান” ফাংশন আছে, যারা পরিসংখ্যানের ভাষায় উত্তর দেয়:

• NTILE(k) — ক্রম অনুযায়ী সারিগুলোকে k-টি প্রায় সমান ভাগে ভাগ করে ভাগ-নম্বর দেয়। NTILE(4) মানে চার ভাগ, অর্থাৎ quartile. n সারি k ভাগে না মিললে প্রথম (n mod k)-টি ভাগে একটি করে সারি বেশি যায়।
• PERCENT_RANK() = (RANK − 1) ÷ (n − 1) — প্রথমজন 0, শেষজন 1.
• CUME_DIST() = (ক্রমে আমি পর্যন্ত, আমার সমান মানের সবাইসহ, কয়টি সারি) ÷ n — “কত শতাংশ সারি আমার সমান বা আগে”। ফল কখনো 0 নয়, শেষজনের 1.

-- প্রতিটি বিভাগের ভেতরে তিন রকম মেধাক্রম
SELECT dept_id, name, cgpa,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY cgpa DESC) AS rn,
       RANK()       OVER (PARTITION BY dept_id ORDER BY cgpa DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY cgpa DESC) AS drnk
FROM students
ORDER BY dept_id, rn;

-- সবাই মিলিয়ে: quartile, percent rank, cumulative distribution
SELECT name, cgpa,
       NTILE(4)                           OVER (ORDER BY cgpa DESC)    AS quartile,
       ROUND(PERCENT_RANK() OVER (ORDER BY cgpa DESC), 3) AS pct_rank,
       ROUND(CUME_DIST()    OVER (ORDER BY cgpa DESC), 3) AS cume
FROM students WHERE cgpa IS NOT NULL
ORDER BY cgpa DESC;

৪ · আগের আর পরের সারি — LAG আর LEAD

মুদি দোকানের খাতায় মালিক প্রায়ই জিজ্ঞেস করেন: “রহিম সাহেব আগেরবার কবে এসেছিলেন?” বা “আজকের বিক্রি গতকালের চেয়ে কত বেশি?” দুটিই একই ধরনের প্রশ্ন — বর্তমান সারির সাথে ক্রমে আগের সারির তুলনা। Window function আসার আগে এর জন্য টেবিলকে নিজের সাথে JOIN করতে হতো। এখন আছে দুটি offset function:

পরের এডিটরে দুটি প্রশ্ন। এক: প্রতিটি অর্ডারের জন্য সেই গ্রাহকের আগের অর্ডারের তারিখ, আর মাঝে কত দিন। SQLite-এ তারিখ লেখা হিসেবে থাকে, তাই দিন গুনতে julianday() লাগে (পর্ব ১৭) — এটি তারিখকে একটি দিন-সংখ্যায় বদলায়, আর দুটির বিয়োগফল দিনের পার্থক্য। দুই: দিনে দিনে বিক্রি কত বাড়ল বা কমল। এর জন্য প্রথমে দৈনিক আয়ের একটি CTE (পর্ব ২৫), তারপর তার ওপর LAG. order_items টেবিলে প্রতিটি অর্ডারের লাইন: order_id, product_id, quantity আর unit_price; বাতিল (cancelled) অর্ডার আয় থেকে বাদ।

-- ১. গ্রাহকের আগের অর্ডার আর মাঝের দিন
SELECT customer_id, order_id, order_date,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_date,
       julianday(order_date) - julianday(LAG(order_date)
           OVER (PARTITION BY customer_id ORDER BY order_date))          AS gap_days
FROM orders ORDER BY customer_id, order_date;

-- ২. দিনে দিনে আয়ের পরিবর্তন
WITH daily AS (
  SELECT o.order_date, SUM(i.quantity * i.unit_price) AS revenue
  FROM orders o JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status <> 'cancelled' GROUP BY o.order_date)
SELECT order_date, revenue,
       LAG(revenue, 1, 0) OVER (ORDER BY order_date)     AS prev,
       revenue - LAG(revenue) OVER (ORDER BY order_date) AS change
FROM daily ORDER BY order_date;

তারিখের বিয়োগ প্রতিটি DBMS-এ আলাদা। SQLite-এ '2025-09-10' - '2025-09-01' লিখলে কোনো ত্রুটি আসে না, ফল আসে 0 — দুটি লেখার শুরুর “2025” সংখ্যায় বদলে বিয়োগ হয়! তাই julianday বাধ্যতামূলক। অন্যদিকে:

-- PostgreSQL: DATE − DATE = দিনের সংখ্যা (integer)
SELECT order_id,
       order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS gap_days
FROM orders;

-- MySQL 8.0: DATEDIFF(পরের, আগের)
SELECT order_id,
       DATEDIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS gap_days
FROM orders;

৫ · Running total, moving average আর মোটের ভাগ

SUM, AVG, COUNT, MIN, MAX — পর্ব ১৮-এর সব aggregate-এর পরে OVER বসালেই তারা window function. ORDER BY ছাড়া তারা পুরো partition-এর হিসাব দেয় (অংশ ১-এর বিভাগের গড়)। ORDER BY দিলে অর্থ বদলে যায়: প্রতিটি সারিতে “শুরু থেকে এই সারি পর্যন্ত” হিসাব — অর্থাৎ running total (চলমান যোগফল)। bKash-এর লেনদেনের তালিকায় প্রতিটি লাইনের পাশের “ব্যালান্স” কলামটি ঠিক এটি।

পরের এডিটরে আরেকটি নতুন জিনিস: window function চলছে GROUP BY-এর ফলের ওপর। প্রথমে দিন অনুযায়ী দল, প্রতিটি দলের SUM(…); তারপর SUM(SUM(…)) OVER (ORDER BY …) — ভেতরের SUM দলের aggregate, বাইরের SUM সেই দলগুলোর ওপর window. আর মোটের শতাংশে SUM(SUM(…)) OVER () সব দিনের মোট দেয়।

SELECT o.order_date,
       SUM(i.quantity * i.unit_price)                                   AS revenue,
       SUM(SUM(i.quantity * i.unit_price)) OVER (ORDER BY o.order_date) AS running,
       ROUND(100.0 * SUM(i.quantity * i.unit_price)
             / SUM(SUM(i.quantity * i.unit_price)) OVER (), 1)         AS pct
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY o.order_date
ORDER BY o.order_date;

এখন moving average (চলমান গড়): প্রতিটি অর্ডারে “এটিসহ শেষ তিনটি অর্ডারের গড়”। এখানে “শুরু থেকে” নয়, একটি নির্দিষ্ট মাপের জানালা লাগে, যা প্রতিটি সারির সাথে সামনে সরে — এর জন্য frame স্পষ্ট লিখতে হয়: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW (দুই সারি আগে থেকে বর্তমান সারি পর্যন্ত)। নিচে জানালাটি সরতে দেখুন:

 order │ date       │ total
 1001  │ 2025-09-01 │ 17400
 1002  │ 2025-09-03 │  1750
 1003  │ 2025-09-10 │  1780
 1005  │ 2025-09-15 │  5050
 1006  │ 2025-09-20 │  3500
 1007  │ 2025-10-01 │  3250
 1008  │ 2025-10-05 │   550
 1009  │ 2025-10-08 │ 18000
 1010  │ 2025-10-09 │  1040

▶ 1001  │ 17400   ← frame
  1002  │  1750
  1003  │  1780
  1005  │  5050
  …

frame-এ ১টি সারি: 17400 ÷ 1 = 17400

  1001  │ 17400   ┐
  1002  │  1750   │ frame
▶ 1003  │  1780   ┘
  1005  │  5050
  …

(17400 + 1750 + 1780) ÷ 3 = 6976.7

  1001  │ 17400   ← বাইরে চলে গেল
  1002  │  1750   ┐
  1003  │  1780   │ frame
▶ 1005  │  5050   ┘
  1006  │  3500
  …

(1750 + 1780 + 5050) ÷ 3 = 2860

  …
  1006  │  3500
  1007  │  3250   ┐
  1008  │   550   │ frame
▶ 1009  │ 18000   ┘
  1010  │  1040

(3250 + 550 + 18000) ÷ 3 = 7266.7
একদিনের 18000 গড়ে 7266.7 — লাফ নরম হয়

WITH t AS (
  SELECT o.order_id, o.order_date, SUM(i.quantity * i.unit_price) AS total
  FROM orders o JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.order_id, o.order_date)
SELECT order_id, order_date, total,
       COUNT(*) OVER (ORDER BY order_date
                      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)     AS n,
       ROUND(AVG(total) OVER (ORDER BY order_date
                      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg3
FROM t ORDER BY order_date;

৬ · Frame গভীরে — ROWS, RANGE আর GROUPS

Frame-এর পূর্ণ গঠন: {ROWS | RANGE | GROUPS} BETWEEN শুরু AND শেষ. শুরু আর শেষ হতে পারে UNBOUNDED PRECEDING (partition-এর শুরু), n PRECEDING, CURRENT ROW, n FOLLOWING বা UNBOUNDED FOLLOWING (partition-এর শেষ)। শুধু শুরু লিখলে (ROWS UNBOUNDED PRECEDING) শেষটি ধরা হয় CURRENT ROW. কিন্তু “n সারি আগে” — সারি কীভাবে মাপা হবে? তিনটি একক:

টেবিলের শেষ কলামটিই মূল কথা। Window-এর ORDER BY-তে যেসব সারির মান সমান, তাদের বলে peer (সমকক্ষ)। ROWS peer চেনে না; RANGE আর GROUPS চেনে। আর এখানেই SQL-এর সবচেয়ে ভুল-বোঝা নিয়মটি:

অংশ ৫-এ প্রতিটি দিনে একটিই সারি ছিল, তাই peer ছিল না, আর RANGE ও ROWS একই ফল দিয়েছে। কিন্তু অর্ডারের লাইন ধরে running total করলে একই তারিখে দুটি লাইন থাকে। প্রথম তিনটি অর্ডারের ছয়টি লাইনে দেখুন:

 # │ order_date │ product │ amount
 1 │ 2025-09-01 │ 101     │ 15000   ┐ peer-দল ক
 2 │ 2025-09-01 │ 102     │  2400   ┘
 3 │ 2025-09-03 │ 104     │   650   ┐ peer-দল খ
 4 │ 2025-09-03 │ 105     │  1100   ┘
 5 │ 2025-09-10 │ 108     │  1260   ┐ peer-দল গ
 6 │ 2025-09-10 │ 109     │   520   ┘

▶ 1 │ 2025-09-01 │ 15000   ← frame
  2 │ 2025-09-01 │  2400
  3 │ 2025-09-03 │   650
  …

SUM = 15000

▶ 1 │ 2025-09-01 │ 15000   ┐ frame
  2 │ 2025-09-01 │  2400   ┘ (peer)
  3 │ 2025-09-03 │   650
  …

SUM = 15000 + 2400 = 17400
“এই সারি পর্যন্ত” মানে “এই তারিখ পর্যন্ত”

ROWS:  লাইন 1, 2, 3
       15000 + 2400 + 650          = 18050

RANGE: লাইন 1, 2, 3, 4  (4 হলো 3-এর peer)
       15000 + 2400 + 650 + 1100   = 19150

RANGE-এ লাইন 3 আর 4 একই উত্তর পায়

  1 │ 2025-09-01 │ 15000   দল ক — বাইরে
  2 │ 2025-09-01 │  2400
  3 │ 2025-09-03 │   650   ┐ দল খ (১টি দল আগে)
  4 │ 2025-09-03 │  1100   │
▶ 5 │ 2025-09-10 │  1260   │ দল গ (বর্তমান)
  6 │ 2025-09-10 │   520   ┘

SUM = 650 + 1100 + 1260 + 520 = 3530

WITH lines AS (
  SELECT o.order_date, i.product_id, i.quantity * i.unit_price AS amount
  FROM order_items i JOIN orders o ON o.order_id = i.order_id
  WHERE o.order_id <= 1003)
SELECT order_date, product_id, amount,
       SUM(amount) OVER (ORDER BY order_date)                        AS dflt_range,
       SUM(amount) OVER (ORDER BY order_date, product_id
                         ROWS UNBOUNDED PRECEDING)                   AS by_rows,
       SUM(amount) OVER (ORDER BY order_date
                         GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS last2_groups
FROM lines ORDER BY order_date, product_id;

কোনটি কখন? “প্রতিটি লেনদেনের পরের ব্যালান্স” চাইলে ROWS, সাথে অনন্য tiebreaker (লেনদেন নম্বর)। “দিনের শেষের মোট” চাইলে RANGE-এর সিঁড়িই ঠিক। আর RANGE-এ সংখ্যার offset দিয়ে মানের জানালা হয়: RANGE BETWEEN 6 PRECEDING AND CURRENT ROW মানে “ORDER BY-এর মান যেসব সারিতে বর্তমানের চেয়ে সর্বোচ্চ ৬ কম” — ORDER BY julianday(order_date) হলে শেষ ৭ দিন, মাঝে কিছু দিন অর্ডার না থাকলেও। এর জন্য ORDER BY-তে ঠিক একটি সংখ্যা-কলাম লাগে; SQLite-এ লেখা-তারিখে offset দিলে ত্রুটি আসে না, কিন্তু frame-এ শুধু বর্তমান সারি আর তার peer থাকে — ফল নিঃশব্দে ভুল। PostgreSQL-এ DATE কলামে RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW সরাসরি চলে।

৭ · FIRST_VALUE, LAST_VALUE, NTH_VALUE — আর default frame-এর ফাঁদ

তিনটি ফাংশন frame-এর ভেতর থেকে একটি নির্দিষ্ট সারির মান তুলে আনে: FIRST_VALUE(expr) — frame-এর প্রথম সারি; LAST_VALUE(expr) — frame-এর শেষ সারি; NTH_VALUE(expr, n) — frame-এর n-তম সারি। “প্রতিটি শিক্ষার্থীর পাশে বিভাগের সেরা জনের নাম” — FIRST_VALUE দিয়ে সহজ। “বিভাগের সবচেয়ে নিচের জনের নাম” — LAST_VALUE দিয়ে, তাই না? চালিয়ে দেখুন (আবার Ishita Das যোগ করা আছে):

SELECT name, cgpa,
       FIRST_VALUE(name) OVER (ORDER BY cgpa DESC) AS top,
       -- ফাঁদ: default frame শেষ হয় বর্তমান সারিতে (আর তার peer-এ)
       LAST_VALUE(name)  OVER (ORDER BY cgpa DESC) AS last_wrong,
       -- সমাধান: frame পুরো partition পর্যন্ত টেনে দিন
       LAST_VALUE(name)  OVER (ORDER BY cgpa DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_right,
       NTH_VALUE(name, 2) OVER (ORDER BY cgpa DESC) AS second
FROM students
WHERE dept_id = 1
ORDER BY cgpa DESC;

WINDOW clause — একই window বারবার না লিখে নাম দিন

ওপরের সমাধানে frame-টি কত লম্বা, দেখেছেন; আর অংশ ৩-এর প্রথম কোয়েরিতে PARTITION BY dept_id ORDER BY cgpa DESC তিনবার লিখতে হয়েছে। লম্বা, আর একটিতে ভুল করে বদলালে বাকিগুলোর সাথে মিলবে না। SQL:2003 standard এর সমাধান দিয়েছে WINDOW clause: কোয়েরির FROM/WHERE/GROUP BY/HAVING-এর পরে, ORDER BY-এর আগে WINDOW w AS (…) লিখে একটি নাম দিন, তারপর OVER w. নামের ওপর আরও কিছু যোগও করা যায়: OVER (w ORDER BY …) — শর্ত হলো, w-এ যা নেই শুধু তা-ই যোগ করা যাবে (w-এ ORDER BY থাকলে আবার ORDER BY নয়)।

উদাহরণ: প্রতিটি পণ্যের দাম তার ক্যাটাগরির মোট দামের কত শতাংশ, আর ক্যাটাগরিতে দামের ক্রম। products টেবিলে product_id, name, category_id, price আর stock; categories-এ category_id আর name.

SELECT c.name AS category, p.name, p.price,
       SUM(p.price) OVER cat                             AS cat_total,
       ROUND(100.0 * p.price / SUM(p.price) OVER cat, 1) AS pct,
       RANK() OVER (cat ORDER BY p.price DESC)           AS rnk
FROM products p JOIN categories c ON c.category_id = p.category_id
WINDOW cat AS (PARTITION BY p.category_id)
ORDER BY c.name, p.price DESC;

৮ · কখন চলে window function — যৌক্তিক ক্রম আর WHERE-এ ছাঁকার সমস্যা

পর্ব ১৮-এ SELECT-এর যৌক্তিক ক্রম দেখেছেন: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Window function-এর জায়গা HAVING-এর পরে, SELECT-এর কলাম তৈরির সময় — অর্থাৎ DISTINCT, ORDER BY আর LIMIT-এর আগে। এর দুটি পরিণতি। এক: window function শুধু SELECT-এর তালিকা আর শেষের ORDER BY-তে লেখা যায়, WHERE, GROUP BY বা HAVING-এ নয়। দুই: window যে সারিগুলো দেখে, সেগুলো WHERE আগেই ছেঁকে রেখেছে। নিচে batch 2022-এর শিক্ষার্থীদের ওপর একটি RANK দেখুন:

SELECT name, cgpa,
       RANK() OVER (ORDER BY cgpa DESC) AS r
FROM students WHERE batch = 2022 ORDER BY r;

 name           │ batch │ cgpa
 Arif Hossain   │ 2021  │ 3.75
 Bristy Saha    │ 2021  │ 3.40
 Chayon Roy     │ 2021  │ 2.95
 Dilruba Khatun │ 2022  │ 3.90
 Emon Mia       │ 2022  │ 3.10
 Fahim Karim    │ 2022  │ NULL
 Gulshan Ara    │ 2023  │ 3.55
 Habib Ullah    │ 2023  │ 2.80

 Dilruba Khatun │ 2022 │ 3.90
 Emon Mia       │ 2022 │ 3.10
 Fahim Karim    │ 2022 │ NULL

এই মুহূর্তে r বলে কিছু নেই —
WHERE r <= 2 লেখা অসম্ভব

 Dilruba Khatun │ 3.90
 Emon Mia       │ 3.10
 Fahim Karim    │ NULL

থাকলে window চলত দলের ফলের ওপর —
যেমন SUM(SUM(x)) OVER (…)

 name           │ cgpa │ r
 Dilruba Khatun │ 3.90 │ 1
 Emon Mia       │ 3.10 │ 2
 Fahim Karim    │ NULL │ 3

সবার মধ্যে Emon 5ম — কিন্তু বাকিরা
WHERE-এ আগেই বাদ পড়েছে

 name           │ cgpa │ r
 Dilruba Khatun │ 3.9  │ 1
 Emon Mia       │ 3.1  │ 2
 Fahim Karim    │ NULL │ 3

ORDER BY window-এর পরে চলে,
তাই ORDER BY r চলে

তাহলে “প্রতিটি বিভাগের সেরা দুজন” কীভাবে? Window-এর ফল একটি CTE বা FROM-এর subquery-তে রাখুন — তখন বাইরের কোয়েরির কাছে r একটি সাধারণ কলাম, যা WHERE-এ চলে। এই কৌশলটি top-N per group সমস্যার মানক সমাধান (পর্ব ২৭-এ আরও রূপ)। এডিটরে ঠিক পথটি আগে, ভুল পথটি শেষে — কারণ ত্রুটি হলে এডিটর সেখানেই থামে:

-- ঠিক পথ: আগে ক্রম, তারপর বাইরে ছাঁকা
WITH ranked AS (
  SELECT dept_id, name, cgpa,
         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY cgpa DESC) AS r
  FROM students
  WHERE cgpa IS NOT NULL
)
SELECT dept_id, name, cgpa, r FROM ranked
WHERE r <= 2
ORDER BY dept_id, r;

-- ভুল পথ: WHERE-এ window-এর alias
SELECT name, cgpa, RANK() OVER (ORDER BY cgpa DESC) AS r
FROM students WHERE r <= 2;

কিছু DBMS এই ঝামেলা কমাতে একটি অতিরিক্ত clause এনেছে — QUALIFY, যা window function-এর পরে চলা একটি WHERE. এটি প্রথম আসে Teradata-তে; এখন Snowflake, Google BigQuery, DuckDB আর Databricks-এ আছে। কিন্তু এটি ISO SQL standard-এর অংশ নয়, আর PostgreSQL, MySQL, SQLite বা SQL Server-এ নেই — তাই বহনযোগ্য কোডে CTE-ই নিরাপদ।

SELECT dept_id, name, cgpa
FROM students
WHERE cgpa IS NOT NULL
QUALIFY DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY cgpa DESC) <= 2;

আরও দুটি নিয়ম, যা এই ক্রম থেকেই আসে: window function-এর ভেতরে আরেকটি window function বসানো যায় না (দরকার হলে CTE-তে ধাপে ধাপে)। আর window aggregate-এ DISTINCT চলে না — COUNT(DISTINCT dept_id) OVER () SQLite-এ দেয় DISTINCT is not supported for window functions, PostgreSQL আর SQL Server-এও নিষেধ।

৯ · পারফরম্যান্স, ইতিহাস আর কোন DBMS-এ কী আছে

খরচ কোথায়। ORDER BY বা PARTITION BY থাকলে DBMS সাধারণত প্রথমে সারিগুলো (partition-কলাম, তারপর order-কলাম অনুযায়ী) সাজায় — n সারির জন্য O(n log n). তারপর একবার পড়ে যায়: প্রতিটি partition-এর শুরুতে হিসাব নতুন করে শুরু। ROW_NUMBER, RANK, LAG বা running total এই এক পাসে প্রতি সারিতে O(1) কাজে হয়, কারণ আগের হিসাবের ওপর একটি করে মান যোগ হয় — prefix sum-এর মতো। ফলে পুরো কোয়েরি O(n log n), যেখানে একই running total correlated subquery দিয়ে লিখলে প্রতিটি সারির জন্য আগের সব সারি আবার পড়তে হয়: মোট 1 + 2 + … + n = n(n + 1)/2 সারি-পড়া, অর্থাৎ O(n²).

• Index সাজানো বাঁচায়। (customer_id, order_date)-এ index থাকলে PARTITION BY customer_id ORDER BY order_date-এর জন্য DBMS index-এর ক্রমেই পড়তে পারে — আলাদা sort লাগে না (পর্ব ৪১)।
• আলাদা আলাদা window মানে আলাদা sort. একটি কোয়েরিতে ORDER BY cgpa আর ORDER BY batch — দুটি window হলে সাধারণত দুবার সাজাতে হয়। একই PARTITION BY/ORDER BY-ওয়ালা window-গুলো DBMS একটি sort-এ সারে, তাই WINDOW clause দিয়ে মিলিয়ে রাখা কাজের।
• সরে-চলা frame. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW-এ SUM-এর জন্য নতুন মান যোগ আর পুরোনোটি বিয়োগ করলেই চলে; কিন্তু MIN/MAX-এ “বিয়োগ” হয় না, তাই সেখানে বেশি কাজ লাগে।

১০ · AI-এর যুগে window function — চলে, কিন্তু সারি ভুল জায়গায়

“প্রতিটি গ্রাহকের শেষ অর্ডার দাও” বা “মাসিক running total” লিখলে AI চমৎকার দেখতে window কোয়েরি দেয়। কিন্তু window-এর ভুল প্রায় কখনো ত্রুটি দেয় না: টাই-এ এলোমেলো সারি, partition ভুলে গেলে এক গ্রাহকের LAG-এ আরেক গ্রাহকের তারিখ, default frame-এ ভুল LAST_VALUE. সবচেয়ে বেশি যা দেখা যায়:

AI-এর লেখা window কোয়েরি হাতে পেলে — যাচাইয়ের চেকলিস্ট

• PARTITION BY ঠিক কলামে; প্রতিটি partition-এর প্রথম সারিতে LAG NULL আর ROW_NUMBER 1
• OVER-এর ORDER BY মিলে অনন্য, অথবা টাই-তে RANK/DENSE_RANK ইচ্ছাকৃতভাবে বাছা
• LAST_VALUE, NTH_VALUE আর running total-এ frame স্পষ্ট লেখা; ROWS না RANGE — জেনে বাছা
• শেষ running total = GROUP BY দিয়ে মোট; শতাংশের যোগ ≈ 100
• টাই, NULL আর একটি মাত্র সারির partition দিয়ে চালিয়ে দেখেছি
• window-এর ফল দিয়ে ছাঁকা CTE/subquery-তে; শেষে আলাদা ORDER BY আছে
• লক্ষ্য DBMS-এ (MySQL, SQL Server …) ফিচারটি আছে; বড় টেবিলে EXPLAIN-এ অপ্রয়োজনীয় sort নেই