وظائف دمج متعددة الصفوف، Collect_set، Collect_list، concat_ws في SQL

خذ حقول N من قاعدة البيانات، ثم قم بدمجها معًا واستخدم "،" لفصل العرض في البداية، فكرت في استخدام CONCAT () لمعالجتها، ولكن تم ذكر CONCAT_WS () بشكل غير متوقع دليل، وهو سهل الاستخدام للغاية.

CONCAT_WS(separator, str1, str2,...)

إنه شكل خاص من CONCAT(). المعلمة الأولى هي الفاصل بين المعلمات المتبقية. يمكن أن يكون المحدد هو نفس سلسلة المعلمات المتبقية. إذا كان المحدد NULL، فستكون قيمة الإرجاع NULL أيضًا سوف يقفز أي سلاسل فارغة وفارغة بعد المعلمة المحددة، سيتم إضافة المحدد بين السلاسل المتسلسلة.

مثال بسيط هو كما يلي:

mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
       -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
       -> 'First name,Last Name'

تتضمن الوظائف المرتبطة بالتجميع في الخلية Collect_list وcollect_set.

يقوم كلاهما بتحويل عمود في المجموعة إلى مصفوفة وإعادته. والفرق هو أن Collect_list لا يزيل التكرارات ولكن Collect_set يفعل ذلك.

قم بإجراء تجربة بسيطة لتعميق فهمك، وقم بإنشاء جدول تجريبي لتخزين سجلات مقاطع الفيديو اليومية حسب الطلب للمستخدمين:

create table t_visit_video (
    username string,
    video_name string
) partitioned by (day string)
row format delimited fields terminated by ',';

قم بإنشاء ملف بيانات اختبار على نظام الملفات المحلي:

张三,大唐双龙传
李四,天下无贼
张三,神探狄仁杰
李四,霸王别姬
李四,霸王别姬
王五,机器人总动员
王五,放牛班的春天
王五,盗梦空间

تحميل البيانات إلى جدول الخلية:

load data local inpath '/root/hive/visit.data' into table t_visit_video partition (day='20180516');

قم بالتجميع حسب المستخدم واسترجاع أسماء جميع مقاطع الفيديو التي يشاهدها كل مستخدم يوميًا:

select username, collect_list(video_name) from t_visit_video group by username ;

张三  ["大唐双龙传","神探狄仁杰"]
李四  ["天下无贼","霸王别姬","霸王别姬"]
…………

ومع ذلك، هناك مشكلة في نتائج الاستعلام أعلاه. نظرًا لأن Farewell My Concubine جيد جدًا، فقد شاهده Li Si مرتين، مما يؤدي مباشرة إلى تكرارات في قائمة مقاطع الفيديو التي تمت مشاهدتها، لذا يجب إضافة التكرارات واستخدام مجموعة_set الفرق بينه وبين Collect_list هو أنه سيزيل التكرارات:

select username, collect_set(video_name) from t_visit_video group by username;

张三  ["大唐双龙传","神探狄仁杰"]
李四  ["天下无贼","霸王别姬"]
…………

تظهر Farewell My Concubine مرة واحدة فقط في سجلات المشاهدة الخاصة بـ Li Si، مما يحقق تأثير إلغاء التكرار.

اختراق المجموعة بالقيود

يمكنك أيضًا استخدام التجميع لاختراق قيود المجموعة حسب، في الخلية، عند الاستعلام عن المجموعة حسب، من المطلوب أن تظهر الأعمدة التي تظهر بعد التحديد بعد المجموعة حسب، أي أن عمود التحديد يجب أن يكون العمود المستخدم كـ. أساس التجميع، ولكن في بعض الأحيان نريد التجميع وفقًا لـ A ثم نخرج بشكل عشوائي B في كل مجموعة. في هذه التجربة، نقوم بالتجميع حسب المستخدم، ثم نختار عشوائيًا اسم الفيديو الذي شاهده:

select username, collect_list(video_name)[0] from t_visit_video group by username;

张三  大唐双龙传
李四 天下无贼
…………

video_name ليس عمود تجميع، ولا يزال من الممكن استرداد البيانات الموجودة في هذا العمود.

مقتطفات ملخصة في:

HTTPS://wuwuwu.cn مدونة on.com/loco有/archive/2006/10/28/542751.HTML

HTTPS://wuwuwu.cn مدونة on.com/Cao Cao11001100/fear/9043946.HTML