الـ JOINs والتجميع: INNER و LEFT و GROUP BY و HAVING و window functions

أهلًا بيك. الدرس اللي فات قسّمنا البيانات على جداول، والنهارده نرجّعها مع بعض، ونطلع منها أرقام. seed dot sql فيه منصة صغيرة: خمس مستخدمين، وأربع كورسات، واشتراكات، ومدفوعات. INNER JOIN، أو JOIN بس: الصفوف اللي ليها تطابق في الجدولين. LEFT JOIN: كل صفوف الجدول الشمال، حتى اللي مالوش تطابق، والأعمدة التانية بتبقى NULL. و NOT EXISTS: الحاجات اللي مالهاش أي تطابق، ودي اسمها anti join. نشغّل الـ seed والـ queries مع بعض.

-- INNER JOIN: الصفوف اللي ليها تطابق في الجدولين بس
SELECT u.name, c.slug, e.progress
FROM enrollments e
JOIN users u ON u.id = e.user_id
JOIN courses c ON c.id = e.course_id
ORDER BY u.name, c.slug;

-- LEFT JOIN: كل المستخدمين، حتى اللي مالوش اشتراك
SELECT u.name, count(e.course_id) AS courses
FROM users u
LEFT JOIN enrollments e ON e.user_id = u.id
GROUP BY u.id, u.name
ORDER BY courses DESC, u.name;

-- anti join: كورسات محدش اشترك فيها
SELECT c.slug FROM courses c
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.course_id = c.id);

$ psql -q -f seed.sql -f queries.sql
 name | slug  | progress 
------+-------+----------
 سارة | node  |      100
 سارة | redis |       10
 عمر  | node  |       70
 كريم | sql   |        0
 مُنى  | node  |      100
 مُنى  | sql   |       40
(6 rows)

 name | courses 
------+---------
 سارة |       2
 مُنى  |       2
 عمر  |       1
 كريم |       1
 ليلى |       0
(5 rows)

  slug  
--------
 docker
(1 row)

الـ JOIN: ست صفوف، اشتراك لكل صف. الـ LEFT JOIN: الخمس مستخدمين، وليلى بـ zero، لأنها مالهاش اشتراكات. لو كان JOIN عادي، ليلى كانت هتختفي من التقرير خالص. ودوكر: محدش اشترك فيه. وخد بالك من count e dot course_id مش count star: مع LEFT JOIN، ليلى ليها صف واحد كله NULL، و count star هيعدّه واحد. التجميع. count star بيعد الصفوف، و count city بيعد اللي مش NULL، و count DISTINCT بيعد القيم المختلفة. GROUP BY بيقسّم الصفوف مجموعات، و HAVING بيفلتر المجموعات بعد التجميع، و WHERE بيفلتر الصفوف قبله. FILTER: تجميع بشرط جُوّه نفس الـ query. وتحت: الـ NULL في المقارنات. نشغّل.

-- count(*) بيعد الصفوف، و count(col) بيعد اللي مش NULL
SELECT count(*) AS users, count(city) AS with_city, count(DISTINCT city) AS cities FROM users;

-- GROUP BY و HAVING و FILTER
SELECT c.slug,
       count(*) AS students,
       count(*) FILTER (WHERE e.progress = 100) AS finished,
       round(avg(e.progress)) AS avg_progress
FROM courses c
JOIN enrollments e ON e.course_id = c.id
GROUP BY c.slug
HAVING count(*) >= 2
ORDER BY students DESC;

-- NULL في المقارنات
SELECT count(*) FILTER (WHERE city = NULL) AS eq_null,
       count(*) FILTER (WHERE city IS NULL) AS is_null,
       count(*) FILTER (WHERE city <> 'Cairo') AS not_cairo
FROM users;

$ psql -q -f seed.sql -f queries.sql
 users | with_city | cities 
-------+-----------+--------
     5 |         4 |      3
(1 row)

 slug | students | finished | avg_progress 
------+----------+----------+--------------
 node |        3 |        2 |           90
 sql  |        2 |        0 |           20
(2 rows)

 eq_null | is_null | not_cairo 
---------+---------+-----------
       0 |       1 |         2
(1 row)

