الاستعلامات الهرمية والمتكررة في لغة SQL

الموسوعة الحرة

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

في معيار SQL:1999، تُنفَّذ الاستعلامات الهرمية باستخدام تعابير الجداول المشتركة المتكررة (CTEs). على عكس عبارة الاتصال السابقة في أوراكل ، صُمِّمت تعابير الجداول المشتركة المتكررة بدلالات النقطة الثابتة منذ البداية. [ 1 ] كانت تعابير الجداول المشتركة المتكررة في المعيار قريبة نسبيًا من التنفيذ الحالي في IBM DB2 الإصدار 2. [ 1 ] كما يدعم Microsoft SQL Server (منذ SQL Server 2008 R2)، [ 2 ] وFirebird 2.1 ، [ 3 ] وPostgreSQL 8.4 والإصدارات الأحدث ، [ 4 ] وSQLite 3.8.3 والإصدارات الأحدث ، [ 5 ] وIBM Informix الإصدار 11.50 والإصدارات الأحدث، و CUBRID ، وMariaDB 10.2 والإصدارات الأحدث، و MySQL 8.0.1 والإصدارات الأحدث، تعابير الجداول المشتركة المتكررة . [ 6 ] يتوفر لدى Tableau وثائق تشرح كيفية استخدام تعابير الجداول المشتركة . لا يدعم TIBCO Spotfire عبارات CTE، بينما يفتقر تطبيق Oracle 11g Release 2 إلى دلالات النقطة الثابتة.

بدون استخدام تعابير الجداول المشتركة أو عبارات الربط، من الممكن تحقيق استعلامات هرمية باستخدام دوال تكرارية معرفة من قبل المستخدم. [ 7 ]

تعبير الجدول المشترك

التعبير الجدولي المشترك، أو CTE، (في SQL ) هو مجموعة نتائج مؤقتة مسماة، مشتقة من استعلام بسيط ومحددة ضمن نطاق تنفيذ عبارة SELECT، INSERT، UPDATE، أو DELETE.

يمكن اعتبار CTEs بدائل للجداول المشتقة ( الاستعلام الفرعي ) والعروض والوظائف المضمنة المعرفة من قبل المستخدم.

تدعم Teradata (بدءًا من الإصدار 14)، و IBM Db2 ، وInformix (بدءًا من الإصدار 14.1)، و Firebird (بدءًا من الإصدار 2.1)، [ 8 ] وMicrosoft SQL Server (بدءًا من الإصدار 2005)، و Oracle (مع خاصية التكرار منذ الإصدار 11g الإصدار 2)، و PostgreSQL (منذ الإصدار 8.4)، و MariaDB (منذ الإصدار 10.2 [ 9 ] )، و MySQL (منذ الإصدار 8.0)، و SQLite (منذ الإصدار 3.8.3)، و HyperSQL ، وInformix (منذ الإصدار 14.10)، [ 10 ] وGoogle BigQuery ، وSybase (بدءًا من الإصدار 9)، وVertica ، وH2 (تجريبي)، [ 11 ] وغيرها الكثير ، تعابير الجداول المشتركة (CTEs). تُطلق Oracle على تعابير الجداول المشتركة اسم "تحليل الاستعلامات الفرعية". [ 12 ]

تكون صيغة CTE (التي قد تكون أو لا تكون تكرارية) كما يلي:

باستخدام [ استعلام تكراري ] مع_الاستعلام [, ...] تحديد ...

حيث with_queryيكون بناء الجملة كالتالي:

query_name [ ( column_name [,...]) ] AS ( SELECT ...)

يمكن استخدام عبارات CTE المتكررة لاجتياز العلاقات (كرسوم بيانية أو شجرية)، على الرغم من أن بناء الجملة أكثر تعقيدًا نظرًا لعدم وجود أعمدة وهمية مُنشأة تلقائيًا (كما هو موضح LEVELأدناه )؛ فإذا لزم الأمر، يجب إنشاؤها في الكود. راجع وثائق MSDN [ 2 ] أو وثائق IBM [ 13 ] [ 14 ] للاطلاع على أمثلة تعليمية.

RECURSIVEلا تكون الكلمة المفتاحية مطلوبة عادةً بعد عبارة WITH في الأنظمة الأخرى غير PostgreSQL . [ 15 ]

في SQL:1999، يمكن استخدام الاستعلام التكراري (CTE) في أي مكان يُسمح فيه بالاستعلام. على سبيل المثال، يمكن تسمية النتيجة باستخدام CREATE[ RECURSIVE] VIEW. [ 16 ] باستخدام CTE داخل عبارة INSERT INTO، يمكن ملء جدول ببيانات مُولَّدة من استعلام تكراري؛ كما يُمكن توليد بيانات عشوائية باستخدام هذه التقنية دون الحاجة إلى أي عبارات إجرائية. [ 17 ]

