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

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

تحليل البيانات داخل قاعدة البيانات هو مصطلح واسع يصف معالجة البيانات داخل مستودعها. في العديد من الأمثلة التطبيقية لعمليات تحليل البيانات، يتم استخراج البيانات من مصدر البيانات وتحميلها إلى نظام مخصص لتحليل البيانات مثل 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 يُرجع ست سجلات.

إذا لم يتم تضمين جملة WHERE، فسيُرجع الاستعلام ملايين الصفوف لجميع الطلبات التي لها عميل مطابق.

RIGHT OUTER JOIN

افترض أن أحد المحللين يريد معرفة العملاء الذين قاموا بإنشاء حساب عبر الإنترنت ولكنهم لم يقدموا طلبًا بعد.

يستخدم الاستعلام التالي RIGHT OUTER JOIN لتحديد العملاء الخمسة الأوائل، مرتبين أبجديًا، الذين لم يقدموا طلبًا.

يتم فرز السجلات باستخدام عبارة ORDER BY.

SELECT c.customer_id,
 c.first_name,
 c.last_name,
 o.order_id
FROM orders o
 RIGHT OUTER JOIN customer c
 ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.last_name,
 c.first_name
LIMIT 5
customer_id first_name last_name order_id
143915 Abigail Aaron
965886 Audrey Aaron
982042 Carter Aaron
125302 Daniel Aaron
103964 Emily Aaron

في استعلام SQL، يتم استخدام RIGHT OUTER JOIN لتحديد أنه يجب إرجاع جميع الصفوف من الجدول المرتبطة الخاصة بالعميل، على الجانب الأيمن (RHS) من الصلة، بغض النظر عما إذا كان هناك customer_id مطابق في جدول الطلبات. في هذا الاستعلام، يقيد بند WHERE النتائج على سجلات العملاء المنضمة فقط حيث لا يوجد order_id مطابق. NULL هي كلمة أساسية خاصة بلغة SQL تشير إلى قيمة غير معروفة. بدون عبارة WHERE، كان من الممكن أن تتضمن المخرجات أيضًا جميع السجلات التي تحتوي على customer_id المطابق في جدول الطلبات، كما هو موضح في استعلام لغة SQL التالي:

SELECT c.customer_id,
 c.first_name,
 c.last_name,
 o.order_id
FROM orders o
 RIGHT OUTER JOIN customer c
 ON o.customer_id = c.customer_id
ORDER BY c.last_name,
 c.first_name
LIMIT 5
customer_id first_name last_name order_id
143915 Abigail Aaron
222599 Addison Aaron 50314-7576-3355-6960
222599 Addison Aaron 21007-7541-1255-3531
222599 Addison Aaron 19396-4363-4499-8582
222599 Addison Aaron 69225-1638-2944-0264

في نتائج الاستعلام، العميل الأول، أبيجيل آرون، لم يقدم طلبًا، لكن العميل التالي، أديسون آرون، قدم أربع طلبات على الأقل.

صلات أخرى في لغة الاستلعام

هناك عدة أنواع أخرى من الصلات. تؤدي LEFT OUTER JOIN نفس الوظيفة مثل RIGHT OUTER JOIN فيما عدا أنه يتم أخذ جميع السجلات من الجدول على الجانب الأيسر (LHS) في الاعتبار.

يتضمن FULL OUTER JOIN كل السجلات من كلا الجدولين بغض النظر عما إذا كان هناك سجل مطابق في الجدول الآخر.

يجمع CROSS JOIN بين جدولين عن طريق مطابقة كل صف من الجدول الأول مع كل صف من الجدول الثاني.

إذا كان الجدولان يحتويان على 100 و1000 صف، على التوالي، فإن CROSS JOIN الناتج من هذين الجدولين سيكون به 100000 صف.

تعتمد السجلات الفعلية التي يتم إرجاعها من أي عملية صلة على المعايير المنصوص عليها في بند WHERE.

وبالتالي، يجب الدراسة المتأنية في استخدام جملة WHERE، خاصة مع الصلات الخارجية. وإلا، فقد يتم التراجع المقصود عن استخدام الصلة الخارجية.

عمليات المجموعات في لغة الاستعلام Set Operations

توفر لغة الاستعلام SQL القدرة على أداء عمليات المجموعات، مثل الاتحادات والتقاطعات، على صفوف البيانات.

على سبيل المثال، افترض أن جميع السجلات في جدول الطلبات مقسمة إلى جدولين.

يحتوي جدول orders_arch، وهو اختصار للطلبات المؤرشفة، على الطلبات التي تم إدخالها قبل يناير 2013.

يتم تخزين الطلبات التي تم التعامل معها في يناير 2013 أو بعده في جدول الطلبات الأحدث orders_recent.

