データベース講習会（初級編）
於 国立天文台三鷹キャンパス
主催 天文データセンター
講師 小澤 武揚（天文データセンター）

データベース講習会
2019年 2月 14 - 15日

2

目次
1.

2.

3.

4.

5.

はじめに
1.1 背景1.2 本講習会の内容
1.3 参考資料
データベースの基礎
6.
2.1 データベースとは
2.2 データベースの種類
2.3 リレーショナルデータベース管理システム
2.4 SQL言語
PostgreSQLについて
3.1 PostgreSQLとは
3.2 PostgreSQL小史
3.3 PostgreSQLの特徴
3.4 対応するプラットフォーム
PostgreSQLのインストール
4.1 概要
4.2 作業準備
4.3 PostgreSQLのインストール
4.4 CentOS 7以外のOSへのインストールについて
7.
PostgreSQLの初期設定
5.1 概要5.2 Linuxユーザとデータベースロールの設定
5.2.1 Linuxユーザ「postgres」のパスワードの変更
5.2.2 データベースロール「postgres」のパスワード変更
5.2.3 データベースロール「dbr」の作成
5.3 PostgreSQLサーバ制御ファイル（postgresql.conf）の設定
5.3.1 変更すべき項目
5.3.2 postgresql.confの設定

2019年 2月 14 - 15日

5.4 アクセス制御ファイル（pg_hba.conf）の設定
5.4.1 pg_hba.confの書式
5.4.2 pg_hba.confの設定
5.4.3 pg_hba.confの設定確認
データベースの作成
6.1 概要6.2 テーブル空間の作成
6.3 データベースの作成
6.4 テーブルの作成
6.4.1 テーブルの構造
6.4.2 PostgreSQLがサポートするデータ型
6.4.3 PGC2003カタログ
6.4.4 テーブルの作成
6.4.5 天体カタログの登録
6.5 インデックスの作成
6.5.1 インデックスの仕組み
6.5.2 インデックスの作成と検索速度の比較
6.6 NVSSカタログ用のテーブルの作成とデータの登録
psqlの使用方法
7.1 概要7.2 「psql」コマンドの基本的な使い方
7.3 SQLコマンド
7.4 メタコマンド
7.5 ログインパスワードの入力省略設定

データベース講習会

3
8.

テーブルへの問い合わせ
8.1 概要8.2 SELECT文の基本形
8.2.1 基本形
8.2.2 検索結果の出力件数の指定
8.2.3 検索結果の並び替え
8.2.4 カラム・テーブルの別名の定義
8.3 検索条件の指定
8.3.1 比較演算子
8.3.2 IS演算子
8.3.3 論理演算子
8.3.4 BETWEEN句
8.3.5 パターンマッチング
8.4 カラムに対する演算
8.4.1 算術演算子
8.4.2 文字列演算子
8.4.3 型変換関数
8.4.4 算術関数
8.4.5 集計・統計関数
8.5 副問い合わせ
8.5.1 SELECT句での副問い合わせ
8.5.2 FROM句での副問い合わせ
8.5.3 WHERE句での副問い合わせ
8.5.4 共通テーブル式
8.6 テーブルの結合
8.6.1 クロス結合
8.6.2 内部結合
8.6.3 外部結合
8.7 問い合わせ結果の結合
8.7.1 和集合
8.7.2 積集合
8.7.3 差集合
8.8 遅くならないWHERE句の書き方

2019年 2月 14 - 15日

9.

10.

ユーザ定義関数9.1 概要9.2 SQL関数
9.2.1 一行一列の値を返すSQL関数
9.2.2 一行複数列の値を返すSQL関数
9.2.3 複数行複数列の値を返すSQL関数
9.3 PL/pgSQL関数
9.3.1 一行一列の値を返すPL/pgSQL関数
9.3.2 一行複数列の値を返すPL/pgSQL関数
9.3.3 複数行複数列の値を返すPL/pgSQL関数
9.3.４ PL/pgSQLの制御構文
9.4 SQL関数とPL/pgSQL関数の使い分け
指定座標を中心とした任意角度範囲内の天体の検索
10.1 概要
10.2 直交座標系用テーブルの作成
10.2.1 テーブルの作成
10.2.2 座標変換用関数の作成
10.2.3 テーブルへのデータの登録
10.2.4 複合インデックスの作成
10.3 コーンサーチの実装
10.3.1 コーンサーチに必要な関数の作成
10.3.2 コーンサーチの練習
10.3.3 コーンサーチの実装
10.4 クロスマッチの実装
10.4.1 クロスマッチ用ユーザ定義関数の作成
10.4.2 クロスマッチの実行

データベース講習会

1. はじめに2019年 2月 14 - 15日

データベース講習会

5

1.1 背景v 観測機器の高性能化に伴うデータの大規模化によって高速な検索や統計処理の必要性が生じ、個人や各研究所でローカルにデータベースをもつ必要性が生じてくるケースが増えてくると考えられる。
v 大規模なデータの検索や集計は従来データセンターの役目であったが、計算機の高性能化と低廉化により個
人においてもデータベースを構築・活用できるようになってきた。
v ローカルにデータベースを持つことで必要な付加情報を加えたオリジナルのカタログを作成し、効率的にデータの検索や統計処理を行うことができる。

2019年 2月 14 - 15日

データベース講習会

6

1.2 本講習会の内容
v リレーショナルデータベース管理システムである「PostgreSQL」のインストール方法、初期設定方法、データベースの構築方法、データベースの問い合わせ
方法を、Linux端末を操作して実践的に学び習得する。
v 本講習会では次の天文カタログをデータベース化する。
– PGC2003カタログ：983261天体– NVSSカタログ：1773484天体

2019年 2月 14 - 15日

データベース講習会

7

1.3 参考資料
v天文データセンター 2012年度SQL講習会[初級編]資料（山内 千里）
v天文データセンター 2016年度SQL講習会[中級編：データベース構築編]資料（山内千里）
v PostgreSQL本家（https://www.postgresql.org）
v 日本PostgreSQLユーザ会（https://www.postgresql.jp）– PostgreSQL 10.5文書（https://www.postgresql.jp/document/10/html/index.html）– Let’s Postgres（https://lets.postgresql.jp）

v 基礎から始めるデータベース入門セミナー（
https://www.oracle.com/technetwork/jp/articles/index-155234-ja.html）
v PostgreSQL 全機能バイブル（技術評論社）
v 内部構造から学ぶPostgreSQL 設計・運用計画の鉄則（技術評論社）

2019年 2月 14 - 15日

データベース講習会

2. データベースの基礎

2019年 2月 14 - 15日

データベース講習会

9

2.1 データベースとは
v データベースとは– 系統的に整理・管理された情報の集まり。特にコンピューター
で、さまざまな情報検索に高速に対応できるように大量のデータを統一的に管理したファイル。また、そのファイルを管理するシステム（広辞苑第五版より）。
– 整理・統合して蓄積したデータを検索できるようにした仕組みのこと。

v データベースの例• 辞書、電話帳、検索システム、Web通販の商品情報・アカウント
情報、銀行の勘定系システム・・・• 観測データ、天体カタログ、文献データベース・・・

2019年 2月 14 - 15日

データベース講習会

10

2.2 データベースの種類
v データベースは主に「階層型」、「ネットワーク型」、「リレーショナル型」に分類できる
v 階層型データベース– データを親子関係を持った階層構造で管理するデータベース。
• 組織図やファイル管理のような仕組み。

– 欠点• データが冗長的になる。
– 親データは複数の子データを持てるが、子データは1つの親デー
タしか持てない。子データが複数の親データに属する場合、それぞれの親データの下に同一の子データが配置される。

• データの構造が変化した場合、プログラムの修正が必要。
– ファイルのパスを変更した時、そのファイルを使用しているプログ
ラムも修正しなければならない。

2019年 2月 14 - 15日

データベース講習会

11

v ネットワーク型データベース– データを網の目構造で管理するデータベース。
– 利点• データの冗長化が起こらない。

– 欠点• データ同士の関係が複雑になる。
• データ構造が変化した場合、プログラムの修正が必要。
ネットワーク型データベース

階層型データベース

B
A
C
2019年 2月 14 - 15日

D
E
F
G
E

B
A
C

D
E
F
G
データベース講習会

12v リレーショナル型データベース（RDB）– データを行と列からなる表で管理するデータベース。
• データの関係を表と表の関係で表す。
– 表と表に共通するデータをもたせることで関連付けを行う。

• SQLによりデータを自由に取り出せる。

– 利点• プログラムとデータの分離。
– データの独立性が高く、データ構造に変更が生じてもプログラムへの影響が少ない。

– 欠点• どのようなデータであっても表形式にしなければならない。
– 設計方法でパフォーマンスに差が出る。

所属部署

社員データベース

2019年 2月 14 - 15日

id

名字

名前

id

部署

1

山田

太郎

1

A

2

山田

花子

2

B

3

山田

隆夫

3

A

3

B
データベース講習会

2.3 リレーショナルデータベース管理
システム

13

v リレーショナルデータベース管理システム（RDBMS）– RDBをコンピュータ上で実現するためのソフトウェア。
• 有償のものと無償のものが存在する。
– 有償：Oracle Database、Microsoft SQL Server、IBM DB2– 無償：MySQL、PostgreSQL、MariaDB– クライアントサーバモデルを採用しており、クライアントから要求された検索を、データベースを持つサーバーが実行する。
– データベースへの検索要求はSQL言語で行われる。

2019年 2月 14 - 15日

データベース講習会

14

vRDBMSの系譜– http://data-e-education.com/E107_History_of_RDBMS.html より

2019年 2月 14 - 15日

データベース講習会

15

2.4 SQL言語
v SQL（Structured Query Language）– リレーショナルデータベースにおいて、データの定義や問い合わせを行うための言語。
– 米国規格協会（ANSI）と国際標準化機構（ISO）により標準
SQL規格の作成が進められている（最新： SQL:2016）。
– RDBMSは基本的に標準SQL規格に対応するように開発が進められており、異なるRDBMSを使っても使用するSQLが”大きく”変わることはない。
• 関数に関してはRDBMSによって互換性が無いことが多々ある。

2019年 2月 14 - 15日

データベース講習会

3. PostgreSQLについて

2019年 2月 14 - 15日

データベース講習会

17

3.1 PostgreSQLとはv PostgreSQLとは– オープンソースソフトウェア（OSS）のオブジェクトリレーショナル
データベース管理システム（ORDBMS）。
– PostgreSQL Global Development Group（PGDG）によって開発が行われている。
• PGDGは、5人のコアメンバー、34人に主要開発者（日本人3名）、数十名の貢献者から構成される（2019年2月現在）。

– 「ポストグレスキューエル」、「ポストグレス」、「ポスグレ」と呼ばれる。
– OSSのRDBMSとしては、日本ではPostgreSQLのシェアがMySQLを上回っていたが、2009年前後を境にMySQLのシェアがPostgreSQLを上回っている。
• 参考：第3回オープンソースソフトウエア活用ビジネス実態調査

2019年 2月 14 - 15日

データベース講習会

18

3.2 PostgreSQL小史
v PostgreSQL小史– PostgreSQLは最初期のRDBMSとして知られるIngresに端を発している。1986年にカリフォルニア大学バークレー校（UCB）でIngresの後継としてオブジェクト指向を取り入れたRDBMSである
POSTGRES（Post-Ingres）の開発が開始され、1993年に公開されたPOSTGRES 4.2をもって開発が終了した。
– 1995年にUCBの大学院生であったAndrew YuとJolly ChenによってSQL言語インタプリタが追加されたPostgres 95が公開された。
– 1997年にはSQLに対応したことを踏まえて名称がPostgreSQLに改称され、UCB時代のバージョン番号を引き継いだPostgreSQL
6.0が公開された。同時期にPGDGが結成され、インターネットを通じた開発体制が確立された。また1999年には日本のユーザコミュ
ニティである日本PostgreSQLユーザ会（JPUG）が設立された。
– 2019年現在ではPostgreSQL 11が公開されている。
2019年 2月 14 - 15日

データベース講習会

19

3.3 PostgreSQLの特徴
v 天文学の研究や業務にとって嬉しいPostgreSQLの特徴– 個人・商用問わず無償で利用・配布・改変可能– 豊富な資料• 公式ドキュメント– 基礎的な内容から応用的な内容まで多くの例題を使って解説がなされており、これを読めば分からないことはない（かもしれない）。
– JPUGによって全文日本語訳されている。

• ウェブ資料– 歴史的に日本ではコアなユーザが多く、資料が充実している。

– 公開サービス向きの機能• 豊富なクライアントインターフェース– C、Java、JavaScript、ODBC、Perl、PHP、Python、Ruby他、多くの言語からアクセス可能。

• レプリケーション（データベースの複製同期機能）をサポート• 商用版のPowerGresが存在– ソフトウェアが出荷されてから7年間サポートが有るため、長期間同じ環境を維持したい場合などに有用。

• SSL対応

2019年 2月 14 - 15日

データベース講習会

20– 天文データ検索向きの機能• 強力なユーザ定義関数– 通常関数は返り値として1つの値しか返せないが、PostgreSQLではテーブル全体を返すことができる。この機能はORDBMSであるPostgreSQLの大きな特徴であり、複雑な処理も単純な記述で実現できる。

• 強力なユーザ定義関数の開発環境– SQL言語、C言語、PL/pgSQL、PL/Tcl、PL/Perl、PL/Python他、多くの言語を使ってユーザ定義関数を作成することができる。

• 巨大なテーブルに対応する機能– 式インデックスや部分インデックスが使用可能。
– テーブルパーティショニング（テーブルの分割化）が可能。

v PostgreSQLが科学用途に向く理由– 天文データベースでは座標に対する検索が頻繁に行われるが、座標に対する検索はRDBMSにとってはやや特殊な処理であることが多く、何らかのアルゴリズムの実装と最適化が必要となる場合が多い。
– 例えば本講習会で行う「ある座標を中心とした任意角度範囲内の天体の検索」では、天球面上の2点間の角度を高速で計算するアルゴリズムを実装しなければならない。
– PostgreSQLは強力なユーザ定義関数とその開発環境を備えており、目的のアルゴリズムの実装と最適化を簡単に行うことができる。
2019年 2月 14 - 15日

データベース講習会

21

3.4 対応するプラットフォーム
v 以下のプラットフォームで動作する（ことが期待される）– CPUアーキテクチャ※PostgreSQL11 時点• X86、X86_64、IA64、PowerPC、PowerPC 64、S/390、S/
390x、Sparc、Sparc 64、ARM、MIPS、MIPSEL、PA-RISC等– OS• Linux（最近のディストリビューション全て）• Windows（Win2000 SP4以降）• FreeBSD、OpenBSD、NetBSD• OS X• AIX、HP/UX、Solaris 等

本講習会ではCentOS 7へのインストールを実施
2019年 2月 14 - 15日

データベース講習会

4. PostgreSQLのインストール

2019年 2月 14 - 15日

データベース講習会

23

4.1 概要v CentOS 7へのPostgreSQLのインストールを実施– 本講習会では端末「new-rXX」上の仮想マシンにインストールされたCentOS 7を使用する。

v 作業環境– 端末名：new-r[02-14]– ホストOS：CentOS 6(64bit)– 仮想マシン：VMware Workstation 14 Player– ゲストOS：CentOS 7• メモリ：8GB• インストール時のベース環境：サーバー（GUI使用）

v 4章目次– 4.1 概要
– 4.2 作業準備
– 4.3 PostgreSQLのインストール– 4.4 CentOS 7以外のOSへのインストールについて
2019年 2月 14 - 15日

データベース講習会

24

4.2 作業準備v 【実習】 new-rXXへのログイン
1. ターミナルへ配布されたアカウントとパスワードを入力。
• アカウント：別紙参照• パスワード：別紙参照
2. GUIを起動。
$ startx• 一般ユーザで実行するコマンドは「$」を、ルートユーザで実行するコマンドは「#」を行頭につけます。

v 【実習】 ゲストOSへのログイン
1. デスクトップ画面左上の「Application」をクリックし、「System Tools」にカーソルを合わせ一覧から「VMware Player」を選択。
2. 一覧から「CentOS7-DB」をダブルクリックし起動。
3. ログイン画面で「lecture」をクリックし、ゲストOS用のパスワードを入力。
• パスワード：lec1902• 本資料の末尾にパスワードの一覧があります。
2019年 2月 14 - 15日

データベース講習会

25

v 【実習】 ゲストOS上での作業環境の確認
1. デスクトップ画面左上の「アプリケーション」をクリックし、「お気に入り」にカーソルを合わせ一覧から「端末」を選択。
2. 作業用ディレクトリの確認。
$ ls
DB Documents Music Public Videos
Desktop Downloads Pictures Templates$ ls lecture
DB ┬ catalogue ┬ nvss.dat├ sql└ pgc2003.dat└ result• catalogue：天文カタログを収めたディレクトリ
• sql：作成したsqlファイルを収めるディレクトリ
• result：出力結果を収めるディレクトリ

v ゲストOS上のファイルのUSBメモリへの移動方法
1. new-rXXにUSBメモリを挿す。
2. VMwareの上部メニューで「Virtual Machine」 > 「Removable Devices」 > 「USB」 >「connect」を選択し、ゲストOSにUSBメモリをマウント。
3. データを移動する。
4. 2と同手順でUSBメモリをアンマウント。
2019年 2月 14 - 15日

データベース講習会

26

4.3 PostgreSQLのインストールv 【実習】 本家ウェブサイトからPostgreSQLをダウンロードしCentOS 7にインストール
1. デスクトップ画面左上の「アプリケーション」をクリックし、「お気に入り」にカーソルを合わせ一覧から「Firefox」を選択し起動する。
2. PostgreSQLの本家ウェブサイトにアクセス。
https://www.postgresql.org
3. ページ上部の「Download」をクリック。
4. 「Binary packages」欄の「Red Hat」をクリック。
5. 「To use the PostgreSQL Yum Repository, follow these steps」：以降の項目を、以下のように選択。
1. Select version：10
2. Select platform：CentOS 7
3. Select architecture：x86_64
2019年 2月 14 - 15日

