غالبًا ما يسألون عما إذا كان هناك نظائر لوظائف (نافذة) التحليلية في MySQL.
ملاحظة في وقت كتابة هذا التقرير ، لم تكن هناك نظائر مماثلة ، لكن المقال ما زال ذا أهمية أكاديمية من حيث تحليل النهج الأصلي لاستخدام المتغيرات في MySQL.لاستبدال الدوال التحليلية ، غالبًا ما يتم استخدام استعلامات الاتصال الذاتي والاستعلامات الفرعية المعقدة والمزيد. معظم هذه الحلول غير فعالة من حيث الأداء.
أيضا في MySQL ليس هناك عودية. ومع ذلك ، يمكن معالجة بعض المهام التي عادة ما يتم حلها عن طريق الدوال التحليلية أو الإعادة بواسطة أدوات MySQL.
واحدة من هذه الأدوات هي فريدة من نوعها ، غير معهود لآلية DBMS الأخرى للعمل مع المتغيرات داخل استعلام SQL. يمكننا إعلان متغير داخل الاستعلام ، وتغيير قيمته واستبداله في SELECT للإخراج. علاوة على ذلك ، يمكن تعيين ترتيب معالجة الأسطر في الطلب ، ونتيجة لذلك ، يمكن تعيين ترتيب تعيين القيم للمتغيرات في ترتيب مخصص!
تحذير تفترض المقالة أن معالجة التعبيرات في جملة SELECT يتم تنفيذها من اليسار إلى اليمين ، ومع ذلك ، لا يوجد تأكيد رسمي لترتيب المعالجة هذا في وثائق MySQL. يجب وضع ذلك في الاعتبار عند تغيير إصدار الخادم. لضمان الاتساق ، يمكنك استخدام عبارة CASE أو IF وهمية.
العودية التناظرية
فكر في مثال بسيط يولد تسلسل فيبوناتشي (في تسلسل فيبوناتشي ، كل مصطلح يساوي مجموع المجموعتين السابقتين ، والثاني الأول يساوي واحد):
SELECT IF(X=1, Fn_1, Fn_2) F FROM( SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2 FROM (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a, (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b, (SELECT @I := 1, @J := 1)IJ )T, (SELECT 1 X UNION ALL SELECT 2)X;
يولد هذا الاستعلام 18 رقمًا فيبوناتشي ، دون حساب الرقمين الأولين:
2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765
الآن دعونا نرى كيف يعمل.
في الأسطر 5) 6) يتم إنشاء 9 سجلات. لا شيء غير عادي هنا.
في السطر 7) نعلن اثنين من المتغيراتI ،J وتعيينهم 1.
في السطر 3) ، يحدث ما يلي: أولاً ، يتم تعيين المتغير @ I على مجموع المتغيرين. ثم نخصص نفسه للمتغيرJ ، مع الأخذ في الاعتبار حقيقة أن قيمةI قد تغيرت بالفعل.
بمعنى آخر ، يتم إجراء العمليات الحسابية في SELECT من اليسار إلى اليمين - راجع أيضًا الملاحظة في بداية المقالة.
علاوة على ذلك ، يتم تغيير المتغيرات في كل من سجلاتنا التسعة ، أي عند معالجة كل سطر جديد ، سوف تحتوي المتغيراتI وJ على القيم المحسوبة بمعالجة السطر السابق.
لحل نفس المشكلة بمساعدة قواعد بيانات إدارة قواعد البيانات الأخرى ، يتعين علينا كتابة
استعلام تكراري!ملاحظة:يجب التصريح عن المتغيرات في استعلام فرعي منفصل (السطر 7) ، إذا أعلنا عن متغير في جملة SELECT ، فمن المرجح أن يتم تقييمه مرة واحدة فقط (على الرغم من أن السلوك المحدد يعتمد على إصدار الخادم). يتم تحديد نوع المتغير حسب القيمة التي يتم بها تهيئة. هذا النوع يمكن أن يتغير بشكل حيوي. إذا قمت بتعيين المتغير إلى NULL ، فسيكون نوعه BLOB.يعتمد ترتيب معالجة الصفوف في SELECT ، كما ذكر أعلاه ، على الفرز المخصص. مثال بسيط لترقيم الأسطر بترتيب معين:
SELECT val, @I:=@I+1 Num FROM (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a, (SELECT @I := 0)I ORDER BY val;
Val Num 10 1 20 2 30 3 50 4
نظائرها من وظائف التحليل
يمكن أيضًا استخدام المتغيرات لاستبدال الدوال التحليلية. فيما يلي بعض الأمثلة. للبساطة ، نفترض أن جميع الحقول ليست فارغة ، وأن الفرز والتقسيم (PARTITION BY) يحدث في حقل واحد. استخدام القيم الفارغة والفرز المعقدة سيجعل الأمثلة أكثر تعقيدًا ، لكن الجوهر لن يتغير.
على سبيل المثال ، قم بإنشاء جدول TestTable:
CREATE TABLE TestTable( group_id INT NOT NULL, order_id INT UNIQUE NOT NULL, value INT NOT NULL );
اين
group_id - معرف المجموعة (التناظرية لنافذة الوظيفة التحليلية) ؛
order_id - حقل فريد للفرز ؛
القيمة هي بعض القيمة العددية.
املأ جدولنا ببيانات الاختبار:
INSERT TestTable(order_id, group_id, value) SELECT * FROM( SELECT 1 order_id, 1 group_id, 1 value UNION ALL SELECT 2, 1, 2 UNION ALL SELECT 3, 1, 2 UNION ALL SELECT 4, 2, 1 UNION ALL SELECT 5, 2, 2 UNION ALL SELECT 6, 2, 3 UNION ALL SELECT 7, 3, 1 UNION ALL SELECT 8, 3, 2 UNION ALL SELECT 9, 4, 1 UNION ALL SELECT 11, 3, 2 )T;
أمثلة على استبدال بعض الوظائف التحليلية.
1) ROW_NUMBER () OVER (ترتيب ORDER BY order_id)
SELECT T.*, @I:=@I+1 RowNum FROM TestTable T,(SELECT @I:=0)I ORDER BY order_id;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10
2) ROW_NUMBER () تجاوز (PARTITION BY group_id ORDER BY order_id)
SELECT group_id, order_id, value, RowNum FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNum, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1
3) SUM (value) OVER (PARTITION BY group_id ORDER BY order_id)
SELECT group_id, order_id, value, RunningTotal FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+value, @I:=value) RunningTotal, @last_group_id := group_id FROM TestTable T, (SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1
4) LAG (قيمة) تجاوز (PARTITION BY group_id ORDER BY order_id)
SELECT group_id, order_id, value, LAG FROM( SELECT T.*, IF(@last_group_id = group_id, @last_value, NULL) LAG, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL
بالنسبة إلى LEAD ، كل شيء هو نفسه ، فقط تحتاج إلى تغيير الفرز إلى ORDER BY group_id ، order_id DESC
بالنسبة للوظائف COUNT و MIN و MAX ، يكون كل شيء أكثر تعقيدًا إلى حد ما ، لأنه حتى نحلل جميع الخطوط في المجموعة (نافذة) ، لن نتمكن من معرفة قيمة الوظيفة. MS SQL ، على سبيل المثال ، "التخزين المؤقت" نافذة لهذه الأغراض (يضع مؤقتًا صفوف النافذة في جدول مؤقت مخفي للوصول إليها مرة أخرى) ، في MySQL لا يوجد مثل هذا الاحتمال. ولكن يمكننا حساب قيمة الوظيفة في الصف الأخير لكل نافذة لفرز معين (على سبيل المثال ، بعد تحليل الإطار بأكمله) ، وبعد ذلك ، فرز الخطوط في النافذة بالترتيب العكسي ، ثم ضع القيمة المحسوبة عبر النافذة بأكملها.
لذلك نحن بحاجة إلى اثنين من الفرز. بحيث يظل الفرز النهائي كما هو في الأمثلة أعلاه ، نقوم أولاً بالفرز حسب الحقول group_id ASC ، order_id DESC ، ثم حسب الحقول group_id ASC ، order_id ASC.
5) COUNT (*) OVER (PARTITION BY group_id)
في النوع الأول ، نقوم ببساطة بترقيم الإدخالات. في الثانية ، نقوم بتعيين الحد الأقصى لعدد جميع أسطر النافذة ، والتي سوف تتوافق مع عدد الخطوط في النافذة.
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxRowNum, @MaxRowNum := RowNumDesc) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNumDesc, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxRowNum:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1
يتم حساب الدالتين MAX و MIN عن طريق القياس. سأقدم فقط مثالًا لماكس:
6) MAX (القيمة) أكثر من (PARTITION BY group_id)
SELECT group_id, order_id, value, MaxVal FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxVal, @MaxVal := MaxVal) MaxVal, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, GREATEST(@MaxVal, value), @MaxVal:=value) MaxVal, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
7) COUNT (قيمة مميزة) أكثر من (PARTITION BY group_id)
شيء مثير للاهتمام غير متوفر في MS SQL Server ، ولكن يمكن حسابه باستخدام استعلام فرعي عن طريق أخذ MAX من RANK. سنفعل نفس الشيء هنا. في النوع الأول ، نحسب RANK () OVER (PARTITION BY group_id ORDER BY value DESC) ، ثم في الترتيب الثاني نضع القيمة القصوى لجميع الأسطر في كل نافذة:
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @Rank, @Rank := Rank) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, IF(@last_value = value, @Rank, @Rank:=@Rank+1) , @Rank:=1) Rank, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL, @Rank:=0)I ORDER BY group_id, value DESC, order_id DESC )T,(SELECT @last_group_id:=NULL, @Rank:=NULL)I ORDER BY group_id, value, order_id )T;
group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
الأداء
بادئ ذي بدء ، قارنا أداء ترقيم الأسطر في استعلام باستخدام الربط الذاتي والمتغيرات.
1) الطريقة الكلاسيكية مع ربط النفس
SELECT COUNT(*)N, T1.* FROM TestTable T1 JOIN TestTable T2 ON T1.order_id >= T2.order_id GROUP BY T1.order_id;
ماذا عن سجلات 10000 في الجدول تنتج TestTable:
المدة / إحضار
16.084 ثانية / 0.016 ثانية
2) استخدام المتغيرات:
SELECT @N:=@N+1 N, T1.* FROM TestTable T1, (SELECT @N := 0)M ORDER BY T1.order_id;
وتنتج:
المدة / إحضار
0.016 ثانية / 0.015 ثانية
النتيجة تتحدث عن نفسها. ومع ذلك ، يجب فهم أن القيم المحسوبة باستخدام المتغيرات لا يتم استخدامها على النحو الأمثل في شروط التصفية. سيحدث الفرز والحساب لجميع الصفوف ، على الرغم من أننا في النهاية نحتاج إلى جزء صغير منها فقط.
دعنا نفكر بمزيد من التفاصيل بمثال هذه المهمة:
قم بطباعة أول صفين من جدول TestTable لكل قيمة group_id ، مرتبة حسب order_id.فيما يلي كيفية حل هذه المهمة في نظام إدارة قواعد البيانات مع دعم للوظائف التحليلية:
SELECT group_id, order_id, value FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum FROM TestTable )T WHERE RowNum <= 2;
ومع ذلك ، لا يعرف مُحسِّن MySQL شيئًا عن القواعد التي نحسب بها حقل RowNum. سيتعين عليه ترقيم جميع الخطوط ، وعندها فقط حدد الخطوط اللازمة.
الآن تخيل أن لدينا 1 مليون سجل و 20 قيمة group_id فريدة. أي لتحديد 40 صفًا ، سيقوم MySQL بحساب قيمة RowNum لمليون صف! لا يوجد حل جميل لهذه المشكلة باستخدام استعلام واحد في MySQL. ولكن يمكنك أولاً الحصول على قائمة بقيم group_id الفريدة ، على سبيل المثال ، مثل هذا:
SELECT DISTINCT group_id FROM TestTable;
ثم ، باستخدام أي لغة برمجة أخرى ، قم بإنشاء استعلام للنموذج:
SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2 UNION ALL SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2 UNION ALL … SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2;
20 استعلامات سهلة ستعمل بشكل أسرع بكثير من حساب RowNum لمليون سطر.