解決したかった問題

Tableで表示するときはちゃんとしていても、pivotで表示したりデータをCSVとしてダウンロードしたら1,000,000が1000000に、1.12が1.122345323みたいになっていて見づらいため直して欲しいと言われた。

正規表現を避けてきたせいもあり、地味に時間かかったのでこのような問題に時間を取られたくない人の助けになればと思いましたので書き残します。

解決策

まず事前に、今回は全て数字をvarchar（文字）として出力するので、tableで表示する際はEdit visualizationからTypeをNumberからTextに変えてください。そうしないと0とかになります。

一応言っておきますが、cvとかcntとかamountとかのところはちゃんと入れ替えてくださいね。このままコピペしても動きません。

ただカンマを入れる場合

regexp_replace(cast(MAX(cv) as varchar), '(\d)(?=(\d{3})+$)', '$1,')

小数点を切り捨ててカンマを入れる場合

regexp_replace(regexp_replace(cast(MAX(round(CAST(amount AS REAL) / cv)) as varchar), '\.\d{1,}', ''), '(\d)(?=(\d{3})+$)', '$1,')

￥をつける場合

'¥' || だけ。非エンジニアも見るかもしれないので一応書きました。%つけたい時も同じようにできます。

'¥' || regexp_replace(cast(MAX(cv) as varchar), '(\d)(?=(\d{3})+$)', '$1,')

小数点第二位まで表示したい

こんな感じでやりました。

round(cast(cv as real) * 100 / cnt, 2)

正規表現解説

エンジニアじゃない人は見なくても良いと思います。
正規表現ってたまに使う時があるけどなんだかんだなんとかなるためちゃんと勉強するのを怠けてしまっていたのですが、反省。ちゃんと蓄積していきたい。

(\d)(?=(\d{3})+$)
後ろからみて、3つの数字が後に続く一つの数字がマッチする。この時マッチする数字は一つではなく複数の場合もある。
1000だと1がマッチする。1003000だと1と3がマッチする。
\dで一つの数字を表す。(\d)カッコをつけてグループ化する。グループ化することでグループ単位で処理することができる。↑の正規表現には2つのグループがあり、2つ目の引数内の$1は1つ目のグループを指定している。

例えば、↓の例では2つのグループを作成していて、'(\d+)([ab]) 'にマッチした文字列の2つ目のグループ（$2で2つ目のグループを指定）に3cをつけるという意味になる。

SELECT regexp_replace('1a 2b 14m', '(\d+)([ab]) ', '3c$2 '); -- '3ca 3cb 14m'

https://prestodb.io/docs/current/functions/regexp.html より

ここまで読めば、最初分からなかったとしても↓の意味がすんなりと分かるのではないでしょうか。

regexp_replace(cast(MAX(cv) as varchar), '(\d)(?=(\d{3})+$)', '$1,')

\.\d{1,}
.は任意の1文字という正規表現の意味もあるので.を文字として扱うためにちゃんとエスケープする。{1,}は1文字以上。+と同等。\dは数字なので、\.\d{1,}は.12345などの小数点以下の数字を小数点を含めて全てマッチするという意味になる。
ここでは小数点以下を消したいのでマッチした部分を''と入れ替えることで実現している。

regexp_replace('1,22', '\.\d{1,}', '')

以上です。