はじめに
Redshift を使っていて「えっ？」となった型キャストの挙動があったのでメモ兼 Qiita 記事としてまとめます。
同じ値でも、どの型から INT にキャストするかで結果が変わる、という話です。
事象
次の SQL を実行します。
SELECT
2.4 :: INT AS a,
2.5 :: INT AS b,
-2.5 :: INT AS c,
(2.4 :: DOUBLE PRECISION) :: INT AS d,
(2.5 :: DOUBLE PRECISION) :: INT AS e,
(-2.5 :: DOUBLE PRECISION) :: INT AS f;
結果はこうなります。
| column | value |
|---|---|
| a | 2 |
| b | 3 |
| c | -3 |
| d | 2 |
| e | 2 |
| f | -2 |
何が問題なのか
私をはじめ多くの人は、自然と次のように考えると思います。
-
2.5は四捨五入されて3 -
-2.5は-3 - 途中で
DOUBLE PRECISIONを挟んでも結果は同じ
しかし実際には：
-
2.5 :: INT→ 3 -
(2.5 :: DOUBLE PRECISION) :: INT→ 2 -
-2.5 :: INT→ -3 -
(-2.5 :: DOUBLE PRECISION) :: INT→ -2
と、結果が変わっています。困ります。
原因：型ごとに異なる丸めルール
この挙動の原因は、Redshift が型ごとに異なる丸め（変換）ルールを持っているためです。
NUMERIC（DECIMAL）→ INT
2.5 :: INT のようなリテラルは、内部的には NUMERIC として扱われます。
NUMERIC から整数型へのキャストは 0.5 を超える場合は絶対値が大きい方向へ丸め （いわゆる四捨五入）です。
DOUBLE PRECISION → INT
一方で、DOUBLE PRECISION から INT へのキャストは挙動が異なります。
この場合 小数点以下を切り捨て（0 方向への丸め / truncation） になります。
※ 浮動小数点数の内部表現や Redshift の実装都合も絡みますが、少なくとも「四捨五入ではない」ことが重要です。
まとめ
Redshift では、
- NUMERIC → INT: 四捨五入（0.5 は切り上げ）
- DOUBLE PRECISION → INT: 切り捨て（0 方向）
という違いがあります。
そのため、
2.5 :: INT
と
(2.5 :: DOUBLE PRECISION) :: INT
は 同じ結果になりません。
いやー......気付いたときはびっくりしました。
対策
- 「どの型で計算されているか」を常に意識する
- 結果を整数で使う前提なら、途中の型変換も含めて確認する
- 最終的な丸めルールは
ROUND/TRUNC/FLOOR/CEILINGで明示する
SELECT ROUND(AVG(amount))::INT -- 意図を明示
FROM table;
おわりに
今回は他 DB から移行する際に数値がずれて気付きましたが、細かいところなので見逃がしそうな落とし穴だと思います。「なんとなく動く SQL」ほど怖いものはありませんね。
ちなみに今回紹介した挙動は、INT だけではなく BIGINT でも同様 ということが確認できております。
同じような挙動を踏んだ方の助けになれば幸いです。
いいねやストックで応援いただけると励みになるので、ぜひポチっとお願いします！