さて、今回はSQLシリーズの第5弾、SQLの述語とCASE式について説明していきます。
述語とは何か
SQLにおける述語とは、「戻り値が真理値になる関数」のことです。
普通、関数は戻り値、つまり出力に数値であったり、文字列だったり日付だったりを出力します。
しかし、述語の場合は戻り値が真理値(TRUE/FALSE/UNKNOWN)になります。
なので、たとえば「=」や「<」、「<>」などの比較演算子は述語になります。
では、他には具体的にどのような述語があるのか見ていきましょう。
LIKE述語
LIKE述語は、部分一致検索を行うときに使います。
そして、この部分一致検索には「前方一致」、「中間一致」、「後方一致」の3種類があります。
では、それぞれ具体的に見ていきましょう。
前方一致検索
まずは、テーブルを用意しましょう。
create table SampleLike
( strcol varchar(6) not null,
primary key (strcol));
テーブルにデータを登録していきます。
begin transaction;
insert into SampleLike(strcol) values('abcddd');
insert into SampleLike(strcol) values('dddabc');
insert into SampleLike(strcol) values('abdddc');
insert into SampleLike(strcol) values('abcdd');
insert into SampleLike(strcol) values('ddabc');
insert into SampleLike(strcol) values('abddc');
commit;
確認。
-- 確認
select * from SampleLike;
strcol
--------
abcddd
dddabc
abdddc
abcdd
ddabc
abddc
(6 行)
では次に、前方一致検索を行ってみましょう。
コードは以下のようになります。
select * from SampleLike
where strcol LIKE 'ddd%';
実行結果です。
strcol
--------
dddabc
(1 行)
これで、「dddで始まるすべての文字列の検索」に成功しています。
そして、この「%」というのは「任意の文字列」を表しています。
つまり、「ddd」で始まるなら後ろの文字は何でもいいですよという意味です。
中間一致検索
次に、中間一致検索を行ってみます。
書き方は前方一致と変わりません。
select * from SampleLike
where strcol LIKE '%ddd%';
実行結果です。
strcol
--------
abcddd
dddabc
abdddc
(3 行)
ん？と思う方もいると思いますが、中間一致検索は中間にdddという文字列がある検索ではありません。
中間一致検索は、「文字列のどこかにdddという文字列がある列」を検索しています。
後方一致検索
最後に、後方一致検索です。
select * from SampleLike
where strcol LIKE '%ddd';
実行結果です。
strcol
--------
abcddd
(1 行)
はい。正しく出力されています。
BETWEEN述語
BETWEEN述語は範囲検索を行います。
たとえば、以下。
select shohin_mei, hanbai_tanka
from Shohin
where hanbai_tanka between 100 and 1000;
実行結果です。
shohin_mei | hanbai_tanka
--------------+--------------
Tシャツ | 1000
穴あけパンチ | 500
フォーク | 500
おろしがね | 880
ボールペン | 100
(5 行)
はい。販売単価が100円から1000円の商品の検索を行っています。
ちなみに、上のコードでは100円と1000円を含む検索を行っています。
100円と1000円を含みたくない場合は以下のように書きます。
select shohin_mei, hanbai_tanka
from Shohin
where hanbai_tanka > 100
and hanbai_tanka < 1000;
実行結果です。
shohin_mei | hanbai_tanka
--------------+--------------
穴あけパンチ | 500
フォーク | 500
おろしがね | 880
(3 行)
100円と1000円の商品が除かれているのを確認してください。
NULLか非NULLかの判定
ある列がNULLの行を検索するときには「=」のような比較演算子は使えません。
じゃあ、どうするか。
「IS NULL」という述語を使います。
select shohin_mei, shiire_tanka
from Shohin
where shiire_tanka IS NULL;
実行結果です。
shohin_mei | shiire_tanka
------------+--------------
フォーク |
ボールペン |
(2 行)
逆に、NULL以外の行を検索したいときには「IS NOT NULL」という述語を使います。
select shohin_mei, shiire_tanka
from Shohin
where shiire_tanka IS NOT NULL;
実行結果です。
shohin_mei | shiire_tanka
----------------+--------------
Tシャツ | 500
穴あけパンチ | 320
カッターシャツ | 2800
包丁 | 2800
圧力鍋 | 5000
おろしがね | 790
印鑑 | 10
(7 行)
NULLが含まれていないことを確認してください。
IN述語
さて、続いて「仕入単価が320円、500円、5000円の商品を選択する」ことを考えてみましょう。
ORを使えば、下のように書けそうです。
select shohin_mei, shiire_tanka
from Shohin
where shiire_tanka = 320
or shiire_tanka = 500
or shiire_tanka = 5000;
実行結果です。
shohin_mei | shiire_tanka
--------------+--------------
Tシャツ | 500
穴あけパンチ | 320
圧力鍋 | 5000
(3 行)
はい。これも正解です。
しかし、この書き方だと、たとえば選択対象としたい値が10、20となると面倒くさい上に読みにくくなってしまいますよね。
そんなときに使うのが「IN述語」です。
具体的には以下のように書けます。
select shohin_mei, shiire_tanka
from Shohin
where shiire_tanka in (320, 500, 5000);
確認してみます。
shohin_mei | shiire_tanka
--------------+--------------
Tシャツ | 500
穴あけパンチ | 320
圧力鍋 | 5000
(3 行)
先ほどと同じ結果を得ることに成功しました。
逆に、「320円、500円、5000円以外の商品」を検索したいときには「NOT IN述語」を使います。
select shohin_mei, shiire_tanka
from Shohin
where shiire_tanka not in (320, 500, 5000);
実行結果です。
shohin_mei | shiire_tanka
----------------+--------------
カッターシャツ | 2800
包丁 | 2800
おろしがね | 790
印鑑 | 10
(4 行)
IN述語とNOT IN述語のどちらを使う場合でもNULLは選択できないことに注意してください。
さて、述語に関してはこれで以上とします。
もちろん、これら以外にも述語はありますが、初級者の人はこれらを理解できていれば十分かと思います。
CASE式
さて、次はCASE式について説明していきます。
CASE式というのは、「条件分岐」のことです。
CASE(場合)という名前がそれを示していますね。
では、CASE式の使い方を見ていきましょう。
CASE式の使い方
たとえば、いまShohinテーブルには衣服、事務用品、キッチン用品という3種類の商品分類が格納されています。
これを、以下のような表示に変えてみます。
- A: 衣服
- B: 事務用品
- C: キッチン用品
では、コードを書いていきます。
select shohin_mei,
case when shohin_bunrui = '衣服'
then 'A:' || shohin_bunrui
when shohin_bunrui = '事務用品'
then 'B:' || shohin_bunrui
when shohin_bunrui = 'キッチン用品'
then 'C:' || shohin_bunrui
else null
end as abc_shohin_bunrui
from Shohin;
結果を見てみます。
shohin_mei | abc_shohin_bunrui
----------------+-------------------
Tシャツ | A:衣服
穴あけパンチ | B:事務用品
カッターシャツ | A:衣服
包丁 | C:キッチン用品
圧力鍋 | C:キッチン用品
フォーク | C:キッチン用品
おろしがね | C:キッチン用品
ボールペン | B:事務用品
印鑑 | B:事務用品
(9 行)
CASE式の上の6行がそのまま1つの列を作り出しています。
商品分類の名前に応じて3つの分岐をWHEN句によって作っています。
そして、最後に「ELSE NULL」とすることでそれ以外の場合はNULLを返すようにしています。
また、最後の「END」は省略不可なので、書き落とさないようにしましょう。
CASE式の便利な機能
CASE式の便利な点は、行列変換ができる点です。
たとえば、商品分類ごとに販売単価を合計した結果をGROUP BY句で出力すると以下のようになります。
select shohin_bunrui,
sum(hanbai_tanka) as sum_tanka
from Shohin
group by shohin_bunrui;
確認してみます。
shohin_bunrui | sum_tanka
---------------+-----------
キッチン用品 | 11180
衣服 | 5000
事務用品 | 695
(3 行)
これは行によって結果を集約しています。
これを以下のように書くことで、列で集計することができます。
select
sum(case when shohin_bunrui = '衣服'
then hanbai_tanka else 0 end) as sum_tanka_ihuku,
sum(case when shohin_bunrui = 'キッチン用品'
then hanbai_tanka else 0 end) as sum_tanka_kitchen,
sum(case when shohin_bunrui = '事務用品'
then hanbai_tanka else 0 end) as sum_tanka_jimu
from Shohin;
実行結果です。
sum_tanka_ihuku | sum_tanka_kitchen | sum_tanka_jimu
-----------------+-------------------+----------------
5000 | 11180 | 695
(1 行)
これで、行列変換ができました。
CASE式の中で行っていることは、商品分類が「衣服」なり「事務用品」なりと合致した場合はその販売単価を出力し、そうでない場合は0にする、ということです。
そして、その結果を合計することで各商品分類ごとに販売単価の合計値を算出しているわけです。
今回はこれで以上となります。
コメント