المقدمة

في الجزء الأول فهمنا يعني إيه window function واتكلمنا عن 3 window functions وهم RANK, DENSE_RANK, ROW_NUMBER وشوفنا أمثلة عليهم. في المقال ده هنتكلم عن 2 window functions جداد وهم LEAD, LAG وهنشوف ممكن نستخدمهم في إيه. المقال ده معتمد بشكل كبير على المقال اللي فات فمحتاج تكون قرأت المقال اللي فات عشان تقدر تقرأ المقال ده وتفهمه بشكل أحسن.

Product Prices

هنبدأ من المثال الأخير في المقال الأول وهو جدول أسعار المنتجات اللي بنتابع فيه تغير أسعار كل منتج:

في المقال اللي فات استخدمنا الـ ranking functions عشان نعرف حاجات زي أحدث أو أقدم سعر لكل منتج، بس المرة دي عايزين كل ما سعر منتج يتغير نقارن بين السعر الجديد والسعر القديم بالشكل ده:

لاحظ الصفوف اللي عندها السعر القديم NULL، ده بسبب إن سعر المنتج وقتها كان أول سعر ليه وبالتالي مكنش فيه سعر قبله عشان كدة قيمة السعر القديم NULL في الحالة دي. 

عشان ننفذ المطلوب في المثال هنستخدم أول window function هنتكلم عنها في المقال ده وهي LAG ، LAG بتاخد عمود معين في الجدول كـ argument ولكل صف في الجدول بتجيب قيمة العمود ده بس في الصف اللي قبله، بالشكل ده: 

• ده LAG عشان نعرف لكل سعر كان إيه السعر اللي قبله عن طريق إننا نستخدم العمود بتاع السعر كـ argument لـ LAG.
•  وبما إن LAG دي window function فالطريقة اللي نقول بيها إن دي window function هي إننا نستخدم OVER.

SELECT product_id, price_date, price, LAG(price) OVER() 
FROM ProductPriceHistory

•  وبما إننا عايزين نتابع السعر القديم والجديد لكل منتج فهنقول PARTITION BY product_id

SELECT product_id, price_date, price, LAG(price) OVER(PARTITION BY product_id) 
FROM ProductPriceHistory

• زي الـ ranking functions لازم نستخدم ORDER BY مع LAG عشان نديها الترتيب اللي بناءًا عليه هتحدد ترتيب الصفوف وتحدد أي صف جي قبل الثاني، في المثال ده هنرتب بالتواريخ من الأقدم للأحدث:

SELECT product_id, price_date, price, 
LAG(price) OVER(PARTITION BY product_id ORDER BY price_date) as previous_price 
FROM ProductPriceHistory

لما نعمل run للكود دي النتيجة اللي هتطلع:

توضيح للكود:

LAG Arguments

زي ما قولنا إن LAG بتاخد عمود معين في الجدول كـ argument، بس مش ده الـ argument الوحيد فيه 2 كمان ولكنهم اختياري:

• offset:

الطبيعي بتاع LAG إنها لكل صف تجيب قيمة عمود معين للصف اللي قبله على طول، الـ argument ده بنستخدمه لو عايز صف أبعد من الصف اللي قبلي على طول مثلاََ الصف اللي قبلي بصفين أو 3 وهكذا. لو محددناش قيمة للـ argument ده قيمته بتكون 1 وهو الطبيعي بتاع LAG هيجيب قيمة العمود في الصف اللي قبل الصف الحالي.

توضيح لاستخدام LAG بقيمة أكبر من 1 للـ offset:

هنلاحظ هنا لما الـ offset تساوي 2 قيمة LAG بتكون NULL في أول صفين مش أول صف بس، لأن عند الصف التاني بيكون مطلوب قيمة السعر في الصف اللي قبل الصف التاني بصفين، مفيش صف قبل الصف التاني بصفين وبالتالي النتيجة بتكون NULL. وبالمثل لما الـ offset تكون 3 قيمة LAG بتكون NULL في أول 3 صفوف.

