برنامج تعليمي لمستكشف البيانات - الجزء 2 - أساسيات SQL في Discourse

[SaraDev (Sara Devlaeminck)]

يُعد هذا الدليل استمرارًا لـ دليل مستكشف البيانات - الجزء 1 - كتابة استعلامك الأول.

الآن بعد أن رأيت كيف يعمل مستكشف البيانات عمليًا، دعنا نتحدث عن بعض أساسيات SQL التي ستكون مفيدة عند خوض مغامرات استكشاف البيانات.

عبارات SQL

تتم جميع الإجراءات التي ستحتاج إلى تنفيذها على قاعدة بيانات Discourse باستخدام عبارات SQL.

كلمات SQL غير حساسة لحالة الأحرف: select هي نفسها SELECT، ولكن في هذا الدليل، ولأفضل الممارسات، سنكتب جميع كلمات SQL بأحرف كبيرة.

إليك بعض عبارات SQL الأكثر شيوعًا التي قد تستخدمها. لاحظ أننا استخدمنا بعضها في استعلامنا السابق:

• SELECT: تُستخدم لاختيار البيانات من قاعدة بيانات. تُخزن البيانات المُرجعة في جدول نتائج يُسمى مجموعة النتائج.

SELECT column1, column2, ...
FROM table_name;

• WHERE: تُستخدم لتصفية السجلات.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• ORDER BY: تُستخدم لفرز مجموعة النتائج بترتيب تصاعدي أو تنازلي.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

• COUNT: تُرجع عدد السجلات التي تطابق معيارًا محددًا.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

• GROUP BY: تُستخدم غالبًا مع دوال التجميع (COUNT, MAX, MIN, SUM, AVG) لتجميع مجموعة النتائج بناءً على عمود واحد أو أكثر.

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

• HAVING: تُستخدم لتصفية نتائج عملية GROUP BY. تُستخدم جملة HAVING في SQL لأن كلمة WHERE لا يمكن استخدامها مع دوال التجميع (مثل: COUNT() ، MAX() ، MIN() ، SUM() ، AVG()).

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;

• IN: تُستخدم للتحقق مما إذا كانت قيمة موجودة في مجموعة من القيم أو تُرجعها استعلام فرعي. إنها اختصار لعدة شروط OR، مما يجعل استعلامات SQL الخاصة بك أكثر إيجازًا وأسهل في القراءة.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

الفواصل المنقوطة ليست ضرورية عند كتابة استعلامات SQL في مستكشف البيانات.

PostgreSQL

تستخدم Discourse حاليًا إصدار 13 من PostgreSQL لقاعدة بياناتها، وهو نظام إدارة قواعد بيانات علائقية مفتوح المصدر (RDBMS) يركز على قابلية التمدد والامتثال لـ SQL.

لفهم PostgreSQL بشكل مفصل، قد ترغب في قراءة الوثائق الرسمية لـ PostgreSQL. على وجه التحديد، قد تجد أقسام الاستعلامات، وأنواع البيانات، والدوال ذات صلة خاصة عند كتابة استعلامات SQL.

جداول قاعدة البيانات

تتكون قاعدة بيانات Discourse من جداول. الجداول هي الطريقة الأساسية لتخزين البيانات في نظام إدارة قواعد بيانات علائقية.

يتم تحديد كل جدول في Discourse باسم (مثل posts أو topics)، وتحتوي كل جداول على سجلات (صفوف) تحتوي على بيانات. تُعرف الأعمدة في الجداول عادةً بالحقول.

تحتوي قاعدة بيانات Discourse على أكثر من 240 جدولًا!

يُدرج مستكشف البيانات 9 جداول مهمة أولاً في واجهة مستخدم تحرير الاستعلام، ويمكنك رؤية هيكل وأعمدة جميع الجداول وأنواعها بنقرة واحدة:

مخطط قاعدة البيانات

في PostgreSQL، وكذلك في قواعد البيانات العلائقية الأخرى، تُعد مفاهيم المفتاح الأساسي والمفتاح الأجنبي أساسية. تُستخدم لربط الجداول وضمان اتساق البيانات وسلامتها.