ومع ذلك، فإن كافة أوامر معرّف product_id 33611 مطلوبة لإجراء تحليل.

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

يمكن بعد ذلك دمج نتائج الاستعلامين في ملف أو جدول منفصل.

UNION ALL

بدلا من ذلك، يمكن كتابة استعلام واحد باستخدام عامل التشغيل UNION ALL على النحو التالي:

SELECT customer_id,
 order_id,
 order_datetime,
 product_id,
 item_quantity AS qty
FROM orders_arch
WHERE product_id = 33611
UNION ALL
SELECT customer_id,
 order_id,
 order_datetime,
 product_id,
 item_quantity AS qty
FROM orders_recent
WHERE product_id = 33611
ORDER BY order_datetime
customer_id order_id order_datetime product_id qty
643126 13501-6446-6326-0182 2005-01-02 19:28:08 33611 1
725940 70738-4014-1618-2531 2005-01-08 06:16:31 33611 1
742448 03107-1712-8668-9967 2005-01-08 16:11:39 33611 1
640847 73619-0127-0657-7016 2013-01-05 14:53:27 33611 1
660446 55160-7129-2408-9181 2013-01-07 03:59:36 33611 1
647335 75014-7339-1214-6447 2013-01-27 13:02:10 33611 1

يتم عرض السجلات الثلاثة الأولى من كل جدول في المخرجات.

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

تقوم UNION ALL بدمج نتائج عبارتين SELECT بغض النظر عن أي سجلات مكررة تظهر في كل من عبارات SELECT.

إذا تم استخدام UNION فقط، فسيتم حذف أي سجلات مكررة، بناءً على جميع الأعمدة المحددة.

INTERSECT

يحدد عامل التشغيل INTERSECT أي سجلات متطابقة يتم إرجاعها بواسطة عبارتين SELECT.

على سبيل المثال، إذا أردنا معرفة العناصر التي تم شراؤها قبل عام 2013 وما بعده، فسيكون استعلام SQL باستخدام عامل التشغيل INTERSECT كما يلي:

SELECT product_id
FROM orders_arch
INTERSECT
SELECT product_id
FROM orders_recent
product_id
22
30
31

من المهم ملاحظة أن التقاطع يُرجع فقط product_id إذا ظهر في كلا الجدولين ويعيد مثيلا واحدًا بالضبط لمعرّف product_id.

وبالتالي، لا يتم عرض سوى قائمة بمعرفات المنتجات المميزة بواسطة الاستعلام.

EXCEPT

لحساب عدد المنتجات التي تم طلبها قبل عام 2013 ولكن ليس بعد تلك النقطة الزمنية، يمكن استخدام عامل التشغيل EXCEPT لاستبعاد معرفات المنتجات في جدول orders_recent من معرفات المنتجات في جدول Orders_arch، كما هو موضح في استعلام SQL التالي:

SELECT COUNT(e.*)
FROM (SELECT product_id
 FROM orders_arch
 EXCEPT
 SELECT product_id
 FROM orders_recent) e
13569

يستخدم الاستعلام السابق الدالة التجميعية COUNT لتحديد عدد الصفوف التي تم إرجاعها من استعلام SQL الثاني الذي يتضمن عامل التشغيل EXCEPT.

يُطلق على استعلام SQL هذا الذي يكون داخل استعلام آخر أحيانًا اسم استعلام فرعي أو استعلام متداخل.

تتيح الاستعلامات الفرعية إنشاء استعلامات معقدة إلى حد ما دون الحاجة إلى تنفيذ الأجزاء أولا، وتفريغ الصفوف في جداول مؤقتة، ثم تنفيذ استعلام SQL آخر لمعالجة تلك الجداول المؤقتة.

يمكن استخدام الاستعلامات الفرعية بدلا من جدول داخل عبارة FROM أو يمكن استخدامها في جملة WHERE.

ملحقات التجميع في لغة الاستعلام Grouping Extensions

في السابق، تم استخدام الدالة التجميعية COUNT() لحساب عدد الصفوف التي تم إرجاعها من الاستعلام.

غالبًا ما تلخص هذه الوظائف الإجمالية مجموعة البيانات بعد تطبيق بعض عمليات التجميع عليها.

الدالة التجميعية SUM()

على سبيل المثال، قد يكون من المرغوب فيه معرفة الإيرادات حسب السنة أو الشحنات في الأسبوع.

يستخدم استعلام SQL التالي الدالة التجميعية SUM() جنبًا إلى جنب مع عامل التشغيل GROUP BY لتوفير العناصر الثلاثة الأولى المرتبة بناءً على item_quantity.

SELECT i.product_id,
 SUM(i.item_quantity) AS total
