備忘録的プログラミングリファレンス

数値データ型 INT, DECIMAL, SERIAL

　SQL において数値を扱うデータ型には主に以下のようなものがあります。

　数値データ型には整数型や、浮動小数点と連番型があります。DECIMAL のような精度設定が可能なデータ型があります。

型名サイズ説明
　整数型
INT, INTEGER4バイト整数。-2147483648から+2147483647。MySQL では UNISIGNED で最大 4294967295
BIGINT8バイトINT より大きい整数。-9223372036854775808から9223372036854775807。MySQL では UNISIGNED で最大 18446744073709551615
　浮動小数点型
DECIMAL可変長通貨に用いる。ユーザ指定精度、正確。小数点前までは131072桁、小数点以降は16383桁。MySQL では 65 桁
NUMERIC可変長DECIMAL と等価
REAL4バイト可変精度。不正確。6桁精度
DOUBLE PRECISION8バイト可変精度。不正確。15桁精度
　連番型
SERIAL4バイト正の整数。1から2147483647。MySQL では BIGINT で最大 18446744073709551615

　データ型は入力と出力で合わせる必要があります。同じ数値を扱う場合のおいてもデータ型の違いでエラーになることがあります。

　データベースごとに数値型の仕様が異なります。詳しくは調べてみてください。

整数型　integer、bigint

　小数点以下や分数以外である整数を扱うことができます。扱えない数値はエラーを返します。

　BIGINT のバイト数はプラットフォームに依存しますので注意が必要です。

型名サイズ説明
INT, INTEGER4バイト整数。-2147483648から+2147483647
BIGINT8バイトINT より大きい整数。-9223372036854775808から9223372036854775807

　データベースごとの仕様に違いがあります。詳しくはそれぞれの整数型で調べてみてください。

浮動小数点型

DECIMAL、NUMERIC

　DECIMAL 型は、大きな桁数の数値を格納でき正確な計算ができます。 金額や正確性が求められる数量を扱う場合には、この型を推奨します。ただし、計算速度は遅くなります。

　DECIMAL 型で扱う数値の有効桁を「DECIMALの精度」といいます。小数点以下のことを「DECIMALの位取り」といいます。
　例えば、123456.789の精度は9で位取りは3といいます。この精度と位取りはデータ型を宣言する時に指定することができます。

DECIMAL(precision, scale)

　precisionは精度, scaleは位取りです。どちらも整数で指定しprecisionは1から、scaleは0から指定できます。
　以下のようにscaleを省くと小数点以下は扱いません。

DECIMAL(precision)

　もしも、NUMERICとだけ型宣言すると最大の精度で数値を格納します。最大精度は1000です。

DECIMAL

　精度や位取りが宣言されたカラムに数値を入力しようとすると、そのカラムの型が優先されます。移植性を考慮すると精度と位取りは宣言したほうがよいかもしれません。
　精度や位取りが宣言されたカラムにその宣言を超えた数値を入力しようとすると、精度より大きい値にはエラーが返ります。位取りが宣言されたよりも小さい小数点以下の桁は丸められます。

　DECIMAL型のカラムには'NaN'という値が入れられます。値がないという意味です。NaNを引用符で囲んでください。

REAL、DOUBLE PRECISION

　数値を表すデータ型で、小数点以下の桁数が可変長の実数になります。もしも金額といった正確性を必要とする場合は DECIMAL 型を使います。

　２進数で記憶するために入力と出力で差異が出ます。例えば、5.1234 は、メモリ上では0100 0000 1010 0011 1111 0010 1110 0101となりますが、１０進数に戻すと5.123となり差異が出ます。

浮動小数点による差異
入力　：　10進数
5.1234
↓
メモリ上　：　2進数
0100 0000 1010 0011 1111 0010 1110 0101
↓
出力　：　10進数
5.123 ← ＊入出力で差異が生じる

　浮動小数点数型は、正負を表す「符号部」、数値を「仮数部」、小数点の位置を表す「指数部」で表します。 例えば上記の5.1234 を２進数で表した、0100 0000 1010 0011 1111 0010 1110 0101は以下のように表します。

0(符号部(+)) / 100 0000 1(指数部(8bit)) / 010 0011 1111 0010 1110 0101(仮数部(23bit))

　符号部は0.. +、1.. -1。指数部は 10-1。仮数部は

　この型の精度はシステムに依存しますが、標準規格のIEEE 754形式である場合が多いです。

浮動小数点型（単精度型）の計算

　浮動小数点の計算がどのように行われているかを説明します。コンピュータ内で、「符号部」、「仮数部」、「指数部」を別々に２進数に変換して、さらに浮動小数点型に変換するための独特の計算を行っています。
　以下はコンピュータ内でどのように浮動小数点型に変換されるかの説明です。浮動小数点型の計算は自動で行われているために実用の上では気にする必要はないでしょう。参考程度に読んでください。

符号部の計算

　符号部はマイナス - またはプラス + を表します。マイナスは 1 、プラスは 0 です。この場合はプラスですので符号部は0です。

仮数部の計算

　5.1234 を 24bit の 2 進数に変換するには、まず 5 と 0.1234 に分けます。

　5 は 2 進数で表すと、101になります。

