初めに
数値データであれば合計を取ったり，平均を取ったりすればいいですが，
文字データの場合は合計も平均も取れないので，大抵は中身を全て配列に入れてしまうかすると思います．
例えば，以下のようなデータがあるとします．
（適当に作成したので旬がいつとかは無視でお願いします．）
「果物」の項目で集計したい場合，
| 日付 | 果物 | 英名 | 収穫担当 | 収穫量(kg) |
|---|---|---|---|---|
| 9月1日 | りんご | apple | 鈴木 | 4 |
| 9月5日 | みかん | orange | 佐藤 | 2 |
| 9月10日 | りんご | apple | 鈴木 | 3 |
| 9月12日 | もも | peach | 田中 | 5 |
| 9月14日 | りんご | apple | 高橋 | 3 |
| 9月18日 | みかん | orange | 佐藤 | 2 |
以下のようにするのがよさそうです．（日付は表示しないようにする）
| 果物 | 英名 | 収穫担当 | 収穫量の合計(kg) |
|---|---|---|---|
| りんご | apple | [鈴木，高橋] | 10 |
| みかん | orange | [佐藤] | 4 |
| もも | peach | [田中] | 5 |
「収穫担当」の列は，Snowflakeならarray_aggを，
「収穫量の合計」の列は，sumを使えば良いかと思います．
ただ，「英名」の列は，「果物」の列の要素と1対1で対応しています．
「収穫担当」の列同様，配列としてまとめてしまうことも可能ですが，他にもいろいろ方法がありそうなので，いろいろ調べてみました．
※本記事はSnowflake上での話になります．
候補
MAXを使う(クリックでコードを表示)
SELECT 果物, MAX(英名), array_agg(DISTINCT 収穫担当), sum(収穫量)
FROM <テーブル名>
GROUP BY 果物;
列に格納された数値で最大のものを得るための関数ですが，文字配列に対しても使用可能です．
GROUP BYでまとめた時に，「果物」の値に対応する文字列が一つしかないならば，これでOKです．
GROUP BYに入れてしまう．(クリックでコードを表示)
SELECT 果物, 英名, array_agg(DISTINCT 収穫担当), sum(収穫量)
FROM <テーブル名>
GROUP BY 果物, 英名;
- 「果物」 = りんご というデータ
- 「果物」 = りんご かつ 「英名」 = Apple というデータ
果物一つに対して英名が一つしかないのだから，上記でのどちらでまとめても得られる結果は同じになります．
array_aggを使う．(クリックでコードを表示)
SELECT 果物, array_agg(DISTINCT 英名), array_agg(DISTINCT 収穫担当), sum(収穫量)
FROM <テーブル名>
GROUP BY 果物, 英名;
[]がつきますが，「収穫担当」の佐藤，田中のような感じでまとめられます．
modeを使う．(クリックでコードを表示)
SELECT 果物, mode(英名), array_agg(DISTINCT 収穫担当), sum(収穫量)
FROM <テーブル名>
GROUP BY 果物;
最頻値を出力します．「果物」 = りんごのデータには，「英名」 = apple というデータしか入ってないので，当然りんごに対してはappleが得られますね．
他にも候補がありましたらコメントで教えていただけるとありがたいです．
メリット・デメリット
職場のデータで実行速度をまとめてみました．（試行回数3回）
(500万件→4万件に集約）
| MAX | GROUP BY | array_agg | mode |
|---|---|---|---|
| 71 ms | 50 ms | 61 ms | 56 ms |
| 67 ms | 69 ms | 65 ms | 64 ms |
| 43 ms | 68 ms | 56 ms | 55 ms |
誤差！自分が試した限りではどれも変わらないっぽいです．
それぞれの関数に対してのメリット・デメリット(所感)ですが，
- MAX : データ全件において，1対1対応していることを確認しないと使いづらい
- GROUP BY : SELECTの部分にも，GROUP BYの部分にも列の名前を書かなければならないため面倒
- array_agg : 1対1で対応していなくても使える
(「みかん」という値に対して，orangeだけじゃなく，tangerineという値が格納されていても集計時にそれに気づくことができる．）
（ただし，orangeに対してtangerineがごく少数でも，[orange, tangerine]とあたかも同数かのように表示されてしまう） - mode : 1対1対応していなくても使える．
(orengeというスペルミスが少量あっても，表示されるのは大多数を占める正しい表記「orange」である．ただ，集計時にスペルミスがあることには気付けない．）
これぐらいでしょうか．
総括
array_aggが，イレギュラーにも対応しやすいですし．やはり便利でしょうか．
ただ，関数の使い方に自分ルールを設ければ，後からコードを見返した時に列の対情報がわかるのはメリットだと思います．
自分ルールの例:
・MAXを使っている列はGROUP BYで指定された列と1対1で対応している．
・array_aggを使っている列はGROUP BYで指定された列と1対1で対応していない．
・modeを使っている列はGROUP BYで指定された列とほとんどの場合1対1で対応しているが，例外もある．
・GROUP BYで複数の列が指定されている場合，その列同士は1対1で対応していない．
ここまで読んでいただきありがとうございました．