標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ! Tweet
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の4日目です。
モルダー、あなた疲れてるのよ！ 的なタイトルになってしまったw
エラーにはならないけど、この手の差異もなかなか嫌らしい差異ですよね。
結論から言うと、この差異の影響を避けるためにはデフォルトの挙動任せは危険かな。と言うことになるのですが、まあ、色々ありますよね。長いこと同じエンジン使って他ところへ乗り換えるとなると。
Oracle Database
なお、SQL*plusのnumformatパラメータは未設定の状態（デフォルト）です。
SQL> SELECT CAST((1.0/6.0) AS NUMBER(38,37)) FROM DUAL;
リテラル値の除算結果は小数点以下1桁の状態でデフォルトのキャストを利用した場合、内部的に小数点以下37桁精度。
実は、表示されている桁数は、SQL*Plusのnumformatの影響を受け、numformat(デフォルト小数点以下7桁)で丸る
一見 小数点以下8桁に見えるが、
SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;
CAST((9/7)ASNUMBER(38,37))
--------------------------
1.28571429
numformatの桁数を大きくすると内部的は小数点以下37桁精度！
SQL> set numformat "0.0999999999999999999999999999999999999"
SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;
CAST((9/7)ASNUMBER(38,37))
----------------------------------------
1.2857142857142857142857142857142857143
ね！
SQL> SELECT CAST((9.0/7.0) AS NUMBER(38,37)) FROM DUAL;
CAST((9.0/7.0)ASNUMBER(38,37))
----------------------------------------
1.2857142857142857142857142857142857143
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
1.2857142857142857142857142857142857143
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
1.2857142857142857142857142857142857143
Oracleの場合、返却時の精度が全ての計算精度に影響
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,8))/CAST(7.0 AS NUMBER(38,8)) AS NUMBER(38,37)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,8))/CAST(7.0ASNUMBER(38,8))ASNUMBER(38,37))
--------------------------------------------------------------------
1.2857142857142857142857142857142857143
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,8))/CAST(7.0 AS NUMBER(38,8)) AS NUMBER(38,8)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,8))/CAST(7.0ASNUMBER(38,8))ASNUMBER(38,8))
-------------------------------------------------------------------
1.2857142900000000000000000000000000000
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,8)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,8))
---------------------------------------------------------------------
1.2857142900000000000000000000000000000
SQL> set numformat "0.09999999999999999999999999999999999999999999999"
SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;
CAST((9/7)ASNUMBER(38,37))
--------------------------------------------------
1.28571428571428571428571428571428571430000000000
SQL> SELECT CAST((9.0/7.0) AS NUMBER(38,37)) FROM DUAL;
CAST((9.0/7.0)ASNUMBER(38,37))
--------------------------------------------------
1.28571428571428571428571428571428571430000000000
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
1.28571428571428571428571428571428571430000000000
PostgreSQLだとどうなるかと言うと、
整数として扱われ、除算結果もinteger型！
sql=> SELECT 9/7;
?column?
----------
1
pg_typeof
-----------
integer
リテラル値は小数点以下1桁の精度だが、除算結果は小数点以下16桁精度のnumeric型で返されるようだ。
PostgreSQLのマニュアルには記載されていないようだが、どこかに記載されてるのだろうか。。。知ってる方がいたらコメントお待ちしております。:)
sql=> SELECT 9.0/7.0;
?column?
--------------------
1.2857142857142857
pg_typeof
-----------
numeric
小数点以下の精度0でnumeric型にキャスト**した場合も、除算結果は小数点以下16桁精度のnumeric型となるようだが、これもPostgreSQLのマニュアルには記載されていないようだ。
なお、PostgreSQLの場合numeric型へのキャストで指定できる最大精度は1000と記載され内部的にはそれ以上の精度となっている
詳細は
8.1.2. 任意の精度を持つ数
https://www.postgresql.jp/document/11/html/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
表8.2 数値データ型
https://www.postgresql.jp/document/11/html/datatype-numeric.html#DATATYPE-NUMERIC-TABLE
を参照のこと
sql=> SELECT 9::NUMERIC(100,0)/7::NUMERIC(100,0);
?column?
--------------------
1.2857142857142857
pg_typeof
-----------
numeric
小数点以下精度1桁でnumeric型へキャスト**した場合も同様に、除算結果は小数点以下16桁のnumeric型になるのな。これ
sql=> SELECT 9::NUMERIC(100,1)/7::NUMERIC(100,1);
?column?
--------------------
1.2857142857142857
pg_typeof
-----------
numeric
同様に小数点以下2桁でnumeric型へキャストし、除算した結果も小数点以下16桁精度のnumeric型として返される模様。
sql=> SELECT 9::NUMERIC(100,2)/7::NUMERIC(100,2);
?column?
--------------------
1.2857142857142857
pg_typeof
-----------
numeric
小数点以下15桁のnumeric型にキャストした場合も結果は小数点以下16桁精度のnumeric型！
sql=> SELECT 9::NUMERIC(100,15)/7::NUMERIC(100,15);
?column?
--------------------
1.2857142857142857
pg_typeof
-----------
numeric
小数点以下16桁のnumber型にキャストした場合は、指定したscaleで返された。
sql=> SELECT 9::NUMERIC(100,16)/7::NUMERIC(100,16);
?column?
--------------------
1.2857142857142857
pg_typeof
--------------------
numeric
小数点以下17桁精度でnumeric型へキャストすると、指定した小数点以下17桁で除算結果が返された。
(どうやら、小数点以下16桁未満は小数点以下の精度を16桁として扱っているように見える
sql=> SELECT 9::NUMERIC(100,17)/7::NUMERIC(100,17);
?column?
---------------------
1.28571428571428571
pg_typeof
-----------
numeric
表示される桁数が16桁なので内部も16桁かと思ったら、少々多めに持っているらしい。このあたりマニュアルに記載がない....
内部でも小数点以下16精度なのか？ という疑問を確認するための確認の結果、小数点以下20桁なので多少大きめの精度で持っているだが、この辺りもPostgreSQLのマニュアルを見る限り記載されていないようだ。
sql=> SELECT CAST((1.0/6.0) AS NUMERIC(38,37));
numeric
-----------------------------------------
0.1666666666666666666700000000000000000
しかし、小数点以下1桁のリテラル値については、小数点以下16桁の精度で計算されてしまう。（ルールはあるようだがドキュメントに明確な記載が見当たらないのは辛いとこと。
sql=> SELECT CAST((9.0/7.0) AS NUMERIC (38,37));
numeric
-----------------------------------------
1.2857142857142857000000000000000000000
結局のところ、デフォルトの挙動に任せず、必要な精度にキャストすることで必要な精度を持たせるようにすることでOracleのデフォルトの挙動と同様の精度になりそう。これが無難な対応なんだろうね。
sql=> SELECT CAST((9.0::NUMERIC(38,37)/7.0::NUMERIC(38,37)) AS NUMERIC(38,37));
numeric
-----------------------------------------
1.2857142857142857142857142857142857143
PostgreSQLでは可能な精度の検証。Oracleからの移行ではここまでの精度は必要としない、よね。多分。
sql=> SELECT 9::NUMERIC(100,99)/7::NUMERIC(100,99);
?column?
-------------------------------------------------------------------------------------------------------
1.285714285714285714285714285714285714285714285714285714285714285714285714285714285714285714285714286
pg_typeof
-----------
numeric
PostgreSQLで指定可能な最大精度（内部的な精度は1000を超えるがして可能な精度は1000)
試して！ Oracler皆びっくり！ な感じ
sql=> SELECT 9::NUMERIC(1000,999)/7::NUMERIC(1000,999);
そして、
計算結果とそれぞれのリテラル値の精度ではどちらが優先されるのか確認したところ、Oracleとは異なり、最大精度ではなく、リテラル値の制度が優先される結果となった。この点、Oracleからの移行では注意が必要かな。
以下は、小数点以下の精度を37桁に統一した結果だが、この場合Oracleと同様の精度の結果が得られる
sql=> SELECT (9.0::NUMERIC(38,37)/7.0::NUMERIC(38,37))::NUMERIC(38,37);
numeric
-----------------------------------------
1.2857142857142857142857142857142857143
しかし、リテラル側の精度を落とし8桁にすると、16桁以下は全て16桁の精度になるという挙動が見られ流・
sql=> SELECT (9.0::NUMERIC(38,8)/7.0::NUMERIC(38,8))::NUMERIC(38,37);
numeric
-----------------------------------------
1.2857142857142857000000000000000000000
sql=> SELECT (9.0::NUMERIC(38,16)/7.0::NUMERIC(38,16))::NUMERIC(38,37);
numeric
-----------------------------------------
1.2857142857142857000000000000000000000
リテラル値の精度を17桁にすると、指定精度で結果を返すが、やはり計算結果の精度にはならない
sql=> SELECT (9.0::NUMERIC(38,17)/7.0::NUMERIC(38,17))::NUMERIC(38,37);
numeric
-----------------------------------------
1.2857142857142857100000000000000000000
さらに、17桁の精度で計算し結果を16桁精度で返す例。
sql=> SELECT (9.0::NUMERIC(38,17)/7.0::NUMERIC(38,17))::NUMERIC(38,16);
numeric
--------------------
1.2857142857142857
おまけ
Redshiftのnumeric型はPostgreSQL由来ではあるが、デフォルトの精度に関する挙動はPostgreSQLに近い。ただし、多少異なる部分もある。以下の例はPostgreSQL 11.xでは、小数点以下 20桁精度だったが、Redshiftでは16桁精度となっている。
微妙な違いだが違いがあるのは確かなので注意した方が良さげ
sql=# select cast((1.0/6.0) as numeric(38,37));
numeric
-----------------------------------------
0.1666666666666667000000000000000000000
噂のプログラム発動中だったのか。そんなの気にしてなかった。全くwwww
実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺） Advent Calendar 2019
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
| 固定リンク | 0
コメント