المفاتيح الأساسية

المفتاح الأساسي هو عمود أو مجموعة أعمدة في جدول يحدد بشكل فريد كل صف في ذلك الجدول. لا يمكن أن يكون لصفين في جدول نفس قيمة المفتاح الأساسي. لا يمكن أن تكون قيمة المفتاح الأساسي NULL، ويجب أن تكون القيمة فريدة. يُستخدم المفتاح الأساسي لفهرسة البيانات في الجدول، مما يجعل استرجاع البيانات أسرع بكثير. في مستكشف مخطط Discourse، يُدرج المفتاح الأساسي للجدول دائمًا في المرتبة الأولى.

مثال: المفتاح الأساسي لجدول posts هو الحقل id.

المفاتيح الأجنبية

المفتاح الأجنبي هو عمود أو مجموعة أعمدة في جدول تُستخدم لإنشاء رابط بين البيانات في جدولين. يعمل كمرجع متبادل بين الجداول لأنه يشير إلى المفتاح الأساسي لجدول آخر، مما يقيم رابطًا بينهما. يُسمى الجدول الذي يحتوي على المفتاح الأجنبي الجدول الفرعي، ويُسمى الجدول الذي يحتوي على المفتاح الأساسي الجدول المرجعي أو الجدول الأب. في مستكشف مخطط Discourse، يُشار إلى المفتاح الأجنبي بالنص fkey متبوعًا باسم الجدول الأب.

مثال: في جدول posts، يُعد user_id مفتاحًا أجنبيًا لجدول users.

استخدام المفاتيح الأساسية والأجنبية

عند كتابة استعلامات في مستكشف البيانات، يمكنك استخدام هذه المفاتيح الأساسية والأجنبية لـ JOIN الجداول معًا والحصول على بيانات أكثر تعقيدًا. على سبيل المثال، إذا أردت العثور على جميع المنشورات التي كتبها مستخدم معين، يمكنك كتابة استعلام مثل هذا:

SELECT p.id, p.created_at, p.raw
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.username = 'username_here'

في هذا الاستعلام، نستخدم المفتاح الأجنبي user_id في جدول posts للربط مع المفتاح الأساسي id في جدول users. هذا يسمح لنا بالعثور على جميع المنشورات التي كتبها مستخدم معين.

عمليات JOIN في SQL

كما ذُكر سابقًا، تُستخدم عبارة JOIN لدمج الصفوف من جدولين أو أكثر، بناءً على عمود مرتبط بينهما.

من المهم ملاحظة أن هناك عدة أنواع من عبارات JOIN في SQL، كل منها يخدم غرضًا مختلفًا:

1. INNER JOIN: تختار كلمة INNER JOIN السجلات التي تحتوي على قيم متطابقة في كلا الجدولين. تُرجع الصفوف من كلا الجدولين حيث يوجد تطابق. استخدم INNER JOIN عندما تريد فقط إرجاع السجلات التي يوجد فيها تطابق في كلا الجدولين. عندما تُستخدم JOIN دون تحديد نوع الربط (مثل LEFT ، RIGHT ، أو FULL )، فإنها تكون افتراضيًا INNER JOIN.

2. LEFT (OUTER) JOIN: تُرجع كلمة LEFT JOIN جميع السجلات من الجدول الأيسر (table1)، والسجلات المطابقة من الجدول الأيمن (table2). تكون النتيجة NULL من الجانب الأيمن إذا لم يكن هناك تطابق. استخدم LEFT JOIN عندما تريد إرجاع جميع السجلات من الجدول الأيسر والسجلات المطابقة من الجدول الأيمن. إذا لم يكن هناك تطابق، تكون النتيجة NULL على الجانب الأيمن.