تدعم بعض قواعد البيانات، مثل PostgreSQL، صيغة CREATE RECURSIVE VIEW المختصرة والتي تُترجم داخليًا إلى ترميز WITH RECURSIVE. [ 18 ]

فيما يلي مثال على استعلام تكراري لحساب مضروب الأعداد من 0 إلى 9:

WITH recursive temp ( n , fact ) AS ( SELECT 0 , 1 -- الاستعلام الفرعي الأولي UNION ALL SELECT n + 1 , ( n + 1 ) * fact FROM temp WHERE n < 9 -- الاستعلام الفرعي المتكرر ) SELECT * FROM temp ;

تواصل عبر

يُعدّ التركيب غير القياسي CONNECT BYصيغةً بديلةً، وقد قدّمته أوراكل في ثمانينيات القرن الماضي. [ 19 ] قبل إصدار أوراكل 10g، كان هذا التركيب مفيدًا فقط لاجتياز الرسوم البيانية غير الدورية لأنه كان يُرجع خطأً عند اكتشاف أي دورات؛ في الإصدار 10g، قدّمت أوراكل ميزة NOCYCLE (والكلمة المفتاحية)، مما جعل الاجتياز يعمل في وجود الدورات أيضًا. [ 20 ]

CONNECT BYيدعمها كل من Snowflake و EnterpriseDB [ 21 ] وقاعدة بيانات Oracle [ 22 ] و CUBRID [ 23 ] و IBM Informix [ 24 ] و IBM Db2 ، ولكن فقط في حال تفعيلها كوضع توافق. [ 25 ] ويكون بناء الجملة كما يلي:

SELECT select_list FROM table_expression [ WHERE ... ] [ START WITH start_expression ] CONNECT BY [ NOCYCLE ] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr } [ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ... ] [ GROUP BY ... ] [ HAVING ... ] ...
على سبيل المثال،
SELECT LEVEL , LPAD ( ' ' , 2 * ( LEVEL - 1 )) || ename "employee" , empno , mgr "manager" FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr ;

ستكون نتيجة الاستعلام أعلاه على النحو التالي:

المستوى | موظف | رقم الموظف | مدير -------+-------------+-------+--------- 1 | كينج | 7839 | 2 | جونز | 7566 | 7839 3 | سكوت | 7788 | 7566 4 | آدامز | 7876 | 7788 3 | فورد | 7902 | 7566 4 | سميث | 7369 | 7902 2 | بليك | 7698 | 7839 3 | ألين | 7499 | 7698 3 | جناح | 7521 | 7698 3 | مارتن | 7654 | 7698 3 | تيرنر | 7844 | 7698 3 | جيمس | 7900 | 7698 2 | كلارك | 7782 | 7839 3 | ميلر | 7934 | 7782 (14 صفًا)

الأعمدة الزائفة

  • مستوى
  • الاتصال بأوراق الجزيرة
  • الاتصال عبر الدراجة
  • الاتصال عبر الجذر

عوامل أحادية

يعرض المثال التالي اسم عائلة كل موظف في القسم 10، وكل مدير أعلى من ذلك الموظف في التسلسل الهرمي، وعدد المستويات بين المدير والموظف، والمسار بينهما:

SELECT ename "Employee" , CONNECT_BY_ROOT ename "Manager" , LEVEL - 1 "Pathlen" , SYS_CONNECT_BY_PATH ( ename , '/' ) "Path" FROM emp WHERE LEVEL > 1 AND deptno = 10 CONNECT BY PRIOR empno = mgr ORDER BY "Employee" , "Manager" , "Pathlen" , "Path" ;

الوظائف

  • SYS_CONNECT_BY_PATH

انظر أيضاً