• default:

زي ما قولنا إن لو مفيش صف قبل الصف الحالي نتيجة LAG هتكون NULL، لو إحنا مش عايزين NULL تظهر في النتيجة ويظهر بدالها رقم مثلاََ أو string بنستخدم الـ argument ده عشان كدة. لو محددناش قيمة للـ argument ده قيمته بتكون NULL. 

توضيح لاستخدام default:

         هنلاحظ في الصورة الصفوف اللي عندها قيمة LAG كانت NULL بقى قيمتها 0. 

ملحوظة محتاجين نقولها وهي إن ترتيب الـ arguments هو LAG(column_name, offset, default)، وبالتالي لو محتاجين نحدد قيمة default لازم نحدد قيمة للـ offset. 

Rising Temperature

بعد ما فهمنا LAG هنحل مع بعض سؤال Rising Temperature على LeetCode. في السؤال ده معانا جدول اسمه Weather فيه id لكل يوم وتاريخ اليوم ودرجة الحرارة في اليوم ده: 

كل صف في الجدول بيمثل يوم واحد بس، ومطلوب مننا نجيب الأيام اللي درجة الحرارة فيها كانت أعلى من درجة الحرارة في اليوم اللي قبله، ده مثال على الجدول والمطلوب مننا: 

في المثال ده النتيجة كانت الأيام اللي الـ id بتاعتها 2 و 4 لأن كل يوم في الأيام دي درجة الحرارة كانت أعلى من درجة الحرارة في اليوم اللي قبله على طول. عشان نحل السؤال ده محتاجين لكل يوم موجود في الجدول نقارن بين درجة الحرارة في اليوم ده ودرجة الحرارة في اليوم اللي قبله ولو درجة الحرارة أعلى من اليوم اللي قبله يبقى اليوم ده معانا في النتيجة. إزاي لكل صف في الجدول نجيب بيانات موجودة في صف قبله؟ LAG. 

بما إننا عايزين نعرف درجة الحرارة في اليوم اللي قبلنا فهنقول إننا عايزين نعمل LAG على درجة الحرارة. وجوا OVER هنرتب بالتاريخ من الأقدم للأحدث بحيث لكل صف الصف اللي قبله يكون فيه تاريخ اليوم اللي قبله على طول:

SELECT 
	id, 
  recordDate AS today, 
  temperature AS todayTemp,
  LAG(temperature) OVER(ORDER BY recordDate) AS yesterdayTemp
FROM Weather

لما نعمل run للكود دي النتيجة اللي هتظهر:

من الصفوف اللي ظهرت دي محتاجين نختار بس الأيام اللي درجة الحرارة فيها أعلى من درجة الحرارة في اليوم اللي قبله، عشان كدة هنحط الكود ده جوا subquery وهنحدد شرط يختار الصفوف اللي محتاجينها بس: 

SELECT id 
FROM
(
    SELECT 
			id, 
		  recordDate AS today, 
		  temperature AS todayTemp,
		  LAG(temperature) OVER(ORDER BY recordDate) AS yesterdayTemp
		FROM Weather
) sub 
WHERE todayTemp > yesterdayTemp

نتيجة الكود: 

توضيح للكود: 

الكود اللي كتبناه طلع النتيجة المطلوبة لما جربناه على الجدول اللي في المثال، بس لو جينا نعمل submit للكود ده هنلاقي النتيجة غلط: 

إيه المشكلة في الكود؟ LeetCode جابلنا الجدول اللي جرب عليه الكود بتاعنا وطلع نتيجة غلط:

هنشوف ازاي الكود بتاعنا بيشتغل على الجدول ده، هنبدأ بالصف الأول، بما إن ده أول تاريخ معانا فنتيجة LAG هتكون NULL: 

عند تاني صف LAG هتجيب درجة الحرارة من الصف الأول اللي هي 3، هنقارنها بدرجة الحرارة اللي في الصف التاني اللي هي 5، 5 أكبر من 3 يبقى الصف التاني معانا في النتيجة. ودي النتيجة النهائية لأن مفيش صفوف بعد الصف التاني:

