データベースからフィールドをN個取り出して、それを結合して「,」で区切って表示するのですが、最初はCONCAT()で処理しようと思ったのですが、意外とCONCAT_WS()が出てきて面倒でした。マニュアル、とても使いやすいです。
CONCAT_WS(separator, str1, str2,...)
これは CONCAT() の特殊な形式で、最初のパラメータは残りのパラメータ間の区切り文字になります。区切り文字が NULL の場合、戻り値も 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'
Hive の収集関連関数には、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 ',';
ローカル ファイル システム上にテスト データ ファイルを作成します。
张三,大唐双龙传 李四,天下无贼 张三,神探狄仁杰 李四,霸王别姬 李四,霸王别姬 王五,机器人总动员 王五,放牛班的春天 王五,盗梦空间
データを Hive テーブルにロードします。
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 ;
张三 ["大唐双龙传","神探狄仁杰"] 李四 ["天下无贼","霸王别姬","霸王别姬"] …………
ただし、上記のクエリ結果には問題があります。Li Si はそれを 2 回視聴したため、視聴したビデオのリストに重複が発生するため、重複を追加し、collect_set を使用する必要があります。これとcollect_listの違いは、重複を削除することです。
select username, collect_set(video_name) from t_visit_video group by username;
张三 ["大唐双龙传","神探狄仁杰"] 李四 ["天下无贼","霸王别姬"] …………
「さらば我が愛妾」は李斯の視聴記録に一度だけ登場し、重複排除の効果を発揮します。
グループによる制限を突破する
また、collect を使用して group by の制限を突破することもできます。Hive で group by をクエリする場合、select の後に表示される列は group by の後に表示される必要があります。つまり、select 列は、グループ化として使用される列である必要があります。グループ化の基礎ですが、場合によっては、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