IT技術で仕事を減らしたい!

ITエンジニアのメモ+α

SQLで負の値のゼロ表示テクニック

どうも、nippaです。

今日は最近学んだSQLのテクニックについてです。

何かとデータベースを使うことが多くなってきたのですが、BIツール関連を使おうとすると

SQLである程度カバーできると、テーブル設計が比較的簡単になりますね。

テーブルは最小限かつシンプルなものにを心がけています。

環境

PostgreSQL 10.12

1. サンプルテーブル作成とデータの敷き込み

PostgreSQLにログインして、以下のコマンドでSQLテーブルを作成します。

CREATE TABLE public.sample (
  count INTEGER,
  int   INTEGER,
  real  REAL
);

作成したテーブルの確認です。

\d sample

/* 結果表示*/
Table "public.sample"
Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
count  | integer |           |          |
int    | integer |           |          |
real   | real    |           |          |

今回は簡単なテストなので、シリアル型(MySQLのAUTO_INCREMENT)、インデックス等は特に指定しません。

次にデータの敷き込みです。以下のSQLコマンドでテーブルにデータの敷き込みます。

INSERT INTO public.sample (
  count, int, real  
)
VALUES
(1,  1,  1.1),
(2, -1,  1.1),
(3,  1, -1.1),
(4, -1, -1.1);

データの確認をします。

SELECT * FROM public.sample ;

/* 出力結果 */
 count | int | real
-------+-----+------
     1 |   1 |  1.1
     2 |  -1 |  1.1
     3 |   1 | -1.1
     4 |  -1 | -1.1

確認ができたので、負の値の取り扱いに移りたいと思います。

負の値を0として取り扱う

負の値を0として扱う場合、絶対値を利用します。

式で表現すると、

([絶対値] + [生値])/ 2 = [表示値]

となり、生値が負であれば、表示される値は0になります。

また、生値が正の場合は、正で表現されます。

実際に、確認してみます。

SELECT
  count,
  ((ABS(int   ) + int   ) * 0.5)::integer AS "int",
  ((ABS(real  ) + real  ) * 0.5)::real    AS "real",
FROM public.sample;

/* 出力結果 */
count | int | real
-------+-----+------
    1 |   1 |  1.1
    2 |   0 |  1.1
    3 |   1 |    0
    4 |   0 |    0

となり、ゼロもしくは正の数で表現されます。

ここでいくつか注意点ですが、PostgreSQL10.12では、小数型の制度は倍精度(8バイト、~15桁精度)なので、誤差が生まれる場合があります。

なので、各変数の型と精度の性質を理解して使うことをおすすめします。

感想

負の値は表示させたくないけど、データがあることは表現したいなどといった場合に使える方法だと思います。

計算方法テクニックなので、MySQLでも、プログラムでの数値の算術にも使えます。

テーブルをあれこれイジるより簡単なので、利用してみたください。

ではでは、また次回。