بس LeetCode بيقول إن النتيجة المفروض جدول فاضي، ليه النتيجة المفروض جدول فاضي؟ هنركز على التواريخ اللي في الجدول ده هنلاقيهم 2000-12-14 و 2000-12-16:

زي ما فهمنا إن المطلوب هنا هو إننا نجيب الأيام اللي درجة الحرارة فيها أعلى من اليوم اللي قبله على طول، اليوم اللي قبل 2000-12-14 هو 2000-12-13 وده مش موجود في الجدول ونفس الكلام اليوم اللي قبل 2000-12-16 هو 2000-12-15 وده كمان مش موجود، وبالتالي مش هنقدر نقارن كل يوم باللي قبله هنا لأن البيانات دي مش موجودة في الجدول أصلاََ، مفيش غير يومين في الجدول وبما إن اليومين دول مش ورا بعض فمش هنقدر نقارنهم ببعض ،عشان كدة المفروض النتيجة تكون جدول فاضي، طيب ليه الكود بتاعنا مبيطلعش جدول فاضي وبيجيب اليوم اللي الـ Id بتاعته 2؟ 

لأن LAG هتجيبلنا درجة الحرارة في الصف اللي قبلنا وخلاص مش هتتأكد الأول اليومين دول جايين ورا بعض ولا لأ هي مجرد بتمشي على كل صف وتجيب درجة الحرارة في الصف اللي قبله، في الكود بتاعنا LAG بتيجي عند الصف التاني اللي التاريخ فيه 2000-12-16 وتقولنا درجة الحرارة في الصف الأول 3، بس الصف الأول ده تاريخه 2000-12-14 وده مش اليوم اللي قبل 2000-12-16. وبالتالي قبل ما نقارن درجات الحرارة بين الصف اللي في احنا فيه والصف اللي قبلنا محتاجين نتأكد هل اليومين دول جايين ورا بعض ولا لأ. 

عشان نعمل كدة محتاجين نجيب التاريخ اللي في الصف اللي قبلنا مع درجة الحرارة، يبقى في الـ subquery هنزود كمان LAG بس على العمود بتاع التاريخ:

SELECT id 

FROM
(
	SELECT 
				id, 
			  recordDate AS today, 
			  temperature AS todayTemp,
			  LAG(recordDate) OVER(ORDER BY recordDate) AS yesterday
			  LAG(temperature) OVER(ORDER BY recordDate) AS yesterdayTemp,
			  
    FROM Weather
) sub 
where datediff(day,yesterday , today) = 1 and todayTemp > yesterdayTemp

بعد كدة هنزود شرط في الـ WHERE قبل ما نقارن درجات الحرارة هنقارن التواريخ عشان نتأكد إن اليومين دول جايين ورا بعض: 

SELECT id 

FROM
(
	SELECT 
				id, 
			  recordDate AS today, 
			  temperature AS todayTemp,
			  LAG(recordDate) OVER(ORDER BY recordDate) AS yesterday
			  LAG(temperature) OVER(ORDER BY recordDate) AS yesterdayTemp,
			  
    FROM Weather
) sub 

WHERE DATEDIFF(DAY,yesterday , today) = 1 AND todayTemp > yesterdayTemp

ولو عملنا submit للكود هنلاقي الحل ده صح:

توضيح للكود: 

ملخص خطوات حل السؤال:

• المطلوب كان إننا نجيب الأيام اللي درجة الحرارة فيها أعلى من اليوم اللي قبله
• استخدمنا LAG عشان لكل صف نجيب درجة الحرارة في الصف اللي قبله، بعد كدة استخدمنا الـ query اللي فيها LAG كـ subquery في query تانية 
• الـ query التانية هدفها إننا نجيب الأيام اللي درجة الحرارة فيها أعلى من اليوم اللي قبله
• لقينا إن ممكن يكون صفين جايين ورا بعض في الجدول بس التواريخ اللي فيهم مش تواريخ يومين ورا بعض
• زودنا جزء في الـ subquery عشان لكل صف يجيب التاريخ اللي في الصف اللي قبله مع درجة الحرارة
• وزودنا شرط عشان نتأكد إن اليوم اللي في الصف الحالي واليوم اللي في الصف اللي قبله دول يومين جايين ورا بعض
• لو الشرط ده اتحقق وطلعوا يومين ورا بعض فعلا هنقارن درجات الحرارة، لو درجة حرارة اليوم الحالي أعلى من درجة حرارة اليوم اللي فات يبقى اليوم الحالي معانا في النتيجة

Consecutive Numbers

هنحل سؤال كمان على LeetCode وهو سؤال consecutive numbers في السؤال ده معانا جدول فيه عمودين: id, num. ومطلوب مننا نجيب الأرقام اللي جت ورا بعض 3 مرات أو أكتر: 

هنا رقم 1 ظهر 3 مرات ورا بعض فعشان كدة هو معانا في النتيجة، رقم 2 ظهر 3 مرات بس مش ورا بعض عشان كدة مش معانا. 

علشان نحل السؤال محتاجين نعدي على كل صف ونقارن الرقم اللي في الصف الحالي بالأرقام اللي في الصفين اللي قبله، لو الـ 3 صفوف فيهم نفس الرقم يبقى الرقم ده معانا في النتيجة. وعلشان نقدر نقارن الرقم اللي في الصف الحالي بالأرقام اللي في الصفين اللي قبله هنستخدم LAG. 

