تعلّمك هذه الدورة من أكاديمية إشراقة 7 تصميم قاعدة بيانات علائقية وتغذيتها والاستعلام عنها بلغة SQL، وصولًا إلى دوال النوافذ والاستعلامات التحليلية. وتستعمل الفصول الاثنا عشر القاعدة نفسها، وهي النشاط التجاري لموزّع معدات مكتبية، وتنتهي بمشروع متكامل.
المسار التعليمي
| المستوى | الفصول |
|---|---|
| مبتدئ | 1. النموذج العلائقي · 2. التثبيت · 3. الجداول والقيود · 4. SELECT وWHERE وORDER BY |
| متوسط | 5. التجميع · 6. الربط · 9. INSERT وUPDATE وDELETE |
| متقدم | 7. الاستعلامات الفرعية وCTE · 8. دوال النوافذ · 10. المعاملات والفهارس · 11. SQL للتحليل · 12. المشروع النهائي |
المحرّك والملفات
PostgreSQL هو المحرّك الرئيسي للدورة. ويُقدَّم كل سكربت أيضًا لـ MySQL/MariaDB ولـ SQLite مع التكييفات اللازمة في الصياغة. ويقدّم قسم التنزيل: الدليل، ودفتر التمارين، والحلول، وتوثيق المخطط، ودليل التثبيت، ودليل المشروع بصيغة PDF؛ وسكربتات الإنشاء والإدراج والتمارين والحلول والمشروع؛ وبيانات CSV؛ وأرشيفًا كاملًا.
المستوى 1 · مبتدئ — الفصل 1
مدخل إلى قواعد البيانات العلائقية
تحفظ أغلب تطبيقات التسيير بياناتها في قاعدة بيانات علائقية وتستعلم عنها بلغة SQL. وفهم كيفية توزيع البيانات على جداول مرتبط بعضها ببعض هو أول مهارة يحتاجها المحلّل والمطوّر.
قاعدة البيانات العلائقية
تنظّم قاعدة البيانات العلائقية المعلومات في جداول، يصف كل منها نوعًا واحدًا من الكائنات: العملاء، أو المنتجات، أو الطلبيات. ويمثّل الصف (أو السجلّ) كائنًا واحدًا، ويصف العمود (أو الخاصية) إحدى خصائصه بنوع محدّد: نص، أو عدد، أو تاريخ. ويحفظ نظام إدارة قواعد البيانات – مثل PostgreSQL وMySQL وSQLite وSQL Server – هذه الجداول، ويضمن اتساقها، وينفّذ الاستعلامات المكتوبة بلغة SQL (Structured Query Language).
المفاتيح الأساسية والمفاتيح الخارجية
يحدّد المفتاح الأساسي كل صف تحديدًا فريدًا: customer_id في customers، وorder_id في orders. أما المفتاح الخارجي فعمود يشير إلى المفتاح الأساسي لجدول آخر: إذ يشير orders.customer_id إلى customers.customer_id. وعندئذ يرفض النظام أي طلبية لعميل غير موجود: وهذه هي السلامة المرجعية.
لماذا عدة جداول؟
في جدول البيانات، كثيرًا ما يُنسخ اسم العميل ومدينته في كل صف مبيعات. فإذا انتقل العميل، وجب تصحيح مئات الصفوف، وظهرت نسخ متناقضة. ويقوم التطبيع على حفظ كل معلومة مرة واحدة وربط الجداول بالمفاتيح: فتُكتب مدينة العميل مرة واحدة في customers.
مخطط الحالة الدراسية
| الجدول | الصف الواحد = | المفتاح الأساسي | المفاتيح الخارجية |
|---|---|---|---|
orders |
طلبية | order_id |
customer_id وrep_id وchannel_code |
order_lines |
سطر طلبية | (order_id, line_no) |
order_id وproduct_id |
customers |
عميل | customer_id |
segment_code وregion_code |
products |
منتج | product_id |
category_code |
sales_reps |
بائع | rep_id |
region_code |
targets |
شهر × جهة | (month, region_code) |
region_code |
regions وsegments وchannels وcategories |
رمز وتسميته | الرمز | — |
مفتاح order_lines مركّب: فرقم الطلبية وحده لا يكفي، لأن للطلبية عدة أسطر. وتضم البيانات 3,307 طلبية و8,472 سطر طلبية لـ120 عميلًا.
أولى الاستعلامات
SELECT COUNT(*) AS n_orders FROM orders;
SELECT region_code, region_name
FROM regions
ORDER BY region_code;
يحدّد SELECT الأعمدة المطلوبة، وFROM الجدول، وORDER BY ترتيب العرض. وتعدّ COUNT(*) الصفوف، ويمنح AS اسمًا للعمود المحسوب. وتصف SQL النتيجة المنتظرة لا طريقة الحصول عليها: فالنظام هو الذي يختار كيفية قراءة البيانات.
المستوى 1 · مبتدئ — الفصل 2
التثبيت وبيئة العمل
للتدرّب نحتاج إلى نظام لإدارة قواعد البيانات، وأداة لكتابة الاستعلامات، وقاعدة محمَّلة ببيانات الحالة الدراسية. يثبّت هذا الفصل PostgreSQL، المحرّك الرئيسي للدورة، ويعرض البديلين MySQL وSQLite، ويتحقق من التحميل.
ثلاثة محرّكات ولغة SQL واحدة
| المحرّك | الخصائص | الإصدار المختبَر |
|---|---|---|
| PostgreSQL | خادم متكامل شديد المطابقة لمعيار SQL، وهو المحرّك الرئيسي للدورة | 16.15 |
| MySQL / MariaDB | خادم واسع الانتشار في الويب (WordPress مثلًا) | MariaDB 10.11.14 |
| SQLite | قاعدة في ملف واحد دون خادم، مثالية للتدرّب | 3.45.1 (CLI) / 3.53.1 (Python) |
نُفّذت كل استعلامات الدورة على المحرّكات الثلاثة وقورنت نتائجها. ويُشار إلى الفوارق القليلة في الصياغة (التواريخ والأنواع)، وتُرفق سكربتات مكيَّفة لـ MySQL وSQLite.
تثبيت PostgreSQL
- نزّل برنامج التثبيت الرسمي من postgresql.org (لـ Windows وmacOS) أو استعمل مدير الحزم (لـ Linux).
- سجّل كلمة مرور المستخدم
postgresالتي اخترتها أثناء التثبيت. - استعمل أداة سطر الأوامر
psqlالمرفقة، أو أداة رسومية مثل pgAdmin أو DBeaver.
إنشاء قاعدة الدورة
CREATE DATABASE ishraqa7_sql ENCODING 'UTF8' TEMPLATE template0;
\c ishraqa7_sql
\i postgresql/01_schema.sql
\i postgresql/02_data.sql
الأوامر التي تبدأ بالرمز \ خاصة بـ psql: فيتصل \c بقاعدة، وينفّذ \i ملفًا. واعمل دائمًا في قاعدة مخصّصة للدورة.
الاستعلام عن الفهرس
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
ORDER BY table_name;
يصف المخطط information_schema، المعرَّف في معيار SQL، القاعدةَ نفسها: الجداول والأعمدة والقيود. ويوجد في PostgreSQL وMySQL، بينما توفّر SQLite بدلًا منه الجدول sqlite_master. فنحصل على 10 جداول و10 مفاتيح خارجية.
التحقق من التحميل
SELECT ROUND(SUM(l.quantity * l.unit_price * (1 - l.discount_rate)), 2) AS net_all_lines
FROM order_lines l;
يؤكد مجموع مرجعي (30,546,303.45 درهم لكل الأسطر بجميع الحالات) والتاريخان الأقصيان (2024-01-01 و2025-12-31) أن التحميل كامل. فقارن دائمًا أي تحميل بقيم معروفة.
المستوى 1 · مبتدئ — الفصل 3
الجداول والأنواع والقيود
القاعدة الموثوقة ترفض البيانات غير الصحيحة لحظة إدخالها. وأنواع الأعمدة والقيود – المفتاح الأساسي والمفتاح الخارجي وNOT NULL وUNIQUE وCHECK – هي الضمانات التي تجعل التحليلات جديرة بالثقة.
اختيار الأنواع
| المعطى | النوع في PostgreSQL | السبب |
|---|---|---|
| المعرّف «O00001» | VARCHAR(6) |
نص: لا حساب عليه، وتُحفظ الأصفار |
| المبلغ | NUMERIC(10,2) |
عدد عشري دقيق دون أخطاء تقريب ثنائية |
| نسبة الخصم | NUMERIC(4,3) |
ثلاثة أرقام عشرية: 0.050 |
| الكمية | INTEGER |
عدد صحيح |
| التاريخ | DATE |
حسابات ومقارنات تواريخ موثوقة |
تجنّب أنواع الفاصلة العائمة (REAL وDOUBLE) للمبالغ المالية، لأنها تخزّن قيمة تقريبية ثنائية. وتستعمل MySQL النوع DECIMAL المكافئ لـ NUMERIC. أما SQLite فتطبّق «ألفات» مرنة وتخزّن التواريخ نصوصًا بصيغة ISO.
التصريح بالقيود
CREATE TABLE order_lines (
order_id VARCHAR(6) NOT NULL,
line_no SMALLINT NOT NULL,
product_id VARCHAR(4) NOT NULL,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
discount_rate NUMERIC(4,3) NOT NULL DEFAULT 0 CHECK (discount_rate BETWEEN 0 AND 0.5),
PRIMARY KEY (order_id, line_no),
CONSTRAINT fk_lines_order FOREIGN KEY (order_id) REFERENCES orders (order_id),
CONSTRAINT fk_lines_product FOREIGN KEY (product_id) REFERENCES products (product_id)
);
NOT NULL: القيمة إلزامية.CHECK: قاعدة مهنية يُتحقَّق منها عند كل كتابة (كمية موجبة، وخصم بسقف).DEFAULT: القيمة المستعملة إذا لم يُقدَّم العمود.CONSTRAINT الاسم: تسمية القيد تجعل رسائل الخطأ مقروءة.
القيود أثناء العمل
INSERT INTO order_lines (order_id, line_no, product_id, quantity, unit_price, discount_rate)
VALUES ('O00001', 99, 'P01', -2, 2450, 0);
ترفض PostgreSQL الإدراج: «new row for relation « order_lines » violates check constraint « order_lines_quantity_check »». ومع منتج غير موجود تصبح الرسالة: «insert or update on table « order_lines » violates foreign key constraint « fk_lines_product »». وتجيب SQLite: «CHECK constraint failed: quantity > 0». فالرسالة تسمّي القيد المعني، وهنا تظهر فائدة التسمية.
في الجدول orders، يتعلق القيد ck_orders_ship بعمودين: تاريخ الشحن فارغ أو لاحق لتاريخ الطلبية. ويصرّح الجدول regions أيضًا بأن region_name فريد (UNIQUE): فلا يمكن لجهتين أن تحملا التسمية نفسها.
تعديل جدول
ALTER TABLE price_list ADD COLUMN unit_cost NUMERIC(10,2);
تضيف ALTER TABLE عمودًا أو قيدًا أو تعدّله أو تحذفه دون إعادة إنشاء الجدول. وعلى جدول ضخم قيد الخدمة، تحتاج هذه العمليات إلى تخطيط، لأنها قد تعطّل الكتابة أثناء تنفيذها.
المستوى 1 · مبتدئ — الفصل 4
الاستعلام: SELECT وWHERE وORDER BY
قراءة الصفوف والأعمدة المناسبة هي العملية الأكثر تكرارًا في SQL. يتناول هذا الفصل الاختيار، والتصفية بالشروط، والترتيب، وتحديد عدد النتائج.
الاختيار والتصفية
SELECT order_id, order_date, customer_id, channel_code
FROM orders
WHERE status = 'cancelled'
AND order_date BETWEEN '2025-12-01' AND '2025-12-31'
ORDER BY order_date, order_id;
يُرجع الاستعلام 3 طلبيات ملغاة في دجنبر 2025. وترتيب الكتابة ثابت: SELECT ثم FROM ثم WHERE ثم ORDER BY. وتُكتب النصوص والتواريخ بين علامتي تنصيص مفردتين.
عوامل الشرط
| العامل | مثال |
|---|---|
= و<> و< و>= |
status <> 'cancelled' |
AND وOR وNOT |
segment_code = 'EDU' AND region_code = 'S' |
IN |
channel_code IN ('WEB', 'PAR') |
BETWEEN |
list_price BETWEEN 100 AND 500 (الحدّان مشمولان) |
LIKE |
customer_name LIKE '%Solutions%' |
IS NULL |
ship_date IS NULL |
يُقيَّم AND قبل OR، فاستعمل الأقواس متى جمعت بينهما. والقيمة المفقودة (NULL) لا تساوي أي شيء، ولا حتى NULL: فتُختبر بواسطة IS NULL لا بواسطة = NULL.
تصفية سنة
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
يعمل هذا المجال نصف المفتوح في كل المحرّكات ومع كل أنواع التواريخ، بما فيها ما يتضمن الساعة. ويتيح أيضًا للنظام استعمال فهرس على التاريخ (الفصل 10)، وهو ما يمنعه تطبيق دالة على العمود.
الترتيب والتحديد
SELECT product_id, product_name, list_price
FROM products
ORDER BY list_price DESC, product_id
LIMIT 5;
يرتّب DESC تنازليًا، ويفصل مفتاح الترتيب الثاني بين القيم المتساوية، فتصبح النتيجة مستقرة. وتقبل PostgreSQL وMySQL وSQLite العبارة LIMIT، بينما يكتب معيار SQL العبارة FETCH FIRST 5 ROWS ONLY (وتستعمل SQL Server العبارة TOP).
المستوى 2 · متوسط — الفصل 5
التجميع: GROUP BY وHAVING
نادرًا ما تتعلق أسئلة التسيير بصف واحد: كم حسب السنة؟ وحسب الجهة؟ وحسب القناة؟ وتحوّل دوال التجميع والتجميع حسب المجموعات آلاف الصفوف إلى بضعة مؤشرات.
دوال التجميع
| الدالة | النتيجة | القيم NULL |
|---|---|---|
COUNT(*) |
عدد الصفوف | تُحسب |
COUNT(col) |
عدد القيم غير الفارغة | تُتجاهل |
COUNT(DISTINCT col) |
عدد القيم المميَّزة | تُتجاهل |
SUM وAVG |
المجموع والمتوسط | تُتجاهل |
MIN وMAX |
القيم القصوى | تُتجاهل |
التجميع
SELECT EXTRACT(YEAR FROM o.order_date) AS year,
COUNT(DISTINCT o.order_id) AS n_orders,
ROUND(SUM(l.quantity * l.unit_price * (1 - l.discount_rate)), 2) AS net_sales
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status = 'delivered'
GROUP BY EXTRACT(YEAR FROM o.order_date)
ORDER BY year;
تُكتب قاعدة حساب الحالة الدراسية مباشرة في الاستعلام: صافي المبيعات = الكمية × سعر الوحدة × (1 − الخصم)، للطلبيات المسلَّمة فقط. النتيجة: 2024 = 13,556,438.85 درهم (1,480 طلبية)، و2025 = 15,097,590.45 درهم (1,628 طلبية). وCOUNT(DISTINCT o.order_id) ضروري، لأن الطلبية تظهر مرة لكل سطر بعد الربط (الفصل 6).
القاعدة الذهبية: كل عمود في SELECT غير مجمَّع يجب أن يرد في GROUP BY. وفي SQLite تُستخرج السنة بواسطة CAST(strftime('%Y', order_date) AS INTEGER)، وتتضمن سكربتات SQLite المرفقة هذه الصيغة.
تصفية المجموعات: HAVING
SELECT p.category_code, ROUND(SUM(l.quantity * l.unit_price * (1 - l.discount_rate)), 2) AS net_sales
FROM order_lines l
JOIN orders o ON o.order_id = l.order_id
JOIN products p ON p.product_id = l.product_id
WHERE o.status = 'delivered'
AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
GROUP BY p.category_code
HAVING SUM(l.quantity * l.unit_price * (1 - l.discount_rate)) > 3500000
ORDER BY net_sales DESC;
تصفّي WHERE الصفوف قبل التجميع، وتصفّي HAVING المجموعات بعده. لذا لا يمكن أن يرد شرط على مجمَّع في WHERE. والترتيب المنطقي للتنفيذ: FROM ← WHERE ← GROUP BY ← HAVING ← SELECT ← ORDER BY.
المتوسطات: انتبه إلى المقام
يُحسب متوسط السلة بوصفه مجموع المبيعات ÷ عدد الطلبيات: SUM(net) / COUNT(DISTINCT order_id). أما AVG على الأسطر فيعطي متوسط قيمة السطر، وهو مقدار آخر. فحدّد دائمًا ما يمثّله المقام.
المستوى 2 · متوسط — الفصل 6
الربط بين الجداول (JOIN)
تتوزع المعلومات المفيدة على عدة جداول: المبلغ في الأسطر، والتاريخ في الطلبيات، والجهة لدى العميل. ويجمعها الربط. وإتقانه يعني تجنّب التكرارات والصفوف المفقودة.
الربط الداخلي
SELECT r.region_name, ROUND(SUM(l.quantity * l.unit_price * (1 - l.discount_rate)), 2) AS net_sales
FROM order_lines l
JOIN orders o ON o.order_id = l.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN regions r ON r.region_code = c.region_code
WHERE o.status = 'delivered'
AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
GROUP BY r.region_name
ORDER BY net_sales DESC;
تقرن JOIN … ON (أو INNER JOIN) كل صف بصفوف الجدول الآخر التي تحقّق الشرط. ونتّبع مسار المفاتيح: السطر ← الطلبية ← العميل ← الجهة. وتختصر الأسماء المستعارة (l وo وc وr) الكتابة، وتزيل أي لبس في الأعمدة المتشابهة الأسماء. وتأتي جهة الوسط في الصدارة بمبلغ 6,356,820.60 درهم، وجهة الشرق في المؤخرة بمبلغ 2,164,279.95 درهم.
الربط الخارجي الأيسر
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.order_date >= '2025-10-01' AND o.order_date < '2026-01-01'
WHERE o.order_id IS NULL
ORDER BY c.customer_id;
يحتفظ LEFT JOIN بكل صفوف الجدول الأيسر، وحين لا يوجد مقابل تكون أعمدة الجدول الأيمن NULL. ثم تعطي التصفية على o.order_id IS NULL العملاء الذين ليست لهم طلبية في الربع الرابع من 2025: وهذا ربط عكسي. وانتبه: يجب أن يبقى شرط التاريخ في ON؛ فلو وُضع في WHERE لحذف الصفوف التي لا طلبيات لها، ولتحوّل الربط إلى ربط داخلي.
العدديّة: فخّ التكرارات
ربط جدول من جهة «الواحد» (الطلبيات) بجدول من جهة «المتعدد» (الأسطر) يكرّر الطلبية على كل سطر. وجمع قيمة على مستوى الطلبية بعد الربط يحسبها عدة مرات. فتمسّك بعادتين: العدّ بواسطة COUNT(DISTINCT …)، والتحقق من عدد الصفوف قبل الربط وبعده.
| الربط | الصفوف المحفوظة |
|---|---|
INNER JOIN |
المتقابلة فقط |
LEFT JOIN |
كل صفوف اليسار، إضافة إلى المتقابلة |
RIGHT JOIN |
مقابل LEFT (قليل الاستعمال) |
FULL JOIN |
الكل من الجهتين (غير متوفر في MySQL) |
المستوى 3 · متقدم — الفصل 7
الاستعلامات الفرعية وتعابير الجداول المشتركة (CTE)
تُحلّ بعض الأسئلة على مراحل: حساب متوسط، ثم مقارنة كل عميل به. وتتيح الاستعلامات الفرعية وتعابير الجداول المشتركة (CTE) كتابة هذه المراحل بوضوح.
تعبير CTE: تسمية مرحلة
WITH sales_2025 AS (
SELECT EXTRACT(MONTH FROM o.order_date) AS month_no,
l.quantity * l.unit_price * (1 - l.discount_rate) AS net
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status = 'delivered'
AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
)
SELECT month_no, ROUND(SUM(net), 2) AS net_sales
FROM sales_2025
GROUP BY month_no
ORDER BY month_no;
تعرّف WITH الاسم AS (استعلام) جدولًا مؤقتًا مرئيًا في بقية الاستعلام. ويمكن تسلسل عدة تعابير CTE تفصل بينها فواصل. فتُقرأ الشيفرة من الأعلى إلى الأسفل كأنها استدلال. يناير 2025: 1,250,699.95 درهم.
الاستعلام الفرعي العددي
WITH by_customer AS (
SELECT o.customer_id, SUM(l.quantity * l.unit_price * (1 - l.discount_rate)) AS net
FROM orders o JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status = 'delivered'
AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
GROUP BY o.customer_id
)
SELECT COUNT(*) AS n_above_avg
FROM by_customer
WHERE net > (SELECT AVG(net) FROM by_customer);
يُستعمل الاستعلام الفرعي الذي يُرجع قيمة واحدة كأنه ثابت. وهنا يشتري 42 عميلًا أكثر من متوسط العميل (125,813.25 درهم).
EXISTS وNOT EXISTS
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM order_lines l
JOIN orders o ON o.order_id = l.order_id
WHERE l.product_id = p.product_id
AND o.status = 'delivered'
AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
);
الاستعلام الفرعي هنا مترابط: إذ يشير إلى الصف الحالي p. وتكون NOT EXISTS صحيحة حين لا يُرجع شيئًا. وخلافًا لـ NOT IN، تبقى صحيحة مع وجود قيم NULL في الاستعلام الفرعي. وتضم النتيجة 0 صفوف: فكل المنتجات بيعت في 2025. والنتيجة الفارغة جواب صالح.
متى نستعمل كلًّا منها؟
- قيمة مرجعية (متوسط، حد أقصى): استعلام فرعي عددي.
- الوجود أو الغياب:
EXISTS/NOT EXISTS. - عدة مراحل، أو نتيجة تُستعمل مرتين: CTE.
- أعمدة من جدول آخر: الربط.
المستوى 3 · متقدم — الفصل 8
دوال النوافذ (Window functions)
تحسب دوال النوافذ على مجموعة من الصفوف المرتبطة بالصف الحالي – مجموع تراكمي، أو رتبة، أو قيمة سابقة – دون تجميع الصفوف. وهي تجيب عن أكثر أسئلة التحليل تكرارًا: الترتيب، والتطوّر، والمتوسطات المتحركة.
التجميع دون دمج الصفوف
WITH m AS (
SELECT EXTRACT(YEAR FROM o.order_date) AS year,
EXTRACT(MONTH FROM o.order_date) AS month_no,
SUM(l.quantity * l.unit_price * (1 - l.discount_rate)) AS net
FROM orders o JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status = 'delivered'
GROUP BY EXTRACT(YEAR FROM o.order_date), EXTRACT(MONTH FROM o.order_date)
)
SELECT month_no,
ROUND(net, 2) AS net_sales,
ROUND(SUM(net) OVER (ORDER BY month_no), 2) AS running_total,
ROUND(AVG(net) OVER (ORDER BY month_no ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3m
FROM m
WHERE year = 2025
ORDER BY month_no;
تجمع SUM(net) OVER (ORDER BY month_no) الأشهر من البداية إلى الصف الحالي: وهذا مجموع تراكمي. وفي نهاية يونيو 2025 يبلغ 6,503,297.90 درهم. ويقصر الإطار ROWS BETWEEN 2 PRECEDING AND CURRENT ROW النافذة على الأشهر الثلاثة الأخيرة: متوسط متحرك قدره 954,100.18 درهم في مارس. وتبقى كل الصفوف معروضة، خلافًا لـ GROUP BY.
التقسيم والترتيب
RANK() OVER (PARTITION BY region_code ORDER BY net DESC) AS rank_in_region
تعيد PARTITION BY الحساب من جديد لكل جهة. وترقّم ROW_NUMBER دون تساوٍ، وتعطي RANK الرتبة نفسها للمتساويين ثم تقفز (1، 1، 3)، ولا تقفز DENSE_RANK (1، 1، 2). وللاحتفاظ بالأول في كل مجموعة نصفّي على الرتبة داخل CTE، لأن دالة النافذة لا يمكن أن ترد في WHERE.
المقارنة عبر الزمن: LAG
ROUND(100 * (net / LAG(net, 12) OVER (ORDER BY year, month_no) - 1), 1) AS growth_pct
تُرجع LAG(net, 12) القيمة التي تسبق باثني عشر صفًّا: أي الشهر نفسه من السنة السابقة، شرط وجود كل الأشهر. ويسجّل يناير 2025 نموًّا بنسبة 25.4%، ويتغيّر فبراير بنسبة -17.2%. وتقرأ LEAD الصف التالي.
التوفر
تتوفر دوال النوافذ في PostgreSQL وMySQL 8 وMariaDB 10.2 وما بعده وSQLite 3.25 وما بعده، وقد اختُبرت على المحرّكات الثلاثة للدورة.
المستوى 2 · متوسط — الفصل 9
تعديل البيانات: INSERT وUPDATE وDELETE
لا تكفي قراءة البيانات: إذ يجب أيضًا إضافتها وتصحيحها وحذفها دون خطأ. يعرض هذا الفصل أوامر التعديل والاحتياطات التي تمنع الأضرار، على جدول عمل يُنشأ لهذا الغرض.
العمل على نسخة
CREATE TABLE price_list AS
SELECT product_id, product_name, category_code, list_price FROM products;
تنشئ CREATE TABLE … AS SELECT جدولًا من نتيجة استعلام. وتتيح النسخة (30 صفًّا) التدرّب دون المساس بالكتالوج المرجعي. ولا ترث قيود الجدول الأصلي.
الإدراج
INSERT INTO price_list (product_id, product_name, category_code, list_price)
VALUES ('P31', 'مصباح مكتب LED', 'FUR', 390.00),
('P32', 'لوحة فأرة مريحة', 'ITE', 120.00);
سمِّ الأعمدة دائمًا: فيبقى الترتيب صحيحًا حتى لو تغيّرت بنية الجدول. ويمكن إدراج عدة صفوف في أمر واحد. وتُدرج INSERT INTO … SELECT … نتيجة استعلام.
التعديل
UPDATE price_list
SET list_price = ROUND(list_price * 1.03, 2)
WHERE category_code = 'SUP';
دون WHERE تُعدَّل كل الصفوف. وقبل أي UPDATE، نفّذ SELECT بالشرط WHERE نفسه للتحقق من النطاق. ويمكن للتحديث أيضًا أن يقرأ جدولًا آخر عبر استعلام فرعي مترابط:
UPDATE price_list
SET unit_cost = (SELECT p.unit_cost FROM products p WHERE p.product_id = price_list.product_id);
بالنسبة إلى المنتجين المضافين، غير الموجودين في products، لا يجد الاستعلام الفرعي شيئًا: فتبقى التكلفة NULL. والتحقق من هذه الحالة جزء من التحديث.
الحذف
DELETE FROM price_list WHERE product_id IN ('P31', 'P32');
DROP TABLE price_list;
تحذف DELETE صفوفًا، وتحذف DROP TABLE الجدول نفسه. وفي جدول يشير إليه مفتاح خارجي، ترفض القاعدة حذف صف ما يزال مستعملًا: وهذه حماية لا ينبغي الالتفاف عليها.
منهجية آمنة
- كتابة
SELECTالذي يستهدف الصفوف وتنفيذه. - التحقق من عدد الصفوف المعنية.
- تنفيذ التعديل داخل معاملة (الفصل 10).
- التحقق من النتيجة، ثم الاعتماد (
COMMIT) أو الإلغاء (ROLLBACK).
المستوى 3 · متقدم — الفصل 10
المعاملات والفهارس وتحسين الأداء
يجب أن تبقى القاعدة المشتركة متّسقة حتى حين تفشل عملية في منتصفها، وسريعة حين تكبر الأحجام. وتضمن المعاملات الاتساق، وتضمن الفهارس وقراءة خطط التنفيذ الأداء.
المعاملات
تجمع المعاملة (transaction) عدة عمليات تنجح معًا أو تفشل معًا. وتلخّص خصائصها الكلمة المختصرة ACID: الذرّية (الكل أو لا شيء)، والاتساق (تبقى القيود محترمة)، والعزل (لا تتداخل المعاملات المتزامنة)، والديمومة (بعد الاعتماد يصمد التعديل أمام أي عطل).
BEGIN;
INSERT INTO orders VALUES ('O99997', '2025-12-31', 'C001', 'R01', 'WEB', 'delivered', '2026-01-02');
INSERT INTO order_lines VALUES ('O99997', 1, 'P99', 1, 100, 0); -- المنتج P99 غير موجود: يفشل الإدراج
ROLLBACK;
SELECT COUNT(*) AS n_kept FROM orders WHERE order_id = 'O99997';
يخرق الإدراج الثاني المفتاح الخارجي، فتلغي ROLLBACK الإدراج الأول أيضًا. ويعطي العدّ النهائي 0: فلم تبقَ في القاعدة أي طلبية بلا أسطر. وفي PostgreSQL، بعد الخطأ، ترفض المعاملة أي أمر آخر حتى ROLLBACK، وتكتب MySQL العبارة START TRANSACTION بدل BEGIN.
الفهارس
الفهرس بنية مرتّبة (غالبًا شجرة B) تتيح إيجاد الصفوف دون قراءة الجدول كله، كفهرس الكتاب. وتُفهرس المفاتيح الأساسية وقيود UNIQUE تلقائيًا. وتُضاف الفهارس إلى الأعمدة التي يكثر استعمالها في التصفية أو الربط.
EXPLAIN SELECT COUNT(*) FROM orders WHERE order_date = '2025-09-15';
CREATE INDEX ix_orders_date ON orders (order_date);
ANALYZE orders;
EXPLAIN SELECT COUNT(*) FROM orders WHERE order_date = '2025-09-15';
| المحرّك | دون فهرس | مع الفهرس |
|---|---|---|
| PostgreSQL | Seq Scan on orders |
Index Only Scan using ix_orders_date on orders |
MariaDB (العمود type) |
ALL |
ref |
| SQLite | SCAN orders |
SEARCH orders USING COVERING INDEX ix_orders_date (order_date=?) |
دون فهرس يقرأ كل محرّك الجدول كله، ومع الفهرس يصل مباشرة إلى طلبيات اليوم البالغ عددها 9. والنتيجة متطابقة، ولا يتغيّر إلا المسار. وعلى جدول من بضعة آلاف من الصفوف يكون الربح الزمني غير محسوس، لكنه يصبح حاسمًا مع ملايين الصفوف.
كلفة الفهارس
يسرّع كل فهرس القراءة لكنه يبطئ الكتابة، التي يجب أن تحدّثه، ويشغل مساحة. لذا تُفهرس الأعمدة المستعملة فعلًا في التصفية والربط، لا كل الأعمدة.
قواعد التحسين
- اختيار الأعمدة المفيدة فقط بدل
SELECT *. - التصفية مبكرًا في
WHEREبشروط يمكنها استعمال فهرس (دون دالة على العمود المصفّى). - فهرسة المفاتيح الخارجية المستعملة في الربط.
- قراءة خطة التنفيذ (
EXPLAIN، وEXPLAIN ANALYZEفي PostgreSQL) قبل الاستنتاج.
المستوى 3 · متقدم — الفصل 11
SQL لتحليل البيانات
SQL أداة تحليل أيضًا: جداول متقاطعة، ونسب، ومقارنة بالأهداف، وهوامش، ومدد. يجمع هذا الفصل التقنيات السابقة لإنتاج المؤشرات التي تنتظرها الإدارة التجارية، مع الإشارة إلى الفوارق بين المحرّكات.
جدول متقاطع بواسطة CASE
SELECT c.region_code,
ROUND(SUM(CASE WHEN EXTRACT(QUARTER FROM o.order_date) = 1
THEN l.quantity * l.unit_price * (1 - l.discount_rate) ELSE 0 END), 0) AS q1,
ROUND(SUM(CASE WHEN EXTRACT(QUARTER FROM o.order_date) = 4
THEN l.quantity * l.unit_price * (1 - l.discount_rate) ELSE 0 END), 0) AS q4
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
GROUP BY c.region_code;
تُرجع CASE WHEN … THEN … ELSE … END قيمة حسب شرط. وحين توضع داخل SUM توزّع المبالغ على أعمدة: وهذا مكافئ الجدول المحوري، وتعمل هذه الصيغة في كل المحرّكات. الجهة C: 1,285,308 درهم في الربع الأول، و1,915,340 درهم في الرابع.
نسب دقيقة
ROUND(100.0 * SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancel_pct
بين عددين صحيحين تُجري PostgreSQL وSQLite قسمة صحيحة (7 / 2 = 3). والضرب في 100.0 يفرض حسابًا عشريًا. وتنتقل نسبة الإلغاء في قناة WEB من 3.1% في 2024 إلى 4.5% في 2025.
المحقَّق مقابل الهدف
تأتي المبيعات من الأسطر، والأهداف من جدول آخر بحبيبية أخرى. فنجمّع كل مصدر في CTE خاص به على المستوى نفسه (الجهة)، ثم نربط النتيجتين. أما الربط قبل التجميع فيضاعف الأهداف بعدد أسطر المبيعات. وتبلغ الجهة S نسبة 133.3% من هدفها السنوي، والجهة C نسبة 89.6%.
الفوارق بين المحرّكات
| الحاجة | PostgreSQL | MySQL / MariaDB | SQLite |
|---|---|---|---|
| سنة التاريخ | EXTRACT(YEAR FROM d) |
EXTRACT(YEAR FROM d) |
strftime('%Y', d) |
| الفارق بالأيام | d2 - d1 |
DATEDIFF(d2, d1) |
julianday(d2) - julianday(d1) |
| الوسيط | PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) |
غير متوفر مباشرة | غير متوفر مباشرة |
| دمج النصوص | a || b |
CONCAT(a, b) |
a || b |
في PostgreSQL يبلغ وسيط الطلبيات المسلَّمة في 2025 7,131.70 درهم، مقابل متوسط قدره 9,273.70 درهم. وفي MySQL يكون || افتراضيًا عامل «أو» منطقيًا: وهو مصدر خطأ تقليدي عند الترحيل.
المستوى 3 · متقدم — الفصل 12
المشروع النهائي: قاعدة للتحليل التجاري
يطبّق المشروع النهائي الدورة كلها على قاعدة الحالة الدراسية: توثيق المخطط، وإنشاء عرض للتحليل، وحساب المؤشرات الرئيسية، وتحليل الفوارق، والتحقق من اتساق النتائج. وهو يطابق العمل المنتظر من محلّل SQL في المؤسسة.
الجهة الطالبة والسؤال
مشروع أكاديمية إشراقة 7: قاعدة للتحليل التجاري. تريد الإدارة التجارية لموزّع معدات مكتبية (حالة دراسية، بيانات تدريبية) مؤشرات موثوقة قابلة لإعادة الحساب في أي وقت، وأجوبة دقيقة: ما النمو حسب الشريحة؟ أي الجهات لا تبلغ أهدافها، وكم شهرًا؟ وما وزن أكبر العملاء؟
عرض لتعريف واحد
CREATE VIEW v_sales AS
SELECT o.order_id, o.order_date, o.customer_id, o.rep_id, o.channel_code, o.status,
c.region_code, c.segment_code, p.category_code, l.product_id, l.quantity,
l.quantity * l.unit_price * (1 - l.discount_rate) AS net,
l.quantity * p.unit_cost AS cost
FROM order_lines l
JOIN orders o ON o.order_id = l.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = l.product_id;
العرض (view) استعلام محفوظ يُستعلم عنه كأنه جدول. ويجمع الروابط وقاعدة حساب المبلغ الصافي: فتستعمله كل استعلامات المشروع، ويتم أي تعديل للقاعدة في مكان واحد. ويضم 8,472 صفًّا، صفًّا لكل سطر طلبية.
المخرجات
| المُخرَج | المحتوى |
|---|---|
| توثيق المخطط | الجداول والحبيبية والمفاتيح والقيود والعلاقات |
| سكربت العرض | v_sales مع التعليقات |
| استعلامات المؤشرات | المبيعات والهامش والطلبيات والسلة والعملاء النشطون حسب السنة |
| استعلامات التحليل | النمو حسب الشريحة، والأشهر دون الهدف، وتركّز العملاء |
| الضوابط | اتساق العرض مع الجداول، والقيم المرجعية |
| مذكرة تركيبية | صفحة واحدة: ملاحظات مرقَّمة وثلاث توصيات |
النتائج المحصَّلة
| المؤشر | القيمة |
|---|---|
| صافي المبيعات 2025 (2024) | 15,097,590 درهم (13,556,439 درهم) |
| نسبة الهامش 2025 | 38.3% |
| الطلبيات المسلَّمة / متوسط السلة 2025 | 1,628 / 9,274 درهم |
| العملاء النشطون 2024 ← 2025 | 104 ← 120 |
| حصة أول 10 عملاء في 2025 | 19.6% |
شبكة التقييم
| المعيار | النقاط |
|---|---|
| توثيق المخطط والقيود | 15 |
| العرض وقاعدة الحساب الموحَّدة | 15 |
| دقة المؤشرات (القيم المرجعية) | 25 |
| جودة الاستعلامات: الوضوح وCTE ودوال النوافذ والتعليقات | 20 |
| ضوابط الاتساق | 10 |
| مذكرة تركيبية معلَّلة | 15 |
دفتر تحملات المشروع الختامي
- توثيق الجداول ومفاتيحها وقيودها.
- إنشاء العرض v_sales الذي يحمل قاعدة المبلغ الصافي.
- حساب المؤشرات الرئيسية لسنتي 2024 و2025.
- تحليل النمو حسب الشريحة والأشهر دون الهدف حسب الجهة.
- قياس تركّز المبيعات لدى أكبر العملاء.
- التحقق من اتساق العرض مع الجداول المصدرية.
- تحرير مذكرة تركيبية بثلاث توصيات.
