2014年7月24日
+ 加算 - 減算 * 乗算 / 除算 % 剰余これらの優先順位は数式での演算順位と同じであり，() を使って演算の順序を変更できる。
SELECT *, popul/1000 FROM population ; name | popul | house | ?column? --------+--------+--------+---------- 函館市 | 294264 | 128411 | 294 七飯町 | 28424 | 10363 | 28 北斗市 | 48056 | 17779 | 48 鹿部町 | 4919 | 1646 | 4 森町 | 19149 | 7363 | 19
SELECT name, popul/1000 AS sennin FROM population WHERE popul/1000 = 28; name | sennin --------+-------- 七飯町 | 28
SELECT name || '(hakodate)' FROM spring WHERE area = '01202';
?column?
------------------------
谷地頭温泉(hakodate)
湯の川温泉街(hakodate)
川汲温泉郷(hakodate)
戸井温泉(hakodate)
SELECT new_post_code AS postcode, town_kanji || zone_kanji AS shi_cho FROM postcode WHERE new_post_code = '0400083'; postcode | shi_cho ----------+-------------- 0400083 | 函館市八幡町
SELECT *, CHAR_LENGTH(name) FROM spring ;
name | area | char_length
--------------+-------+-------------
谷地頭温泉 | 01202 | 5
湯の川温泉街 | 01202 | 6
東大沼温泉郷 | 01337 | 6
川汲温泉郷 | 01202 | 5
戸井温泉 | 01202 | 4
せせらぎ温泉 | 01236 | 6
鹿部温泉郷 | 01343 | 5
濁川温泉郷 | 01345 | 5
仁山温泉 | 01337 | 4
SELECT * FROM spring WHERE CHAR_LENGTH(name) = 5;
name | area
------------+-------
谷地頭温泉 | 01202
川汲温泉郷 | 01202
鹿部温泉郷 | 01343
濁川温泉郷 | 01345
なお，CHAR_LENGTH の引数には，
CHAR_LENGTH('函館') のように定数を与えることも可能。
select 1 + 2;
select '函館' || '市';
select CHAR_LENGTH('函館');
COUNT(*) 行数 (*の代わりに列名指定も可) SUM(列名) 合計 AVG(列名) 平均 MAX(列名) 最大値 MIN(列名) 最小値
population テーブルの内容は次のとおりである。
SELECT * FROM population ; name | popul | house --------+--------+-------- 函館市 | 294264 | 128411 七飯町 | 28424 | 10363 北斗市 | 48056 | 17779 鹿部町 | 4919 | 1646 森町 | 19149 | 7363
このテーブルに対して，次のように集合関数を適用できる。
SELECT COUNT(*), MAX(popul), SUM(popul), MAX(house), SUM(house)
FROM population WHERE popul < 100000;
count | max | sum | max | sum
-------+-------+--------+-------+-------
4 | 48056 | 100548 | 17779 | 37151
集合関数は WHERE 句の検索条件には使えない。 また，集合関数の値とテーブル内の値を同時に表示するときには， 次節に示す GROUP BY を使ったテーブルのグループ化が必要である。
SELECT name, COUNT(*) FROM population; ERROR: column "population.name" must appear in the GROUP BY clause or be used in an aggregate function ERROR: 列"population.name"はGROUP BY句で出現しなければならないか、集約 関数内で使用しなければなりません。
まず，これまでに紹介した方法で，spring テーブルの内容全てを area 列で昇順に並べ替えて表示する。
SELECT * FROM spring ORDER BY area;
name | area
--------------+-------
谷地頭温泉 | 01202
湯の川温泉街 | 01202
川汲温泉郷 | 01202
戸井温泉 | 01202
せせらぎ温泉 | 01236
東大沼温泉郷 | 01337
仁山温泉 | 01337
鹿部温泉郷 | 01343
濁川温泉郷 | 01345
これを area 列でグループ化して，各グループに同じエリアの行が何行ずつあるのかを表示する SQL と実行結果は，次の通りとなる。 表示順は，行数の多い順（降順）としている。
SELECT area, COUNT(*) FROM spring GROUP BY area ORDER BY count DESC; area | count -------+------- 01202 | 4 01337 | 2 01345 | 1 01343 | 1 01236 | 1
一般に，GROUP BY 句を含む SELECT 文において， SELECT の後に指定できる列名は， 各グループに対して値が一意に定まる列に限られる。
ただし，この授業で使用している PostgreSQL 8.4 の場合， SELECT の後には GROUP BY で用いた列のみ指定可能である。
SELECT name, area FROM spring GROUP BY area; ERROR: column "spring.name" must appear in the GROUP BY clause or be used in an aggregate function
SELECT area, COUNT(*) FROM spring GROUP BY area HAVING COUNT(*) > 1; area | count -------+------- 01337 | 2 01202 | 4 SELECT area, COUNT(*) FROM spring GROUP BY area HAVING area LIKE '012%'; area | count -------+------- 01202 | 4 01236 | 1
GROUP BY 句や HAVING 句は WHERE 句と共に使うこともできる。 WHERE 句での検索条件が GROUP BY でグループ化される以前の各行を特定するのに対し， HAVING 句での検索条件はグループ化された後の行を特定することに注意せよ。
SELECT * FROM area_code, spring WHERE area_code.code = spring.area; name | code | name | area --------+-------+--------------+------- 函館市 | 01202 | 谷地頭温泉 | 01202 函館市 | 01202 | 湯の川温泉街 | 01202 函館市 | 01202 | 川汲温泉郷 | 01202 函館市 | 01202 | 戸井温泉 | 01202 北斗市 | 01236 | せせらぎ温泉 | 01236 七飯町 | 01337 | 東大沼温泉郷 | 01337 七飯町 | 01337 | 仁山温泉 | 01337 鹿部町 | 01343 | 鹿部温泉郷 | 01343 森町 | 01345 | 濁川温泉郷 | 01345 SELECT area_code.name AS area_name, COUNT(*) FROM area_code, spring WHERE area_code.code = spring.area GROUP BY area_code.name; area_name | count -----------+------- 鹿部町 | 1 七飯町 | 2 北斗市 | 1 森町 | 1 函館市 | 4 SELECT area_code.name AS area_name, count(*) FROM area_code, spring WHERE area_code.code = spring.area GROUP BY area_code.name HAVING count(*) > 1; area_name | count -----------+------- 七飯町 | 2 函館市 | 4
SELECT に続いて記述するキーワードや句の順序は定められており，
それに違反すると syntax error が起きる。
SELECT 文の記述の書式は，psql のヘルプコマンド \h
を使って調べることができる。
\h select
Command: SELECT
Description: retrieve rows from a table or view
Syntax:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
--- 以下省略 ---
ここで [ ] 内の要素は省略可能であることを意味し，| で区切ら
れた要素はそのうちの何れかを指定できることを意味する。
なお，
関数 CAST を用いて int 型の列を float 型等に一時的に型変換（キャスト）すれば，
小数点以下を含めて平均人数を求められる。
例えば，popul 列を float 型にキャストするには，
列名 popul に代え CAST(popul AS float) を使う。
loc_name
--------------------
函館市谷地頭温泉
函館市湯の川温泉街
函館市川汲温泉郷
函館市戸井温泉
北斗市せせらぎ温泉
七飯町東大沼温泉郷
七飯町仁山温泉
鹿部町鹿部温泉郷
森町濁川温泉郷
SELECT town_kanji, old_post_code, new_post_code FROM postcode WHERE old_post_code LIKE '041%' order by new_post_code;
さらに，旧郵便番号が 041 で始まる各行について， 同じ市町村名の行が何行ずつあるのか求めなさい。 次の結果を得ること。 なお，行の順序は異なってよい。
town_kanji | count --------------+------- 函館市 | 79 亀田郡七飯町 | 22 茅部郡鹿部町 | 6 北斗市 | 18