関数、述語、CASE式
算術関数
・ +(足し算)
・ -(引き算)
・ *(掛け算)
・ /(割り算)
まずはデータの用意
create table samplemath
(m numeric(10,3),
n integer,
p integer);
begin transaction;
insert into samplemath(m,n,p) values (500,0,null);
INSERT 0 1
insert into samplemath values
(-180,0,null),
(null,null,null),
(null,7,3),
(null,5,2),
(null,4,null),
(8,null,3),
(2.27,1,null),
(5.55,2,null),
(null,1,null),
(8.76,null,null);
INSERT 0 10
commit;
select * from samplemath;
m | n | p
----------+---+---
500.000 | 0 |
-180.000 | 0 |
| |
| 7 | 3
| 5 | 2
| 4 |
8.000 | | 3
2.270 | 1 |
5.550 | 2 |
| 1 |
8.760 | |
(11 rows)
絶対値 ABS(absolute value)
select m, abs(m) as abs_col
from samplemath;
--NULLの結果はNULLになる
m | abs_col
----------+---------
500.000 | 500.000
-180.000 | 180.000
|
|
|
|
8.000 | 8.000
2.270 | 2.270
5.550 | 5.550
|
8.760 | 8.760
(11 rows)
余剰 MOD(module)
select n, p, mod(n, p) as mod_col
from samplemath;
n | p | mod_col
---+---+---------
0 | |
0 | |
| |
7 | 3 | 1
5 | 2 | 1
4 | |
| 3 |
1 | |
2 | |
1 | |
| |
(11 rows)
四捨五入 ROUND
--nに入る値で、小数点以下が決まる。
select m,n,round(m,n)as roud_col
from samplemath;
m | n | roud_col
----------+---+----------
500.000 | 0 | 500 --小数点以下なし
-180.000 | 0 | -180 --小数点以下なし
| |
| 7 |
| 5 |
| 4 |
8.000 | |
2.270 | 1 | 2.3 --小数点以下1
5.550 | 2 | 5.55 --小数点以下2
| 1 |
8.760 | |
select m,n,round(m,10)as roud_col --小数点以下10
from samplemath;
m | n | roud_col
----------+---+-----------------
500.000 | 0 | 500.0000000000
-180.000 | 0 | -180.0000000000
| |
| 7 |
| 5 |
| 4 |
8.000 | | 8.0000000000
2.270 | 1 | 2.2700000000
5.550 | 2 | 5.5500000000
| 1 |
8.760 | | 8.7600000000
(11 rows)
文字列関数
まずは準備
create table samplestr
(str1 varchar(40),
str2 varchar(40),
str3 varchar(40));
CREATE TABLE
begin transaction;
BEGIN
insert into samplestr values ('あいう','えお',null),
('abc','def',null),
('山田','太郎','です'),
('aaa',null,null),
(null,'あああ',null),
('@!#$%',null,null),
('ABC',null,null),
('aBC',null,null),
('abc太郎','abc','ABC'),
('abcdefabc','abc','ABC'),
('ミックマック','ッ','っ');
INSERT 0 11
commit;
COMMIT
select * from samplestr;
str1 | str2 | str3
--------------+--------+------
あいう | えお |
abc | def |
山田 | 太郎 | です
aaa | |
| あああ |
@!#$% | |
ABC | |
aBC | |
abc太郎 | abc | ABC
abcdefabc | abc | ABC
ミックマック | ッ | っ
(11 rows)
連結 ||
select str1,str2,
str1 || str2 as str_concat
from samplestr;
str1 | str2 | str_concat
--------------+--------+----------------
あいう | えお | あいうえお
abc | def | abcdef
山田 | 太郎 | 山田太郎
aaa | |
| あああ |
@!#$% | |
ABC | |
aBC | |
abc太郎 | abc | abc太郎abc
abcdefabc | abc | abcdefabcabc
ミックマック | ッ | ミックマックッ
(11 rows)
3つの文字の連結
select str1, str2, str3,
str1 || str2 || str3 as str_concat
from samplestr
where str1 = '山田';
str1 | str2 | str3 | str_concat
------+------+------+--------------
山田 | 太郎 | です | 山田太郎です
(1 row)
文字列長LENGTH
select str1, length(str1) as len_str
from samplestr;
str1 | len_str
--------------+---------
あいう | 3
abc | 3
山田 | 2
aaa | 3
|
@!#$% | 5
ABC | 3
aBC | 3
abc太郎 | 5
abcdefabc | 9
ミックマック | 6
(11 rows)
小文字化LOWER
select str1,
lower(str1) as low_str
from samplestr
where str1 in ('ABC','aBC','abc','山田');
str1 | low_str
------+---------
abc | abc
山田 | 山田
ABC | abc
aBC | abc
(4 rows)
大文字化UPPER
select str1,
upper(str1) as low_str
from samplestr
;
str1 | low_str
--------------+--------------
あいう | あいう
abc | ABC
山田 | 山田
aaa | AAA
|
@!#$% | @!#$%
ABC | ABC
aBC | ABC
abc太郎 | ABC太郎
abcdefabc | ABCDEFABC
ミックマック | ミックマック
(11 rows)
文字列の置換REPLACE
REPLACE(対象文字列,置換前の文字列,置換後の文字列)
select str1,str2,str3,
replace(str1,str2,str3) as rep_str
from samplestr;
str1 | str2 | str3 | rep_str
--------------+--------+------+--------------
あいう | えお | |
abc | def | |
山田 | 太郎 | です | 山田
aaa | | |
| あああ | |
@!#$% | | |
ABC | | |
aBC | | |
abc太郎 | abc | ABC | ABC太郎
abcdefabc | abc | ABC | ABCdefABC
ミックマック | ッ | っ | ミっクマっク
(11 rows)
文字列の切り出しSUBSTRING
切り出し開始位置の数え方は左から何文字目
forに数字は文字数。指定がない場合はfrom以降全ての文字。
select str1,
substring(str1 from 3 for 2) as sub_str --３文字目から2文字
from samplestr;
str1 | sub_str
--------------+---------
あいう | う
abc | c
山田 |
aaa | a
|
@!#$% | #$
ABC | C
aBC | C
abc太郎 | c太
abcdefabc | cd
ミックマック | クマ
(11 rows)
select str1,
substring(str1 from 3 ) as sub_str --3文字目から全て
from samplestr;
str1 | sub_str
--------------+----------
あいう | う
abc | c
山田 |
aaa | a
|
@!#$% | #$%
ABC | C
aBC | C
abc太郎 | c太郎
abcdefabc | cdefabc
ミックマック | クマック
(11 rows)
現在日付 CURRENT_DATE
select current_date;
current_date
--------------
2020-04-18
(1 row)
現在時間CRENT_TIME
select current_time;
current_time
--------------------
00:44:20.808903+09
(1 row)
現在日時CURRENT_TIMESTAMP
select current_timestamp;
current_timestamp
-------------------------------
2020-04-18 00:52:17.644223+09
日付要素の切り出しEXRACT
EXRACT(日付要素 FROM 日付)
select current_timestamp,
extract(year from current_timestamp) as year,
extract(month from current_timestamp) as month,
extract(day from current_timestamp) as day,
extract(hour from current_timestamp) as hour,
extract(minute from current_timestamp) as minute,
extract(second from current_timestamp) as second;
current_timestamp | year | month | day | hour | minute | second
-------------------------------+------+-------+-----+------+--------+-----------
2020-04-18 00:59:45.901865+09 | 2020 | 4 | 18 | 0 | 59 | 45.901865
(1 row)
変換変数
型変換CAST
CAST(変換前の値 AS 変換するデータ型)
--string→integer
select cast('0001' as integer) as int_col;
int_col
---------
1
(1 row)
--string→date
select cast('2009-12-14' as date) as date_col;
date_col
------------
2009-12-14
(1 row)
NULLを値へ変換するCOALESCE
select coalesce(null, 1) as col_1,
coalesce(null,'test',null) as col_2,
coalesce(null,null,'2009-11-01') as col_3;
col_1 | col_2 | col_3
-------+-------+------------
1 | test | 2009-11-01
(1 row)
select str2,coalesce(str2, 'nullです')
from samplestr;
str2 | coalesce
--------+----------
えお | えお
def | def
太郎 | 太郎
| nullです
あああ | あああ
| nullです
| nullです
| nullです
abc | abc
abc | abc
ッ | ッ
(11 rows)
述語
述語の特徴は戻り値が文字列や日付ではなく全て真理値（true/false/unknown)
LIKE述語
文字列の部分一致検索をする場合はLIKE述語を使う。
また部分一致には大きく分けて前方一致、中間一致、後方一致の3つある。
まずはデータの準備
create table samplelike
(strcol varchar(6) not null,
primary key (strcol));
begin transaction;
BEGIN
insert into samplelike values ('abcddd'),
('dddabc'),
('abdddc'),
('abcdd'),
('ddabc'),
('abddc');
INSERT 0 6
commit;
COMMIT
％は「０文字以上の任意の文字列」という意味
--前方一致
select *
from samplelike
where strcol like 'ddd%';
strcol
--------
dddabc
(1 row)
ーー後方一致
select *
from samplelike
where strcol like '%ddd';
strcol
--------
abcddd
(1 row)
--中間一致
select *
from samplelike
where strcol like '%ddd%';
strcol
--------
abcddd
dddabc
abdddc
(3 rows)
%の代わりに「_」を使うこともできる。
意味は任意の1文字
select *
from samplelike
where strcol like 'abc__';
strcol
--------
abcdd
(1 row)
postgres=#
postgres=# select *
from samplelike
where strcol like 'abc___';
strcol
--------
abcddd
(1 row)
BETWEEN
範囲検索が行える
引数を3つ使うのが特徴
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 rows)
--100と01000両端も含む
100と1000を含まないようにするのは以下
select shohin_mei, hanbai_tanka
from shohin
where hanbai_tanka > 100
and hanbai_tanka < 1000;
IS NULL、IS NOT NULL
NULLかNULLじゃないか
NULLの行を選択するにはIS NULLを使う
select shohin_mei, shiire_tanka
from shohin
where shiire_tanka is null;
shohin_mei | shiire_tanka
------------+--------------
フォーク |
ボールペン |
(2 rows)
逆に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
(6 rows)
IN
ORの便利な省略系が使える
まずは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 rows)
上記をINを使うと以下になる
select shohin_mei, shiire_tanka
from shohin
where shiire_tanka in ( 320,500,5000);
shohin_mei | shiire_tanka
--------------+--------------
Tシャツ | 500
穴あけパンチ | 320
圧力鍋 | 5000
(3 rows)
--逆はNOT IN を使ってかける。ただしNULLのデータを選択できない。
select shohin_mei, shiire_tanka
from shohin
where shiire_tanka not in ( 320,500,5000);
shohin_mei | shiire_tanka
----------------+--------------
カッターシャツ | 2800
包丁 | 2800
おろしがね | 790
(3 rows)
INとサブクエリ
まずはデータの準備
create table tenposhohin
(tenpo_id char(4) not null,
tenpo_mei varchar(200) not null,
shohin_id char(4) not null,
suryo integer not null,
primary key (tenpo_id, shohin_id));
CREATE TABLE
begin transaction;
BEGIN
insert into tenposhohin (tenpo_id,tenpo_mei, shohin_id, suryo)
values
('000A','東京','0001',30),
('000A','東京','0002',50),
('000A','東京','0003',15),
('000B','名古屋','0002',30),
('000B','名古屋','0003',120),
('000B','名古屋','0004',20),
('000B','名古屋','0006',10),
('000B','名古屋','0007',40),
('000C','大阪','0003',20),
('000C','大阪','0004',50),
('000C','大阪','0006',90),
('000C','大阪','0007',70),
('000D','福岡','0001',100);
INSERT 0 13
commit;
COMMIT
たとえば大阪の店舗が販売している商品の販売単価を表示しようとおもったら、
--まずは大阪の店舗が扱う商品IDを取得
select shohin_id
from tenposhohin
where tenpo_id = '000C';
shohin_id
-----------
0003
0004
0006
0007
(4 rows)
--上記の商品IDの販売単価を探すという手順になる
select shohin_mei, hanbai_tanka
from shohin
where shohin_id = '0003'
or shohin_id = '0004'
or shohin_id = '0006'
or shohin_id = '0007';
shohin_mei | hanbai_tanka
----------------+--------------
カッターシャツ | 4000
包丁 | 3000
フォーク | 500
おろしがね | 880
(4 rows)
それをサブクエリとINを使えば以下のように表現できる
select shohin_mei,hanbai_tanka
from shohin
where shohin_id in (
select shohin_id
from tenposhohin
where tenpo_id = '000C'
);
shohin_mei | hanbai_tanka
----------------+--------------
カッターシャツ | 4000
包丁 | 3000
フォーク | 500
おろしがね | 880
(4 rows)
NOT INを使ったサブクエリ
以下は東京の店舗以外で販売している商品以外の商品名と販売単価を表示するという意味。
東京で販売している商品はTシャツ'0001',穴あけパンチ'0002',カッターシャツ'0003'なので、
それ以外の商品名と販売単価を表示する
select shohin_mei,hanbai_tanka
from shohin
where shohin_id not in (select shohin_id
from tenposhohin
where tenpo_id = '000A'
);
shohin_mei | hanbai_tanka
------------+--------------
包丁 | 3000
圧力鍋 | 6800
フォーク | 500
おろしがね | 880
ボールペン | 100
(5 rows)
EXISTS
ある条件に合致するレコードの存在有無を調べる
たとえば、INでも求めた大阪に置いてある商品名と販売単価を再度EXISTSを使ってもとめる
特徴は引数が１つなので、相関サブクエリを常に指定する。
--s
select shohin_id, shohin_mei, hanbai_tanka
from shohin;
shohin_id | shohin_mei | hanbai_tanka
-----------+------------+--------------
0001 | Tシャツ | 1000
0002 | 穴あけパンチ | 500
0003 | カッターシャツ | 4000
0004 | 包丁 | 3000
0005 | 圧力鍋 | 6800
0006 | フォーク | 500
0007 | おろしがね | 880
0008 | ボールペン | 100
(8 rows)
--ts
select *
from tenposhohin ;
tenpo_id | tenpo_mei | shohin_id | suryo
----------+-----------+-----------+-------
000A | 東京 | 0001 | 30
000A | 東京 | 0002 | 50
000A | 東京 | 0003 | 15
000B | 名古屋 | 0002 | 30
000B | 名古屋 | 0003 | 120
000B | 名古屋 | 0004 | 20
000B | 名古屋 | 0006 | 10
000B | 名古屋 | 0007 | 40
000C | 大阪 | 0003 | 20
000C | 大阪 | 0004 | 50
000C | 大阪 | 0006 | 90
000C | 大阪 | 0007 | 70
000D | 福岡 | 0001 | 100
(13 rows)
select shohin_mei, hanbai_tanka
from shohin as s
where exists (select *
from tenposhohin as ts
where ts.tenpo_id = '000C'
and ts.shohin_id = s.shohin_id );
shohin_mei | hanbai_tanka
----------------+--------------
カッターシャツ | 4000
包丁 | 3000
フォーク | 500
おろしがね | 880
(4 rows)
NOTを使って書くこともできる。
select shohin_mei, hanbai_tanka
from shohin as s
where not exists (select *
from tenposhohin as ts
where ts.tenpo_id = '000C'
and ts.shohin_id = s.shohin_id );
shohin_mei | hanbai_tanka
--------------+--------------
Tシャツ | 1000
穴あけパンチ | 500
圧力鍋 | 6800
ボールペン | 100
(4 rows)
CASE
CASE式は場合分けをするときに使う。
つまり条件分岐である。
CASE式は２種類あり、単純CASE式と検索CASE式がある。
CASE WHEN 評価式 THEN 式
WHEN 評価式 THEN 式
WHEN 評価式 THEN 式
:
ELSE 式
END
WHEN句の評価式は戻り値が真理値
もし評価が真になれば、THEN句で指定された式が戻されて、CASE式全体が終了する。
評価が偽なら次のWHEN句の評価に移る。
最後のWHEN句まで繰り返しても真にならなかっった場合は、
ELSEで指定された式がもどされて終了する。
--検索case式 検索case式の場合、様々な条件式をかける。
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;
--else null は最後にnullを返すという意味
--else nullは省略可能で自動的にelse nullになる
shohin_mei | abc_shohin_bunrui
----------------+-------------------
Tシャツ | A:衣服
穴あけパンチ | B:事務用品
カッターシャツ | A:衣服
包丁 | C:キッチン用品
圧力鍋 | C:キッチン用品
フォーク | C:キッチン用品
おろしがね | C:キッチン用品
ボールペン | B:事務用品
(8 rows)
--単純case式
select shohin_mei,
case shohin_bunrui
when '衣服' then 'A:' || shohin_bunrui
when '事務用品' then 'B:' || shohin_bunrui
when 'キッチン用品' 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:事務用品
(8 rows)
CASE式は式なので、たとえば以下のようなにGROUP BY句のように行でしか表せないものを、列で表すことができる。
select shohin_bunrui, sum(hanbai_tanka) as sum_tanka
from shohin
group by shohin_bunrui;
shohin_bunrui | sum_tanka
---------------+-----------
キッチン用品 | 11180
衣服 | 5000
事務用品 | 600
(3 rows)
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 | 600
(1 row)