データベース講習会

27

n PostgreSQLダウンロードページ入力結果

2019年 2月 14 - 15日

データベース講習会

28

6. PostgreSQLのリポジトリを登録。
$ su
パスワード：tokken1902# yum install https://download.postgresql.org/pub/
repos/yum/10/redhat/rhel-7-x86_64/pgdgcentos10-10-2.noarch.rpm
7. PostgreSQLのパッケージをインストール。
# yum install postgresql10
postgresql10-server
postgresql10-devel
postgresql10-contrib
postgresql10-libs
•
•
•
•
•

postgresql10：psqlやpg_dumpなどのクライアントユーティリティ。
postgresql10-server：initdbやpg_ctlなどのサーバユーティリティ。
postgresql10-devel：pg_configなどの開発用モジュール。
postgresql10-contrib：pgbenchやpg_statementなどの追加モジュール。
postgresql10-libs：libpqなどのライブラリ群。

2019年 2月 14 - 15日

データベース講習会

29

8. データベースクラスタの作成。
作成方法# su postgres$ /usr/pgsql-10/bin/initdb --no-locale --encoding=UTF8-D /var/lib/pgsql/10/data$ exit­ データベースクラスタ：データベースを格納する領域。
­ initdb：データベースクラスタを作成するコマンド。
•••--no-locale：デフォルトではOSで設定されているロケール（ここではja_JP）が設定される。ロケールの設定を行うと、データベース内での文字列処理、日付や通貨の表示、メッセージの言語などを地域化することができる。地域化を行うとPostgreSQLの一部の機能が使えなくなるため、ロケールは設定しない。
--encording=UTF8：データベースのデフォルトの符号化方式を指定。
-D：データベースクラスタを作成するディレクトリを指定。

削除方法
$ su
パスワード：tokken1902# systemctl stop postgresql-10# su postgres$ rm –r /var/lib/pgsql/10/data•

データベースクラスタを削除することで、データベースを完全に初期化できる。

2019年 2月 14 - 15日

データベース講習会

30

n データベースクラスタ概略• 主要なディレクトリとファイルについて。
# ls -la /var/lib/pgsql/10/data
data/

base/

Ø 各データベース毎のデータが格納される。

global/Ø データベースクラスタ間で共有するテーブル（システムカタログ等）が格納される。

log/

Ø PostgreSQLサーバのログが保管される。

pg_xlog/Ø WALファイル（データの更新操作を記録したもの）が格納される。

pg_clog/

Ø データの更新操作の実行状態が記録される。

pg_hba.conf

Ø PostgreSQLサーバへのアクセス制御を行う
ためのファイル。

postgresql.conf Ø PostgreSQLサーバの制御ファイル。
2019年 2月 14 - 15日

データベース講習会

9. PostgreSQLの起動方法。
起動# systemctl start postgresql-10

31

状態の確認
# systemctl status postgresql-10
終了# systemctl stop postgresql-10
10. PostgreSQLの自動起動設定。
設定# systemctl enable postgresql-10
設定解除# systemctl disable postgresql-10•

CentOS 7では自動起動を設定しないと、OSを起動した時に自動的に
PostgreSQLが起動しない。

2019年 2月 14 - 15日

データベース講習会

4.4 CentOS 7以外のOSへのインストールについて

32v 以下のOSにはバイナリーパッケージが用意されており簡単にインストールが可能– BSD（FreeBSD、OpenBSD）– Linux（Red Hat系、Debian、Ubuntu、SuSE等）– macOS– Solaris– Windows

2019年 2月 14 - 15日

データベース講習会

5. PostgreSQLの初期設定

2019年 2月 14 - 15日

データベース講習会

34

5.1 概要v PostgreSQLインストール後に必要な初期設定を実施– ユーザとデータベースロールの設定作業– PostgreSQLのパフォーマンスに関わる設定作業– アクセス制御• 個人環境にPostgreSQLをインストールすることを想定。
• PostgreSQLへのリモートアクセスは禁止し、端末を直接操作してPostgreSQLを使用する環境を構築。

v 5章目次– 5.1 概要
– 5.2 Linuxユーザとデータベールロールの設定– 5.3 PostgreSQLサーバ制御ファイル（postgresql.conf）の設定– 5.4 アクセス制御ファイル（pg_hba.conf）の設定
2019年 2月 14 - 15日

データベース講習会

35

5.2 Linuxユーザとデータベースロールの設定

v PostgreSQLを管理するために使用するLinuxユーザとPostgreSQL
への接続時に使用するデータベースロールの設定をそれぞれ実施– Linuxユーザ「postgres」のパスワード設定• PostgreSQLサーバを管理するためのLinuxユーザ。
• PostgreSQLインストール時に作成される。
• 初期状態ではパスワードが設定されていない。

– データベースロール「postgres」のパスワード設定• データベースロール：データベース接続時に使うユーザのようなもの。
• データベースクラスタ作成時に、スーパーユーザ「postgres」が作成される。
• 初期状態ではパスワードが設定されていない。

– データベースロール「dbr」の作成• 一般権限のデータベースロール「dbr」を作成。
• データベースへの接続、テーブルの作成、問い合わせ等に利用。

v 5.2節目次– 5.2.1 Linuxユーザ「postgres」のパスワード設定– 5.2.2 データベースロール「postgres」のパスワード設定– 5.2.3 データベースロール「dbr」の作成
2019年 2月 14 - 15日

データベース講習会

5.2.1 Linuxユーザ「postgres」のパスワード設定36

v 【実習】 ユーザ「postgresql」のパスワードの設定
1. 「su」コマンドでルートユーザにスイッチ。
$ su
パスワード：tokken1902
2. 「passwd」コマンドでパスワードを設定。
# passwd postgres
ユーザー postgres のパスワードを変更。
新しいパスワード：pos1902
新しいパスワードを再入力して下さい：pos1902
passwd: すべての認証トークンが正しく更新できました。
# exit3. 設定確認$ su postgres
パスワード：pos1902
bash-4.2$ exit
2019年 2月 14 - 15日

データベース講習会

37

5.2.2 データベースロール「postgres」のパスワード設定
v 【実習】 ロール「postgresql」のパスワードの設定
1. 「psql」コマンドを使いロール「postgres」でデータベース「postgres」に接続。
$ psql -U postgres postgres• psql：PostgreSQLの標準ターミナル。7章で詳しく説明。
• データベース「postgres」：データベースクラスタ作成時に作成されるデータベース。
主にロール「postgres」でスーパーユーザ権限を要する操作を行うときに利用する。
2. 「ALTER ROLE」コマンドでパスワードを変更。
postgres=# ALTER ROLE postgres WITH PASSWORD ‘sql1902’;• ALTER ROLE：ロールの属性を変更するためのSQLコマンド。
3. 「\q」コマンドでpsqlを終了。
postgres=# \q• アクセス制御ファイル「pg_hba.conf」が初期状態ではデータベースへのログイ
ンを無条件に許可しているため、現時点ではデータベースにログインしてもパスワードを求められない。
2019年 2月 14 - 15日

データベース講習会

38

5.2.3 データベースロール「dbr」の作成
v 【実習】 ロール「dbr」の作成
1. 「psql」コマンドを使いロール「postgresql」でデータベース「postgres」に接続。
$ psql -U postgres• 接続先データベースを指定しない場合、接続ロールと同名のデータベースへの接続が行われる。
2. 「CREATE ROLE」コマンドでロールを作成。
postgres=# CREATE ROLE dbr LOGIN PASSWORD ’dbr1902’;• CREATE ROLE：ロールを新規作成するためのSQLコマンド。
3. ロールが作成できたか確認
postgres=# \du• \du：ロールの一覧を表示するpsqlのメタコマンド。
4. 「\q」コマンドでpsqlを終了。
postgres=# \q
2019年 2月 14 - 15日

データベース講習会

5.3 PostgreSQLサーバ制御ファイ
ル（postgresql.conf）の設定

39

v postgresql.conf– PostgreSQLサーバ制御用の設定パラメータを記述するファイル。
– 在り処：/var/lib/pgsql/10/data/postgresql.conf– ファイルの所有者：postgres（Linuxユーザ）$ su postgres
パスワード：pos1902
bash-4.2$ cat /var/lib/pgsql/10/data/postgresql.conf

v 設定項目­­­­­­ファイルの場所
接続と認証
資源の配分
WAL
レプリケーション
問い合わせ計画

2019年 2月 14 - 15日

­
­
­
­
­
­

エラー報告とログ取得
稼働統計情報Auto vacuum
クライアント接続デフォルト
ロック管理
バージョン/プラットフォーム互換性
データベース講習会

40

5.3.1 変更すべき項目
v 資源の配分（RESOURCE USAGE）– デフォルトのメモリ使用量は保守的であり、現在のマシンでは本来の性能を発揮できない。以下の項目は値を変更すべき。
shared_buffers• テーブルやインデックスのデータをキャッシュする領域。
• 膨大なデータから少数のデータを読み書きする処理が頻繁に行われる処理に影響。
• カーネルがキャッシュしたデータをPostgreSQLのshared_buffersが読み込むため、shared_buffersに大きな値を割り当てるとキャッシュするデータのほとんどが重複し、メモリの利用効率が悪化する。
• 経験的にshared_buffersのサイズは「実メモリの20~30%程度、もしくは頻繁にアクセスするテーブ
ルのデータがshared_buffersに載る程度」とされる。

work_mem• 検索時にテーブル結合や並び替え操作を行うための領域。
• 多くのレコードにアクセスする統計処理や、多くのレコードからデータを取り出す処理に影響。
• work_memの値は最大でも「(実メモリ – shared_buffers)/max_connections」程度とし、スワップが発生しないように設定する。

maintenance_work_mem• バキュームやインデックス作成等のメンテナンス時に使用される。

effective_cache_size• OSがディスクキャッシュにどの程度のメモリを利用できるのかを予測するための推定値。
• 実メモリの50%程度が良いとされる。
2019年 2月 14 - 15日

データベース講習会

41

5.3.2 postgresql.confの設定
v 【実習】 postgresql.confの設定
1. 「su」コマンドでLinuxユーザ「postgres」にスイッチ。
$ su postgres
パスワード：pos1902
2. テキストエディタで「postgresql.conf」を開く。
bash-4.2$ emacs /var/lib/pgsql/10/data/postgresql.conf
3. 「postgresql.conf」を以下のように編集（行頭の’#’は消す）。
shared_buffers = 2048MB
work_mem = 2048MB
maintenance_work_mem = 4096MB
effective_cache_size = 4096MB
4. ルートユーザでPostgreSQLを再起動。
bash-4.2$ exit
$ su
パスワード：tokken1902# systemctl restart postgresql-10# exit2019年 2月 14 - 15日

データベース講習会

42

5.4 アクセス制御ファイル（pg_hba.conf）の設定
v pg_hba.conf （hbaは「Host Based Authentication」の略）– PostgreSQLサーバへのアクセスを制御するためのファイル– 在り処：/var/lib/pgsql/10/data/pg_hba.conf– ファイルの所有者：postgres（Linuxユーザ）$ su postgres
パスワード：pos1902$ cat /var/lib/pgsql/10/data/pg_hba.conf

v 設定方針– ローカルからのアクセスは許可。
– リモートからのアクセスは不許可。
– アクセス時にパスワードを要求。
2019年 2月 14 - 15日

データベース講習会

43

5.4.1 pg_hba.confの書式
v 書式
タイプ

データベース

ロール

例１

local

all

all

例２

host

all

all

ネットワークアドレス

認証方式
trust

127.0.0.1/32

trust

– タイプ
• local：ローカルホストに対するアクセス制御。
• host：リモートホストに対するアクセス制御。

– データベース• データベース名：指定したデータベースのみアクセス可能。
• all：全てのデータベースにアクセス可能。

– ロール• ロール名：指定したロールのみアクセス可能。
• all：全てのロールがアクセス可能。

– ネットワークアドレス• 接続元のネットワークアドレスとネットマスクを記述。
• タイプが「local」の場合は記述しない。

– 認証方式• trust：無条件に接続を許可。
• reject：無条件に接続を拒否。
• md5：md5で暗号化されたパスワードによる認証。
2019年 2月 14 - 15日

データベース講習会

44

5.4.2 pg_hba.confの設定
v 【実習】 pg_hba.confの設定
1. 「su」コマンドでLinuxユーザ「postgres」にスイッチ。
$ su postgres
パスワード：pos1902
2. テキストエディタで「pg_hba.conf」を開く。
bash-4.2$ emacs /var/lib/pgsql/10/data/pg_hba.conf
3. 「pg_hba.conf」を以下のように編集。
trust を md5 に置換
4. ルートユーザでPostgreSQLを再起動。
bash-4.2$ exit
$ su
パスワード：tokken1902# systemctl restart postgresql-10# exit2019年 2月 14 - 15日

データベース講習会

45

5.4.3 pg_hba.confの設定確認
v 【実習】 ロール「postgres」の接続確認$ psql -U postgres
ユーザー postgres のパスワード：sql1902
postgres=# \qv 【実習】 ロール「dbr」の接続確認$ psql -U dbr postgres
ユーザー dbr のパスワード：dbr1902
postgres=> \q• データベース名「postgres」を省略した場合はデータベース「dbr」への接続が行われるが、データベース「dbr」は存在しないため接続に失敗する。

2019年 2月 14 - 15日

データベース講習会

6. データベースの作成

2019年 2月 14 - 15日

データベース講習会

47

6.1 概要v データベースの作成とデータの登録を実施– 天体カタログ「PGC2003」と「NVSS」をデータベース化。
– 外付けHDD上にデータを収めることを想定し、データベースクラス
タ外に作成したテーブル空間にデータを保存。

v データベースの構造
データベースクラスタ
データベース1
テーブル1

データベース2

–
–

テーブル2

インデックス1 インデックス2

データベース3––
テーブル空間1
2019年 2月 14 - 15日

テーブル空間2

データベースクラスタ• データベースの全情報を保存する領域。
データベース• 複数のオブジェクト（テーブル、ビュー、インデック
ス）を格納する箱であり、物理的にはオブジェクト
ファイル等を収めるディレクトリ。
• データベースを跨いでのデータのアクセスは不可。
テーブル• データを収める表であり、物理的にはテーブル
データを収める1つのファイル。
テーブル空間• データベースディレクトリやテーブルファイル等を保存する物理領域。
データベース講習会

48

v 作成するデータベースの構造
テーブル空間「lecspace」
データベース「lecdb」
テーブル「pgc2003」

テーブル「nvss」

v 6章目次– 6.1 概要
– 6.2 テーブル空間の作成– 6.3 データベースの作成– 6.4 テーブルの作成– 6.5 インデックスの作成

2019年 2月 14 - 15日

データベース講習会

49

6.2 テーブル空間の作成
v CREATE TABLESPACE文の書式
CREATE TABLESPACE テーブル空間名
OWNER ロール名
LOCATION ‘テーブル空間を作成する場所のパス’;– CREATE TABLESPACE• テーブル空間を作成するためのコマンド。実行にはスーパーユーザ権限が必要。
• OWNER句でテーブル空間を所有するロールを指定可能。

v 【実習】 テーブル空間の作成– 「/home/data」にテーブル空間「lecspace」を作成。
– テーブル空間「lecspace」の所有者としてロール「dbr」を指定。
1. dataディレクトリを作成。
$ su
パスワード：tokken1902# mkdir /home/data• テーブル空間用ディレクトリは、Linuxユーザ「postgres」がアクセスできる場所に作成しなければならない。
2019年 2月 14 - 15日

データベース講習会

50

2. dataディレクトリの所有者の変更。
# chown postgres:postgres /home/data# exit• テーブル空間用ディレクトリの所有者は、Linuxユーザ「postgres」である必要がある。
3. 「psql」コマンドを使いロール「postgres」でデータベース「postgres」に接続。
$ psql -U postgres
パスワード：sql1902• テーブル空間の作成にはスーパーユーザ権限を持つロールが必要。
4. テーブル空間を作成。
postgres=# CREATE TABLESPACE lecspace
OWNER dbr
LOCATION ‘/home/data’;
5. 作成したテーブル空間を確認。
postgres=# \db

v テーブル空間の削除方法
postgres=# DROP TABLESPACE lecspace;• テーブル空間の削除はスーパーユーザ権限を持つロールか、テーブルを所有するロールのみ実行可能。
• テーブル空間内にデータベースオブジェクトが存在する場合は削除できない。
2019年 2月 14 - 15日

データベース講習会

51

6.3 データベースの作成
v CREATE DATABASE文の書式
CREATE DATABASE データベース名
OWNER ロール名
TABLESPACE テーブル空間名;– CREATE DATABASE• データベースを作成するためのコマンド。実行にはデータベース作成権限を持つロールが必要。
• テーブル空間を指定することで、テーブル空間として指定されているディレクトリ配下に
データベースディレクトリが作成される。

v 【実習】 データベースの作成– データベース「lecdb」をテーブル空間「lecspace」内に作成。
– データベース「lecdb」の所有者としてロール「dbr」を指定。
1. 「psql」コマンドを使いロール「postgres」でデータベース「postgres」に接続。
$ psql -U postgres
パスワード：sql1902• 「CREATE DATABASE」の実行にはデータベース作成権限を持つロールが必要。
2019年 2月 14 - 15日

データベース講習会

2. データベースを作成。

52

postgres=# CREATE DATABASE lecdb
OWNER dbr
TABLESPACE lecspace;

3. 作成したデータベースを確認。
postgres=# \l• 「/home/data」以下も確認してみましょう。

v データベースの削除方法
postgres=# DROP DATABASE lecdb;• データベースの削除はスーパーユーザ権限を持つロールか、データベースを所有するロールのみ実行可能。

2019年 2月 14 - 15日

データベース講習会

53

6.4 テーブルの作成
v テーブルの作成を実施– 天体カタログ「PGC2003」を収めるテーブル「pgc2003」の作成を実施。
– テーブル「pgc2003」へのデータの登録を実施。

v 6.4節目次– 6.4.1 テーブルの構造– 6.4.2 PostgreSQLがサポートするデータ型– 6.4.3 PGC2003– 6.4.4 テーブルの作成– 6.4.5 データの登録