FROM orders_recent i
GROUP BY i.product_id
ORDER BY SUM(i.item_quantity) DESC
LIMIT 3
product_id total
15072 6089
15066 6082
15060 6053

عامل التشغيل ROLLUP()

يمكن لـ GROUP BY استخدام عامل التشغيل ROLLUP() لحساب الإجماليات الفرعية والمجاميع الكلية.

يستخدم استعلام SQL التالي الاستعلام السابق كاستعلام فرعي في جملة WHERE لتوفير عدد العناصر المطلوبة حسب السنة للعناصر الثلاثة الأولى المطلوبة بشكل عام.

يوفر عامل التشغيل ROLLUP المجاميع الفرعية التي تطابق الإخراج السابق لكل product_id، بالإضافة إلى الإجمالي الكلي.

SELECT r.product_id,
 DATE_PART('year', r.order_datetime) AS year,
 SUM(r.item_quantity) AS total
FROM orders_recent r
WHERE r.product_id IN (SELECT o.product_id
 FROM orders_recent o
 GROUP BY o.product_id
 ORDER BY SUM(o.item_quantity) DESC
 LIMIT 3)
GROUP BY ROLLUP( r.product_id, DATE_PART('year', r.order_datetime) )
ORDER BY r.product_id,
 DATE_PART('year', r.order_datetime)
product_id year total
15060 2013 5996
15060 2014 57
15060 6053
15066 2013 6030
15066 2014 52
15066 6082
15072 2013 6023
15072 2014 66
15072 6089
 18224

عامل تشغيل CUBE في لغة الاستعلام

يتوسع عامل تشغيل CUBE في وظائف مشغل ROLLUP من خلال توفير مجاميع فرعية لكل عمود محدد في بيان CUBE.

يؤدي تعديل الاستعلام السابق عن طريق استبدال عامل التشغيل ROLLUP بمعامل CUBE إلى نفس الإخراج مع إضافة المجاميع الفرعية لكل عام.

SELECT r.product_id,
 DATE_PART('year', r.order_datetime) AS year,
 SUM(r.item_quantity) AS total
FROM orders_recent r
WHERE r.product_id IN (SELECT o.product_id
 FROM orders_recent o
 GROUP BY o.product_id
 ORDER BY SUM(o.item_quantity) DESC
 LIMIT 3)
