データベースとテーブルの作成

PostgreSQL環境での開発は、ストレージ単位であるデータベースの作成から始まります。

CREATE DATABASE meteorology_db;

不要になったデータベースは、以下のコマンドで物理的に削除します。

DROP DATABASE meteorology_db;

テーブル構造の定義

観測データを格納するメインテーブルと、地域マスター情報を管理する補助テーブルを作成します。カラム名とデータ型は業務要件に合わせて設計します。

CREATE TABLE observation_logs (
    location_id     varchar(50),
    low_temp        integer,
    high_temp       integer,
    precipitation   double precision,
    record_date     date
);

CREATE TABLE location_master (
    location_id     varchar(50) PRIMARY KEY,
    geo_position    point
);

テーブルの破棄は DROP TABLE を使用し、依存関係に注意して実行します。

DROP TABLE location_master;
DROP TABLE observation_logs;

レコードの投入

スキーマが構築できたら、INSERT INTO 文を用いてデータを取り込みます。カラムリストを明示的に記述することで、値の順序不一致によるエラーを未然に防ぎます。

INSERT INTO observation_logs (location_id, low_temp, high_temp, precipitation, record_date)
VALUES ('Kyoto', 8, 15, 2.5, '2023-11-10');

INSERT INTO location_master (location_id, geo_position)
VALUES ('Kyoto', '(135.7681, 35.0116)');

-- カラムを指定し、任意の順序で値を挿入
INSERT INTO observation_logs (record_date, location_id, high_temp, low_temp)
VALUES ('2023-11-12', 'Nara', 17, 10);

データ抽出と条件指定

格納された情報にアクセスするには SELECT 文を構築します。基礎的な抽出、算術演算、ソート、重複排除の構文を確認します。

基礎抽出と列計算

SELECT * FROM observation_logs;

-- 特定カラムの抽出と平均気温の算出
SELECT location_id, (high_temp + low_temp) / 2.0 AS avg_temp, record_date
FROM observation_logs;

フィルタリングとソート

-- 降水量が0より大きい日の京都データを取得
SELECT * FROM observation_logs
WHERE location_id = 'Kyoto' AND precipitation > 0.0;

-- 地域IDと最低気温の昇順で並べ替え
SELECT * FROM observation_logs ORDER BY location_id, low_temp;

-- 重複した地域名の排除
SELECT DISTINCT location_id FROM observation_logs ORDER BY location_id;

複数テーブルの結合

異なるテーブルに跨がるデータを一体化するには結合演算子を使用します。可読性と保守性を高めるため、PostgreSQLでは明示的な JOIN 構文の採用が推奨されます。

内部結合 (INNER JOIN)

結合条件を満たす行のみに絞って結果を返します。

SELECT o.location_id, o.low_temp, o.high_temp, o.precipitation, o.record_date, m.geo_position
FROM observation_logs o
INNER JOIN location_master m ON o.location_id = m.location_id;

左外部結合 (LEFT OUTER JOIN)

左テーブルの全レコードを保持し、右テーブルに一致しない場合は NULL で補完します。

SELECT o.location_id, o.low_temp, o.high_temp, o.precipitation, o.record_date, m.geo_position
FROM observation_logs o
LEFT OUTER JOIN location_master m ON o.location_id = m.location_id;

自己結合 (Self Join)

同一テーブルを別名で2回参照し、行同士の比較処理を行います。

SELECT 
    s1.location_id AS area_a, s1.low_temp AS min_a, s1.high_temp AS max_a,
    s2.location_id AS area_b, s2.low_temp AS min_b, s2.high_temp AS max_b
FROM observation_logs s1
JOIN observation_logs s2 
    ON s1.low_temp < s2.low_temp
    AND s1.high_temp > s2.high_temp;

集約処理とグループ化

複数行のデータに対して統計計算を実行するには、集約関数と GROUP BY 句を組み合わせて利用します。

極値の取得とサブクエリ

-- データセット全体の最低気温の最大値を算出
SELECT MAX(low_temp) FROM observation_logs;

-- 該当値を記録した地域を特定
SELECT location_id FROM observation_logs
WHERE low_temp = (SELECT MAX(low_temp) FROM observation_logs);

グループ化後の条件絞込み (HAVING)

グループ化された結果セットに対して条件を適用するには HAVING 句を使用します。

-- 各都市における最低気温の最大値を計算
SELECT location_id, MAX(low_temp) AS peak_low
FROM observation_logs
GROUP BY location_id;

-- 集約値が20未満のグループのみ抽出
SELECT location_id, MAX(low_temp)
FROM observation_logs
GROUP BY location_id
HAVING MAX(low_temp) < 20;

-- WHERE（集約前）とHAVING（集約後）の併用
SELECT location_id, MAX(low_temp)
FROM observation_logs
WHERE location_id LIKE 'K%'
GROUP BY location_id
HAVING MAX(low_temp) < 20;