Mehrzeilige Zusammenführungsfunktionen „collect_set“, „collect_list“, „concat_ws“ in SQL

Nehmen Sie N Felder aus der Datenbank, kombinieren Sie sie und verwenden Sie „“, um die Anzeige zu trennen. Zuerst dachte ich daran, CONCAT() zur Verarbeitung zu verwenden, aber es war unerwartet, dass CONCAT_WS() darin erwähnt wurde Handbuch, das sehr einfach zu bedienen ist.

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

Es handelt sich um eine Sonderform von CONCAT(). Der erste Parameter ist das Trennzeichen zwischen den übrigen Parametern. Wenn das Trennzeichen NULL ist, ist der Rückgabewert auch NULL Überspringt alle NULL- und leeren Zeichenfolgen nach dem Trennzeichenparameter. Das Trennzeichen wird zwischen den zu verkettenden Zeichenfolgen eingefügt.

Ein einfaches Beispiel ist wie folgt:

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'

Zu den sammelbezogenen Funktionen in Hive gehören „collect_list“ und „collect_set“.

Beide konvertieren eine Spalte in der Gruppierung in ein Array und geben sie zurück. Der Unterschied besteht darin, dass „collect_list“ keine Duplikate entfernt, „collect_set“ jedoch.

Führen Sie ein einfaches Experiment durch, um Ihr Verständnis zu vertiefen, und erstellen Sie eine experimentelle Tabelle, um die Aufzeichnungen der täglichen On-Demand-Videos der Benutzer zu speichern:

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

Erstellen Sie eine Testdatendatei im lokalen Dateisystem:

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

Daten in die Hive-Tabelle laden:

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

Gruppieren Sie nach Benutzer und rufen Sie die Namen aller Videos ab, die sich jeder Benutzer jeden Tag ansieht:

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

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

Es gibt jedoch ein Problem mit den obigen Abfrageergebnissen, da Li Si es zweimal angesehen hat. Dies führt direkt zu Duplikaten in der Liste der angesehenen Videos, daher sollte „Collect_Set“ verwendet werden Der Unterschied zu „collect_list“ besteht darin, dass Duplikate entfernt werden:

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

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

Farewell My Concubine erscheint nur einmal in Li Sis Betrachtungsaufzeichnungen, wodurch der Effekt einer Deduplizierung erzielt wird.

Durchbrechen Sie Gruppenbeschränkungen

Sie können Collect auch verwenden, um die Einschränkungen von Group By zu durchbrechen. In Hive ist es beim Abfragen von Group By erforderlich, dass die nach Select angezeigten Spalten nach Group By angezeigt werden, dh die Select-Spalte muss die Spalte sein, die als verwendet wird die Grundlage für die Gruppierung, aber einige Manchmal möchten wir nach A gruppieren und dann zufällig ein B aus jeder Gruppe herausnehmen. In diesem Experiment gruppieren wir nach Benutzer und wählen dann zufällig den Namen eines Videos aus, das er gesehen hat:

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

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

video_name ist keine Gruppierungsspalte und die Daten in dieser Spalte können weiterhin abgerufen werden.

Auszüge zusammengefasst in:

HTTPS://wuwuwu.cn Blog on.com/loco有/archive/2006/10/28/542751.HTML

HTTPS://wuwuwu.cn blog on.com/Cao Cao11001100/fear/9043946.HTML