3. RIGHT (OUTER) JOIN: تُرجع كلمة RIGHT JOIN جميع السجلات من الجدول الأيمن (table2)، والسجلات المطابقة من الجدول الأيسر (table1). تكون النتيجة NULL من الجانب الأيسر عندما لا يكون هناك تطابق. استخدم RIGHT JOIN عندما تريد إرجاع جميع السجلات من الجدول الأيمن والسجلات المطابقة من الجدول الأيسر. إذا لم يكن هناك تطابق، تكون النتيجة NULL على الجانب الأيسر.

4. FULL (OUTER) JOIN: تُرجع كلمة FULL JOIN جميع السجلات عندما يكون هناك تطابق في أي من سجلات الجدول الأيسر (table1) أو الجدول الأيمن (table2). استخدم FULL JOIN عندما تريد إرجاع جميع السجلات عندما يكون هناك تطابق في أحد الجداول.

يمكنك أيضًا دمج عبارات JOIN متعددة لربط عدة جداول معًا. على سبيل المثال، يمكننا ربط جدول topic_tags بجدول topics، ثم ربط جدول tags بجدول topic_tags للوصول إلى أسماء الوسوم المرتبطة بموضوع معين.

SELECT 
    t.id AS topic_id, 
    tg.name as tag_name
FROM 
    topics t
JOIN topic_tags tt ON t.id = tt.topic_id
JOIN tags tg ON tg.id = tt.tag_id

عبارات WITH والاستعلامات الفرعية

تُستخدم عبارة WITH في SQL، والمعروفة أيضًا باسم تعبير الجدول المشترك (CTE)، لإنشاء مجموعة نتائج مؤقتة يمكن الإشارة إليها داخل عبارة SELECT أخرى. يمكن أن يكون هذا مفيدًا بشكل خاص عند العمل مع استعلامات SQL معقدة حيث يمكن أن يساعد في تبسيطها، مما يجعلها أسهل في القراءة والصيانة.

إليك مثال أساسي على عبارة WITH:

WITH post_counts AS (
  SELECT user_id, COUNT(*) as post_count
  FROM posts
  GROUP BY user_id
)

SELECT u.username, post_counts.post_count
FROM users u
JOIN post_counts ON u.id = post_counts.user_id
ORDER BY post_counts.post_count DESC;

في هذا الاستعلام، تُنشئ عبارة WITH جدولًا مؤقتًا post_counts يحتوي على معرف كل مستخدم وإجمالي عدد منشوراته. ثم يقوم الاستعلام الرئيسي بربط هذا الجدول بجدول users للحصول على أسماء المستخدمين المرتبطة بكل معرف، ويرتب النتيجة حسب عدد المنشورات بترتيب تنازلي.

سيُرجع هذا الاستعلام عدد المنشورات التي قام بها كل مستخدم.

الاستعلامات الفرعية هي استعلامات مضمنة داخل استعلام آخر. يمكن استخدام الاستعلام الفرعي داخل عبارات SELECT أو داخل استعلام فرعي آخر. يمكن للاستعلام الفرعي أن يُرجع مجموعة من السجلات، أو سجلاً واحدًا، أو قيمة واحدة.

إليك مثال على استعلام فرعي:

SELECT u.username 
FROM users u
WHERE u.id IN (
  SELECT p.user_id 
  FROM posts p
  GROUP BY p.user_id 
  HAVING COUNT(p.id) > 100
)

في هذا المثال، يُرجع الاستعلام الفرعي (SELECT p.user_id FROM posts p GROUP BY p.user_id HAVING COUNT(p.id) > 100) معرف user_id من جدول posts للمستخدمين الذين كتبوا أكثر من 100 منشور. ثم يحصل الاستعلام الرئيسي على username من جدول users لتلك user_ids.

سيُرجع هذا الاستعلام قائمة بأسماء المستخدمين الذين كتبوا أكثر من 100 منشور.

مواضيع أخرى في هذه السلسلة

• دليل مستكشف البيانات - الجزء 1 - كتابة استعلامك الأول
• هذا الموضوع
• استخدام المعاملات في استعلامات مستكشف البيانات
• تنسيق نتائج جداول مستكشف البيانات