postgreSQL その8 課題リストは2018/4/30時点でのオイラの所見
[P.272]
■組込関数と演算子
【集約関数】
[準備]
testdb=> select * from member;
id | 名前 | 年齢 | 部署
----+------+------+------
1 | 鈴木 | 25 | 営業
2 | 佐藤 | 30 | 開発
3 | 田中 | 25 | 開発
4 | 渡辺 | 30 | 総務
5 | 後藤 | 41 | 営業
6 | 渡辺 | 25 | 開発
(6 rows)
・count()
[書式]
select count (列名) from テーブル名 where 検索条件;
[例]
・memberテーブルの全行数をカウント
testdb=> select count(*) from member;
count
-------
6
(1 row)
・memberテーブルのうち、「部署」が開発である行の数をカウント
testdb=> select count(*) from member where 部署 = '開発';
count
-------
3
(1 row)
・部署でグループ化し、各部署の人数をカウントし、総人数が3人以上の部署を検索
testdb=> select 部署,count(*) from member group by 部署;
部署 | count
------+-------
総務 | 1
開発 | 3
営業 | 2
(3 rows)
testdb=> select 部署,count(*) from member group by 部署 having (count(*) >= 3);
部署 | count
------+-------
開発 | 3
(1 row)
・sum()
[書式]
select sum(列名) from テーブル名 where 検索条件;
[例]
・memberテーブルの年齢の合計
testdb=> select sum(年齢) from member;
sum
-----
176
(1 row)
・部署でグループ化して各部署の「年齢」の合計
testdb=> select 部署,sum(年齢) from member group by 部署;
部署 | sum
------+-----
総務 | 30
開発 | 80
営業 | 66
(3 rows)
・avg()
[書式]
select avg(列名) from テーブル名 where 検索条件;
[例]
・部署でグループ化して各部署の「年齢」の平均
testdb=> select 部署,avg(年齢) from member group by 部署;
部署 | avg
------+---------------------
総務 | 30.0000000000000000
開発 | 26.6666666666666667
営業 | 33.0000000000000000
(3 rows)
・部署でグループ化して各部署の「年齢」の合計と平均
testdb=> select 部署,sum(年齢),avg(年齢) from member group by 部署;
部署 | sum | avg
------+-----+---------------------
総務 | 30 | 30.0000000000000000
開発 | 80 | 26.6666666666666667
営業 | 66 | 33.0000000000000000
(3 rows)
・min()とmax()
[書式]
select min(列名) from テーブル名 where 検索条件;
select max(列名) from テーブル名 where 検索条件;
[例]
・各部署ごとの「年齢」の最大値と最小値
testdb=> select 部署,min(年齢),max(年齢) from member group by 部署;
部署 | min | max
------+-----+-----
総務 | 30 | 30
開発 | 25 | 30
営業 | 25 | 41
(3 rows)
【比較演算子】
|
演算子 |
説明 |
|---|---|
|
< |
小なり |
|
> |
大なり |
|
<= |
以下 |
|
>= |
以上 |
|
= |
等しい |
|
<> |
等しくない |
|
!= |
等しくない |
[例]
testdb=> select 2 > 1;
?column?
----------
t
(1 row)
testdb=> select 'ABC' = 'ABC';
?column?
----------
t
(1 row)
testdb=> select 2 != 2;
?column?
----------
f
(1 row)
NULLかどうかを判定する場合には、比較演算子「=」などは使えない。代わりに「X IS NULL」や「X IS NOT NULL」を使う
[例]
testdb=> select (select 年齢 from member where id = 1) IS NOT NULL;
?column?
----------
t
(1 row)
testdb=> select (select 年齢 from member where id = 1) = NULL;
?column?
----------
(1 row)
【算術関数と算術演算子】
・算術関数
|
関数名 |
説明 |
使用例 |
結果 |
|---|---|---|---|
|
abs(x) |
xの絶対値 |
abs(-2.5) |
2.5 |
|
div(y,x) |
y/xの整数商 |
div(4,3) |
1 |
|
mod(y,x) |
y/xの剰余 |
mod(4,3) |
1 |
|
sqrt(x) |
xの平方根 |
sqrt(2) |
1.4142135623731 |
|
power(x,y) |
xのy乗 |
power(3,4) |
81 |
|
celi(x) |
xより小さくない最小の整数 |
celi(10.5) |
11 |
|
floor(x) |
xより大きくない最大の整数 |
floor(10.5) |
10 |
|
round(x) |
xの小数点を四捨五入 |
round(10.5) |
11 |
|
trunc(x) |
xの小数点を切り捨て |
trunc(10.5) |
10 |
|
log(x) |
xの常用対数 |
log(100) |
2 |
|
pi() |
円周率 |
pi() |
3.14159265358979 |
|
random() |
0.0以上1.0未満の乱数値 |
random() |
0.0390656110830605 |
[例]
testdb=> select random();
random
--------------------
0.0390656110830605
(1 row)
testdb=> select random()*100;
?column?
------------------
98.6058771144599
(1 row)
testdb=> select (random()*100)::int;
int4
------
82
(1 row)
testdb=> select (random()*100)::int % 100;
?column?
----------
50
(1 row)
・算術演算子
|
演算子 |
説明 |
使用例 |
結果 |
|---|---|---|---|
|
+ |
和 |
1+1 |
2 |
|
- |
差 |
2-1 |
1 |
|
* |
積 |
2*2 |
4 |
|
/ |
商（整数では余りを切り捨て） |
5/2 |
2 |
|
% |
剰余 |
5%2 |
1 |
|
^ |
べき乗 |
2^2 |
4 |
|
|/ |
平方根 |
|/2 |
1.4142135623731 |
|
||/ |
立方根 |
||/8 |
2 |
|
! |
階乗 |
3! |
6 |
|
!! |
階乗 |
!!3 |
6 |
|
@ |
絶対値 |
@ -3.5 |
3.5 |
[例]
testdb=> select 1+1;
?column?
----------
2
(1 row)
testdb=> select 2/0;
ERROR: division by zero
testdb=> select 1/2;
?column?
----------
0
(1 row)
testdb=> select 1/2.0;
?column?
------------------------
0.50000000000000000000
(1 row)
testdb=> select 1.0/2;
?column?
------------------------
0.50000000000000000000
(1 row)
testdb=> select (1::float)/2;
?column?
----------
0.5
(1 row)
testdb=> select (1.0+1)/3;
?column?
------------------------
0.66666666666666666667
(1 row)
testdb=> select 7 - 3!;
?column?
----------
24
(1 row)
testdb=> select 7 - (3!);
?column?
----------
1
(1 row)
・演算の優先順序は他のプログラム開発言語と同じ
・小括弧で演算優先順位を制御できるのも他のプログラム開発言語と同じ
【文字列演算子と述語】
・「||」演算子
[例]
・文字列の結合
testdb=> select 'あいう' || 'えお';
?column?
------------
あいうえお
(1 row)
testdb=> select 名前,年齢 || '歳' from member;
名前 | ?column?
------+----------
鈴木 | 25歳
佐藤 | 30歳
田中 | 25歳
渡辺 | 30歳
後藤 | 41歳
渡辺 | 25歳
(6 rows)
・LIKE
LIKEは、文字列に対する前方/後方/中間一致検索を行う
[書式]
select 列名 from テーブル名 where 列名 like '条件';
「条件」には、文字列、「%」、「_」を用いることができる。「%」は任意の文字列、「_」は任意の1文字として扱われる。
[例]
testdb=> select * from member where 名前 like '%藤';
id | 名前 | 年齢 | 部署
----+------+------+------
2 | 佐藤 | 30 | 開発
5 | 後藤 | 41 | 営業
(2 rows)
testdb=> select * from member where 年齢::text like '2_';
id | 名前 | 年齢 | 部署
----+------+------+------
1 | 鈴木 | 25 | 営業
3 | 田中 | 25 | 開発
6 | 渡辺 | 25 | 開発
(3 rows)
・SIMILAR TO
SIMILAR TOは、正規表現を使ったパターンマッチングができる。
[書式]
文字列 similar to '条件';
「条件」には、likeで使用できる「%」、「_」に加え、下表に示すメタ文字を使用することができる。
メタ文字
|
文字など |
説明 |
|---|---|
|
| |
二者択一 |
|
* |
直前の項目の0回以上の繰り返し |
|
+ |
直前の項目の1回以上の繰り返し |
|
? |
直前の項目の0回もしくは1回の繰り返し |
|
{m} |
直前の項目のm回の繰り返し |
|
{m,} |
直前の項目のm回以上の繰り返し |
|
{m,n} |
直前の項目のm回以上かつn回以下の繰り返し |
|
() |
()ないを1つの論理項目として指定 |
|
[] |
[]内のいずれか1つの文字に一致 |
[準備]
testdb=> select * from tab1;
c1
---------
ABCDEFG
TUVWXYZ
AAAAAAA
TYXWVUZ
(4 rows)
[例]
testdb=> select c1 from tab1 where c1 similar to '%(B|D)%';
c1
---------
ABCDEFG
(1 row)
testdb=> select c1 from tab1 where c1 similar to '%(U|Z)+%';
c1
---------
TUVWXYZ
TYXWVUZ
(2 rows)
testdb=> select c1 from tab1 where c1 similar to 'A{2,}%';
c1
---------
AAAAAAA
(1 row)
testdb=> select c1 from tab1 where c1 similar to '[A-Z]*';
c1
---------
ABCDEFG
TUVWXYZ
AAAAAAA
TYXWVUZ
(4 rows)
・「~」演算子
文字列に対して使用できる演算子「~」は、文字列のパターンマッチングを行う演算子。
下記の4通りの使い方がある。
・文字列 ~ 正規表現;
正規表現に一致する場合に真となる。大文字小文字が区別される。
・文字列 ~* 正規表現;
正規表現に一致する場合に真となる。大文字小文字が区別されない。
・文字列 !~ 正規表現;
正規表現に一致しない場合に真となる。大文字小文字が区別される。
・文字列 !~* 正規表現;
正規表現に一致しない場合に真となる。大文字小文字が区別されない。
[準備]
testdb=> select * from tab1;
c1
-----
ABC
DEF
CDF
(3 rows)
[例]
testdb=> select c1 from tab1 where c1 ~ '^A';
c1
-----
ABC
(1 row)
testdb=> select c1 from tab1 where c1 ~* 'f$';
c1
-----
DEF
CDF
(2 rows)
testdb=> select c1 from tab1 where c1 !~ '^C';
c1
-----
ABC
DEF
(2 rows)
testdb=> select c1 from tab1 where c1 !~* '^d';
c1
-----
ABC
CDF
(2 rows)