2019年 2月 14 - 15日

データベース講習会

54

6.4.1 テーブルの構造
v RDBのテーブルはカラムとレコードから構成される– カラム• 表における列に相当。
• カラム毎にデータ型が定義され、データ型と異なるデータは入力できない。
• 制約を定義したカラムには、制約に違反したデータは入力できない。

– レコード• 表における行に相当。
• データベースにおけるデータそのもの。
カラム

レコード

id

ra

dc

mag

1

83.625+22.0168.4

2

323.375-0.8166.3

3

205.550+31.716

6.3

テーブルを作成するということは、カラムを定義すること。
2019年 2月 14 - 15日

データベース講習会

6.4.2 PostgreSQLがサポートする
データ型

55

v PostgreSQLは以下のデータ型をサポート– 数値データ型– 文字データ型– 日付・時刻データ型– ブーリアン型– 幾何データ型– ネットワークアドレスデータ型– ビット列データ型– 通貨データ型– 疑似データ型– バイナリ列データ型– 文字列検索型– XML型– Range型
数値データ型、文字データ型、日付・時刻データ型を紹介。
2019年 2月 14 - 15日

データベース講習会

56v 数値データ型
データ型名

格納サイズ

説明

範囲

SMALLINT

2バイト

半精度整数型-32768から+32767

INTEGER

4バイト

単精度整数型-2147483648から+2147483647

BIGINT

8バイト

倍精度整数型-9223372036854775808から+9223372036854775807

REAL

4バイト

単精度浮動小数点数型

6桁精度

DOUBLE PRECISION 8バイト

倍精度浮動小数点数型

15桁精度

NUMERIC、
DECIMAL

任意精度固定小数点数型

小数点より上は131072桁まで、小数点より下は16383桁まで

可変長– REAL型とDOUBLE PRECISION型• 浮動小数点数型であるため、丸め誤差が発生する。
• 正確な記録と計算が必要な時はNUMERIC型かDECIMAL型を使用する。

– NUMERIC型とDECIMAL型• NUMERIC型とDECIMAL型は全く同じ。
• 精度（全体の桁数）と小数点以下の桁数を指定でき、正確な計算が可能。
• 整数型、浮動小数点数型に対し、計算が非常に遅い。

2019年 2月 14 - 15日

データベース講習会

57

v 文字データ型
データ型名
CHARACTER(N)、CHAR(N)

格納文字数
N文字

説明
N文字の固定長文字列型。

CHARACTER VARYING(N)、VARCHAR(N) N文字以下

最大N文字の可変長文字列型。

TEXT

文字数制限の無い可変長文字列型。

無制限– CHARACTER(N)型、CHAR(N)型• 最大n文字までの文字列を格納できる。
• 入力文字数がN文字に満たない場合は、残りの部分が空白文字で埋められる。
• 使うメリットはない。