　0.1234 は 2 進数で表すと、0.000111111001011100101になります。小数点以下を 2 進数 に変換すると無限大に続くことが多いです。このことも差異を生じる原因ではないかと思います。

　そのため、5.1234 を 2 進数に変換すると、101.000111111001011100101となります。

指数部の計算

　5.1234 を 2 進数に変換した101.000111111001011100101を、浮動少数点の計算方法にしたがって、指数を使って １ 桁に変換します。1.01000111111001011100101 × 102と変換します。

　この時の指数 2 に +127 を足します。そして、129 を 2 進数に変換します。

　129 の 2 進数は10000001となります。

浮動小数点型（単精度型）への変換

　仮数部の最初の 1 を省きます(浮動少数点の計算方法)。01000111111001011100101

　そして、符号部 +.. 0、指数部は、10000001、仮数部は 01000111111001011100101 をつなげます。

0(符号部(+)) / 100 0000 1(指数部(8bit)) / 010 0011 1111 0010 1110 0101(仮数部(23bit))

　以上の説明はコンピュータ内で自動で計算されることなので気にする必要はありません。しかしながら、なぜ入出力で差異が発生するのかの理解を深めるために書きました。

数値の計算

　データベースでは、記録されたデータを元に＋、ー、✕、÷ といった四則計算や切り捨て、四捨五入といった小数点以下の扱いを計算することができます。

四則計算

　SQL では＋、ー、✕、÷ といった四則計算を行うことができます。掛け算は*、割り算は/演算子です。

和（ + ）
SELECT 1 + 1, 1 - 1, 1 * 1, 1 / 1;

..
| 1 + 1 | 1 - 1 | 1 * 1 | 1 / 1  |
+-------+-------+-------+--------+
|     2 |     0 |     1 | 1.0000 |

　余りは%を使用します。

余り（ % ）
SELECT 1 % 1;
..
| 1 % 1 |
+-------+
|     0 |

関数

　浮動小数点を扱う場合は切り上げなのか切り下げ、切り捨て、四捨五入といった近似値の扱いが重要になります。

　例えば、小数点以下は扱うわないといった計算を必要とする場合あります。その場合に切り上げ、切り下げ、切り捨て、四捨五入といった計算方法を選択します。

　切り上げ、切り下げ、切り捨て、四捨五入には以下のような関数が用意されています。

  • 切り上げ.. CEIL()
  • 切り下げ.. FLOOR()
  • 切り捨て.. TRUNCATE()、PostgreSQL では TRUNC()
  • 四捨五入.. ROUND()
CEIL()、FLOOR()、TRUNCATE()、ROUND()
SELECT CEIL( 100.15 ), FLOOR( 100.15 ), TRUNCATE( 100.15, 0 ), ROUND( 100.15, 0 );

..
| CEIL( 100.15 ) | FLOOR( 100.15 ) | TRUNCATE( 100.15,0 ) | ROUND( 100.15 , 0) |
+----------------+-----------------+----------------------+--------------------+
|            101 |             100 |                  100 |                100 |

　TRUNCATE(), ROUND() は第２引数で切り捨てまたは四捨五入する桁位置を指定できます。

TRUNCATE()、ROUND() 桁位置の指定①
SELECT TRUNCATE( 100.15, 1 ), ROUND( 100.15, 1 );

..
| TRUNCATE( 100.15, 1 ) | ROUND( 100.15, 1 ) |
+-----------------------+--------------------+
|                 100.1 |              100.2 |

　PostgreSQL では TRUNC() です。

　第２引数をマイナスで指定すると整数の桁位置になります。

TRUNCATE()、ROUND() 桁位置の指定②
SELECT TRUNCATE( 345.15, -1 ), ROUND( 345.15, -1 );

..
| TRUNCATE( 345.15, -1 ) | ROUND( 345.15, -1 ) |
+------------------------+---------------------+
|                    340 |                 350 |

　数値に関する関数には、負数、0 または 整数であるかを -1, 0, 1 で示す SIGN() や絶対値を返す ABS() といった便利なものもあります。
　詳しくはデータベースごとの数値に関する演算子で調べてみてください。

連番型　smallserial、serial、bigserial

　smallserial、serial、bigserial といった連番型は自動で番号を付加する場合に利用する数値型です。 数値型に分類されていますが、設定された変数は自動で番号が付いていきます。

serial型を指定
CREATE TABLE example (
    num SERIAL
);

　例えば、以上のように変数 number を serial 型に指定すると、レコードを追加するたびに number の値は ＋１ ずつインクリメントされた値が入ります。

　上記の例は以下の例と同じです。以下は、シーケンスとして定義しています。番号を操作する場合は以下のように定義したほうがよいでしょう。

serial型を指定
CREATE SEQUENCE example_num_seq;
CREATE TABLE example (
    num integer NOT NULL DEFAULT nextval('example_num_seq')
);
ALTER SEQUENCE example_num_seq OWNED BY example.num;

　もしも番号を変更したい場合は setval() シーケンス関数を使います。

　シーケンスはそれを利用するカラムが削除されると同時に削除されます。

　smallserial、serial、bigserial の違いは、扱える数値の大きさです。上記の数値型の一覧を参照してください