لغة الاستعلام الهيكلية واستخداماتها

استخدام لغة الاستعلام الهيكلية داخل قواعد البيانات

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

تتضمن تطبيقات التحليلات في قاعدة البيانات كل من:

• الكشف عن عمليات الاحتيال في معاملات بطاقات الائتمان
• توصيات المنتج
• اختيار إعلانات الويب المصممة لمستخدم معين

وغيرها من التطبيقات

في هذا الموضوع سوف يتم استعراض بعض الأساسيات والأساليب المتقدمة في لغة الاستعلام الهيكلية SQL.

تم اختبار أمثلة كود SQL المقدمة في أحد قواعد البيانات Greenplum 4.1.1.1، والتي تستند إلى PostgreSQL 8.2.15. ومع ذلك، فإن المفاهيم المقدمة قابلة للتطبيق على بيئات SQL الأخرى.

أساسيات لغة الاستعلام الهيكلية SQL

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

يحتوي جدول الطلبات على سجلات لكل حركة طلب.

يحتوي كل سجل على عناصر بيانات مثل:

• حقل product_id المطلوب
• حقل customer_id للعميل الذي قدم الطلب
• الحقل order_datetime

وغيرها من البيانات.

توفر الجداول الأربعة الأخرى تفاصيل إضافية حول الأصناف المطلوبة والعميل.

توضح الخطوط الموجودة بين الجداول في الشكل أعلاه العلاقات بين الجداول.

على سبيل المثال، يمكن إقران الاسم الأول للعميل واسم العائلة والجنس من جدول العميل بسجل الطلبات بناءً على المساواة في customer_id في هذين الجدولين.

ميزات العلاقات بين الجداول

على الرغم من أنه من الممكن إنشاء جدول واحد كبير للاحتفاظ بجميع تفاصيل الطلبات والعميل، إلا أن استخدام خمسة جداول له مميزاته. الميزة الأولى هي توفير مساحة التخزين على القرص. بدلا من تخزين اسم المنتج، الذي يمكن أن يصل طوله إلى عدة مئات من الأحرف، في جدول الطلبات بشكل متكرر، يمكن استخدام معرّف product_id أقصر بكثير، ربما يكون ببضعة وحدات بايت، بدلا من اسم المنتج.

ميزة أخرى هي أن التغييرات والتصحيحات تتم بسهولة. في هذا المثال، يتم استخدام فئة الجدول لتصنيف كل منتج. إذا تم اكتشاف أنه تم تعيين فئة غير صحيحة لعنصر منتج معين، فيجب فقط تحديث category_id في جدول المنتج بدلا من تصحيحه في كل السجلات في جدول الطلبات. حيث أنه بدون جداول المنتجات والفئات، قد يكون من الضروري تحديث مئات الآلاف من السجلات في جدول الطلبات.

الميزة الثالثة هي أنه يمكن إضافة المنتجات إلى قاعدة البيانات قبل تقديم أي طلبات.

وبالمثل، يمكن إنشاء فئات جديدة تحسبًا لخطوط إنتاج جديدة تمامًا يتم إضافتها إلى عروض بائع التجزئة عبر الإنترنت لاحقًا.

في تصميم قاعدة البيانات العلائقية، يفضل عدم تكرار أجزاء من البيانات مثل اسم العميل عبر سجلات متعددة.

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

استخدام لغة الاستعلام الهيكلية SQL

بغض النظر عن الغرض من قاعدة البيانات، عادةً ما يتم استخدام لغة الاستعلام SQL للاستعلام عن محتويات جداول قاعدة البيانات العلائقية وكذلك لإدراج البيانات وتحديثها وحذفها.

قد يبدو استعلام SQL الأساسي مقابل جدول العملاء كما يلي:

SELECT first_name,
 last_name
FROM customer
WHERE customer_id = 666730
first_name last_name
Mason Hu

يقوم هذا الاستعلام بإرجاع معلومات العميل الخاصة بالزبون الذي لديه المعرف رقم: 666730.

يتكون استعلام SQL هذا من ثلاثة أجزاء رئيسية:

• SELECT: يحدد أعمدة الجدول المراد عرضها
• FROM: يحدد اسم الجدول الذي سيتم الاستعلام عنه
• WHERE: يحدد المعيار أو عامل التصفية المطلوب تطبيقه

في قاعدة البيانات العلائقية، غالبًا ما يكون من الضروري الوصول إلى البيانات ذات الصلة من جداول متعددة في وقت واحد. لإنجاز هذه المهمة، يستخدم استعلام لغة SQL عبارات JOIN لتحديد العلاقات بين الجداول المتعددة.

الصِلات في لغة الاستعلام (Joins)

الصلات Joins تمكِّن مستخدم قاعدة البيانات من تحديد الأعمدة بشكل مناسب من جدولين أو أكثر.

INNER JOIN

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

SELECT c.customer_id,
 o.order_id,
 o.product_id,
 o.item_quantity AS qty
FROM orders o
 INNER JOIN customer c
 ON o.customer_id = c.customer_id
WHERE c.first_name = 'Mason'
 AND c.last_name = 'Hu'
customer_id order_id product_id qty
666730 51965-1172-6384-6923 33611 5
666730 79487-2349-4233-6891 34098 1
666730 39489-4031-0789-6076 33928 1
666730 29892-1218-2722-3191 33625 1
666730 07751-7728-7969-3140 34140 4
666730 85394-8022-6681-4716 33571 1

يعرض هذا الاستعلام تفاصيل الطلبات التي قدمها العميل Mason Hu.

يقوم استعلام SQL بوصل الجدولين في عبارة FROM استنادًا إلى تساوي قيم customer_id. في هذا الاستعلام، لا يلزم أن يعرف المبرمج قيمة customer_id المحددة لـ Mason Hu؛ يجب فقط معرفة الاسم الكامل للعميل.

تم تقديم بعض الوظائف الإضافية بخلاف استخدام INNER JOIN في استعلام SQL هذا.

يتم تعيين الأسماء المستعارة o و c لأوامر الجداول والعميل على التوالي. يتم استخدام الأسماء المستعارة بدلا من أسماء الجداول الكاملة لتحسين إمكانية قراءة الاستعلام. حسب التصميم، يتم أيضًا توفير أسماء الأعمدة المحددة في عبارة SELECT في المخرجات. ومع ذلك، يمكن تعديل اسم العمود الناتج باستخدام الكلمة الأساسية AS. في استعلام SQL، يتم عرض قيم item_quantity، ولكن يسمى هذا العمود الناتج الآن الكمية.

ترجع INNER JOIN تلك الصفوف من الجدولين حيث يتم استيفاء معيار ON. من الاستعلام السابق في جدول العملاء، يوجد صف واحد فقط في الجدول للعميل Mason Hu.

نظرًا لأن customer_id المقابل لـ Mason Hu يظهر ست مرات في جدول الطلبات، فإن استعلام INNER JOIN يُرجع ست سجلات.