• عشان نعرف الرقم اللي في الصف اللي قبل الصف الحالي على طول هنقول LAG(num
• وعلشان نعرف الرقم اللي في الصف اللي قبل الصف الحالي بصفين هنقول LAG(num,2
• في الحالتين هنرتب بالـ id جوا OVER لأننا عايزين نمشي على الصفوف بنفس ترتيبهم في الجدول، ومكتوب في السؤال إن الـ id هنا autoincrement و بيبدأ من 1، معنى كدة إن الـ id بيبدأ من 1 وكل صف بيزود 1 وبالتالي الصفوف في الجدول مترتبة بالـ id، فلما نقول جوا OVER رتب بالـ id فـ LAG هتمشي على الصفوف بنفس ترتيبهم في الجدول

ده الكود إلى الآن: 

  SELECT
	  num, 
	  LAG(num,1) OVER(ORDER BY id) AS prevNum, 
	  LAG(num,2) OVER(ORDER BY id) AS prevprevNum
  FROM Logs

ودي النتيجة اللي هتظهر لنا لو عملنا run للكود ده:

باقي نزود شرط إننا عايزين بس الأرقام اللي جت 3 مرات ورا بعض، فهنحط الكود ده جوا subquery ونزود الشرط ده

SELECT num AS ConsecutiveNums

FROM (
	  SELECT
		  num, 
		  LAG(num,1) OVER(ORDER BY id) AS prevNum, 
		  LAG(num,2) OVER(ORDER BY id) AS prevprevNum
	  FROM Logs
) sub

WHERE num = prevNum AND num = prevprevNum

لو عملنا run للكود هتظهر لنا النتيجة المطلوبة: 

بس بردو لو جينا نعمل submit هنلاقي فيه غلطة:

في الجدول اللي الكود غلط عنده رقم 3 جه 4 مرات ورا، وبما إن المطلوب هو إننا نجيب الأرقام اللي ظهرت 3 مرات ورا بعض أو أكتر فالمفروض النتيجة تكون 3، الكود بتاعنا طلع 3 فعلاََ بس طلع 3 مرتين.

زي ما عملنا في المثال اللي فات هنشوف ازاي الكود بيشتغل على الجدول ده. أول حاجة لكل صف هنجيب الأرقام اللي في الصفين اللي قبله: 

بعد كدة نختار الأرقام اللي جت في 3 صفوف ورا بعض، هنلاحظ هنا إن 3 جت 3 مرات ورا بعض مرتين زي ما هو موضح في الصورة، علشان كدة 3 طلعت مرتين في النتيجة:

حل المشكلة دي بسيط و هو إننا نزود DISTINCT بس عشان نقول إن لو جالنا الرقم متكرر عايزينه يظهر مرة واحدة بس: 

SELECT DISTINCT num AS ConsecutiveNums

FROM (
	  SELECT
		  num, 
		  LAG(num,1) OVER(ORDER BY id) AS prevNum, 
		  LAG(num,2) OVER(ORDER BY id) AS prevprevNum
	  FROM Logs
) sub

WHERE num = prevNum AND num = prevprevNum

ولو عملنا submit للكود هنلاقي الحل صح:

توضيح للكود: 

بجانب التطبيق على LAG ممكن نتعلم من السؤالين اللي فاتوا إننا منعتمدش على مثال واحد بس عشان نتأكد إن الكود شغال صح ونحاول نجرب الكود على أكتر من مثال ونفكر في أمثلة ممكن الكود يطلع نتايج غلط عليها ونجرب الكود بإيدنا على الأمثلة دي قبل ما نعمل submit. 

ملخص اللي قولناه لحد دلوقتي هو إن LAG دي window function لكل صف بتجيب قيمة عمود معين بس في الصف اللي قبله، ولو مفيش صف قبل الصف الحالي نتيجة LAG بتكون NULL، وLAG بتحدد أنهي صف جي قبل تاني عن طريق الترتيب اللي بنحدده في ORDER BY اللي جوا OVER. ونقدر كمان نجيب صفوف أبعد من الصف الحالي زي الصف اللي قبل الصف الحالي بصفين أو 3 عن طريق إننا نستخدم argument اسمه offset. ونقدر نستخدم argument اسمه default عشان نحدد قيمة تظهر في الأوقات اللي LAG نتيجتها هتكون NULL.

LEAD

بعد ما اتكلمنا عن LAG هنتكلم دلوقتي عن LEAD، LEAD بتشتغل عكس LAG بالظبط، زي ما قولنا إن LAG بتاخد عمود معين ولكل صف في الجدول بتجيب قيمة العمود ده بس الصف اللي قبله، LEAD بردو بتاخد عمود معين ولكل صف في الجدول بتجيب قيمة العمود ده بس في الصف اللي بعده. وبالتالي LEAD نفس فكرة LAG بس الفرق الوحيد إن LAG عند كل صف بتبص عالصف اللي قبله ولكن LEAD عند كل صف بتبص عالصف اللي بعده. ده توضيح لـ LEAD: 

هنلاحظ هنا إن LEAD نتيجتها بتكون NULL لما يكون مفيش صفوف بعد الصف الحالي، LEAD كمان بتاخد نفس الـ arguments بتاعة LAG، ممكن نستخدم الـ offset مع LEAD عشان نقول بص عالصف اللي بعدي بصفين أو 3 أو أكتر، وممكن نستخدم default عشان نحدد قيمة تظهر بدل NULL لو نتيجة LEAD كانت NULL. 

توضيح لاستخدام LEAD بقيمة أكبر من 1 للـ offset:

توضيح لاستخدام default:

Customer Subscriptions

في المثال ده معانا بيانات لموقع أو تطبيق باشتراك، الموقع فيه خطط للاشتراك وفي الجدول ده بنتابع تاريخ كل مستخدم وخطط اشتراكه من أول ما سجل في الموقع لحد دلوقت، نفس الفكرة بالظبط بتاعة جدول تغير أسعار المنتجات:

دي خطط الاشتراك اللي الموقع بيقدمها:

• trial:

 دي الخطة اللي المستخدم بيشترك فيها تلقائي أول ما يعمل أكونت على الموقع وهي عبارة عن اشتراك ببلاش لمدة أسبوع بعدها المستخدم يقدر يختار خطة اشتراك تانية لو حابب يكمل أو ميشتركش خالص ويقفل الأكونت بتاعه

• monthly: اشتراك شهري
• annual: اشتراك سنوي
• churn: معناها إن المستخدم قفل الأكونت بتاعه

بعد ما فهمنا البيانات اللي معانا عايزين نعرف فيه كام مستخدم قفل الأكونت بتاعه بعد ما الـ free trial خلصت على طول؟ بمعنى تاني كام مستخدم خطة الاشتراك بتاعته كانت trial وخطة الاشتراك اللي بعدها على طول كانت churn؟ 

علشان نجاوب على السؤال ده محتاجين نعرف لكل مستخدم إيه خطة الاشتراك اللي بعد الـ free trial، لو كانت churn هيكون موجود معانا في النتيجة، وبالتالي لكل مستخدم محتاجين نبص على الصف اللي قيمة العمود بتاع الـ plan فيه تساوي trial ونشوف الصف اللي وراه على طول قيمة plan فيه تساوي churn ولا لأ، إيه الـ function اللي بتجيبلنا قيمة عمود معين بس في صف بعدنا؟ LEAD. 

• هنبدأ أول خطوة وهي إننا نحدد العمود اللي عايزين نطبق عليه LEAD في الحالة دي عايزين نعمل LEAD على العمود بتاع الـ plan
• لأن LEAD دي window function فهنستخدم OVER 
• محتاجين نعرف لكل مستخدم إيه خطة الاشتراك اللي بعد الـ free trial، عشان قولنا لكل مستخدم يبقى هنستخدم PARTITION BY customer_id
• وبما إن الـ free trial هي أول خطة اشتراك لكل مستخدم فمحتاجين نمشي بترتيب التواريخ من الأقدم للأحدث فهنقول ORDER BY start_date 

SELECT 
	customer_id, 
	plan_name, 
	start_date,
	LEAD(plan_name) OVER(PARTITION BY customer_id ORDER BY start_date) AS next_plan
FROM customer_subscriptions

لما نعمل run للكود دي النتيجة اللي هتطلع:

محتاجين نختار بس الصفوف اللي فيها الخطة الأولى trial والتانية churn، فهنستخدم الـ query ده كـ subquery عشان نختار الصفوف اللي محتاجينها:

SELECT customer_id
FROM (
	SELECT 
		customer_id, 
		plan_name,
		start_date, 
		LEAD(plan_name) OVER(PARTITION BY customer_id ORDER BY start_date) AS next_plan
	FROM customer_subscriptions
) sub
WHERE plan_name = 'trial' AND next_plan = 'churn'

نتيجة الكود:

بما إن المطلوب هو عدد المستخدمين مش المستخدمين نفسهم فهنستخدم count:

SELECT COUNT(customer_id) AS number_of_customers_who_churned_after_trial
FROM (
	SELECT 
		customer_id, 
		plan_name, 
		start_date,
		LEAD(plan_name) OVER(PARTITION BY customer_id ORDER BY start_date) AS next_plan
	FROM customer_subscriptions
) sub
WHERE plan_name = 'trial' AND next_plan = 'churn'

النتيجة النهائية: 

توضيح للكود: 

في الختام

وبكدة نكون وصلنا لنهاية المقال، في المقال ده اتكلمنا عن  2 window functions وهم LAG, LEAD، وفهمنا كل function منهم بتشتغل ازاي وشوفنا أمثلة على استخداماتهم. أتمنى يكون المقال مفيد ومفهوم.

المصادر

• SQL Value Window Functions | LEAD, LAG, FIRST_VALUE, LAST_VALUE | #SQL Course #6
• The LAG Function and the LEAD Function in SQL | LearnSQL.com
• SQL Time-Series Window Functions: LEAD & LAG Tutorial
• LAG (Transact-SQL) - SQL Server | Microsoft Learn
• LEAD (Transact-SQL) - SQL Server | Microsoft Learn