BigQuery の CAST 関数で数値フォーマット時に ### が表示される問題と解決法
BigQuery の CAST 関数で数値フォーマット時に ### が表示される問題と解決法
BigQuery で CAST 関数 を使って STRING FORMAT を指定する際、
指定したフォーマットパターンの桁数を超える値が入力されるとハッシュ記号(#)で埋められる現象について検証し、
対策を考えました。
背景
BigQuery Console で桁数の多い数値が読みづらかったため、3 桁区切りのカンマ付きの数値に変換することにしました。
しかし、CAST 関数を使った際にハッシュ記号(#)で埋められる問題に遭遇したため、調査を行いました。
検証用クエリ
以下のクエリで、異なる桁数の数値に対して CAST 関数 と FORMAT 関数 を実行したときの違いを比較します。
with records as (
select
'a' as text
, 9999 as number1
, 9999.999 as number2
union all
select
'b' as text
, 1111 as number1
, 1111.111 as number2
union all
select
'b' as text
, 2222 as number1
, 2222.222 as number2
union all
select
'c' as text
, 999999999 as number1 -- 9桁の数値(問題が発生する)
, 999999999.999 as number2
)
select
text
-- number1: 整数値のフォーマット比較
-- * CAST 関数でのフォーマット(桁数制限あり)
, cast(sum(number1) as string format '999,999') as cast_number1
-- * FORMAT 関数(桁数制限なし、3 桁区切りのカンマ付き)
, format("%'d", sum(number1)) as format_number1
-- number2: 小数値のフォーマット比較
-- * CAST 関数でのフォーマット(桁数制限あり)
, cast(sum(number2) as string format '999,999.999') as cast_number2
-- * FORMAT 関数(桁数制限なし、小数点後 3 桁、3 桁区切りのカンマ付き)
, format("%'.3f", sum(number2)) as format_number2
from records
group by
text
実行結果
問題となる現象の詳細
整数値の場合(number1)
cast(sum(number1) as string format '999,999')- フォーマットパターン
999,999は 6 桁までの値しか対応できない number1が 7 桁以上の値(例:9,999,999以上)になると、###,###と表示
小数値の場合(number2)
cast(sum(number2) as string format '999,999.999')- フォーマットパターン
999,999.999は小数点前 6 桁、小数点後 3 桁まで対応 - この桁数を超える値が入ると、同様に
###,###.###と表示
根本原因
CAST 関数 の STRING FORMAT は、
指定されたパターンの桁数が固定されており、
それを超える値を表現できない場合にハッシュ記号で置き換えられる仕様になっています。
解決策
1. FORMAT 関数を使用(推奨)
桁数が不明な場合は、FORMAT 関数を使用することで桁数制限を回避できます。
-- 整数値の場合
format("%'d", sum(number1)) -- 桁数制限なし、3 桁区切りのカンマ付き
-- 小数値の場合
format("%'.3f", sum(number2)) -- 桁数制限なし、小数点後 3 桁、3 桁区切りのカンマ付き
2. CAST 関数で十分な桁数を確保
CAST 関数を使い続けたい場合は、想定される最大桁数に対応したフォーマットパターンを指定します。
-- 12 桁まで対応
cast(sum(number1) as string format '999,999,999,999')
-- 小数点前 12 桁、小数点後 3 桁まで対応
cast(sum(number2) as string format '999,999,999,999.999')
まとめ
BigQuery で数値を 3 桁区切りでフォーマットする際の選択指針
FORMAT 関数を優先的に使用する
- 桁数制限がなく、動的に対応可能
- より柔軟で保守性が高い
CAST 関数を使用する場合の注意点
- 想定される最大桁数を考慮してフォーマットパターンを設計する
- ハッシュ記号で埋められる現象を避けるため、十分な桁数を確保する