مراجع

  1. 1 2 جيم ميلتون؛ آلان ر. سيمون (2002). SQL:1999: فهم مكونات لغة العلاقات . مورغان كوفمان. ISBN 978-1-55860-456-8.
  2. 1 2 مايكروسوفت. "الاستعلامات المتكررة باستخدام تعابير الجداول المشتركة" . تم الاسترجاع في 23-12-2009 .
  3. ↑ هيلين بوري (15 يوليو 2008). "ملاحظات إصدار فايربيرد 2.1" . تم الاطلاع عليه بتاريخ 24 نوفمبر 2015 .
  4. ↑ "مع الاستفسارات" . 10 فبراير 2022.PostgreSQL
  5. ↑ "عبارة مع" .SQLite
  6. ↑ "مختبرات MySQL 8.0: تعابير الجداول المشتركة [ التكرارية ] في MySQL (CTEs)" . مؤرشف من الأصل بتاريخ 16 أغسطس 2019. تم الاطلاع عليه بتاريخ 20 ديسمبر 2017 .mysqlserverteam.com
  7. ↑ شركة باراغون: استخدام الدوال المعرفة من قبل المستخدم في PostgreSQL لحل مشكلة الشجرة ، 15 فبراير 2004، تم الاطلاع عليه في 19 سبتمبر 2015
  8. ↑ "تحديث مرجع لغة فايربيرد 2.5" (ملف PDF) . مؤرشف من النسخة الأصلية (ملف PDF) بتاريخ 14-11-2011.
  9. ↑ "سجل تغييرات MariaDB 10.2.0" . قاعدة معارف MariaDB . تم الاطلاع عليه بتاريخ 22-12-2024 .
  10. ↑ ممكن قبل الإصدار 14.10 باستخدام الجداول المؤقتة https://stackoverflow.com/questions/42579298/why-does-a-with-clause-give-a-syntax-error-on-informix
  11. ↑ "متقدم" .
  12. ^ كارين مورتون. روبين ساندز؛ جاريد ستيل؛ رياج شمس الدين؛ كيري أوزبورن (2010). برو أوراكل SQL . Apress. ص. 283. ردمك  978-1-4302-3228-5.
  13. ↑ "مستندات IBM" .
  14. ↑ "مستندات IBM" .
  15. ↑ ريجينا أوبي؛ ليو هسو (2012). PostgreSQL: Up and Running . دار نشر أورايلي. ص 94. ISBN  978-1-4493-2633-3.
  16. ↑ جيم ميلتون؛ آلان ر. سيمون (2002). SQL:1999: فهم مكونات لغة قواعد البيانات العلائقية . مورغان كوفمان. ص 352. ISBN  978-1-55860-456-8.
  17. ↑ دون تشامبرلين (1998). دليل شامل لقاعدة بيانات DB2 العالمية . مورغان كوفمان. الصفحات 253-254 . ISBN  978-1-55860-482-7.
  18. ↑ "إنشاء عرض" . 10 فبراير 2022.
  19. ↑ بينيديكت، م.؛ سينيلارت، ب. (2011). "قواعد البيانات". في بلوم، إدوارد ك.؛ أهو، ألفريد ف. (محرران). علوم الحاسوب: المكونات المادية والبرمجية وجوهرها . ص 189. doi : 10.1007/978-1-4614-1168-0_10 . ISBN  978-1-4614-1167-3.
  20. ↑ سانجاي ميشرا؛ آلان بوليو (2004). إتقان لغة SQL في أوراكل . دار نشر أورايلي ميديا، ص 227. ISBN  978-0-596-00632-7.
  21. ↑ استعلامات هرمية مؤرشفة بتاريخ 21-06-2008 على موقع Wayback Machine ، EnterpriseDB
  22. ↑ الاستعلامات الهرمية ، أوراكل
  23. ↑ "استعلام CUBRID الهرمي" . مؤرشف من الأصل بتاريخ 14 فبراير 2013. تم الاطلاع عليه بتاريخ 11 فبراير 2013 .
  24. ↑ العبارة الهرمية ، IBM Informix
  25. ↑ جوناثان جينيك (2010). دليل SQL المختصر ( الطبعة الثالثة). دار نشر أورايلي ميديا، ص 8. ISBN   978-1-4493-9409-7.

للمزيد من القراءة

  • سي جيه ديت (2011). لغة SQL ونظرية العلاقات: كيفية كتابة كود SQL دقيق (  الطبعة الثانية). دار نشر أورايلي ميديا. الصفحات 159-163 . رقم ISBN  978-1-4493-1640-2.

الكتب الدراسية الأكاديمية . لاحظ أن هذه الكتب تغطي معيار SQL:1999 فقط (و Datalog)، ولكنها لا تغطي امتداد Oracle.

  • أبراهام سيلبرشاتز؛ هنري كورث؛ إس. سودارشان (2010). مفاهيم نظم قواعد البيانات (الطبعة السادسة  ). ماكجرو هيل. الصفحات 187-192 . ISBN  978-0-07-352332-3.
  • راغو راماكريشنان؛ يوهانس جيركي (2003). أنظمة إدارة قواعد البيانات (الطبعة الثالثة  ). ماكجرو هيل. رقم ISBN 978-0-07-246563-1.الفصل 24.
  • هيكتور غارسيا مولينا؛ جيفري د. أولمان؛ جينيفر ويدوم (2009). أنظمة قواعد البيانات: الكتاب الكامل (  الطبعة الثانية). بيرسون برنتيس هول. الصفحات 437-445 . ISBN  978-0-13-187325-4.