GROUP BY CUBE( r.product_id, DATE_PART('year', r.order_datetime) )
ORDER BY r.product_id,
 DATE_PART('year', r.order_datetime
product_id year total
15060 2013 5996
15060 2014 57
15060 6053
15066 2013 6030
15066 2014 52
15066 6082
15072 2013 6023
15072 2014 66
15072 6089
 2013 18049 ← additional row
 2014 175 ← additional row
 18224

دالة التجميع GROUPING()

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

على سبيل المثال، قد تكون القيم الخالية جزءًا من مجموعة البيانات التي يتم تحليلها.

يمكن أن تحدد الدالة GROUPING() الصفوف ذات القيم الخالية المستخدمة في الإجماليات الفرعية أو الإجماليات الكلية.

SELECT r.product_id,
 DATE_PART('year', r.order_datetime) AS year,
 SUM(r.item_quantity) AS total,
 GROUPING(r.product_id) AS group_id,
 GROUPING(DATE_PART('year', r.order_datetime)) AS group_year
FROM orders_recent r
WHERE r.product_id IN (SELECT o.product_id
 FROM orders_recent o
 GROUP BY o.product_id
 ORDER BY SUM(o.item_quantity) DESC
 LIMIT 3)
GROUP BY CUBE( r.product_id, DATE_PART('year', r.order_datetime) )
ORDER BY r.product_id,
 DATE_PART('year', r.order_datetime)
product_id year total group_id group_year
15060 2013 5996 0 0
15060 2014 57 0 0
15060 6053 0 1
15066 2013 6030 0 0
15066 2014 52 0 0
15066 6082 0 1
15072 2013 6023 0 0
15072 2014 66 0 0
15072 6089 0 1
 2013 18049 1 0
 2014 175 1 0
 18224 1 1

في الاستعلام السابق، يتم تعيين group_year إلى 1 عند حساب الإجمالي عبر قيم السنة.

وبالمثل، يتم تعيين group_id على 1 عندما يتم حساب الإجمالي عبر قيم product_id.

تخصيص وظائف ROLLUP وCUBE في لغة الاستعلام

يمكن تخصيص وظائف ROLLUP وCUBE عبر مجموعاتGROUPING SETS.

يمكن استبدال استعلام SQL الذي يستخدم عامل التشغيل CUBE بالاستعلام التالي الذي يستخدم التجميع حسب مجموعات GROUPING لتقديم نفس النتائج:

SELECT r.product_id,
 DATE_PART('year', r.order_datetime) AS year,
 SUM(r.item_quantity) AS total
FROM orders_recent r
WHERE r.product_id IN (SELECT o.product_id
 FROM orders_recent o
 GROUP BY o.product_id
 ORDER BY SUM(o.item_quantity) DESC
 LIMIT 3)
GROUP BY GROUPING SETS( ( r.product_id,
 DATE_PART('year', r.order_datetime) ),
 ( r.product_id ),
 ( DATE_PART('year', r.order_datetime) ),
 ( ) )
ORDER BY r.product_id,
 DATE_PART('year', r.order_datetime)

تحدد مجموعات التجميع المدرجة الأعمدة التي سيتم توفير المجاميع الفرعية لها.

تحدد مجموعة التجميع الأخيرة، ()، أن الإجمالي الكلي يتم توفيره في نتائج الاستعلام.

على سبيل المثال، إذا كان المطلوب هو الإجمالي الكلي فقط، فيمكن استخدام استعلام SQL التالي باستخدام GROUPING SETS:

SELECT r.product_id,
 DATE_PART('year', r.order_datetime) AS year,
 SUM(r.item_quantity) AS total
FROM orders_recent r
WHERE r.product_id IN (SELECT o.product_id
 FROM orders_recent o
 GROUP BY o.product_id
 ORDER BY SUM(o.item_quantity) DESC
 LIMIT 3)
GROUP BY GROUPING SETS( ( r.product_id,
 DATE_PART('year', r.order_datetime) ),
 ( ) )
ORDER BY r.product_id,
 DATE_PART('year', r.order_datetime)
product_id year total
15060 2013 5996
15060 2014 57
15066 2013 6030
15066 2014 52
15072 2013 6023
15072 2014 66
 18224

دالة GROUP_ID()

نظرًا لأن جملة GROUP BY يمكن أن تحتوي على عدة مواصفات CUBE أو ROLLUP أو عمود، فقد تحدث مجموعات تجميع مكررة.

تحدد الدالة GROUP_ID() الصفوف الفريدة ذات القيمة صفر والصفوف الزائدة التي تحتوي على 1، 2، … لتوضيح الوظيفة GROUP_ID().

يتم استخدام كل من ROLLUP وCUBE عند فحص معرّف product_id واحد فقط كما يلي:

SELECT r.product_id,
 DATE_PART('year', r.order_datetime) AS year,
 SUM(r.item_quantity) AS total,
 GROUP_ID() AS group_id
FROM orders_recent r
WHERE r.product_id IN ( 15060 )
GROUP BY ROLLUP( r.product_id, DATE_PART('year', r.order_datetime)),
 CUBE( r.product_id, DATE_PART('year', r.order_datetime) )
ORDER BY r.product_id,
 DATE_PART('year', r.order_datetime),
 GROUP_ID()
product_id year total group_id
15060 2013 5996 0
15060 2013 5996 1
15060 2013 5996 3
15060 2013 5996 4
15060 2013 5996 5
15060 2013 5996 6
15060 2014 57 0
15060 2014 57 1
15060 2014 57 2
15060 2014 57 3
15060 2014 57 4
15060 2014 57 5
15060 2014 57 6
15060 6053 0
15060 6053 1
15060 6053 2
 2013 5996 0
 2014 57 0
 6053 0

استخدام عبارة HAVING في التصفية

التصفية على قيم group_id المساوية للصفر ينتج عنها سجلات فريدة.

يمكن تحقيق هذه التصفية باستخدام عبارة HAVING، كما هو موضح في استعلام SQL التالي:

SELECT r.product_id,
 DATE_PART('year', r.order_datetime) AS year,
 SUM(r.item_quantity) AS total,
 GROUP_ID() AS group_id
FROM orders_recent r
WHERE r.product_id IN ( 15060 )
GROUP BY ROLLUP( r.product_id, DATE_PART('year', r.order_datetime) ),
 CUBE( r.product_id, DATE_PART('year', r.order_datetime) )
HAVING GROUP_ID() = 0
ORDER BY r.product_id,
 DATE_PART('year', r.order_datetime),
 GROUP_ID()
product_id year total group_id
15060 2013 5996 0
15060 2014 57 0
15060 6053 0
 2013 5996 0
 2014 57 0
 6053 0

المصدر

• أساسيات لغة الاستعلام الهيكلية، ترجمة وإعداد: د.م. مصطفى عبيد، مركز البحوث والدراسات متعدد التخصصات، 2020.
• كتاب علم البيانات وتحليل البيانات الضخمة، Data Science and Big Data Analytics، Discovering, Analyzing, Visualizing and Presenting DataPublished by، John Wiley & Sons, Inc. 10475 Crosspoint Boulevard، Indianapolis, IN 46256، www.wiley.com EMC .Education Services