منذ البداية ، أود أن أوضح أنه في هذه الحالة يتم تقديم Oracle كلغة SQL جماعية. تنطبق المجموعات وكيفية تطبيقها على عائلة SQL بأكملها (والتي تُفهم هنا على أنها لغة استعلام منظمة) وتنطبق على جميع الاستعلامات ، مع تصحيحات لبناء جملة كل لغة.
سأحاول أن أشرح بإيجاز وبسهولة جميع المعلومات الضرورية في جزأين. سيكون المنشور على الأرجح مفيدًا للمطورين المبتدئين. من يهتم - مرحبا بكم في القط.
الجزء 1: ترتيب العروض حسب ، المجموعة حسب ، وجود
سنتحدث هنا عن الفرز - الترتيب حسب ، والتجميع - التجميع حسب ، والتصفية - وجود ، وخطة الاستعلام. لكن أول الأشياء أولاً.
ترتيب حسب
يقوم الترتيب حسب المشغل بفرز قيم الإخراج ، أي يفرز القيمة المسترجعة حسب عمود معين. يمكن أيضًا تطبيق الفرز بواسطة الاسم المستعار للعمود الذي تم تعريفه باستخدام عامل التشغيل.
ميزة Order by هي أنه يمكن تطبيقه على كل من الأعمدة الرقمية والسلسلة. عادة ما يتم فرز أعمدة السلسلة أبجديًا.
يتم تطبيق التصنيف التصاعدي افتراضيًا. إذا كنت تريد فرز الأعمدة بترتيب تنازلي ، فاستخدم عامل التشغيل DESC الإضافي.
بناء الجملة:
حدد العمود 1 ، العمود 2 ، ... (يشير إلى الاسم)
من اسم الجدول الترتيب
حسب العمود 1 ، العمود 2 ... ASC | DESC ؛
دعونا نلقي نظرة على كل شيء مع الأمثلة:
في الجدول الأول ، نحصل على جميع البيانات ونفرزها بترتيب تصاعدي حسب عمود المعرف.
في الثانية ، نحصل أيضًا على جميع البيانات. قم بالفرز حسب عمود المعرف بترتيب تنازلي باستخدام الكلمة الأساسية DESC .
يستخدم الجدول الثالث عدة حقول فرز. يأتي أولاً الفرز حسب القسم. إذا كان العامل الأول مساويًا للحقول التي لها نفس القسم ، فسيتم تطبيق شرط الفرز الثاني ؛ في حالتنا هذا هو الراتب.
انها بسيطة جدا. يمكننا تحديد أكثر من شرط فرز واحد ، مما يسمح لنا بفرز قوائم الإخراج بشكل أكثر ذكاءً.
مجموعة من
في SQL ، تجمع المجموعة حسب العبارة البيانات المسترجعة من قاعدة بيانات في مجموعات محددة. يقسم التجميع جميع البيانات إلى مجموعات منطقية بحيث يمكن إجراء الحسابات الإحصائية بشكل منفصل في كل مجموعة.
يستخدم هذا العامل لدمج نتائج التحديد بعمود واحد أو أكثر. بعد التجميع ، سيكون هناك إدخال واحد فقط لكل قيمة مستخدمة في العمود.
يرتبط استخدام SQL Group حسب العبارة ارتباطًا وثيقًا باستخدام الوظائف التجميعية وعبارة امتلاك SQL. الدالة التجميعية في SQL هي دالة تقوم بإرجاع قيمة مفردة عبر مجموعة من قيم الأعمدة. على سبيل المثال: COUNT () ، MIN () ، MAX () ، AVG () ، SUM ()
بناء الجملة:
حدد اسم العمود (الأسماء)
من اسم الجدول
أين الشرط تجميع
حسب اسم العمود (الأسماء)
ترتيب حسب اسم العمود (الأسماء) ؛ يظهر التجميع
حسب بعد جملة WHERE الشرطية في استعلام SELECT . يمكنك اختياريا استخدام ORDER BY لفرز قيم المخرجات.
لذلك ، بناءً على الجدول من المثال السابق ، نحتاج إلى إيجاد الحد الأقصى للراتب للموظفين في كل قسم. أن تتضمن العينة النهائية اسم القسم والراتب الأقصى.
الحل 1 (بدون استخدام التجميع):
SELECT DISTINCT
ie.department
ie.slary
FROM itx_employee ie
WHERE ie.salary = (
SELECT
max(ie1.salary)
FROM itx_employee ie1
WHERE ie.department = ie1.department
)
الحل 2 (باستخدام التجميع):
SELECT
department,
max(salary)
FROM itx_employee
GROUP BY department
في المثال الأول ، نحل المشكلة دون استخدام التجميع ، ولكن باستخدام التحديد الفرعي ، أي ضع الثاني في اختيار واحد. في الحل الثاني ، نستخدم التجميع.
المثال الثاني أقصر وأكثر قابلية للقراءة ، على الرغم من أنه يؤدي نفس الوظائف مثل الأول.
كيف يعمل "التجميع حسب" بالنسبة لنا: أولاً يتم تقسيم القسمين إلى مجموعتي qa و dev. ثم يبحث عن الحد الأقصى للراتب لكل منهم.
نأخذ
امتلاك أداة تصفية. يشير إلى نتيجة أداء وظائف مجمعة. يتم استخدام شرط وجود في SQL حيث لا يمكن استخدام WHERE.
إذا كانت جملة WHERE تحدد مسندًا لتصفية الصفوف ، فسيتم استخدام الخاصية بعد التجميع لتعريف المسند المنطقي الذي يقوم بتصفية المجموعة حسب قيم الوظائف التجميعية. البند ضروري لاختبار القيم التي تم الحصول عليها باستخدام وظائف التجميع من مجموعات الصفوف.
بناء الجملة:
SELECT COLUMN_NAME (ق)
من TABLE_NAME
WHERE حالة
GROUP BY COLUMN_NAME (ق)
بصعوبات حالة
الأولى، نعرض الإدارات مع أكبر متوسط الراتب من 4000. ثم نعرض الحد الأقصى للراتب باستخدام تصفية.
الحل 1 (بدون استخدام GROUP BY و HAVING):
SELECT DISTINCT
ie.department AS "DEPARTMENT",
(
(SELECT
AVG(ie1.salary)
FROM itx_employee ie1
WHERE ie1.department = ie.department)
) AS "AVG SALARY"
FROM itx_employee ie
where (SELECT
AVG(ie1.salary)
FROM itx_employee ie1
WHERE ie1.department = ie.department) > 4000
الحل 2 (باستخدام GROUP BY و HAVING):
SELECT
department,
AVG(salary)
FROM itx_employee
GROUP BY department
HAVING AVG(salary) > 4000
يستخدم المثال الأول خيارين فرعيين ، أحدهما للعثور على الحد الأقصى للراتب ، والآخر لتصفية متوسط الراتب. المثال الثاني ، مرة أخرى ، جاء بشكل أبسط وأكثر إيجازًا.
اطلب الخطة
غالبًا ما تكون هناك مواقف عندما يتم تشغيل الطلب لفترة طويلة ، مما يستهلك موارد كبيرة من الذاكرة والأقراص. لفهم سبب تشغيل الاستعلام لفترة طويلة وغير فعالة ، يمكننا إلقاء نظرة على خطة الاستعلام.
خطة الاستعلام هي خطة التنفيذ المقصودة للاستعلام ، أي كيف سيقوم DBMS بتنفيذه. سيقوم DBMS بتدوين جميع العمليات التي سيتم تنفيذها داخل الاستعلام الفرعي. بعد تحليل كل شيء ، سنتمكن من فهم نقاط الضعف في الطلب واستخدام خطة الاستعلام يمكننا تحسينها.
يؤدي تنفيذ أي عبارة SQL في Oracle إلى استرداد ما يسمى بـ "خطة التنفيذ". خطة تنفيذ الاستعلام هي وصف لكيفية قيام Oracle بجلب البيانات وفقًا لعبارة SQL التي يتم تنفيذها. الخطة هي شجرة تحتوي على ترتيب الخطوات والعلاقة بينهما.
الأدوات التي تسمح لك بالحصول على خطة التنفيذ المقدرة لاستعلام ما تشمل Toad ، و SQL Navigator ، و PL / SQL Developer ، وما إلى ذلك ، فهي توفر عددًا من مؤشرات استهلاك الموارد لاستعلام ، من بينها المؤشرات الرئيسية: التكلفة - تكلفة التنفيذ والعلاقة الأساسية (أو الصفوف ) - العلاقة الأساسية (أو الكمية) خطوط).
وكلما زادت قيمة هذه المؤشرات ، قل كفاءة الاستعلام.
أدناه يمكنك رؤية تحليل خطة الاستعلام. يستخدم الحل الأول تحديدًا فرعيًا ، بينما يستخدم الحل الثاني التجميع. لاحظ أن الحل الأول عالج 22 صفًا ، بينما تمت معالجة الحل الثاني 15.
تحليل خطة الاستعلام: تحليل خطة استعلام
آخر يستخدم اختيارين فرعيين:
يتم تقديم هذا المثال كمتغير للاستخدام غير الفعال لأدوات SQL ولا أوصي باستخدامه في استعلاماتك.
ستجعل جميع الميزات المذكورة أعلاه حياتك أسهل عند كتابة الاستفسارات وتزيد من جودة الكود وقابليته للقراءة.
الجزء 2: وظائف النافذة
يعود تاريخ وظائف النافذة إلى Microsoft SQL Server 2005. وهي تقوم بإجراء عمليات حسابية على نطاق معين من الصفوف ضمن عبارة "تحديد". باختصار ، "النافذة" هي مجموعة من الأسطر التي يتم فيها الحساب. تسمح لك "النافذة" بتقليل البيانات ومعالجتها بشكل أفضل. تتيح لك هذه الميزة تقسيم مجموعة البيانات بأكملها إلى نوافذ.
Windowing ميزة كبيرة. ليست هناك حاجة لتكوين مجموعة بيانات للحسابات ، مما يسمح لك بحفظ جميع صفوف المجموعة بمعرفها الفريد. تتم إضافة نتيجة وظائف النافذة إلى التحديد الناتج في حقل آخر.
بناء الجملة:
حدد اسم
العمود (الأسماء) التجميعية (العمود المراد حسابه)
أكثر من ([ الجزء حسبعمود للمجموعة]
FROM table_name
[ ترتيب حسب العمود للفرز]
[ ROWS أو RANGE تعبير لتقييد الصفوف داخل مجموعة])
OVER PARTITION BY هي خاصية لتعيين حجم النافذة. هنا يمكنك تحديد معلومات إضافية ، وإعطاء أوامر الخدمة ، على سبيل المثال ، إضافة رقم سطر. يناسب بناء جملة وظيفة النافذة الحق في تحديد العمود.
دعونا نلقي نظرة على كل شيء بمثال: تمت إضافة قسم آخر إلى جدولنا ، والآن يوجد 15 صفًا في الجدول. سنحاول سحب الموظفين ورواتبهم وكذلك الحد الأقصى لراتب المنظمة.
في الحقل الأول نأخذ الاسم ، وفي الحقل الثاني - الراتب. بعد ذلك ، نستخدم وظيفة النافذة ()... نستخدمها للحصول على الحد الأقصى للراتب في جميع أنحاء المنظمة ، حيث لا يتم تحديد حجم "النافذة". يتم تطبيق Over () بأقواس فارغة على التحديد بالكامل. لذلك ، الحد الأقصى للراتب في كل مكان هو 10000. يتم إضافة نتيجة عمل وظيفة النافذة إلى كل سطر.
إذا أزلنا ذكر وظيفة النافذة من السطر الرابع للاستعلام ، أي فقط ماكس (الراتب) متبقي ، لن يعمل الطلب. ببساطة لا يمكن حساب الحد الأقصى للراتب. نظرًا لأنه سيتم معالجة البيانات سطرًا بسطر ، وفي وقت الاتصال بحد أقصى (الراتب) ، سيكون هناك رقم واحد فقط في السطر الحالي ، أي موظف حالي. هذا هو المكان الذي يمكنك فيه رؤية ميزة وظيفة النافذة. في وقت المكالمة ، تعمل مع النافذة بأكملها ومع جميع البيانات المتاحة.
لنلق نظرة على مثال آخر حيث تحتاج إلى عرض الحد الأقصى للراتب لكل قسم:
في الواقع ، قمنا بتعيين إطار "النافذة" ، ونقسمها إلى أقسام. نحن نستخدم القسم كمثال الترتيب. لدينا ثلاثة أقسام: dev ، qa والمبيعات.
يبحث "Window" عن الحد الأقصى للراتب لكل قسم. نتيجة للاختيار ، نرى أنه وجد الحد الأقصى للراتب أولاً للمطور ، ثم qa ، ثم المبيعات. كما هو مذكور أعلاه ، تتم كتابة نتيجة وظيفة النافذة في نتيجة الجلب لكل صف.
في المثال السابق ، لم يتم تحديد الأقواس بعد over. استخدمنا هنا PARTITION BY ، مما سمح لنا بتعيين حجم نافذتنا. هنا يمكنك تحديد بعض المعلومات الإضافية ، وإرسال أوامر الخدمة ، على سبيل المثال ، رقم السطر.
خاتمة
SQL ليست بسيطة كما تبدو للوهلة الأولى. كل ما هو موصوف أعلاه هو الوظيفة الأساسية لوظائف النافذة. بمساعدتهم ، يمكنك "تبسيط" طلباتنا. ولكن هناك الكثير من الإمكانات المخبأة فيها: هناك مشغلي خدمات (على سبيل المثال ROWS أو RANGE) يمكن دمجها لإضافة المزيد من الوظائف إلى الاستعلامات.
آمل أن يكون المنشور مفيدًا لجميع المهتمين بهذا الموضوع.