– CHARACTER VARYING(N)型、VARCHAR(N）型• 最大n文字までの文字列を格納できる。
• PostgreSQL内部ではTEXT型と区別がなく、データ入力時に文字長を確認する点だけが異なる。

– TEXT型• 長さに制限なく文字列を扱える（ただし、PostgreSQLが1行に収めることのできる最大バイト数は1ギガバイトまで）。
• TEXT型は標準SQLにはないが、多くのRDBMSがサポートしている。

2019年 2月 14 - 15日

データベース講習会

58

v 日付・時刻データ型
データ型名

格納サイズ

説明

精度

TIMESTAMP [(P)] [WITHOUT TIME ZONE] 8バイト

日付と時刻

1マイクロ秒TIMESTAMP [(P)] WITH TIME ZONE

8バイト

日付と時刻（時間帯付） 1マイクロ秒

DATE

4バイト

日付

1日

TIME [(P)] [WITHOUT TIME ZONE]

8バイト

時刻

1マイクロ秒

TIME [(P)] WITH TIME ZONE

12バイト

時刻（時間帯付）

1マイクロ秒

INTERVAL [FIELDS] [(P)]

16バイト

時間間隔

1マイクロ秒※(P)：Pは0から6までの整数。秒単位以下の表示精度を指定。

– WITHOUT TIME ZONE と WITH TIME ZONE• WITHOUT TIME ZONEは入力された値をそのまま格納。
• WITH TIME ZONEは入力された値をでUTCに変換し格納。
– ’2019-01-01 00:00:00’という値を入力すると、この時刻をローカル時間（日本時間）と解釈してUTCに変換し、’2018-12-31 15:00:00’として値を格納する。
– 出力時は格納されているUTC時刻を使用しているサーバのローカル時刻に変換し表示。

– 時刻の入力形式• ‘2019-01-01 00:00:00’ でOK。

2019年 2月 14 - 15日

データベース講習会

59

6.4.3 PGC2003
v Principal Galaxy Catalog 2003（Paturel+2003）– HYPERLEDAデータベースの基となっているカタログ。
– 18 B-mag以上の銀河を約100万収録。
– 各銀河の座標、直径、楕円率、位置角、50個のカタログにおける名称を掲載。

v 講習会用PGC2003– 在り処：/home/lecture/DB/catalogue/pgc2003.dat– データベース化のため、以下の加工を行っている。
• 赤経を時角から度角に変換。
• 赤緯を度分秒角から度角に変換。
• 直径、楕円率、位置角が不明の場合の値「9.99」及び「999」を空文字に置換。
• 区切り文字を’,’に変更。
2019年 2月 14 - 15日

データベース講習会

60

v 講習会用PGC2003諸元
列名

単位

id

データ型

Null値

説明

TEXT

無

PGC番号（PGC0000001-PGC3099300）。

radegreeDOUBLEPRECISION

無赤経（J2000）。

dc

degreeDOUBLEPRECISION

無赤緯（J2000）。

otype

TEXT

無

G: galaxy; M: multiple system; GM: galaxy in multiple
system.

mtype

TEXT

有

LEDA（Lyon-Meudon Extragalactic Database）における暫定的な形態分類。

logd250.1arcmin

REAL

有

天体の直径。

e_logd25

0.1arcmin

REAL

有

天体の直径の誤差。

logr25REAL

有

天体の楕円率。

e_logr25

REAL

有天体の楕円率の誤差。

pa

degreeREAL

有天体の位置角。

e_pa

degreeREAL

有天体の位置角の誤差。

o_anames

SMALLINT

無他のカタログにおける天体の別名の数。

anamesTEXT

有他のカタログにおける天体の別名。

• 「dec」はデータ型「decimal」の略なので使わない。
2019年 2月 14 - 15日

データベース講習会

61

6.4.4 テーブルの作成
v CREATE TABLE文の書式
CREATE TABLE テーブル名 (
カラム名1
データ型
[NOT NULL],
カラム名2
データ型
[NOT NULL],⁝[CONSTRAINT 主キー名 PRIMARY KEY (カラム名)]);–

カラム名に大文字と「+*/-.’:」は使用しない••–

制約（CONSTRAINT）••–

テーブル内のレコードを一意に識別（テーブルの行を特定）できるカラムに対し設定できる制約。
テーブル毎に1カラムのみ設定できる。
主キー制約を設定したカラムは、各レコードの値が一意でありかつNULL値でないことが必要となる。
主キー制約を設定したカラムにはインデックスと非NULL制約が自動的に作成・設定される。

非NULL制約（NOT NULL）•–

列及びテーブルに対して課すことのできる制限。
制約に違反したデータを格納しようとするとエラーを返し、データの誤入力を防止できる。

主キー制約（PRIMARY KEY）
•
•
•
•

–

大文字を入力しても小文字として正規化される。
二重引用符「””」で囲めば大文字も記号も使えるが、二重引用符付きのカラム名が出来上がる。

NULL値を取らないカラムに対し設定できる制約。

一意性制約（UNIQUE）• 各レコードの値が一意であるカラムに対し設定できる制約（複数のNULL値は重複とはみなされない）。
• 一意性制約を設定したカラムにはインデックスが自動的に作成される。
2019年 2月 14 - 15日
データベース講習会

62v 【実習】 テーブルの作成– 天体カタログ「PGC2003」を収めるためのテーブルをデータベース「lecdb」に作成。
– 「CREATE TABLE」文を記述したファイルを作成し、データベース内で作成したファイルを読み込むことでSQLを実行する。
1. SQLを記述したファイルを「/home/lecture/DB/sql」以下に作成する。
$ emacs /home/lecture/DB/sql/ctPgc2003.sql
2. テーブル作成用のSQLの記述。
CREATE TABLE pgc2003 (id
TEXT
NOT NULL,ra
DOUBLE PRECISIONNOT NULL,
dc
DOUBLE PRECISIONNOT NULL,
otype
TEXT
NOT NULL,
mtype
TEXT,
logd25REAL,
e_logd25
REAL,
logr25REAL,
e_logr25
REAL,
pa
REAL,
e_pa
REAL,
o_anames
SMALLINTNOT NULL,
anamesTEXT,CONSTRAINT pgc2003_pkey PRIMARY KEY (id));
2019年 2月 14 - 15日

データベース講習会

3. 「psql」コマンドを使い、ロール「dbr」でデータベース「lecdb」に接続。

63$ psql -U dbr lecdb
パスワード：dbr1902
4. 作成したファイル「ctPgc2003.sql」を実行する。
lecdb=> \i ’/home/lecture/DB/sql/ctPgc2003.sql’
5. 作成したテーブルの確認。
lecdb=> \dlecdb=> \d pgc2003

v テーブルの削除方法
lecdb=> DROP TABLE pgc2003;• テーブルの削除はスーパーユーザ権限を持つロールか、テーブルを所有するロー
ルのみ実行可能。

2019年 2月 14 - 15日

データベース講習会

64

6.4.5 データの登録
v \copy FROMコマンドの書式\copy テーブル名 FROM ‘ファイルのパス’ DELIMITER ‘区切り文字’
NULL AS ‘NULL値とする文字’– \copy と COPY
• 「COPY 」という「\copy」と同じ働きをするSQL文が存在する。
• 「\copy」がクライアント側のファイルを操作するのに対し、「COPY」はサーバ側のファイルを操作する。サーバ側のファイルはLinuxユーザ「postgres」が読み込み可能でなければならない且つ絶対パスで記述しなければならない。

v 【実習】 データの登録
1. 「psql」コマンドを使い、ロール「dbr」でデータベース「lecdb」に接続。
$ psql -U dbr lecdb
パスワード：dbr1902
2. 「/home/lecture/DB/catalogue/pgc2003.dat」をテーブル「pgc2003」にコピー。
lecdb=> \copy pgc2003 FROM ‘/home/lecture/DB/catalogue/
pgc2003.dat’ DELIMITER ‘,’ NULL AS ‘’• 983261行コピーされる
2019年 2月 14 - 15日

データベース講習会

3. 「VACUUM ANALYZE」を実行する。

65

lecdb=> VACUUM ANALYZE pgc2003;• VACUUM ANALYZE：不要領域の回収と統計情報の更新を行うコマンド。
• PostgreSQLではテーブルからレコードの削除等を行っても、VACUUMを実行す
るまではテーブルからデータが物理的に削除されない。VACUUM ANALYZEを実行することで不要な領域の回収と統計情報の再計算を行い、回収した領域を再利用できるようにする。
• テーブルの作成や更新操作を行ったら必ず実行する。
4. テーブルの確認。
lecdb=> SELECT * FROM pgc2003 LIMIT 10;v データの削除方法
lecdb=> DELETE FROM pgc2003;• データの削除はスーパーユーザ権限を持つロールか、テーブルを所有するロールのみ実行可能。

2019年 2月 14 - 15日

データベース講習会

66

6.5 インデックスの作成
v インデックスの作成を実施– RDBMSにおけるインデックスの役割りと仕組みを簡単に紹介。
– テーブル「pgc2003」のカラムにインデックスを設定。
– インデックスがない場合とある場合の検索速度の比較を実施。

v 6.5節目次– 6.5.1 インデックスの仕組み– 6.5.2 インデックスの作成と検索速度の比較

2019年 2月 14 - 15日

データベース講習会

67

6.5.1 インデックスの仕組み
v インデックス（索引）– カラムデータを予めツリー構造として展開し、目的のデータまでの検索手順を最小化するデータ構造とその仕組み。インデックスを作成することでRDBMSは本来の性能を発揮し、インデックスがない場合と比べ格段に高速の検索を行える。
– 主キーを設定したカラムには自動的にインデックスが作成されるが、その他のカラムは必要に応じて作成する必要がある。
– PostgreSQLではデフォルトでB-TREEインデックスという方式が使用される。

2019年 2月 14 - 15日

データベース講習会

68v B-TREEインデックス– 平衡探索木型のデータ構造を持つインデックスであり、ある順番でソート可能なデータに対する等価性や範囲の問い合
わせを扱うことができる。
– 例：1から17までのデータの中から16を検索する場合。
B-TREEインデックススキャン

シーケンシャルスキャン
1
2
3
⁝
15
16
17

9
16回の計算

1

2

3

6

4

5

7

3回の計算

12 15

8

13 14

10 11

16 17

• インデックススキャンが使用できない場合はシーケンシャルスキャンが行われる。
2019年 2月 14 - 15日

データベース講習会

6.5.2 インデックスの作成と検索速度の比較

69v CREATE INDEX文の書式
CREATE INDEX インデックス名 ON テーブル名（カラム名）;– CREATE INDEX• 指定したカラムにインデックスを設定するためのSQL文。
• インデックス名を省略すると自動でインデックス名が割り当てられる。

v 【実習】 インデックスがない場合の検索時間の測定– テーブル「pgc2003」から赤緯±5度の領域内にある天体を検索し、その検索時間を測定。
1. 「psql」コマンドを使い、ロール「dbr」でデータベース「lecdb」に接続。
$ psql -U dbr lecdb
パスワード：dbr1902
2. 「\timing」コマンドを実行し、実行時間が計測されるようにする。
lecdb=> \timing
タイミングは on です。
2019年 2月 14 - 15日

データベース講習会

70

3. 以下の検索を3回以上行い、最後の3回の実行時間の中間値をメモする。
lecdb=> SELECT count(*)
FROM pgc2003
WHERE dc BETWEEN -5 and 5;v 【実習】 インデックスの設定– テーブル「pgc2003」のカラム「ra」と「dc」にインデックスを設定。
1. インデックスを作成。
lecdb=> CREATE INDEX ON pgc2003(ra);lecdb=> CREATE INDEX ON pgc2003(dc);
2. インデックスの確認。
lecdb=> \d pgc2003
3. 「VACUUM ANALYZE」の実行。
lecdb=> VACUUM ANALYZE pgc2003;• 必ず実行すること。実行しないと作成したインデックスが使われないことがある。

2019年 2月 14 - 15日

データベース講習会

4. 以下の検索を3回以上行い、最後の3回の実行時間の中間値をメモする。

71

lecdb=> SELECT count(*)
FROM pgc2003
WHERE dc BETWEEN -5 and 5;
lecdb=> \q
5. 両者の実行時間を比較する。

v 【実習】 インデックスの追加作成– テーブル「pgc2003」の残りのカラムにインデックスを設定。
lecdb=> CREATE INDEX ON pgc2003(hoge);• P.60のPGC2003諸元を参照してインデックスを設定しましょう。
• インデックスを設定するとデータの更新処理が遅くなるため、本来は検索に使用するカラムのみに設定するのが正しい。

2019年 2月 14 - 15日

データベース講習会

72

演習 NVSSカタログのDB化
v The NRAO VLA Sky Survey (Condon+1998)– Very Large Array (VLA）を使って、赤緯-40度以北の天域を周波数1.4GHzでサーベイした結果をまとめたカタログ。
– 2.5 mJy 以上の電波源を約177万の収録。
– 各電波源の座標、フラックス密度、長軸長、短軸長、位置角、偏波強度等を掲載。

v 講習会用NVSSカタログ– 在り処：/home/lecture/DB/catalogue/nvss.dat– データベース化のため、以下の加工を行っている。
• 赤経を時角から度角に変換。
• 赤緯を度分秒角から度角に変換。
• Field列、Xpos列、Ypos列の削除。
– サーベイデータのイメージ番号と、天体のXYピクセル座標。

• 区切り文字を’,’に変更。
2019年 2月 14 - 15日

データベース講習会

73

v 講習会用NVSSカタログ諸元
列名

単位

id

データ型

Null値

説明

TEXT

無

NVSSカタログにおける電波源名（HHMMSS+/-DDMMSS）。

ra

degreeDOUBLE PRECISION

無赤経（J2000）。

dc

degreeDOUBLE PRECISION

無赤緯（J2000）。

e_radegreeDOUBLE PRECISION

無赤経の誤差。

e_dc

degreeDOUBLE PRECISION

無赤緯の誤差。

flux

mJy

DOUBLE PRECISION

無電波源の1.4GHz帯の積分フラックス密度（最大値：858423.0）。

e_fluxmJy

DOUBLE PRECISION

無電波源の1.4GHz帯の積分フラックス密度の誤差（最大値：32372.3）。

TEXT

有

電波源の長軸長が上限値であることを示す記号”<“。

REAL

無

電波源の長軸長。

TEXT

有

電波源の短軸長が上限値で有ることを示す記号”<“。

l_majaxis
majaxisarcsecl_minaxis
minaxisarcsecREAL

無電波源の短軸長。

pa

deg

REAL

有

電波源の長軸の位置角。

e_majaxis

arcsecREAL

有電波源の長軸長の誤差。

e_minaxis

arcsecREAL

有電波源の短軸長の誤差。

e_pa

deg

REAL

有

電波源の長軸の位置角の誤差。

TEXT

有

電波源のフィッティング時の残差が大きいことを[PS* ]で示す。ピークフラックス密度の残差が大きい時”S”、積分フラックス密度の残差が大きい時”P”を表示。

f_resflux
resflux

mJy/beamREAL

有電波源のフィッティング時の輝度の残差のピーク値（最大値：9999）。

polflux

mJy

REAL

有

電波源の1.4GHzでの（面積）積分直線偏波フラックス密度（最大値：999.9）。

polpa

degreeREAL

有偏波角（-90度から90度）。

e_polflux

mJy

REAL

有

電波源の1.4GHz帯の積分直線偏波フラックス密度の誤差。

e_polpa

degreeREAL

有偏波角の誤差。

2019年 2月 14 - 15日

データベース講習会

74

v 【演習】 NVSSカタログを収めるテーブル「nvss」を作成
しデータを登録せよ。
1. データベース「lecdb」上にNVSSカタログを格納するためのテーブル「nvss」を作成せよ。
•

P.62参照。

2. テーブル「nvss」にNVSSカタログのデータをコピーせよ。
•

P.64参照。

3. テーブル「nvss」の全カラムにインデックスを設定せよ。
•

2019年 2月 14 - 15日

P70参照。

データベース講習会

75v 【解答例】 NVSSカタログを収めるテーブル「nvss」を作
成しカタログを登録せよ。
1. データベース「lecdb」上にNVSSカタログを格納するためのテーブル「nvss」を作成せよ。
lecdb=> CREATE TABLE nvss (id
TEXT
NOT NULL,ra
DOUBLE PRECISIONNOT NULL,
dc
DOUBLE PRECISIONNOT NULL,
e_ra
DOUBLE PRECISIONNOT NULL,
e_dc
DOUBLE PRECISIONNOT NULL,
flux
DOUBLE PRECISIONNOT NULL,
e_fluxDOUBLE PRECISIONNOT NULL,
l_majaxis TEXT,
majaxisREAL
NOT NULL,
l_minaxis TEXT,
minaxisREAL
NOT NULL,
pa
REAL,
e_majaxis REAL,
e_minaxis REAL,
e_pa
REAL,
f_resflux TEXT,
resflux
REAL,
polflux
REAL,
polpa
REAL,
e_polflux REAL,
e_polpa
REAL,
CONSTRAINT nvss_pkey PRIMARY KEY (id));
2019年 2月 14 - 15日

データベース講習会

76

2. テーブル「nvss」にNVSSカタログのデータをコピーせよ。
lecdb=> \copy nvss FROM ‘/home/lecture/DB/catalogue/
nvss.dat’ DELIMITER ‘,’ NULL AS ‘’• 1773484行コピーされる

3. テーブル「nvss」の全カラムにインデックスを設定せよ。
•

2019年 2月 14 - 15日

解答略

データベース講習会

77v 【コラム】 PostgreSQLにおけるデータの操作コマンド– PostgreSQLには「\copy (COPY) FROM」、「INSERT」、「UPDATE」という3つのデータ操作コマンドが存在する。
– \copy (COPY) FROM
• ファイルからデータをテーブルにコピーするコマンド。
• 大量のデータを高速にデータベース化するのに向いている。
• ファイルからしかコピーできない。

– INSERT• テーブルにデータを追加するためのコマンド。
• 既存のテーブルのデータを追加することができる。
• 1行ずつ処理を行うため遅い。

– UPDATE• テーブルのデータを更新するためのコマンド。

2019年 2月 14 - 15日

データベース講習会

7. psqlの使用方法

2019年 2月 14 - 15日

データベース講習会

79

7.1 概要v psqlの使い方を紹介– psqlはPostgreSQLの標準ターミナルであり、入力された
SQLをPostgreSQLサーバに送信し、その実行結果を受け取り表示する。
– 「psql」コマンドの基本的な使い方やSQLコマンドの実行方法、
psql自身が実行するメタコマンドを紹介。

v 7章目次– 7.1 概要
– 7.2 「psql」コマンドの基本的な使い方– 7.3 SQLコマンド– 7.4 メタコマンド– 7.5 ログインパスワードの入力省略設定
2019年 2月 14 - 15日

データベース講習会

80

7.2 「psql」コマンドの基本的な使い方
v 「psql」コマンドの書式$ psql [オプション] データベース名– 接続関連オプション• -h ホスト名：接続先PostgreSQLサーバのホスト名かIPアドレスを指定。
• -p ポート番号：接続先PostgreSQLサーバのポート番号を指定。デフォルトは「5432」。
• -U データベースロール名：「データベースロール名」でデータベースに接続。

– 問い合わせ結果の出力形式変更用オプション••••-A：位置揃えなしの出力モードに切り替える。
-F 区切り文字：フィールドの区切り文字を指定する。「-A -F ,」でCSV形式で出力。
-H：HTML形式で出力。
-P format=latex：LaTeX形式で出力。

– コマンドラインからのSQL実行時に使用するオプション••••-c “SQL”：SQLを実行しpsqlを終了する。シェルスクリプト等で有用。
-f “バッチファイル”：バッチファイルに記述されたSQLを実行しpsqlを終了する。
-o “ファイル名”：SQLの実行結果をファイルに出力する。標準出力には結果を返さない。
-L “ファイル名”：SQLの実行結果をファイルと標準出力の両方に出力する。

2019年 2月 14 - 15日

データベース講習会

81

v 「psql」コマンドの実行例– ホスト名とポート番号を指定した接続。
$ psql -h localhost -p 5432 -U dbr lecdb
パスワード：dbr1902
lecdb=> SELECT * FROM pgc2003 LIMIT 10;
lecdb=> \q– 問い合わせ結果の出力形式の変更。
$ psql -A -F , -U dbr lecdb$ psql -H -U dbr lecdb$ psql -P format=latex -U dbr lecdb
lecdb=> SELECT * FROM pgc2003 LIMIT 10;
lecdb=> \q– コマンドラインからSQLを実行しファイルに出力。
$ psql -U dbr lecdb -o ‘/home/lecture/DB/result/result.txt’ -c “SELECT * FROM pgc2003 limit 10;”$ cat /home/lecture/DB/result/result.txt
2019年 2月 14 - 15日

データベース講習会

82

7.3 SQLコマンドv psqlにおけるSQLコマンドの書式– SQLはひと続きのコマンドからなり、セミコロン「;」がコマンドの終端となる。
– SQLでは文字列を除き大文字と小文字は区別されない。
• テーブル名やカラム名を二重引用符「”」で囲めばそれぞれを大文字まじりで作成及び指定できるが推奨しない。

– 文字列は単一引用符「’」で囲んで入力する。
– 単一行のコメントアウトは二重ダッシュ記号「--」で行える。
– 複数行のコメントアウトは「/* コメント */」で行える。
lecdb=> SELECT ‘Hello world!’; --文字列入力の例?column?
-------------Hello world!
• 「SELECT」文はテーブルへの問い合わせを行うためのSQLコマンド。詳しい使い方は8章で説明。
2019年 2月 14 - 15日

データベース講習会

83

7.4 メタコマンド
v メタコマンド– psql自身が実行するコマンドのこと。
– メタコマンドはバックスラッシュから始まり、改行がコマンドの終端となる（SQLコマンドとは異なることに注意）。
– ここではよく使われるメタコマンドを紹介。

v バッチファイル実行用メタコマンド
メタコマンド説明\i “ファイル名”

バッチファイルに記述されたSQLを実行。

\g “ファイル名”「SQL文 \g ”ファイル名”」でSQLの実行結果をファイルに出力。

\o “ファイル名”

以降のSQLの実行結果をファイルに出力。「/o」で書き込みを終了。

2019年 2月 14 - 15日

データベース講習会

84

v 情報表示用メタコマンド
メタコマンド説明\db

テーブル空間の一覧を表示。

\du

データベースロールの一覧を表示。

\l

データベースの一覧を表示。

\d

テーブルとビューの一覧を表示。

\d テーブル名

テーブルの情報を表示。

\dS

全テーブルとビューの一覧を表示。

\di

インデックスの一覧を表示。

\df

ユーザが作成した関数の一覧を表示。

\dfS

全関数の一覧を表示。

\dT

ユーザが作成したデータ型の一覧を表示。

\dTS

全データ型の一覧を表示• 「\db+」等、“+”をつけると詳細な情報を表示できる。

2019年 2月 14 - 15日

データベース講習会

85v 表示変更用メタコマンド
メタコマンド説明\x

拡張形式モードの切り替える。

\t

結果のみ表示と完全表示の切り替える。

\a

位置揃えなしの出力モードに切り替える。

\f ‘区切り文字’

カラムの区切り文字を設定する。\aを設定しないと無効。

\H

HTML形式の出力モードに切り替える。

\pset format ‘latex’

LaTeX形式の出力モードに切り替える。

v その他のメタコマンド
メタコマンド説明\h

ヘルプが存在するSQLコマンドの一覧を表示。

\h コマンド名

SQLコマンドの説明を表示。

\s

入力履歴を表示する。

\timing

SQLの実行時間計測の有無を切り替える。

\cd ‘ディレクトリ名’

現在の作業ディレクトリを変更する。

\! UNIXコマンド

UNIXコマンドを実行する。例：\! ls

2019年 2月 14 - 15日

データベース講習会

86

7.5 ログインパスワードの入力省略設定
v 「psql」コマンドでPostgreSQLサーバに接続した際に求められるパスワードの入力省略設定を実施– データベース「lecdb」への接続情報を記述したファイル「.pgpass」をホームディレクトリに作成。

v 「.pgpass」の書式
ホスト名：ポート番号：データベース名：ロール名：パスワード– アクセス権は「600」に設定する。
• 「600」に設定しないと「.pgpass」ファイルが無視される。

– “#”でコメントアウト可能。

2019年 2月 14 - 15日

データベース講習会

87v 【実習】 パスワードの省略設定
1. ホームディレクトリに「.pgpass」を作成。
$ cd$ emacs .pgpass
localhost:5432:lecdb:dbr:dbr1902
2. アクセス権の変更。
$ chmod 600 .pgpass
3. 接続の確認。
$ psql -U dbr lecdb
psql (10.5)“help”でヘルプを表示します。
lecdb=>

2019年 2月 14 - 15日

データベース講習会

8. テーブルへの問い合わせ2019年 2月 14 - 15日

データベース講習会

89

8.1 概要v SELECT文を使ってPostgreSQLサーバ上のテーブルへの問い合わせを行う方法を紹介
v 8章目次– 8.1 概要
– 8.2 SELECT文の基本形– 8.3 検索条件の指定– 8.4 カラムに対する演算– 8.5 副問い合わせ– 8.6 テーブルの結合– 8.7 問い合わせ結果の結合– 8.8 遅くならないWHERE句の書き方
2019年 2月 14 - 15日

データベース講習会

90

8.2 SELECT文の基本形
v SELECT文– SELECT文はテーブルから行を検索し、結果を表示するSQL。
– SELECT文には様々な「句」が存在し、「句」を組み合わせることで様々な検索を行うことができる。

v 8.2節目次– 8.2.1 基本形– 8.2.2 検索結果の出力件数の指定– 8.2.3 検索結果の並び替え– 8.2.4 カラム・テーブルの別名の定義

2019年 2月 14 - 15日

データベース講習会

91

8.2.1 基本形
v SELECT文の書式
SELECT カラム名
FROM テーブル名;– カラムの指定• 複数のカラムを指定する場合はカラム名とカラム名をカンマ「,」で区切る。
• 全カラムを指定する場合はワイルドカード「*」を指定する。

– 検索結果の操作• 「Enter」キーで1行送る。
• 「Space」キーでページ単位で送る。
• 「q」キーで検索結果の表示を終了。

v 実行例

本講習会ではSQLコマンドは
大文字、カラム名とテーブル名
は小文字で書きます。入力時
は小文字でok。

– テーブル「pgc2003」からカラム「id」、「ra」、「dc」を検索。
lecdb=> SELECT id,ra,dc
FROM pgc2003;– テーブル「pgc2003」の全列を検索。
lecdb=> SELECT *FROM pgc2003;2019年 2月 14 - 15日

データベース講習会

92

8.2.2 検索結果の出力件数の指定
v LIMIT句の書式
SELECT カラム名
FROM テーブル名
LIMIT 出力件数 ;– LIMIT句• 指定された件数のみ表示する。
• SELECT文の末尾に記述しなければならない。

v 実行例– 検索結果の先頭10件のみを出力。
lecdb=> SELECT id,ra,dc
FROM pgc2003
LIMIT 10;

2019年 2月 14 - 15日

データベース講習会

93

8.2.3 検索結果の並び替え
v ORDER BY句の書式
SELECT カラム名
FROM テーブル名
ORDER BY カラム名 [ASC|DESC];– 昇順で並び替え• ASC句を使うと指定したカラムの値が小さい順に検索結果を表示。
• デフォルト値であるため「ASC」を省略した場合は昇順並び替えとなる。

– 降順で並び替え• DESC句を使うと指定したカラムの値が大きい順に検索結果を表示。

– 複数カラムによる並び替え• (カラム名 + カラム名 + ・・・)で複数のカラムを指定すると、指定したカラムの値の合計値が小さい順または大きい順に検索結果を表示。

v 実行例– 検索結果をIDが小さい順に並び替えて出力。
lecdb=> SELECT id,ra,dc
FROM pgc2003
ORDER BY id;
2019年 2月 14 - 15日

データベース講習会

94

8.2.4 カラム・テーブルの別名の定義
v 別名の定義方法
SELECT カラム名 AS 別名
FROM テーブル名 AS 別名;– 別名について• カラムに対して定義した別名は、SELECT文の処理の関係上WHERE句では参照できない。ただし、ORDER BY句とGROUP BY句では参照できる。

v 実行例– テーブル「pgc2003」に別名「hoge」を定義した場合。
lecdb=> SELECT hoge.id AS pgc_number,hoge.ra,hoge.dc
FROM pgc2003 AS hoge;• 「テーブル名.カラム名」はカラムの厳密な指定の仕方。
• テーブルに別名を定義した場合は、カラム名を厳密に指定する場合が多い。

2019年 2月 14 - 15日

データベース講習会

95

演習 SELECT文の基本形
v 【演習】 以下の問い合わせを実施せよ。
1. テーブル「pgc2003」のカラム「id」、「ra」、「dc」を、カラム「id」
が小さい順に20件表示せよ。
2. テーブル「pgc2003」のカラム「id」、「ra」、「dc」にそれぞれ「pgc」、「sekkei」、「sekii」という別名を与え、カラム「sekii」が大きい順に20件表示せよ。

2019年 2月 14 - 15日

データベース講習会

96v 【解答例】 以下の問い合わせを実施せよ。
1. テーブル「pgc2003」のカラム「id」、「ra」、「dc」を、カラム「id」が小さい順に20件表示せよ。
lecdb=> SELECT id,ra,dc
FROM pgc2003
ORDER BY id
LIMIT 20;

2. テーブル「pgc2003」のカラム「id」、「ra」、「dc」にそれぞれ「pgc」、「sekkei」、「sekii」という別名を与え、カラム「sekii」
が大きい順に20件表示せよ。
lecdb=> SELECT id as pgc,
ra as sekkei,
dc as sekii
FROM pgc2003
ORDER BY sekii DESC
LIMIT 20;2019年 2月 14 - 15日

データベース講習会

97

8.3 検索条件の指定
v WHERE句– SELECT文中でWHERE句を使って条件式を設定することで、条件式を満たすレコードのみを返すことができる。
– WHERE句内で使われる基本的な演算子と句を紹介。
SELECT カラム名
FROM テーブル名
WHERE 条件式;

v 8.3節目次– 8.3.1 比較演算子– 8.3.2 IS演算子– 8.3.3 論理演算子– 8.3.4 BETWEEN句– 8.3.5 パターンマッチング
2019年 2月 14 - 15日

データベース講習会

98

8.3.1 比較演算子
v 比較演算子一覧
演算子

説明

<

小なり

>

大なり

<=

以下

>=

以上

=

等しい

<> or !=

等しくない（「!=」は内部で「<>」として処理される）

v 実行例– IDがPGC0077777の天体を検索。
lecdb=> SELECT id,ra,dc
FROM pgc2003
WHERE id=‘PGC0077777’;

2019年 2月 14 - 15日

データベース講習会

99

8.3.2 IS演算子
v IS演算子の書式
SELECT カラム名
FROM テーブル名
WHERE カラム名 IS [NOT] NULL;– IS [NOT] NULL
• NULL値を含む（含まない）レコードを検索する際に利用する。
• 通常の比較演算子（「=」や「!=」）でNULL値の比較演算を行うと真や偽ではなく「NULL」
を返すため、NULL値を検索することができない。

v 実行例– カラム「mtype」がNULLであるレコードを、比較演算子「=」を使って検索。
lecdb=> SELECT id,ra,dc,mtype
FROM pgc2003
WHERE mtype=NULL;– カラム「mtype」がNULLであるレコードを、IS演算子を使って検索。
lecdb=> SELECT id,ra,dc,mtype
FROM pgc2003
WHERE mtype IS NULL;
2019年 2月 14 - 15日

データベース講習会

100

8.3.3 論理演算子
v 論理演算子一覧
演算子

説明

AND

論理積

OR

論理和

NOT

否定

v 実行例
– 赤緯が89度以上かつmtypeの値がNULLではない天体を検索。
lecdb=> SELECT id,ra,dc,mtype
FROM pgc2003
WHERE (dc>=89) AND (mtype IS NOT NULL);– 赤緯が-88度以下か+88度以上の天体を検索。
lecdb=> SELECT id,ra,dc
FROM pgc2003
WHERE (dc<=-88) OR (dc>=88);• ()は必須ではない。
2019年 2月 14 - 15日

データベース講習会

101

8.3.4 BETWEEN句v BETWEEN句の書式
SELECT カラム名
FROM テーブル名
WHERE カラム名 [NOT] BETWEEN a AND b;– BETWEEN句• カラムの値がa以上からb以下の間にあるレコードを返す。

v 実行例– 赤緯が-1度以上から+1度以下までの天体を検索。
lecdb=> SELECT id,ra,dc
FROM pgc2003
WHERE dc BETWEEN -1 AND 1;– 赤緯が-88度以上から+88度以下ではない天体を検索。
lecdb=> SELECT id,ra,dc
FROM pgc2003
WHERE dc NOT BETWEEN -88 AND 88;
2019年 2月 14 - 15日

データベース講習会

102

8.3.5 パターンマッチング
v LIKE式とSIMILAR TO式の書式
SELECT カラム名
FROM テーブル名
WHERE カラム名 [NOT] LIKE|SIMILAR TO ‘パターン’;– 使用可能な正規表現
演算子

説明

LIKE

SIMILAR TO%

0文字以上の文字に一致。

◯

◯

_

任意の1文字に一致。

◯

◯

*

直前の文字の0回以上の繰り返し。

☓

◯

+

直前の文字の1回以上の繰り返す。

☓

◯

[文字列]

指定した文字列、文字範囲に含まれるあらゆる文字に一致。

☓◯[^文字列]

指定した文字列、文字範囲に含まれないあらゆる文字に一致。

☓

◯

|

二者択一。

☓

◯

(パターン)

パターンのグループ化。

☓

◯

v 実行例
– 「NGC5194」を検索。
lecdb=> SELECT id,ra,dc,anames
FROM pgc2003
WHERE anames LIKE ‘%NGC5194%’;
2019年 2月 14 - 15日

データベース講習会

103

演習 検索条件の指定
v 【演習】 以下の問い合わせを実施せよ。
1. テーブル「pgc2003」から赤経が359度から1度かつ赤緯が-1度から+1度の範囲にある天体を検索し、カラム「id」、「ra」、「dc」を赤緯が小さい順に表示せよ。
2. テーブル「pgc2003」から「logd25」が3以上である天体を検索し、カラム「id」、「ra」、「dc」、「logd25」、「anames」を「logd25」が大きい順に表示せよ。
•

2019年 2月 14 - 15日

logd25がNULL値であるレコードを除外すること。

データベース講習会

104v 【解答例】 以下の問い合わせを実施せよ。
1. テーブル「pgc2003」から赤経が359度から1度かつ赤緯が-1度から+1度の範囲にある天体を検索し、カラム「id」、「ra」、「dc」を赤緯が小さい順に表示せよ。
lecdb=> SELECT id,ra,dc
FROM pgc2003
WHERE (ra NOT BETWEEN 1 AND 359) AND(dc BETWEEN -1 AND 1)
ORDER BY dc;

2. テーブル「pgc2003」から「logd25」が3以上である天体を検索し、カラム「id」、「ra」、「dc」、「logd25」、「anames」を「logd25」が大きい順に表示せよ。
lecdb=> SELECT id,ra,dc,logd25,anames
FROM pgc2003
WHERE (logd25>=3) AND (logd25 IS NOT NULL)ORDER BY logd25 DESC;

2019年 2月 14 - 15日

データベース講習会

105

8.4 カラムに対する演算
v 演算子と関数– PostgreSQLには様々な演算子と関数が用意されている。
– SELECT文中でカラムに対し演算を行うことで、演算結果を検索結果として得ることができる。

v 8.4節目次– 8.4.1 算術演算子– 8.4.2 文字列演算子– 8.4.3 型変換関数– 8.4.4 算術関数
– 8.4.5 集約・統計関数

2019年 2月 14 - 15日

データベース講習会

106

8.4.1 算術演算子
v 算術演算子一覧
演算子

説明

例

結果

+

和

1+2

3

-

差

1-2

-1

*

積

3*4

12

/

商（整数同士の割り算はの場合は余りを切り捨てる）

7/3

2

%

剰余

7%2

1

^

べき乗

2^3

8

|/

平方根

|/ 144

12

||/

立方根

||/ 27

3

!

階乗

3!

6

@

絶対値

@(-10)

10

2019年 2月 14 - 15日

データベース講習会

107

v 実行例
– 整数と整数の割り算。
lecdb=> SELECT 10/3;– 整数と小数の割り算。
lecdb=> SELECT 10/3.0;• 異なるデータ型同士の演算時は暗黙的な型変換が行われる。
– カラム「logd25」のlogをはずして単位「arcmin」で表示。
lecdb=> SELECT id,ra,dc,(10^logd25)*0.1 AS d,anamesFROM pgc2003
WHERE logd25 IS NOT NULLORDER BY d DESC
LIMIT 10;• 演算結果にカラム名を付与しないと、カラム名が「?column?」となってしまう。
• 「ORDER BY d DESC」で演算結果が大きい順で並び替えている。

2019年 2月 14 - 15日

データベース講習会

108

8.4.2 文字列演算子
v 文字列演算子一覧
演算子
||

説明
文字列の結合例

結果

hoge || hoge

hogehogev 実行例– カラム「id」に文字列「hoge」を結合。
lecdb=> SELECT ‘hoge’ || id AS hogehoge
from pgc2003
LIMIT 10;

2019年 2月 14 - 15日

データベース講習会

109

8.4.3 型変換関数
v 型変換関数一覧
関数

説明

例

結果

CAST(データ AS データ型） データを指定したデータ型に変換。 CAST(1.2 AS INTEGER) 1
データ :: データ型

PostgreSQL独自の記法。

1.2 :: INTEGER

1– CAST関数• 明示的にあるデータのデータ型を他のデータ型に変換することができる。

v 実行例– 整数とdouble precision型に型変換した整数の割り算。
lecdb=> SELECT 10/CAST(3 AS DOUBLE PRECISION);– テーブル「nvss」からカラム「polflux」が999.9である天体を検索。
lecdb=> SELECT id,ra,dc,polflux
FROM nvss
WHERE polflux=CAST(999.9 AS REAL);• WHERE polflux=999.9も試してみましょう。
2019年 2月 14 - 15日

データベース講習会

110

8.4.4 算術関数
v 算術関数一覧
関数
ABS(X)CBRT(DP)CEIL(DP or NUMERIC)
DEGREES(DP)EXP(DP or NUMERIC)FLOOR(DP or NUMERIC)LN(DP or NUMERIC)LOG(DP or NUMERIC)
LOG(B NUMERIC,X NUMERIC)
MOD(Y,X)PI()
POWER(A DP, B DP)
POWER(A NUMERIC,B NUMERIC)RADIANS(DP)RANDOM()ROUND(DP or NUMERIC)ROUND(V NUMERIC,S INTEGER)SETSEED(DP)SIGN(DP or NUMERIC)SQRT(DP or NUMERIC)TRUNC(DP or NUMERIC)TRUNC(V NUMERIC,S INTEGER)

戻り値型
Xと同じ
DP
入力型と同一DP
入力型と同一入力型と同一入力型と同一入力型と同一入力型と同一引数型と同一DPDP
NUMERIC
DP
DP
入力型と同一NUMERICINTEGER
入力型と同一入力型と同一入力型と同一NUMERIC

説明
絶対値
立方根
切り上げ
ラジアンに対応する度
指数
切り下げ
自然対数
常用対数
Bを底としたXの対数
Y/Xの剰余
円周率
AのB乗
AのB乗
度に対応するラジアン
0.0-1.0の乱数値
四捨五入
Sの桁で四捨五入RANDOM()で使用する種を設定
引数の符号
平方根
切り捨て
Sの桁で切り捨て例
ABS(-2.0)
CBRT(27.0)
CEIL(-3.2)
DEGREES(3.14)
EXP(1.0)
FLOOR(-3.2)
LN(2.7)
LOG(100)
LOG(2,256)
MOD(9.3,3)
PI()
POWER(3.0,2.0)
POWER(3.0,2.0)
RADIANS(180)
RANDOM()ROUND(11.1)
ROUND(12.345,1)--SIGN(-2.4)
SQRT(3.0)
TRUNC(17.5)
TRUNC(12.345,1)

結果
2.0
3
-3
179.908747671078
2.7182818284590452-4
0.9932517730102834
28.0000000000000000
0.1
3.14159265358979
9.0000000000000000
93.14159265358979
0.529124391730875
11
12.3
---1
1.732050807568877
17
12.3※DP：DOUBLE PRECISION

2019年 2月 14 - 15日

データベース講習会

111

v 三角関数一覧
関数
ACOS(DP)ASIN(DP)ATAN(DP)ATAN2(Y DP,X DP)
COS(DP)
COT(DP)
SIN(DP)TAN(DP)

戻り値型
DOUBLE PRECISIONDOUBLE PRECISIONDOUBLE PRECISIONDOUBLE PRECISIONDOUBLE PRECISIONDOUBLE PRECISIONDOUBLE PRECISIONDOUBLE PRECISION

説明
逆余弦関数。
逆正弦関数。
逆正接関数。
Y/Xの逆正接関数。
余弦関数。
余接関数。
正弦関数。
正接関数。

v 実行例
– 赤経と赤緯の小数点以下を切り捨て。
lecdb=> SELECT id,
TRUNC(CAST(ra as NUMERIC),0) AS nra,
TRUNC(CAST(dc as NUMERIC),0) AS ndc
FROM pgc2003
LIMIT 50;
• TRUNC関数の入力データ型はDOUBLE PRECISION型かNUMERIC型である必要がある。カラム「ra」と「dc」のデータ型はREAL型であるため、CAST関数で
NUMERICに変更する必要がある。
2019年 2月 14 - 15日

データベース講習会

112– 赤経と赤緯（度角）を赤経（時分秒）と赤緯（度分秒角）に変換。
lecdb=> SELECT id,
TRUNC(CAST((ra/15.0) AS NUMERIC),0) AS hh,
TRUNC(MOD(CAST((ra/15.0) AS NUMERIC),1)*60,0) AS mm,
MOD(MOD(CAST((ra/15.0) AS NUMERIC),1)*60,1)*60 AS ss,
TRUNC(CAST(dc AS NUMERIC),0) AS dd,
ABS(TRUNC(MOD(CAST(dc AS NUMERIC),1)*60,0)) AS mm,
ABS(MOD(MOD(CAST(dc AS NUMERIC),1)*60,1)*60) AS ss
FROM pgc2003
WHERE id=‘PGC0077777’;id| hh | mm |ss
| dd | mm |ss-----------+----+----+------------------+-----+----+-----------PGC0077777 | 7 | 37 | 4.99991999998800 | -11 | 50 | 54.999600• 赤緯（度）：赤緯（度角）の整数部分を取り出す。
• 赤緯（分）：赤緯（度角）の小数部分を取り出して60倍し、整数部分を取り出す。
• 赤緯（秒）：赤緯（分角）の小数部分を取り出し60倍。

2019年 2月 14 - 15日

データベース講習会

113

8.4.5 集約・統計関数v 統計関数一覧
関数

引数型

戻り値型

CORR(Y,X)

DOUBLE PRECISIONDOUBLE PRECISION

相関係数。

COVAR_POP(Y,X)

DOUBLE PRECISIONDOUBLE PRECISION

母共分散。

COVAR_SAMP(Y,X)

DOUBLE PRECISIONDOUBLE PRECISION

標本共分散。

REGR_AVGX(Y,X)

DOUBLE PRECISIONDOUBLE PRECISION

独立変数の平均値（SUM(X)/N)。

REGR_AVGY(Y,X)

DOUBLE PRECISIONDOUBLE PRECISION

従属変数の平均値（SUM(Y)/N)。

REGR_COUNT(Y,X)

DOUBLE PRECISIONBIGINT

両式が非NULLとなる入力行の個数。

REGR_INTERCEPT(Y,X)

DOUBLE PRECISIONDOUBLE PRECISION(X,Y)の組み合わせで決まる、最小二乗法による線形方程式のY切片。

REGR_R2(Y,X)

DOUBLE PRECISIONDOUBLE PRECISION

相関係数の二乗値。

REGR_SLOPE(Y,X)

DOUBLE PRECISIONDOUBLE PRECISION(X,Y)の組み合わせで決まる、最小二乗法による線形方程式の傾き。

REGR_SXX(Y,X)

DOUBLE PRECISIONDOUBLE PRECISION

SUM(X^2)-SUM(X)^2/N（従属変数の二乗和）

REGR_SXY(Y,X)

DOUBLE PRECISIONDOUBLE PRECISION

SUM(X*Y)-SUM(X)*SUM(Y)/N（従属変数と独立変数の積の和）。

REGR_SYY(Y,X)

DOUBLE PRECISIONDOUBLE PRECISION

SUM(Y^2)-SUM(Y)^2/N（独立変数の二乗和）。

STDDEV_POP(式)SMALLINT、
INT、
浮動小数点数型引数はBIGINT、
DOUBLE PRECISION、
REAL、
DOUBLE PRECISION、 その他はNUMERICNUMERICSTDDEV_SAMP(式）
VAR_POP(式)VAR_SAMP（式）2019年 2月 14 - 15日

例

入力値に対する母標準偏差。
入力値に対する標本標準偏差。
入力値に対する母分散。
入力値に対する標本分散。
データベース講習会

114

v 集約関数一覧
関数

戻り値型

説明

AVG(X)

整数型引数はNUMERIC、
浮動小数点数型引数はDOUBLE PRECISION、その他は入力型と同一

全ての入力値の平均値を返す。

COUNT(*)---

入力行の数。

MAX(X)

入力型と同一全ての入力値の中から最大値を返す。

MIN(X)

入力型と同一全ての入力値の中から最小値を返す。

SUM(X)

SMALLINTとINT型引数はBIGINT、BIGINT型引数はNUMERIC、その他は入力型と同一

全ての入力値の和を返す。

– 集約・統計関数• 複数の入力値から1つの結果を返す関数。
• WHERE句では使用できない。

v 実行例– テーブルのレコード数を算出。
lecdb=> SELECT COUNT(*)
FROM pgc2003;– 「logd25」の最大値を検索。
lecdb=> SELECT MAX(logd25)
FROM pgc2003;– 「logd25」の最大値がどのIDなのか知りたいとき（間違い例）。
lecdb=> SELECT id
FROM pgc2003
WHERE logd25=MAX(logd25);
2019年 2月 14 - 15日

データベース講習会

115

v GROUP BY句とHAVING句の書式
SELECT カラム名 FROM テーブル名
GROUP BY カラム名
HAVING 条件式;
– GROUP BY句とHAVING句• GROUP BY句：同じ値を持つ複数のレコードをグループ化する。
• HAVING句：グループ化した結果の中から条件式を満たすレコードを返す。

v 実行例– 「mtype」毎にグループ化し、「SB%」タイプの天体数を表示。
lecdb=> SELECT mtype,count(*)
FROM pgc2003
GROUP BY mtype
HAVING mtype LIKE ‘SB%’;– 「logd25」の小数第二位を切り捨ててグループ化し、グループ毎の天体数を表示。
lecdb=>
SELECT TRUNC(CAST(logd25 AS NUMERIC),1) AS d,COUNT(*)
FROM pgc2003
WHERE logd25>=1 AND logd25 IS NOT NULLGROUP BY d
ORDER BY d DESC;
2019年 2月 14 - 15日

データベース講習会

116

演習 カラムに対する演算v 【演習】 以下の問い合わせを実行せよ。
1. テーブル「nvss」から「flux」が10000mJy以上の電波源の数を計測せよ。
2. テーブル「nvss」から「flux」が100mJy以上の天体を検索し、カラム「id」、「ra」、「dc」、「flux」、「polflux」、「fpol」を「fpol」
が大きい順に20件表示せよ。なお「fpol」は小数第一位で四捨五入すること。
•••

2019年 2月 14 - 15日

偏波率：fpol=(polflux/flux)*100
小数第一位での四捨五入は「ROUND(NUMERIC型の値,1)」で行う。
polfluxがNULL値のレコードは除外すること。

データベース講習会

117v 【解答例】 以下の問い合わせを実施せよ。
1. テーブル「nvss」から「flux」が10000mJy以上の電波源の数を計測せよ。
lecdb=> SELECT COUNT(*)
FROM nvss
WHERE flux>=10000;

2. テーブル「nvss」から「flux」が100mJy以上の天体を検索し、カラム「id」、「ra」、「dc」、「flux」、「polflux」、「fpol」を「fpol」
が大きい順に20件表示せよ。なお「fpol」は小数第一位で四捨五入すること。
lecdb=> SELECT id,ra,dc,flux,polflux,
ROUND(
CAST((polflux/flux)*100 AS NUMERIC),1) AS fpol
FROM nvss
WHERE (flux>=100) AND (polflux IS NOT NULL)ORDER BY fpol DESC
LIMIT 20;2019年 2月 14 - 15日

データベース講習会

118

v 【コラム】 SELECT文の評価順序– SELECT文は以下の順序で処理が行われる。
1.
2.
3.
4.
5.

WITH句
FROM句
WHERE句GROUP BY句HAVING句

6. SELECT句
7. UNION・INTERSECT・EXCEPT句
8. ORDER BY句
9. DISTINCT句
10. LIMIT句–例
lecdb=>
SELECT TRUNC(CAST(logd25 AS NUMERIC),1) AS d,COUNT(*)
FROM pgc2003
WHERE logd25>=1 AND logd25 IS NOT NULLGROUP BY d
ORDER BY d DESC;

1.
2.
3.
4.
5.
2019年 2月 14 - 15日

テーブル「pgc2003」から「logd25」が1以上かつNULL値でないという条件でカラム「d」で同じ値を持つ行をグループ化しグループ毎のcount(*)を計算しカラム「d」の値が大きい順に並び替える。
データベース講習会

119

8.5 副問い合わせv 副問い合わせ– SELECT句での副問い合わせ• 問い合わせの結果に副問い合わせの結果を結合することができる。

– FROM句で副問い合わせ• 副問い合わせの結果に対して問い合わせを行うことができる。

– WHERE句で副問い合わせ• 副問い合わせの結果を基に評価を行うことができる。

v 8.5節目次– 8.5.1 SELECT句での副問い合わせ– 8.5.2 FROM句での副問い合わせ– 8.5.3 WHERE句での副問い合わせ– 8.5.4 共通テーブル式
2019年 2月 14 - 15日

データベース講習会

120

8.5.1 SELECT句での副問い合わせv SELECT句での副問い合わせ書式
SELECT (SELECT カラム名
FROM テーブル名
WHERE 条件式
) AS 別名FROM テーブル名;– SELECT句での副問い合わせについて• SELECT句中での副問い合わせの結果は、一行一列の値でなければならない。

v 実行例– SELECT句での副問い合わせの例
lecdb=> SELECT id,ra,(SELECT dc
FROM pgc2003
WHERE id=‘PGC0077777’) AS dc
FROM pgc2003
WHERE id=‘PGC0077777’;
2019年 2月 14 - 15日

データベース講習会

121

8.5.2 FROM句での副問い合わせv FROM句での副問い合わせ書式
SELECT 別名.カラム名
FROM (
SELECT カラム名
FROM テーブル名
WHERE 条件式
) AS 別名;– FROM句での副問い合わせについて• 副問い合わせの検索結果には別名を指定しなければならない。
• 主SELECT文ではカラム名を「別名.カラム名」で指定しなければならない。
• 主SELECT文ではインデックス検索が行われない。

v 実行例– 赤緯が±1度内の天体を検索し、その中から「mtype」が「SB%」の天体を検索。
lecdb=> SELECT new.id,new.ra,new.dc,new.mtype
FROM (
SELECT *FROM pgc2003
WHERE dc BETWEEN -1 and 1) AS new
WHERE new.mtype LIKE ‘SB%’;2019年 2月 14 - 15日

データベース講習会

122

8.5.3 WHERE句での副問い合わせv WHERE句での副問い合わせ– 一行一列の値を返す副問い合わせ– 複数行複数列の値を返す副問い合わせ– 相関副問い合わせv 一行一列の値を返す副問い合わせ書式
SELECT カラム名
FROM テーブル名
WHERE カラム名 比較演算子 (SELECT カラム名
FROM テーブル名
WHERE 条件式);
– 一行一列の値を返す副問い合わせについて• 副問い合わせが単一の値を主SELECT文に返す。
• WHERE句は論理・比較演算子と副問い合わせで構成される。

2019年 2月 14 - 15日

データベース講習会

123

v 実行例
– 「logd25」の最大値がどのIDなのか知りたいとき。
lecdb=> SELECT id,logd25
FROM pgc2003
WHERE logd25 = (SELECT max(logd25)
FROM pgc2003);– 「SBa」型の天体よりも数が多い「mtype」を検索。
lecdb=> SELECT mtype,COUNT(mtype)
FROM pgc2003
GROUP BY mtype
HAVING COUNT(mtype) > (SELECT COUNT(*)
FROM pgc2003
WHERE mtype LIKE ‘SBa’);

2019年 2月 14 - 15日

データベース講習会

124v 複数行複数列の値を返す副問い合わせ書式
SELECT カラム名
FROM テーブル名
WHERE カラム名 [NOT] IN (SELECT カラム名
FROM テーブル名
WHERE 条件式);
– 複数行複数列の値を返す副問い合わせについて• 副問い合わせが複数の値を主SELECT文に返す。
• IN：副問い合わせが返したそれぞれの値が、指定したカラムに存在するか評価。
• WHERE句内で(カラム1,カラム2)とすることでカラムを複数指定できる。

v 実行例– テーブル「nvss」から赤経赤緯の値がテーブル「pgc2003」と完全に一致するレ
コードを検索。
lecdb=> SELECT id,ra,dc
FROM nvss
WHERE (ra,dc) IN (SELECT ra,dc
FROM pgc2003);
2019年 2月 14 - 15日

データベース講習会

125

v 相関副問い合わせ書式
SELECT カラム名
FROM テーブル名
WHERE [NOT] EXISTS (SELECT カラム名
FROM テーブル名
WHERE 条件式);
– 相関副問い合わせについて• 副問い合わせの結果が存在する場合のみ主SELECT文の結果を返す問い合わせ。
• 主問い合わせのテーブルを副問い合わせで参照するため相関問い合わせと呼ばれる。

v 実行例– テーブル「pgc2003」の赤経赤緯の値がテーブル「nvss」中に存在する場合に結果を返す。
lecdb=> SELECT id,ra,dc FROM pgc2003
WHERE EXISTS (SELECT *FROM nvss
WHERE (pgc2003.ra=nvss.ra) AND(pgc2003.dc=nvss.dc));• この場合、pgc2003のレコードを一行ずつ副問い合わせ中で評価し、真の場合結果を返している。
2019年 2月 14 - 15日

データベース講習会

126

8.5.4 共通テーブル式
v WITH句の書式
WITH 別名1 AS (SELECT カラム名
FROM テーブル名
WHERE 条件式
) [,別名2 AS ・・・];– 共通テーブル式• WITH句に記述した問い合わせの検索結果を一時テーブルとして保持できる。

v 実行例– 赤緯が±1度内の天体を検索し、その中から「mtype」が「SB%」の天体を検索。
lecdb=> WITH new AS (SELECT *FROM pgc2003
WHERE dc BETWEEN -1 and 1)
SELECT new.id,new.ra,new.dc,new.mtype
FROM new
WHERE new.mtype LIKE ‘SB%’;2019年 2月 14 - 15日

データベース講習会

127

演習 副問い合わせv 【演習】 以下の問い合わせを実行せよ。
1. テーブル「nvss」から赤経359度から1度かつ赤緯-1度から1度の領域にある電波源を検索し、その中から「flux」が100mJy以上の電波源のカラム「id」、「ra」、「dc」、「flux」を表示せよ。
•

FROM句での副問い合わせを使う。

2. テーブル「pgc2003」から「logd25」が3以上の天体を検索し、赤経赤緯がそれぞれ小数第一位まで一致する電波源をテーブル「nvss」から検索、カラム「id」、「ra」、「dc」を表示せよ。
••

2019年 2月 14 - 15日

複数行複数列の値を返すWHERE句での副問い合わせを使う。
TRUNC(CAST(カラム名 AS NUMERIC),1)を使う。

データベース講習会

128v 【解答例】 以下の問い合わせを実施せよ。
1. テーブル「nvss」から赤経359度から1度かつ赤緯-1度から1度の領域にある電波源を検索し、その中から「flux」が100mJy以上の電波源のカラム「id」、「ra」、「dc」、「flux」を表示せよ。
lecdb=> SELECT t.id,t.ra,t.dc,t.flux
FROM (
SELECT *FROM nvss
WHERE (ra NOT BETWEEN 1 AND 359) AND(dc BETWEEN -1 AND 1)) AS t
WHERE t.flux>=100;

2019年 2月 14 - 15日

データベース講習会

129

2. テーブル「pgc2003」から「logd25」が3以上の天体を検索し、赤経赤緯がそれぞれ小数第一位まで一致する電波源をテーブル「nvss」から検索、カラム「id」、「ra」、「dc」を表示せよ。
lecdb=> SELECT id,ra,dc
FROM nvss
WHERE (TRUNC(CAST(nvss.ra AS NUMERIC),1),
TRUNC(CAST(nvss.dc AS NUMERIC),1)) IN (
SELECT TRUNC(CAST(pgc2003.ra AS NUMERIC),1),
TRUNC(CAST(pgc2003.dc AS NUMERIC),1)
FROM pgc2003
WHERE logd25>=3);

2019年 2月 14 - 15日

データベース講習会

130

8.6 テーブルの結合
v テーブルの結合– テーブル同士を結合することで、複数のテーブルに対して同時に検索を行うことができる。
– 「交差結合」、「内部結合」、「外部結合」という結合方法が存在する。

v 8.6節目次– 8.6.1 交差結合
– 8.6.2 内部結合
– 8.6.3 外部結合

2019年 2月 14 - 15日

データベース講習会

131

8.6.1 交差結合v 交差結合の書式
SELECT カラム
FROM テーブル1 CROSS JOIN テーブル2;– 交差結合（CROSS JOIN）• テーブル1の1レコード毎にテーブル2の全レコードを結合する。
• クロス結合で得られるテーブルの行数は、テーブル1とテーブル2のレコードの積。

v 実行例– テーブル「pgc2003」とSELECT文を結合。
lecdb=> SELECT pgc2003.id,t.*
FROM pgc2003 CROSS JOIN (SELECT id FROM pgc2003 LIMIT 5) AS t
LIMIT 20;2019年 2月 14 - 15日

データベース講習会

132

8.6.2 内部結合v 内部結合の書式
SELECT カラム
FROM テーブル1 INNER JOIN テーブル2 ON 結合条件;– 内部結合（INNER JOIN）• テーブル1の各レコードに対して、結合条件を満たすテーブル2の各行を結合する。
• 結合条件の例としては「テーブル1.id = テーブル2.id」などが挙げられる。

v 実行例– テーブル「pgc2003」と「nvss」の赤経赤緯の値が完全に一致するレコードを結合して表示。
lecdb=> SELECT pgc2003.id,pgc2003.ra,pgc2003.dc,
nvss.id,nvss.ra,nvss.dc
FROM pgc2003 INNER JOIN nvss ON (pgc2003.ra=nvss.ra AND
pgc2003.dc=nvss.dc);

2019年 2月 14 - 15日

データベース講習会

133– 3個以上のテーブルを結合する場合の例。
SELECT t1.id,t2.id,t3.id
FROM (t1 INNER JOIN t2 ON t1.id = t2.id)
INNER JOIN ON t1.id = t3.id;v 内部結合の書式（簡易形）
SELECT カラム
FROM テーブル1,テーブル2
WHERE 結合条件;

v 実行例– テーブル「pgc2003」と「nvss」の赤経赤緯の値が完全に一致するレコードを結合して表示。
lecdb=> SELECT pgc2003.id,pgc2003.ra,pgc2003.dc,
nvss.id,nvss.ra,nvss.dc
FROM pgc2003,nvss
WHERE (
pgc2003.ra=nvss.ra AND pgc2003.dc=nvss.dc);

2019年 2月 14 - 15日

データベース講習会

134

8.6.3 外部結合
v 外部結合の書式
SELECT カラム
FROM テーブル1 LEFT|RIGHT|FULL OUTER JOIN テーブル2
ON 結合条件;– 外部結合• 結合対象のテーブルに結合条件を満たすレコードが存在しない場合も結合を行う。

– 左外部結合（LEFT OUTER JOIN）• LEFT OUTER JOIN句の左側のテーブルのレコードは全て表示し、右側のテーブルのレコードは結合条件を満たしたもののみを表示する。
• それ以外はNULL値を返す。

– 右外部結合（RIGHT OUTER JOIN）• LEFT INNER JOINと逆の処理を行う。

– 完全外部結合（FULL OUTER JOIN）• 左右のテーブルを全て表示する。
• 結合条件を満たすレコードは結合を行い、満たさないレコードはNULL値を返す。

2019年 2月 14 - 15日

データベース講習会

135

v 実行例
– テーブル「pgc2003」と「nvss」の赤経赤緯の値が完全に一致するレコードを左外部結合して表示。
lecdb=> SELECT pgc2003.id,pgc2003.ra,pgc2003.dc,
nvss.id,nvss.ra,nvss.dc
FROM pgc2003 LEFT OUTER JOIN nvss ON (pgc2003.ra=nvss.ra AND
pgc2003.dc=nvss.dc);– テーブル「pgc2003」と「nvss」の赤経赤緯の値が完全に一致するレコードを完全外部結合して表示。
lecdb=> SELECT pgc2003.id,pgc2003.ra,pgc2003.dc,
nvss.id,nvss.ra,nvss.dc
FROM pgc2003 FULL OUTER JOIN nvss ON (pgc2003.ra=nvss.ra AND
pgc2003.dc=nvss.dc);

2019年 2月 14 - 15日

データベース講習会

136

8.7 問い合わせ結果の結合v 問い合わせ結果の結合– 複数の問い合わせの結果を、「和」、「差」、「積」の集合演算を使って結合することができる。
– 「8.6 テーブルの結合」が結合したテーブルに対して検索を行うのに対し、こちらは検索した結果の結合を行う。

v 8.7節目次– 8.7.1 和集合– 8.7.2 積集合– 8.7.3 差集合

2019年 2月 14 - 15日

データベース講習会

137

8.7.1 和集合
v 和集合の書式
SELECT カラム FROM テーブル1
UNION (
SELECT カラム FROM テーブル2);– 和集合（UNION）• UNION句の後ろの問い合わせ結果を、前の問い合わせ結果に結合する。
• 問い合わせは同じカラム数かつ同じデータ型を返さなければならない。

v 実行例– テーブル「pgc2003」とテーブル「nvss」の検索結果を和集合で結合。
lecdb=> SELECT id,ra,dc
FROM pgc2003
UNION (
SELECT id,ra,dc
FROM nvss)
LIMIT 20;2019年 2月 14 - 15日

データベース講習会

138

8.7.2 積集合
v 積集合の書式
SELECT カラム FROM テーブル1
INTERSECT (SELECT カラム FROM テーブル2);– 積集合（INTERSECT）• INTERSECT句の前と後ろの問い合わせ結果両方に含まれているレコードを返す。
• 問い合わせは同じカラム数かつ同じデータ型を返さなければならない。

v 実行例– テーブル「pgc2003」とテーブル「nvss」の検索結果を積集合で結合。
lecdb=> SELECT ra,dc
FROM pgc2003
INTERSECT (SELECT ra,dc
FROM nvss);
2019年 2月 14 - 15日

データベース講習会

139

8.7.3 差集合
v 差集合の書式
SELECT カラム FROM テーブル1
EXCEPT (SELECT カラム FROM テーブル2);– 差集合（EXCEPT）• EXCEPT句の前の問い合わせ結果から後ろの検索結果を差し引く。
• 問い合わせは同じカラム数かつ同じデータ型を返さなければならない。

v 実行例– テーブル「pgc2003」とテーブル「nvss」の検索結果を差集合で結合。
lecdb=> SELECT ra,dc
FROM pgc2003
EXCEPT (SELECT ra,dc
FROM nvss);
2019年 2月 14 - 15日

データベース講習会

140

8.8 遅くならないWHERE句の書き方
v RDBMSは問い合わせの結果が直ちに返ってくるように利用すべき– 以下の場合、結果がすぐに返ってこない。
1. WHERE句の書き方が悪い。
2. インデックスを張っていないカラムに対して検索している。
3. 検索結果が膨大で、ディスクI/Oがボトルネックとなっている。

– 質の悪いSQL文は作業効率の低下を招くだけでなく、サーバに無用な負担をかけてしまう。

v 実行結果は同じだがWHERE句の書き方が異なる2つのSQL文の実行時間の比較を行う。

2019年 2月 14 - 15日

データベース講習会

141v 【実習】 実行時間の比較
1. 以下のSQL文は「logd25」が1 arcmin 以上の天体の数を計測するものである。SQL文を3回以上実行し、最後の3回の中間値をメモせよ。
2. 「SELECT」の直前に「EXPLAIN ANALYZE」をつけて以下のSQL文を
実行せよ。
– 例1。
lecdb=> \timing
lecdb=> SELECT COUNT(*)
FROM pgc2003
WHERE (10^logd25)*0.1 >= 1;– 例2。
lecdb=> SELECTFROM pgc2003
WHERE logd25 >= LOG(1*10);• 「EXPLAIN ANALYZE」は問い合わせの実行計画を表示するSQLコマンド。
• 例1はSequence Scanが行われているが例2はIndex Scanが行われている。
• WHERE句内でカラムに対する演算を行うと、PostgreSQLはカラムの演算結果に対して評価を行う。したがってカラムにインデックスが設定してあっても、演算を行うとインデックスを使用できない。
• WHERE句内でのカラムに対する演算は厳禁。
2019年 2月 14 - 15日

データベース講習会

9. ユーザ定義関数

2019年 2月 14 - 15日

データベース講習会

143

9.1 概要v SQL言語とPL/pgSQLを使ったユーザ定義関数の作成方法を紹介。
– PostgreSQLではユーザ定義関数の作成にSQL言語、C言語、手続き型言語を使用できる。
– PostgreSQLで標準で使用できる手続き型言語（Procedural
Language）は以下の通り。
• PL/pgSQL, PL/Tcl, PL/Perl, PL/Python ※PL/pgSQL以外は要インストール作業。

v 9章目次– 9.1 概要
– 9.2 SQL関数– 9.3 PL/pgSQL関数– 9.4 SQL関数とPL/pgSQL関数の使い分け
2019年 2月 14 - 15日

データベース講習会

144

9.2 SQL関数
v SQL関数– SQL文で構成される関数。
– 関数内に記述されたSQL文を実行していき、最後のSQL文の実行結果を返す。

v 9.2節目次– 9.2.1 一行一列の値を返すSQL関数– 9.2.2 一行複数列の値を返すSQL関数– 9.2.3 複数行複数列の値を返すSQL関数

2019年 2月 14 - 15日

データベース講習会

145

9.2.1 一行一列の値を返すSQL関数
v 一行一列の値を返すSQL関数の書式
CREATE FUNCTION 関数名(引数1のデータ型,引数2のデータ型,・・・)
RETURNS 戻り値のデータ型 AS $$SQL文;$$ IMMUTABLE|STABLE|VOLATILE LANGUAGE SQL;–––

二重ドル引用符• CREATE FUNCTIONにとって関数の内容（SQL文）は単なる文字列であるため、単一引用符で囲まなければならない。
• 二重ドル引用符は単一引用符と同じ役割りを果たすが、文字列内に単一引用符やバックスラッシュがあっても単なる文字として認識できる。
関数の変動性分類（IMMUTABLE,STABLE,VOLATILE）• 関数がどのような振る舞いをするのか、オプティマイザに教えるためのオプション。
• 分類の指定を省略した場合は「VOLATILE」になる。
• IMMUTABLE：同一引数に対する呼び出しに対し、常に同一の結果を返す関数。
• WHERE句内で関数を使った時、その関数が「IMMUTABLE」でないとインデックス検索が行われない。
引数• SQL文中でカラム名や条件式の値としてN番目の引数の値を「$N」として使用できる。ただし、テーブ
ル名には引数を使用できない。
• 「関数名（仮引数名 データ型）」のように仮引数名を指定することもできるが、SELECT文を使用する
SQL関数では引数とカラム名を混同してしまう危険性があるため非推奨。

2019年 2月 14 - 15日

データベース講習会

146

v 実行例
– Hello, World!
lecdb=> CREATE FUNCTION fSqlA1() RETURNS TEXT AS $$SELECT ‘Hello, World!’;$$ IMMUTABLE LANGUAGE SQL;
lecdb=> \df
lecdb=> SELECT fSqlA1();• 「/home/lecture/DB/sql/fSqlA1.sql」を作成して「\i」で読み込む。
– 引数をとるSQL関数。
lecdb=> CREATE FUNCTION fSqlA2(INTEGER,INTEGER)
RETURNS INTEGER AS $$SELECT $1 + $2;$$ IMMUTABLE LANGUAGE SQL;
lecdb=> \df
lecdb=> SELECT fSqlA2(2000,19) AS ANSWER;

v SQL関数の削除方法。
lecdb=> DROP FUNCTION 関数名;
• 同じ名前の関数が複数存在する場合は「関数名（データ型）」で指定する。
2019年 2月 14 - 15日

データベース講習会

9.2.2 一行複数列の値を返すSQL関
数147v 一行複数列の値を返すSQL関数の書式
CREATE FUNCTION 関数名(引数1のデータ型,引数2のデータ型,・・・)
RETURNS 戻り値のデータ型(複合型) AS $$SQL文;$$ IMMUTABLE|STABLE|VOLATILE LANGUAGE SQL;
– 複合型
• 複数のデータ型を組み合わせて1つのデータ型として扱うデータ型。
• テーブルのレコードの構造を表すために存在。テーブルを作成すると作成したテーブルと同じデータ型構造を持つ、テーブルと同名の複合型が作成される。
• SQL関数の戻り値データ型に複合型を指定することで、複数列の結果を返すことが可能。
• 複合型は任意に定義できる。

v 複合型の定義方法
CREATE TYPE データ型名 AS (変数1 データ型1,
変数2 データ型2,・・・);
2019年 2月 14 - 15日

データベース講習会

148

v 実行例
– 複数列の結果を返すSQL関数。
lecdb=> CREATE TYPE type1 AS (a INTEGER,b INTEGER);
lecdb=> \dTU
lecdb=> CREATE FUNCTION fSqlB1(INTEGER, INTEGER)
RETURNS type1 AS $$SELECT $1+$2,$1-$2;$$ IMMUTABLE LANGUAGE SQL;
lecdb=> \df
lecdb=> SELECT fSqlB1(2000,19);
lecdb=> SELECT a,b FROM fSqlB1(2000,19);• 関数をFROM句に指定すれば、返り値を要素毎に抽出できる。
– テーブルの行を返すSQL関数。
lecdb=> CREATE FUNCTION fSqlB2() RETURNS pgc2003 AS $$SELECT * FROM pgc2003 limit 10;$$ IMMUTABLE LANGUAGE SQL;
lecdb=> \df
lecdb=> SELECT * FROM fSqlB2();• この方法では1行しか返ってこない。

v 作成したデータ型の削除方法
lecdb=> DROP TYPE データ型名;
2019年 2月 14 - 15日

データベース講習会

9.2.3 複数行複数列の値を返すSQL関数

149

v 複数行複数列の値を返すSQL関数の書式
CREATE FUNCTION 関数名(引数1のデータ型,引数2のデータ型,・・・)
RETURNS SETOF 戻り値のデータ型(複合型) AS $$SQL文;$$ IMMUTABLE|STABLE|VOLATILE LANGUAGE SQL;
– SETOF句• SETOFを指定することで関数内の最後に記述されたSQL文が最後まで実行される。

v 実行例– 複数行複数列の値を返すSQL関数。
lecdb=> CREATE FUNCTION fSqlC1() RETURNSSETOF pgc2003 AS $$SELECT * FROM pgc2003 limit 10;$$ IMMUTABLE LANGUAGE SQL;
lecdb=> \df
lecdb=> SELECT * FROM fSqlC1();

2019年 2月 14 - 15日

データベース講習会

150

演習 SQL関数v 【演習】 赤経赤緯（度角）を赤経（時角）と赤緯（度分秒角）に変換するSQL関数の作成
1. NUMERIC型の要素「a,b,c,d,e,f」から構成される複合型「type2」を作成せよ。
2. 赤経赤緯（度角）を赤経（時角）と赤緯（度分秒角）に変換す
るSQL関数「fSqlC2」を作成せよ。
•••

引数型：(DOUBLE PRECISION, DOUBLE PRECISION)。
戻り値データ型：SETOF type2。
SQL文は8.4.4節の実行例を参照。

3. 「id=’PGC0077777’」の天体を検索し、ID、赤経（時）、赤経（分）、赤経（秒）、赤緯（度）、赤緯（分）、赤緯（秒）を表示せよ。
•
2019年 2月 14 - 15日

FROM句でテーブル「pgc2003」とSQL関数「fSqlC2(pgc2003.ra,pgc2003.dc) 」を交差結合する。
データベース講習会

151v 【解答例】 赤経赤緯（度角）を赤経（時角）と赤緯（度分秒角）に変換するSQL関数の作成
1. NUMERIC型の要素「a,b,c,d,e,f」から構成される複合型「type2」を作成せよ。
lecdb=> CREATE TYPE type2 AS(
a NUMERIC, b NUMERIC, cd NUMERIC, e NUMERIC, f);
lecdb=> \dTU

NUMERIC,NUMERIC

2. 赤経赤緯（度角）を赤経（時角）と赤緯（度分秒角）に変換す
るSQL関数「fSqlC2」を作成せよ。
lecdb=>
CREATE FUNCTION fSqlC2(DOUBLE PRECISION,DOUBLE PRECISION)
RETURNS SETOF type2 AS $$SELECT TRUNC(CAST(($1/15.0) AS NUMERIC),0),TRUNC(MOD(CAST(($1/15.0) AS NUMERIC),1)*60,0),MOD(MOD(CAST(($1/15.0) AS NUMERIC),1)*60,1)*60,
TRUNC(CAST($2 AS NUMERIC),0),ABS(TRUNC(MOD(CAST($2 AS NUMERIC),1)*60,0)),ABS(MOD(MOD(CAST($2 AS NUMERIC),1)*60,1)*60);$$ IMMUTABLE LANGUAGE SQL;
lecdb=> \df
2019年 2月 14 - 15日

データベース講習会

152

3. 「id=’PGC0077777’」の天体を検索し、ID、赤経（時）、赤経（分）、赤経（秒）、赤緯（度）、赤緯（分）、赤緯（秒）を表示せよ。
lecdb=>
SELECT id,f.*
FROM pgc2003 CROSS JOIN fSqlC2(pgc2003.ra,pgc2003.dc) AS f
WHERE id=‘PGC0077777’;id| a | b |c
| d | e |f-----------+----+----+------------------+-----+----+-----------PGC0077777 | 7 | 37 | 4.99991999998800 | -11 | 50 | 54.999600

2019年 2月 14 - 15日

データベース講習会

153

9.3 PL/pgSQL関数
v PL/pgSQL– 非手続き型言語であるSQLを拡張した、PostgreSQLで読み込み可能な手続き型言語。
– SQL関数よりも複雑な演算を行うことができる。
– PostgreSQL9.0以降にはデフォルトでインストールされている。

v 9.3節目次– 9.3.1 一行一列の値を返すPL/pgSQL関数– 9.3.2 一行複数列の値を返すPL/pgSQL関数– 9.3.3 複数行複数列の値を返すPL/pgSQL関数– 9.3.4 PL/pgSQLの制御構文

2019年 2月 14 - 15日

データベース講習会

154

9.3.1 一行一列の値を返すPL/
pgSQL関数v 一行一列の値を返すPL/pgSQL関数の書式
CREATE FUNCTION 関数名(引数1 データ型,引数2 データ型,・・・)
RETURNS 戻り値のデータ型 AS $$DECLARE
変数名 データ型;
BEGIN
変数名 := 値;
RETURN 式;
END$$ IMMUTABLE|STABLE|VOLATILE LANGUAGE PLPGSQL;– 変数の宣言• 変数を使用する場合はDECLARE文内で宣言する。

– RETURNコマンド• 本文内に「RETURN 式」を記述することで、式の内容を結果として返すことができる。

2019年 2月 14 - 15日

データベース講習会

155

v 実行例
– Hello, World!
lecdb=> CREATE FUNCTION fPlA1() RETURNS TEXT AS $$DECLAREx TEXT;
BEGIN
x := ‘Hello, World!’;
RETURN x;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
lecdb=> \df
lecdb=> SELECT fPlA1();– 引数をとるPL/pgSQL関数。
lecdb=> CREATE FUNCTION fPlA2(x INTEGER,y INTEGER)
RETURNS INTEGER AS $$DECLAREz INTEGER;
BEGIN
z := x + y;
RETURN z;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
lecdb=> \df
lecdb=> SELECT fPlA2(2000,19) AS ANSWER;
2019年 2月 14 - 15日

データベース講習会

156

9.3.2 一行複数列の値を返すPL/
pgSQL関数v 一行複数列の値を返すPL/pgSQL関数の書式
CREATE FUNCTION 関数名(引数1 データ型,引数2 データ型,・・・)
RETURNS 戻り値のデータ型(複合型) AS $$DECLARE
変数名 複合型;
BEGIN
変数名 := (値1,値2);
SELECT カラム名 INTO 変数名 FROM テーブル名 WHERE 条件;
RETURN 式;
END$$ IMMUTABLE|STABLE|VOLATILE LANGUAGE PLPGSQL;– 複合型• 変数のデータ型として複合型を指定できる。
• 複合型変数への代入方法は、直接代入とSELECT文を使った方法がある。

2019年 2月 14 - 15日

データベース講習会

– 複数列を返すPL/pgSQL関数。
lecdb=> CREATE FUNCTION fPlB1(x INTEGER, y INTEGER)
RETURNS type1 AS $$DECLAREz type1;BEGIN
z := (x+y,x-y);
RETURN z;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
lecdb=> \df
lecdb=> SELECT a,b FROM fPlB1(2000,19);157• type1：9.2.2節で作成した複合型（INTEGER,INTEGER）– テーブルの行を返すPL/pgSQL関数。
lecdb=> CREATE FUNCTION fPlB2() RETURNS pgc2003 AS $$DECLARErow pgc2003;
BEGIN
SELECT * INTO row FROM pgc2003 limit 10;
RETURN row;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
lecdb=> \df
lecdb=> SELECT * FROM fPlB2();• この方法では1行しか返ってこない。
2019年 2月 14 - 15日

データベース講習会

9.3.3 複数行複数列の値を返すPL/
pgSQL関数158

v 複数行複数列の値を返すPL/pgSQL関数の書式
CREATE FUNCTION 関数名(引数1 データ型,引数2 データ型,・・・)
RETURNS SETOF 戻り値のデータ型(複合型) AS $$DECLARE
変数名 複合型;
BEGIN
RETURN QUERY SQL文;RETURN NEXT 変数名;
RETURN;
END$$ IMMUTABLE|STABLE|VOLATILE LANGUAGE PLPGSQL;– RETURN QUERY SQL文
• 問い合わせの実行結果を関数の返り値テーブルに追加する。

– RETURN NEXT 変数名• 「変数名」に代入されているレコードを関数の返り値テーブルに追加する。

– RETURN• 関数が終了したことを表す。
2019年 2月 14 - 15日

データベース講習会

159

v 実行例
– RETURN QUERYの実行例。
lecdb=>
CREATE FUNCTION fPlC1() RETURNS SETOF pgc2003 AS $$BEGIN
RETURN QUERY SELECT * FROM pgc2003 limit 10;
RETURN;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
lecdb=> \df
lecdb=> SELECT * FROM fPlC1();– RETURN NEXTの実行例。
lecdb=>
CREATE FUNCTION fPlC2() RETURNS SETOF pgc2003 AS $$DECLARErow pgc2003;
BEGIN
FOR i IN 1..10 LOOP
SELECT * INTO row FROM pgc2003 LIMIT 1 OFFSET i-1;
RETURN NEXT row;
END LOOP;
RETURN;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
lecdb=> \df
lecdb=> SELECT * FROM fPlC2();• OFFSET：指定した行数を飛ばして表示

2019年 2月 14 - 15日

データベース講習会

160

9.3.4 PL/pgSQLの制御構文
v IF文
IF (条件式) THEN
実行文;
ELSEIF (条件式) THEN
実行文;
ELSE
実行文;
END IF;v 単純CASE文
CASE 式WHEN 定数1 then 実行文;
WHEN 定数2 then 実行文;
ELSE 実行文;
END CASE;

2019年 2月 14 - 15日

データベース講習会

161

v 検索CASE文CASE
WHEN 式 = 定数1 then 実行文;
WHEN 式 = 定数2 then 実行文;
ELSE 実行文;
END CASE;v 整数FORループ文
FOR name IN 整数値..整数値 LOOP
実行文;
END LOOP;• FOR文のname変数はINTEGER型として自動的に定義される。

v 問い合わせFORループ文
FOR 変数名 IN SELECT文 LOOP
実行文;
END LOOP;v WHILE文WHILE 条件式 LOOP
実行文;
END LOOP;
2019年 2月 14 - 15日

データベース講習会

9.4 SQL関数とPL/pgSQL関数の使
い分け

162

v SQL関数– SQLの知識だけで書くことができる。
– 静的なSQLしか書くことができない。
• テーブル名を仮引数とすることができない。

– PL/pgSQLよりもパフォーマンスが良好。

v PL/pgSQL関数– 言語仕様の知識が必要。
– 手続き型言語であるため、テーブルとは無関係な何らかの処理を行って1つの値を返す場合に使うのが基本。
– SQL関数と比較して極めて複雑な処理を行うことができる。

v 使い分け– テーブルを扱わない関数 => PL/pgSQL関数– テーブルを扱う関数 => SQL関数 or PL/pgSQL関数
2019年 2月 14 - 15日

データベース講習会

10. 指定座標を中心とした任意角度
範囲内の天体の検索2019年 2月 14 - 15日

データベース講習会

16410.1 概要v コーンサーチ– 天球面上のある座標を中心とした任意角度範囲内の天体を検索する方法は「Cone Search」や「Radial Search」と呼ばれる。
– 天文業界ではコーンサーチの実装手法がいくつか確立されているが、本講習会では直交座標系を利用したコーンサーチの実装手法を紹介する。

v 直交座標系を利用したコーンサーチ– 天体の位置を単位ベクトルで表し、検索中心と天体との2点間の角度を内積で求め、求めた角度が任意角度以下となる天体を検索すれば良い。
SELECT *FROM テーブル名
WHERE ACOS(x1x2+y1y2+z1z2) < 任意角度;••

検索中心(x1,y1,z1)
天体(x2,y2,z2)

このSQL文はWHERE句内でカラムに対する演算を行っているため検索に時間がかかる。
このような検索をRDBMSに実装する定石として以下のような検索手法が存在する。
1. インデックスを使ってざっくりと検索し、検索母数を減らす。
2. 1の結果に対して厳密な検索を行う。

2019年 2月 14 - 15日

データベース講習会

165

v コーンサーチの実装手法
1. 天体の赤道座標を直交座標に変換したテーブル（天体番号、
x、y、z）を用意する。
2. 検索半径がRの時、検索中心を中心とした1辺2Rの立方体の中にある天体をインデックス検索で取り出す。
3. 2で取り出した天体に対して検索中心からの角度を検索し、条件を満たす天体のみを取り出す。
– この実装手法は極付近の特別扱いが不要であるため単純に実装でき、検索半径が小さい時に高速に検索できるという特徴がある。

v 10章目次– 10.1 概要– 10.2 直交座標系用テーブルの作成– 10.3 コーンサーチの実装– 10.4 クロスマッチの実装
2019年 2月 14 - 15日

データベース講習会

166

10.2 直交座標系用テーブルの作成
v コーンサーチに使用する、天体の赤道座標を直交座標に変換したテーブルを作成
v 10.2節目次– 10.2.1 テーブルの作成– 10.2.2 座標変換用関数の作成– 10.2.3 テーブルへのデータの登録– 10.2.4 複合インデックスの作成

2019年 2月 14 - 15日

データベース講習会

167

10.2.1 テーブルの作成
v 【演習】 テーブルの作成
1. PGC2003カタログの天体の直行座標を収めるためのテーブル「pgc2003_xyz」と、NVSSカタログの天体の直行座標を収めるためのテーブル「nvss_xyz」を作成せよ。
•

カラム
–
–
–
–

•
•

2019年 2月 14 - 15日

id TEXTx DOUBLE PRECISIONy DOUBLE PRECISIONz DOUBLE PRECISION

全てのカラムに「NOT NULL」制約を設定する。
カラム「ID」に主キーを設定する。

データベース講習会

168v 【解答例】 テーブルの作成
1. PGC2003カタログの天体の直行座標を収めるためのテーブル「pgc2003_xyz」と、NVSSカタログの天体の直行座標を収めるためのテーブル「nvss_xyz」を作成せよ。
lecdb=>
CREATE TABLE pgc2003_xyz (id
TEXT
NOT NULL,x
DOUBLE PRECISIONNOT NULL,y
DOUBLE PRECISIONNOT NULL,z
DOUBLE PRECISIONNOT NULL,CONSTRAINT pgc2003_xyz_pkey PRIMARY KEY (id));
lecdb=>
CREATE TABLE nvss_xyz (id
TEXT
NOT NULL,x
DOUBLE PRECISIONNOT NULL,y
DOUBLE PRECISIONNOT NULL,z
DOUBLE PRECISIONNOT NULL,CONSTRAINT nvss_xyz_pkey PRIMARY KEY (id));
2019年 2月 14 - 15日

データベース講習会

169

10.2.2 座標変換用関数の作成
v 赤道座標系から直交座標系への変換式
x = cos(ra) cos(dec)y = sin(ra) cos(dec)z = sin(dec)v 【演習】 座標変換用関数の作成
1. 赤経赤緯を直行座標系のx、y、zに変換するPL/pgSQL関
数「fEq2X(ra,dc)」、「fEq2Y(ra,dc)」、「fEq2Z(ra,dc)」を作
成せよ。
•
•
•

2019年 2月 14 - 15日

引数の型：DOUBLE PRECISION
返り値の型：DOUBLE PRECISION
度をラジアンに変換する組み込み関数：RADIANS()

データベース講習会

170v 【解答例】 座標変換用関数の作成
1. 赤経赤緯を直行座標系のx、y、zに変換するPL/pgSQL関
数「fEq2X(ra,dc)」、「fEq2Y(ra,dc)」、「fEq2Z(ra,dc)」を作成せよ。
lecdb=>
CREATE FUNCTION fEq2X(ra DOUBLE PRECISION,dc DOUBLE PRECISION)
RETURNS DOUBLE PRECISION AS $$BEGIN
RETURN COS(RADIANS(ra))*COS(RADIANS(dc));
END$$ IMMUTABLE LANGUAGE PLPGSQL;
lecdb=>
CREATE FUNCTION fEq2Y(ra DOUBLE PRECISION,dc DOUBLE PRECISION)
RETURNS DOUBLE PRECISION AS $$BEGIN
RETURN SIN(RADIANS(ra))*COS(RADIANS(dc));
END$$ IMMUTABLE LANGUAGE PLPGSQL;
lecdb=>
CREATE FUNCTION fEq2Z(dc DOUBLE PRECISION)
RETURNS DOUBLE PRECISION AS $$BEGIN
RETURN SIN(RADIANS(dc));
END$$ IMMUTABLE LANGUAGE PLPGSQL;
2019年 2月 14 - 15日

データベース講習会

171

10.2.3 テーブルへのデータの登録v INSERTコマンド書式
INSERT INTO テーブル名 (カラム1,カラム2,...)VALUES(値1,値2)|SELECT文 ;– INSERTコマンド• テーブルへレコードを追加するためのコマンド。
• VALUESで指定した値を、SELECT文で別テーブルの値を追加することができる。

v 【実習】 データの登録
1. テーブル「pgc2003_xyz」と「nvxx_xyz」にデータを登録。
lecdb=> INSERT INTO pgc2003_xyz (id,x,y,z)
SELECT id,fEq2X(ra,dc),fEq2Y(ra,dc),fEq2Z(dc)
FROM pgc2003;
lecdb=> INSERT INTO nvss_xyz (id,x,y,z)
SELECT id,fEq2X(ra,dc),fEq2Y(ra,dc),fEq2Z(dc)
FROM nvss;••

成功すると「INSERT 0 追加された行数」が表示される。
sqrt(x^2+y^2+z^2)が1になるか確認しましょう。

2019年 2月 14 - 15日

データベース講習会

172

10.2.4 複合インデックスの作成
v 複合インデックスの作成方法
CREATE INDEX インデックス名 ON テーブル名(カラム1,カラム2,・・・)– 複合インデックス• テーブルの複数のカラムに対して1つのインデックスを定義する方法。
• あるレコードを検索する時に単一カラムに対する検索だけでは十分な絞り込みを行うことができず、複数列に対する検索をしなければならない場合に使用する（例：名字と名前、赤経と赤緯など）。

– 注意点• 複合インデックスのカラムの定義順序は検索性能に影響する。例えば「カラム1」、「カラム2」、「カラム3」に対して「カラム2」=>「カラム1」=>「カラム3」という順番で絞り込むのが最適と考えられる場合、この順番で複合インデックスのカラムを定義すると良い。
• 複合インデックスを設定したカラムに対して単一カラムに対する検索を行った場合、最初に定義したカラム（上記の例だとカラム2）以外はインデックス検索を行えない。

v 【実習】 複合インデックスの作成
1. テーブル「pgc2003_xyz」と「nvss_xyz」のカラム「x」、「y」、「z」に複合インデックスを設定。
lecdb=> CREATE INDEX ON pgc2003_xyz(x,y,z);
lecdb=> CREATE INDEX ON nvss_xyz(x,y,z);
lecdb=> VACUUM ANALYZE pgc2003_xyz;
lecdb=> VACUUM ANALYZE nvss_xyz;•

x、y、zの検索優先度は同一であるため、カラムの定義順序は何でも良い。

2019年 2月 14 - 15日

データベース講習会

173

10.3 コーンサーチの実装
v コーンサーチの実装手法
1. 天体の赤道座標を直交座標に変換したテーブル（天体番号、x、y、z）を用意する。
2. 検索半径がRの時、検索中心を中心とした1辺2Rの立方体の中にある天体をインデックス検索でとりだす。
3. 2で取り出した天体に対して検索中心からの角度を計算し、厳密に条件を満たす天体のみを取り出す。

– 必要なもの• 天球面上の2点間の角度を求める関数• 角度（分角）をラジアンに変換する関数

v 10.3節目次– 10.3.1 コーンサーチに必要な関数の作成– 10.3.2 コーンサーチの練習– 10.3.3 コーンサーチの実装
2019年 2月 14 - 15日

データベース講習会

10.3.1 コーンサーチに必要な関数の作成

174v 【演習】 コーンサーチに必要な関数の作成
1. 天球面上の点1（x1,y1,z1）と点2(x2,y2,z2)の間の角度（分角）を求める関数「fDistanceArcminXyz(x1,y1,z1,x2,y2,z2)」をPL/
pgSQLで作成せよ。なおACOS()の引数が定義域外の時に引数が-1か1となるようにIF文を使用せよ。
•
•
•
•
•

引数の型：DOUBLE PRECISION
返り値の型：DOUBLE PRECISION
2点間の角度：ACOS(x1x2+y1y2+z1z2)
ACOSの定義域：(-1<=x<=1)
丸め誤差により引数の値が定義域外になることがある

2. 分角をラジアンに変換するPL/pgSQL関数「fArcmin2Rad」を作成せよ。
• 引数の型：DOUBLE PRECISION• 返り値の型：DOUBLE PRECISION• 度をラジアンに変換する組み込み関数：RADIANS()• fArcmin2Rad(10800)=3.14159265358979
2019年 2月 14 - 15日

データベース講習会

175v 【回答】 コーンサーチに必要な関数の作成
1. 天球面上の点1（x1,y1,z1）と点2(x2,y2,z2)の間の角度（分角）を求める関数「fDistanceArcminXyz(x1,y1,z1,x2,y2,z2)」をPL/
pgSQLで作成せよ。なおACOS()の引数が定義域外の時に引数が-1か1となるようにIF文を使用せよ。
lecdb=> CREATE FUNCTION fDistanceArcminXyz (x1 DOUBLE PRECISION,
y1 DOUBLE PRECISION,
z1 DOUBLE PRECISION,
x2 DOUBLE PRECISION,
y2 DOUBLE PRECISION,
z2 DOUBLE PRECISION) RETURNS DOUBLE PRECISION AS $$DECLAREa DOUBLE PRECISION;
BEGIN
a := x1*x2+y1*y2+z1*z2;
IF (a>1) THENa := 1;ELSEIF (a<-1) THENa := -1;
END IF;
RETURN DEGREES(ACOS(a))*60;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
2019年 2月 14 - 15日

データベース講習会

176

2. 分角をラジアンに変換するPL/pgSQL関数「fArcmin2Rad」を作成せよ。
lecdb=> CREATE FUNCTION fArcmin2Rad (m DOUBLE PRECISION)
RETURNS DOUBLE PRECISION AS $$BEGIN
RETURN RADIANS(m/60);
END$$ IMMUTABLE LANGUAGE PLPGSQL;

2019年 2月 14 - 15日

データベース講習会

177

10.3.2 コーンサーチの練習v 【実習】 赤経(200[deg])、赤緯(5[deg]）を中心とした半径(60[arcmin])内にある天体をテーブル「pgc2003」
から検索。
1. 検索中心を中心とした1辺2Rの立方体内にある天体の検索。
lecdb=>
SELECT pgc2003.*
FROM pgc2003 INNER JOIN pgc2003_xyz ON (pgc2003.id=pgc2003_xyz.id
)
WHERE
(
x BETWEEN fEq2X(200,5)-fArcmin2Rad(60) AND
fEq2X(200,5)+fArcmin2Rad(60)) AND (y BETWEEN fEq2Y(200,5)-fArcmin2Rad(60) AND
fEq2Y(200,5)+fArcmin2Rad(60)) AND (z BETWEEN fEq2Z(5)-fArcmin2Rad(60) AND
fEq2Z(5)+fArcmin2Rad(60));• 1辺2Rの立方体という制限は、(x BETWEEN 検索中心x-R AND 検索中心x+R)という範囲指定を、xyzそれぞれの方向に対して行うことで実現できる。
2019年 2月 14 - 15日

データベース講習会

178

2. 1で取り出した天体から、赤経（200[deg]）、赤緯(5[deg]）を中心とした半径（60[arcmin]）内にある天体を検索。
lecdb=> SELECT t.*
FROM (
SELECT pgc2003.*,
fDistanceArcminXyz(
fEq2X(200,5),
fEq2Y(200,5),
fEq2Z(5),x,y,z) AS distance
FROM pgc2003 INNER JOIN pgc2003_xyz ON (pgc2003.id=pgc2003_xyz.id
)
WHERE
(
x BETWEEN fEq2X(200,5)-fArcmin2Rad(60) AND
fEq2X(200,5)+fArcmin2Rad(60)) AND (y BETWEEN fEq2Y(200,5)-fArcmin2Rad(60) AND
fEq2Y(200,5)+fArcmin2Rad(60)) AND (z BETWEEN fEq2Z(5)-fArcmin2Rad(60) AND
fEq2Z(5)+fArcmin2Rad(60))) AS t
WHERE t.distance <= 60;• FROM句での副問い合わせ内で関数「fDistanceArcminXyz」を計算している。
2019年 2月 14 - 15日

データベース講習会

179

10.3.3 コーンサーチの実装
v 【演習】 コーンサーチ用SQL関数の作成
1. コーンサーチ用ユーザ定義関数の実行結果を返すために必要な複合データ型「TypeCone」を作成せよ。
•

要素
–
–
–
–

id TEXT
ra DOUBLE PRECISION
dc DOUBLE PRECISION
distance DOUBLE PRECISION

2. テーブル「pgc2003」用のコーンサーチ用SQL関数「fConesearchPgc2003(ra,dc,r)」を作成せよ。
•••

引数の型：DOUBLE PRECISION
返り値の型：SETOF TypeCone
10.3.2節の演習2を参照

3. 作成した「fConesearchPgc2003(ra,dc,r)」の実行結果が10.3.2
節の実習2と同じ件数になるか確認せよ。

2019年 2月 14 - 15日

データベース講習会

180v 【解答例】 コーンサーチ用SQL関数の作成
1. コーンサーチ用ユーザ定義関数の実行結果を返すために必要な複合データ型「TypeCone」を作成せよ。
lecdb=>
CREATE TYPE TypeCone AS (2. テーブル「pgc2003」用のコーンサーチ用SQL関数
id
TEXT,「fConesearchPgc2003(ra,dc,r)」を作成せよ。
ra
DOUBLE PRECISION,);

2019年 2月 14 - 15日

dc
distance

DOUBLE PRECISION,DOUBLE PRECISION

データベース講習会

181

2. テーブル「pgc2003」用のコーンサーチ用SQL関数「fConesearchPgc2003(ra,dc,r)」を作成せよ。
lecdb=>
CREATE FUNCTION fConesearchPgc2003 (DOUBLE PRECISION,DOUBLE PRECISION,DOUBLE PRECISION) RETURNS SETOF TypeCone AS $$SELECT t.id,t.ra,t.dc,t.distance
FROM (
SELECT pgc2003.*,
fDistanceArcminXyz(
fEq2X($1,$2),
fEq2Y($1,$2),
fEq2Z($2),x,y,z) AS distance
FROM pgc2003 INNER JOIN pgc2003_xyz ON (pgc2003.id=pgc2003_xyz.id)
WHERE(x BETWEEN fEq2X($1,$2)-fArcmin2Rad($3) AND fEq2X($1,$2)+fArcmin2Rad($3)) AND(y BETWEEN fEq2Y($1,$2)-fArcmin2Rad($3) AND fEq2Y($1,$2)+fArcmin2Rad($3)) AND(z BETWEEN fEq2Z($2)-fArcmin2Rad($3) AND fEq2Z($2)+fArcmin2Rad($3))) AS t
WHERE t.distance <= $3;$$ IMMUTABLE LANGUAGE SQL;

3. 作成した「fConesearchPgc2003(ra,dc,r)」の実行結果が10.3.2節の実習2と同じ件数になるか確認せよ。
•
2019年 2月 14 - 15日

解答略
データベース講習会

182

v 【コラム】 コーンサーチ用PL/pgSQL関数の例– 関数中のFROM句で仮引数や変数を使うことはできない。
– テーブル名を可変にしたい場合はSQL文を文字列として動的に生成し、EXECUTE
文で文字列の内容を実行する。
lecdb=>
CREATE FUNCTION fConesearchPl (ra DOUBLE PRECISION,
dc DOUBLE PRECISION,r
DOUBLE PRECISION,
tbl TEXT) RETURNS SETOF TypeCone AS $$DECLAREsql TEXT;
BEGIN
sql:=‘
SELECT t.id,t.ra,t.dc,t.distance
FROM (
SELECT‘||tbl||’.*,
fDistanceArcminXyz(
fEq2X(‘||ra||’,’||dc||’),
fEq2Y(‘||ra||’,’||dc||’),
fEq2Z(‘||dc||’),
x,y,z) AS distance
FROM ‘||tbl||’ INNER JOIN ‘||tbl||’_xyz ON ‘||tbl||’.id=‘||tbl||’_xyz.id
WHERE(
x BETWEEN fEq2X(‘||ra||’,’||dc||’)-fArcmin2Rad(‘||r||’) AND
fEq2X(‘||ra||’,’||dc||’)+fArcmin2Rad(‘||r||’)) AND (y BETWEEN fEq2Y(‘||ra||’,’||dc||’)-fArcmin2Rad(‘||r||’) AND
fEq2Y(‘||ra||’,’||dc||’)+fArcmin2Rad(‘||r||’)) AND (z BETWEEN fEq2Z(‘||dc||’)-fArcmin2Rad(‘||r||’) AND
fEq2Z(‘||dc||’)+fArcmin2Rad(‘||r||’))) AS t
WHERE t.distance <= ‘||tbl ;RETURN QUERY EXECUTE sql;
RETURN;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
lecdb=> SELECT * FROM fConesearchPl(200,5,60,’pgc2003’);2019年 2月 14 - 15日

データベース講習会

183

10.3.4 クロスマッチの実装
v クロスマッチの実装– コーンサーチを利用すれば、複数のカタログ間での天体のク
ロスマッチを簡単に実装することができる。
– 実装手法1. カタログAから天体の赤経赤緯を取り出す。
2. カタログBから、1で取り出した赤経赤緯を中心にコーンサー
チを行い、検索半径内で最も近傍の天体を取り出す。

v 10.4節目次– 10.4.1 クロスマッチ用ユーザ定義関数の作成– 10.2.2 クロスマッチの実行2019年 2月 14 - 15日

データベース講習会

10.4.1 クロスマッチ用ユーザ定義関
数の作成

184

v 【演習】 クロスマッチ用SQL関数の作成– テーブル「pgc2003」の天体をテーブル「nvss」から探すために必要なSQL関数を作成する。
1. テーブル「nvss」にコーンサーチを行い、検索中心に最も近い天体のカラム「id」、「ra」、「dc」、「distance」を一つ返す関数「fGetNearestNvssObjID(ra,dc,r)」を作成せよ。
• 引数の型：DOUBLE PRECISION• 返り値の型：TypeCone• 10.3.3節の演習2参照• ORDER BY t.distance LIMIT 1を使用する

2019年 2月 14 - 15日

データベース講習会

185v 【解答例】 クロスマッチ用SQL関数の作成
1. テーブル「nvss」にコーンサーチを行い、検索中心に最も近い天体のカラム「id」、「ra」、「dc」、「distance」を一つ返す関数「fGetNearestNvssObjID(ra,dc,r)」を作成せよ。
lecdb=>
CREATE FUNCTION fGetNearestNvssObjID (DOUBLE PRECISION,DOUBLE PRECISION,DOUBLE PRECISION) RETURNS TypeCone AS $$SELECT t.id,t.ra,t.dc,t.distance
FROM (
SELECT
nvss.*,
fDistanceArcminXyz(
fEq2X($1,$2),
fEq2Y($1,$2),
fEq2Z($2),x,y,z) AS distance
FROM nvss INNER JOIN nvss_xyz ON nvss.id=nvss_xyz.id
WHERE(x BETWEEN fEq2X($1,$2)-fArcmin2Rad($3) AND fEq2X($1,$2)+fArcmin2Rad($3)) AND(y BETWEEN fEq2Y($1,$2)-fArcmin2Rad($3) AND fEq2Y($1,$2)+fArcmin2Rad($3)) AND(z BETWEEN fEq2Z($2)-fArcmin2Rad($3) AND fEq2Z($2)+fArcmin2Rad($3))) AS t
WHERE t.distance <= $3
ORDER BY t.distance
LIMIT 1;$$ IMMUTABLE LANGUAGE SQL;2019年 2月 14 - 15日

データベース講習会

186

10.4.2 クロスマッチの実行
v 【実習】 クロスマッチの実行
1. テーブル「pgc2003」の天体をテーブル「nvss」から0.1分角でクロスマッチさせる。
lecdb=>
SELECT
pgc2003.id,
pgc2003.ra,
pgc2003.dc,
nvss.id,
nvss.ra,nvss.dc,
nvss.distance
FROM pgc2003 CROSS JOIN fGetNearestNvssObjID(ra,dc,0.1) AS
nvss
WHERE nvss.id IS NOT NULLORDER BY pgc2003.dc;• テーブル「pgc2003」の末尾に関数「fGetNearestNvssObjID」を結合し、同行のPGC2003のRADECを関数の引数として計算、返り値が存在する場合のみ結果を表示している。
• 小さい方のカタログをFROM句に持ってくる。
2019年 2月 14 - 15日

データベース講習会

187

パスワード表v new-rXX– アカウント：別紙参照– パスワード：別紙参照

v ゲストOS 一般ユーザ– アカウント：lecture– パスワード：lec1902

v ゲストOS PostgreSQL管理用ユーザ

v PostgreSQL 一般ロール– ロール：dbr– パスワード：dbr1902

v PostgreSQL 特権ロール– ロール：postgres– パスワード：sql1902– アカウント：postgres– パスワード：pos1902

v ゲストOS 特権ユーザ– アカウント：root– パスワード：tokken1902

2019年 2月 14 - 15日

データベース講習会