SAK 図書館
SAK 図書館
PostgreSQL 編11 - 問い合わせ、集計、平均、最大、最小、重複、集合、複合
■集計問い合わせ(合計値)
・集計問い合わせを行うには、group by 句を使用する。
この例では、key の先頭が「a」で始まる明細を key 単位に集計(サマリー)
し、data1 の合計が 10 以上のものを問い合わせる。
having 句は、集計後の結果に対して抽出条件が指定できる。
select key, sum(data1) from testm
where key like 'a%'
group by key
having sum(data1) >= 10
;
・count() を使用すると、合計件数が問い合わせられる。
(問い合わせ件数表示、照会件数表示、抽出件数)
この例では、key 単位に集計し、data1 の合計が 10 以上のものを
問い合わせる。
select key, count(*), sum(data1) from testm
group by key
;
key | count | sum
----------+-------+-----
a001 | 1 | 1
a011 | 1 | 1
b002 | 1 | 10
c003 | 1 | 100
・単純に特定の明細のレコード件数を問い合わせるときは、次のように使用す
る。この例では、key の先頭が「a」で始まる明細の件数が得られる。
select count(*) from testm
where key like 'a%'
;
count
-------
2
・avg を使用すると平均を問い合わせることができる。
この例では、key 毎の data1 の平均が得られる。
select key, avg(data1) from testm
group by key
;
key | avg
----------+----------------
a001 | 1.0000000000
a011 | 1.0000000000
b002 | 10.0000000000
c003 | 100.0000000000
・max、min を使用すると最大値と最長値を問い合わせることができる。
この例では、key 毎の data1 の最大と最小が得られる。
select key, max(data1), min(data1) from testm
group by key
;
key | max | min
----------+-----+-----
a001 | 1 | 1
a011 | 1 | 1
b002 | 10 | 10
c003 | 100 | 100
■重複レコード除去(重複レコード削除)
・testm のキーがユニークでないと仮定すると、同じキーのレコードが複数
存在する事になる。
distinct を指定すると重複レコードを取り除いて問い合わせできる。
distinct の代わりに unique と Oracle のように指定することはできない。
(重複データ)
select distinct key
from testm
;
key
----------
a001
a011
b002
c003
■集合問い合わせ
・union all を使用すると問い合わせ結果をマージできる。
この例では、testm と testmsv の key の先頭が「a」で始まるものが問い
合わされる。union all の代わりに union とだけ指定すると、重複レコード
は含まれない。(表を合わせ、付け足す)
select * from testm
where key like 'a%'
union all
select * from testmsv
where key like 'a%'
;
select * from testm
where key like 'a%'
union all
select * from testm
where key like 'a%'
;
key | data1 | data2 | data3
----------+-------+-------+-------
a001 | 1 | 2 | 3
a011 | 1 | 2 | 3
a001 | 1 | 2 | 3
a011 | 1 | 2 | 3
select * from testm
where key like 'a%'
union
select * from testm
where key like 'a%'
;
key | data1 | data2 | data3
----------+-------+-------+-------
a001 | 1 | 2 | 3
a011 | 1 | 2 | 3
■集合差問い合わせ
・minus、minus all は、PostgreSQL では使用できない。
■集合結合問い合わせ
・intersect を使用すると両方の問い合わせ共通に戻されるレコードだけが
戻る。
select * from testm
where key like 'a%'
intersect
select * from testm
where key = 'a001'
;
key | data1 | data2 | data3
----------+-------+-------+-------
a001 | 1 | 2 | 3
■複合問い合わせ
・in を使用すると副問い合わせの結果で、問い合わせることができる。
この例では、test2m の code1 の先頭が「a」で始まるものから重複レコード
を除いて、testm の key を抽出している。
(副問合せ、サブクエリ、副参照、複問い合わせ、副 select 文)
select * from testm
where key in
(select distinct code1 from test2m where code1 like 'a%')
;
key | data1 | data2 | data3
----------+-------+-------+-------
a001 | 1 | 2 | 3
a011 | 1 | 2 | 3
・この例では、test2m の code1 の先頭が「a」で始まるものから重複レコード
を除いて、その結果以外の testm の key を抽出している。
select * from testm
where key not in
(select distinct code1 from test2m where code1 like 'a%')
;
key | data1 | data2 | data3
----------+-------+-------+-------
b002 | 10 | 20 | 30
c003 | 100 | 200 | 300
・any は、副問い合わせのいずれかと言う条件になる。
この例では、副問い合わせのいずれかより大きければ抽出される。
但し、この例は悪い。現実的な使い方をして下さい。
select * from testm
where key > any
(select distinct code1 from test2m where code1 like 'a%')
;
key | data1 | data2 | data3
----------+-------+-------+-------
a011 | 1 | 2 | 3
b002 | 10 | 20 | 30
c003 | 100 | 200 | 300
・all は、副問い合わせの全てにと言う条件になる。
この例では、副問い合わせの全てより大きければ抽出される。
但し、この例は悪い。現実的な使い方をして下さい。
select * from testm
where key > all
(select distinct code1 from test2m where code1 like 'a%')
;
key | data1 | data2 | data3
----------+-------+-------+-------
b002 | 10 | 20 | 30
c003 | 100 | 200 | 300
・例えば、同じ品番の内で、最大の受注数レコードだけを得るには、
次のように all を使用する。
このような問い合わせを、相関問い合わせ、相関副問い合わせと呼ぶらしい。
副問い合わせのパラメタに主問い合わせの項目が使用されるからだろうか。
select * from 受注m a
where a.受注数 >= all
(select 受注数 from 受注m b where b.品番 = a.品番)
;
・例えば、同じ品番の内で、最新の受注日のレコードだけを得るには、
次のように all を使用する。(最新日付抽出)
select * from 受注m a
where a.受注日 >= all
(select 受注日 from 受注m b where b.品番 = a.品番)
;
・副問い合わせから、更に問い合わせることもできる。
この例では、最初に品番の先頭が「a」で始まるものを抽出して、その結果
に対して、受注数 が 1 以上の抽出を行っている。
但し、この例の結果なら、もっと簡単に一度に求められる。
from 句に副問い合わせを使うことをインラインビューと呼ぶらしい。
select * from
(select * from testm where key like 'a%') a
where a.data3 >= 1
;
key | data1 | data2 | data3
----------+-------+-------+-------
a001 | 1 | 2 | 3
a011 | 1 | 2 | 3
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
■SQL 基礎編資料
■SQL 基礎実地編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
Copyright (C) 1999-2018 Y.SAK