背景
業務で小数点を含む数(のうち整数部のみ)を3桁カンマ区切りにする必要があり、実装してみました。
結論
小数点を含まない場合
整数のみを3桁カンマ区切りにするのは、FORMAT関数を使えば簡単にできました。
https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#format_string
SELECT FORMAT("%'d", 1234567) # => 1,234,567
小数点を含む場合
SQLで頑張る手もありますが、こういう処理は集合指向よりも手続き型の方が書きやすくて保守しやすいと思っています。
BigQueryではユーザー定義関数(UDF)という機能でJavaScriptが使えるため、JavaScriptで書きました。
https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions#javascript-udf-structure
こちらの記事に、JavaScriptで小数点を含む値を3桁カンマ区切りにする方法があったため参考にしています。
https://www.yoheim.net/blog.php?q=20190702#t7
アレンジとして、第2引数で小数点以下の表示桁数を指定できるようにしています。
(丸めは四捨五入にしています)
CREATE TEMP FUNCTION FORMAT_NUMBER(num FLOAT64, digits INT64)
RETURNS STRING
LANGUAGE js AS r"""
if (num === null) return null;
var s = String(Math.round(num * (10 ** digits)) / (10 ** digits)).split('.');
var ret = String(s[0]).replace( /(\d)(?=(\d\d\d)+(?!\d))/g, '$1,');
if (s.length > 1) {
ret += '.' + s[1];
}
return ret;
""";
SELECT FORMAT_NUMBER(1234567, 0) # => 1,234,567
SELECT FORMAT_NUMBER(1234567.123, 1) # => 1,234,567.1
SELECT FORMAT_NUMBER(0.567, 2) # => 0.57