GROUP BY استعلام SELECT إلى وضع التجميع، ويعمل ذلك على النحو التالي:
- يحتوي بند
GROUP BYعلى قائمة من التعبيرات (أو تعبيرًا واحدًا يُعدّ قائمةً بطول عنصر واحد). وتعمل هذه القائمة بوصفها “مفتاح التجميع”، بينما يُشار إلى كل تعبير فيها على حدة باسم “تعبير مفتاح”. - يجب أن تُحتسب جميع التعبيرات في البنود SELECT وHAVING وORDER BY استنادًا إلى تعبيرات المفاتيح أو إلى الدوال التجميعية المطبّقة على تعبيرات ليست مفاتيح (بما في ذلك الأعمدة العادية). وبعبارة أخرى، يجب استخدام كل عمود مُختار من الجدول إما في تعبير مفتاح أو داخل دالة تجميعية، ولكن ليس في كليهما.
- ستحتوي نتيجة تجميع استعلام
SELECTعلى عدد من الصفوف يساوي عدد القيم الفريدة لـ”مفتاح التجميع” في الجدول المصدر. وعادةً ما يقلّل هذا عدد الصفوف بشكل كبير، وغالبًا بعدة مراتب، لكن ليس بالضرورة: إذ يظل عدد الصفوف كما هو إذا كانت جميع قيم “مفتاح التجميع” متميزة.
توجد طريقة إضافية لإجراء التجميع على جدول. إذا كان الاستعلام يحتوي على أعمدة الجدول داخل الدوال التجميعية فقط، فيمكن حذف
بند GROUP BY، وعندئذٍ يُفترض التجميع على مجموعة مفاتيح فارغة. وتُرجع مثل هذه الاستعلامات دائمًا صفًا واحدًا فقط.معالجة NULL
NULL==NULL. وهذا يختلف عن معالجة NULL في معظم السياقات الأخرى.
إليك مثالًا يوضح المقصود بذلك.
افترض أن لديك هذا الجدول:
SELECT sum(x), y FROM t_null_big GROUP BY y ما يلي:
GROUP BY عند y = NULL جمع قيم x كما لو كانت NULL قيمةً فعلية.
إذا مرّرت عدة مفاتيح إلى GROUP BY، فستعطيك النتيجة جميع تركيبات التحديد، كما لو كانت NULL قيمةً محددة.
مُعدِّل ROLLUP
ROLLUP لحساب المجاميع الفرعية لتعبيرات المفاتيح، استنادًا إلى ترتيبها في قائمة GROUP BY. وتُضاف صفوف المجاميع الفرعية بعد جدول النتائج.
تُحسَب المجاميع الفرعية بترتيب عكسي: في البداية تُحسَب المجاميع الفرعية لآخر تعبير مفتاح في القائمة، ثم للتعبير الذي يسبقه، وهكذا حتى أول تعبير مفتاح.
في صفوف المجاميع الفرعية، تُضبط قيم تعبيرات المفاتيح التي سبق “تجميعها” على 0 أو سلسلة فارغة.
انتبه إلى أن عبارة HAVING قد تؤثر في نتائج المجاميع الفرعية.
Query
GROUP BY يحتوي على ثلاثة من تعبيرات المفاتيح، فإن النتيجة تتضمن أربعة جداول مع مجاميع فرعية “مُجمَّعة تصاعديًا” من اليمين إلى اليسار:
GROUP BY year, month, day;GROUP BY year, month(ويُملأ العمودdayبالأصفار);GROUP BY year(ويُملأ الآن كلٌّ من العمودينmonthوdayبالأصفار);- والإجماليات (وتكون أعمدة تعبيرات المفاتيح الثلاثة جميعها أصفارًا).
Response
WITH.
Query
- إعداد group_by_use_nulls للتوافق مع معيار SQL.
المُعدِّل CUBE
CUBE لحساب المجاميع الفرعية لكل توليفة من تعبيرات المفاتيح في قائمة GROUP BY. وتُضاف صفوف المجاميع الفرعية بعد جدول النتائج.
في صفوف المجاميع الفرعية، تُضبط قيم جميع تعبيرات المفاتيح “المُجمَّعة” على 0 أو سلسلة فارغة.
انتبه إلى أن عبارة HAVING قد يؤثر في نتائج المجاميع الفرعية.
Query
GROUP BY يحتوي على ثلاثة من تعبيرات المفاتيح، فإن النتيجة تتضمن ثمانية جداول فيها مجاميع فرعية لكل تركيبات تعبيرات المفاتيح:
GROUP BY year, month, dayGROUP BY year, monthGROUP BY year, dayGROUP BY yearGROUP BY month, dayGROUP BY monthGROUP BY day- والإجماليات.
GROUP BY بالأصفار.
Response
WITH.
Query
- إعداد group_by_use_nulls لضمان التوافق مع معيار SQL.
مُعدِّل WITH TOTALS
WITH TOTALS، فسيُحسَب صف إضافي. سيحتوي هذا الصف على الأعمدة المفتاحية بقيم افتراضية (أصفار أو قيم فارغة)، وعلى أعمدة الدوال التجميعية بالقيم المحسوبة عبر جميع الصفوف (أي قيم “الإجمالي”).
لا يُنتَج هذا الصف الإضافي إلا في التنسيقات JSON* وTabSeparated* وPretty*، ويكون منفصلًا عن الصفوف الأخرى:
- في التنسيقين
XMLوJSON*، يُخرَج هذا الصف كحقلtotalsمنفصل. - في التنسيقات
TabSeparated*وCSV*وVertical، يأتي الصف بعد النتيجة الرئيسية، وتسبقه سطر فارغ (بعد البيانات الأخرى). - في تنسيقات
Pretty*، يُخرَج الصف كجدول منفصل بعد النتيجة الرئيسية. - في تنسيق
Template، يُخرَج الصف وفقًا للقالب المحدد. - في التنسيقات الأخرى، لا يكون متاحًا.
يُخرَج
totals في نتائج استعلامات SELECT، ولا يُخرَج في INSERT INTO ... SELECT.WITH TOTALS بطرق مختلفة عند وجود HAVING. ويعتمد هذا السلوك على الإعداد totals_mode.
ضبط معالجة الإجماليات
totals_mode = 'before_having'. في هذه الحالة، يُحتسَب ‘totals’ على جميع الصفوف، بما في ذلك الصفوف التي لا تجتاز HAVING وmax_rows_to_group_by.
أما البدائل الأخرى، فتشمل في ‘totals’ فقط الصفوف التي تجتاز HAVING، ويختلف سلوكها مع الإعداد max_rows_to_group_by وgroup_by_overflow_mode = 'any'.
after_having_exclusive – لا تُدرِج الصفوف التي لم تجتز max_rows_to_group_by. بعبارة أخرى، سيكون عدد الصفوف في ‘totals’ أقل من أو مساويًا لما سيكون عليه إذا أُزيل max_rows_to_group_by.
after_having_inclusive – أدرِج جميع الصفوف التي لم تجتز max_rows_to_group_by في ‘totals’. بعبارة أخرى، سيكون عدد الصفوف في ‘totals’ أكبر من أو مساويًا لما سيكون عليه إذا أُزيل max_rows_to_group_by.
after_having_auto – احسب عدد الصفوف التي اجتازت HAVING. إذا كان أكبر من حد معيّن (50% افتراضيًا)، فأدرِج جميع الصفوف التي لم تجتز max_rows_to_group_by في ‘totals’. وإلا، فلا تُدرِجها.
totals_auto_threshold – القيمة الافتراضية هي 0.5. وهو المعامل الخاص بـ after_having_auto.
إذا لم يُستخدَم max_rows_to_group_by وgroup_by_overflow_mode = 'any'، فستكون جميع أشكال after_having متطابقة، ويمكنك استخدام أيٍّ منها (على سبيل المثال، after_having_auto).
يمكنك استخدام WITH TOTALS في الاستعلامات الفرعية، بما في ذلك الاستعلامات الفرعية في عبارة JOIN (وفي هذه الحالة، تُدمَج قيم الإجماليات المقابلة).
GROUP BY ALL
GROUP BY ALL إدراج جميع التعبيرات المحددة في عبارة SELECT التي ليست دوالًا تجميعية.
على سبيل المثال:
GROUP BY أكبر عدد ممكن من الحقول غير التجميعية التي يمكن استخراجها منها.
على سبيل المثال:
أمثلة
GROUP BY مجموعة من قيم الدوال التجميعية.
مُعدِّل GROUPING SETS
GROUPING SETS.
وعلى الرغم من أن الاستعلامات التي تستخدم المُعدِّلات ROLLUP وCUBE وGROUPING SETS متكافئة نحويًا، فقد يختلف أداؤها.
فبينما يحاول GROUPING SETS تنفيذ كل شيء بالتوازي، ينفّذ ROLLUP وCUBE الدمج النهائي للتجميعات في خيط تنفيذ واحد.
عندما تحتوي الأعمدة المصدرية على قيم افتراضية، قد يصعب التمييز بين ما إذا كان الصف جزءًا من التجميع الذي يستخدم تلك الأعمدة كمفاتيح أم لا.
ولحل هذه المشكلة، يجب استخدام الدالة GROUPING.
مثال
الاستعلامان التاليان متكافئان.
- إعداد group_by_use_nulls للتوافق مع معيار SQL.
تفاصيل التنفيذ
تحسين GROUP BY اعتمادًا على مفتاح فرز الجدول
GROUP BY يحتوي على بادئة مفتاح الفرز على الأقل أو دوال حقنية. في هذه الحالة، عند قراءة مفتاح جديد من الجدول، يمكن إنهاء النتيجة المرحلية للتجميع وإرسالها إلى العميل. يُفعَّل هذا السلوك بواسطة الإعداد optimize_aggregation_in_order. يقلّل هذا التحسين من استخدام الذاكرة أثناء التجميع، لكنه قد يبطئ تنفيذ الاستعلام في بعض الحالات.
GROUP BY في الذاكرة الخارجية
GROUP BY.
يحدّد الإعداد max_bytes_before_external_group_by حد استهلاك RAM الذي عنده تُكتب البيانات المؤقتة الخاصة بـ GROUP BY إلى نظام الملفات. وإذا ضُبط على 0 (القيمة الافتراضية)، فسيكون معطّلًا.
وبديلًا من ذلك، يمكنك ضبط max_bytes_ratio_before_external_group_by، الذي يتيح استخدام GROUP BY في الذاكرة الخارجية فقط عندما يصل الاستعلام إلى حد معيّن من الذاكرة المستخدمة.
عند استخدام max_bytes_before_external_group_by، نوصي بضبط max_memory_usage على قيمة تقارب الضعف (أو max_bytes_ratio_before_external_group_by=0.5). وهذا ضروري لأن للتجميع مرحلتين: قراءة البيانات وتكوين البيانات الوسيطة (1)، ثم دمج البيانات الوسيطة (2). ولا يمكن كتابة البيانات إلى نظام الملفات إلا خلال المرحلة 1. وإذا لم تُكتب البيانات المؤقتة، فقد تتطلب المرحلة 2 مقدارًا من الذاكرة يصل إلى المقدار نفسه المطلوب في المرحلة 1.
على سبيل المثال، إذا كان max_memory_usage مضبوطًا على 10000000000 وكنت تريد استخدام التجميع الخارجي، فمن المنطقي ضبط max_bytes_before_external_group_by على 10000000000، وmax_memory_usage على 20000000000. وعند بدء التجميع الخارجي (إذا حدثت كتابة واحدة على الأقل للبيانات المؤقتة)، يكون الحد الأقصى لاستهلاك RAM أعلى بقليل فقط من max_bytes_before_external_group_by.
مع معالجة الاستعلامات الموزعة، يُنفَّذ التجميع الخارجي على الخوادم البعيدة. ولكي يستخدم الخادم الذي يرسل الطلب مقدارًا صغيرًا فقط من RAM، اضبط distributed_aggregation_memory_efficient على 1.
عند دمج البيانات التي كُتبت إلى القرص، وكذلك عند دمج النتائج من الخوادم البعيدة عندما يكون الإعداد distributed_aggregation_memory_efficient مفعّلًا، قد يصل استهلاك الذاكرة إلى 1/256 * the_number_of_threads من إجمالي مقدار RAM.
عند تمكين التجميع الخارجي، إذا كان حجم البيانات أقل من max_bytes_before_external_group_by (أي إن البيانات لم تُكتب إلى القرص)، فسيعمل الاستعلام بالسرعة نفسها كما لو لم يكن التجميع الخارجي مفعّلًا. وإذا كُتبت أي بيانات مؤقتة إلى القرص، فسيكون زمن التشغيل أطول عدة مرات (حوالي ثلاث مرات).
إذا كان لديك ORDER BY مع LIMIT بعد GROUP BY، فإن مقدار RAM المستخدم يعتمد على مقدار البيانات في LIMIT، وليس في الجدول بأكمله. ولكن إذا كان ORDER BY لا يحتوي على LIMIT، فلا تنسَ تمكين الفرز الخارجي (max_bytes_before_external_sort).