خمسة مستخدمين، أربعة عندهم مدينة، وتلات مدن مختلفة. node فيه تلاتة واتنين خلصوا، و sql اتنين، و redis اتشال بالـ HAVING لأنه واحد. والـ NULL: equals NULL طلعت zero! لأن أي مقارنة مع NULL نتيجتها NULL، مش true. الصح IS NULL. و not Cairo طلعت اتنين بس، مع إن كريم مش من القاهرة! لأن مدينته NULL، و NULL مختلف عن Cairo؟ مجهول. الـ NULL بيعمل bugs في التقارير طول الوقت، خلي بالك منه. آخر أداة قوية: الـ window functions. GROUP BY بيقفل الصفوف في صف واحد، أما الـ window فبتحسب على مجموعة، وتسيب كل صف زي ما هو. WITH بتعمل CTE: query ليها اسم، زي جدول مؤقت. sum OVER ORDER BY month: مجموع تراكمي. و rank OVER PARTITION BY الكورس: ترتيب جُوّه كل كورس. نشغّل.

-- window functions: حساب على مجموعة، من غير ما تقفل الصفوف
WITH monthly AS (
  SELECT date_trunc('month', paid_at)::date AS month, sum(amount_cents) / 100 AS revenue
  FROM payments GROUP BY 1
)
SELECT month, revenue,
       sum(revenue) OVER (ORDER BY month) AS running_total
FROM monthly ORDER BY month;

SELECT u.name, c.slug, p.amount_cents / 100 AS paid,
       rank() OVER (PARTITION BY c.slug ORDER BY p.amount_cents DESC) AS rank_in_course
FROM payments p
JOIN users u ON u.id = p.user_id
JOIN courses c ON c.id = p.course_id
ORDER BY c.slug, rank_in_course;

$ psql -q -f seed.sql -f queries.sql
   month    | revenue | running_total 
------------+---------+---------------
 2026-09-01 |     630 |           630
 2026-10-01 |     600 |          1230
(2 rows)

 name | slug  | paid | rank_in_course 
------+-------+------+----------------
 مُنى  | node  |  200 |              1
 سارة | node  |  200 |              1
 عمر  | node  |  180 |              3
 سارة | redis |  150 |              1
 مُنى  | sql   |  250 |              1
 كريم | sql   |  250 |              1
(6 rows)

سبتمبر وأكتوبر، والـ running total بيجمع اللي قبله. والترتيب جُوّه كل كورس: في node، مُنى وسارة دفعوا نفس المبلغ فالاتنين واحد، وعمر تالت، لأن rank بتسيب مكان بعد التعادل. ولو عايز من غير فراغات، dense_rank. التحدي: تقرير الكورسات. لكل كورس: عدد الطلبة، واللي خلصوا، والإيراد، والترتيب بالإيراد. وفيه فخ مقصود: لو عملت JOIN لـ enrollments و payments مع بعض، كل اشتراك هيتضرب في كل دفعة، والأرقام هتتضاعف. فالإيراد يتحسب في CTE لوحده الأول. التفاصيل والأرقام في الكومنت.

الحل. CTE اسمه r: مجموع المدفوعات لكل كورس. من courses، LEFT JOIN للاشتراكات، و LEFT JOIN لـ r. count e dot user_id، و FILTER للي خلصوا. COALESCE للإيراد. و rank OVER بالإيراد. و GROUP BY بالـ slug و r dot total. نشغّل.

WITH r AS (
  SELECT course_id, sum(amount_cents) AS total
  FROM payments
  GROUP BY course_id
)
SELECT c.slug,
       count(e.user_id) AS students,
       count(*) FILTER (WHERE e.progress = 100) AS finished,
       COALESCE(r.total, 0) / 100 AS revenue,
       rank() OVER (ORDER BY COALESCE(r.total, 0) DESC) AS rank
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
LEFT JOIN r ON r.course_id = c.id
GROUP BY c.slug, r.total
ORDER BY rank, c.slug;

$ psql -q -f seed.sql -f queries.sql
  slug  | students | finished | revenue | rank 
--------+----------+----------+---------+------
 node   |        3 |        2 |     580 |    1
 sql    |        2 |        0 |     500 |    2
 redis  |        1 |        0 |     150 |    3
 docker |        0 |        0 |       0 |    4
(4 rows)

node الأول بخمسمية وتمانين، و docker بالأصفار في الآخر. الدرس الجاي: JSONB: إمتى تحط JSON جُوّه Postgres، وإمتى لا.