データベース講習会（初級編）
於 Zoom オンライン開催
主催 天⽂データセンター
講師 ⼩澤 武揚（天⽂データセンター）

データベース講習会
2021年12⽉21―22⽇

2

⽬次
1.

2.

3.

4.

5.

はじめに
1.1. 背景1.2. 本講習会の内容
1.3. 参考資料
データベースの基礎
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. PostgreSQLのインストール
4.3. データベースクラスタの作成
4.4. PostgreSQLの起動と終了
PostgreSQLの初期設定
5.1. 概要
5.2. データベースロールの設定
5.2.1. ロール「postgres」のパスワード設定
5.2.2. ロール「dbr」の作成
5.3. PostgreSQLサーバ制御ファイルの設定
5.3.1. 設定変更すべき項目

2021年12⽉21―22⽇

6.

7.

5.3.2. postgresql.confの設定
5.4. アクセス制御ファイルの設定
5.4.1. pg_hba.confの書式
5.4.2. 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. Linuxコマンド「psql」
7.3. SQL
7.4. psqlメタコマンド
7.5. ログインパスワードの入力省略設定

データベース講習会

3
8.

テーブルへの問い合わせ
8.1. 概要
8.2. SELECT文の基本形
8.2.1. 基本形
8.2.2. 検索結果の出力件数の指定
8.2.3. 検索結果の並び替え
8.2.4. 列名・テーブル名の別名の定義
8.2.5. 演習問題
8.3. 検索条件の指定
8.3.1. 比較演算子
8.3.2. IS演算子
8.3.3. 論理演算子
8.3.4. BETWEEN句
8.3.5. LIKE/SIMILAR TO演算子
8.3.6. 演習問題
8.4. 列に対する演算
8.4.1. 型変換関数
8.4.2. 算術演算子
8.4.3. 算術関数
8.4.4. 集約・統計関数
8.4.5. 文字列演算子・関数
8.4.6. 演習問題
8.5. 副問い合わせ
8.5.1. SELECT句での副問い合わせ
8.5.2. FROM句での副問い合わせ
8.5.3. WHERE句での副問い合わせ
8.5.4. 共通テーブル式
8.5.5. 演習問題
8.6. テーブルの結合
8.6.1. 交差結合
8.6.2. 内部結合
8.6.3. 外部結合
8.6.4. 演習問題

2021年12⽉21―22⽇

8.7. 遅くならないSELECT文の書き方
8.7.1. 問い合わせの実行計画の表示
8.7.2. 「8.6.4. 演習問題」の高速化
9. ユーザ定義関数
9.1. 概要
9.2. SQL関数
9.2.1. 一行一列の値を返すSQL関数
9.2.2. 一行複数列の値を返すSQL関数
9.2.3. 複数行複数列の値を返すSQL関数
9.2.4. 演習問題
9.3. PL/pgSQL関数
9.3.1. 一行一列の値を返すPL/pgSQL関数
9.3.2. 一行複数列の値を返すPL/pgSQL関数
9.3.3. 複数行複数列の値を返すPL/pgSQL関数
9.3.４. PL/pgSQLの制御構文
9.3.5. 演習問題
9.4. SQL関数とPL/pgSQL関数の使い分け
10. 指定座標を中心とした任意角度範囲内の天体の検索
10.1. 概要
10.2. 直交座標への変換
10.2.1. 座標変換用関数の作成
10.2.2. 直交座標系ビューの作成
10.2.3. 直交座標系ビューに対する検索の高速化
10.2.4 複合インデックスの作成
10.3. コーンサーチの実装
10.3.1. コーンサーチに必要な関数の作成
10.3.2. コーンサーチ用SQLの作成
10.3.3. コーンサーチの実装
10.4. クロスマッチの実装
10.4.1. クロスマッチの実装
10.4.2. クロスマッチの実行

データベース講習会

1. はじめに2021年12⽉21―22⽇

データベース講習会

5

⾃⼰紹介
名前：⼩澤 武揚 (33)
おざわ たけあき所属：天⽂データセンター
仕事：

講師近影

2021年12⽉21―22⽇• Tomo-e Gozen デ ー タ のSMOKAでの公開作業• 多波⻑データ解析システムのシステム情報データベース化• 計算機室の環境情報のデータ
ベース化• データベースサーバの性能評
価試験
データベース講習会

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

データベース講習会

1.2. 本講習会の⽬的

7vリレーショナルデータベース管理システムである
PostgreSQLのインストール⽅法・初期設定⽅法・
データベース構築⽅法・問い合わせ⽅法をLinux端末を操作して実践的に学び習得
v天⽂カタログをデータベース化し、天⽂データ
ベースのノウハウを習得• PGC2003カタログ：983,261 天体• NVSSカタログ： 1,773,484 天体

2021年12⽉21―22⽇

データベース講習会

1.3. 参考資料

8v 天⽂データセンター 2012年度SQL講習会[初級編]資料（⼭内 千⾥）
v 天⽂データセンター 2016年度SQL講習会[中級編：データベース構築
編]資料（⼭内 千⾥）
v PostgreSQL本家（https://www.postgresql.org）
v ⽇本PostgreSQLユーザ会（https://www.postgresql.jp）
v PostgreSQL 13.1⽂書（https://www.postgresql.jp/document/13/
html/index.html）
v Letʼs Postgres（https://lets.postgresql.jp）
v 基礎から始めるデータベース⼊⾨セミナー（https://www.oracle.com/technetwork/jp/articles/index-155234-ja.html）
v PostgreSQL 全機能バイブル（技術評論社）（PostgreSQL 9.2 対応）
v 内部構造から学ぶPostgreSQL 設計・運⽤計画の鉄則（技術評論社）

2021年12⽉21―22⽇

データベース講習会

2. データベースの基礎

2021年12⽉21―22⽇

データベース講習会

2.1. データベースとは

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

vデータベースの例• 辞書、電話帳、ウェブ検索システム、販売時点情報管理（POS）システム、銀⾏の勘定系システム・・・。
• 観測データ、天体カタログ、⽂献・・・。
2021年12⽉21―22⽇

データベース講習会

2.2. データベースの種類

11vデータベースは主に次の3種類に分類• 階層型データベース• ネットワーク型データベース• リレーショナルデータベース

2021年12⽉21―22⽇

データベース講習会

12

v階層型データベース• データを親⼦関係を持った階層構造で管理するデータ
ベース
• 利点

教員データベース– 検索ルートが限られており⾼速に検索可能。

• ⽋点

01

山田二郎

02

山田三郎

03

山田一郎

01

理学部
○×大学

– ⼦データが複数の親データに属する場合データが重複。
– データを追加したときに階層構造の再設計が必要。
– 階層構造が変化した際に ○×大学
データベースを利⽤するプログラムの修正が必要。

2021年12⽉21―22⽇

山田一郎

工学部

学科を追加
物理学科

山田一郎

01

化学科

山田二郎

02

機械工学科

山田三郎

03

電気工学課

山田一郎

01

理学部

工学部

データベース講習会

13

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

• ⽋点

教員データベース– データ同⼠の関係が複雑。
– データを追加したときに ○×大学
データベース構造の再設計が必要。
– 階層構造が変化した際にデータベースを利⽤するプログラムの修正が必要。

2021年12⽉21―22⽇

山田一郎

01

山田二郎

02

山田三郎

03

理学部

工学部

データベース講習会

14

vリレーショナルデータベース• データを⾏と列からなる表で管理するデータベース– 表と表に共通するデータをもたせてデータを関連づける。

• 利点– データ構造に変更が⽣じても、プログラムへの影
響が少ない。

教員テーブル
ID

名前

01

山田一郎

02

山田二郎

03

山田三郎

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

2021年12⽉21―22⽇

ID

学科

01

物理学科

01

電気工学科

02

化学科

03

機械工学科

学部テーブル
ID

学部

01

理学部

01

工学部

02

理学部

03

工学部

学科テーブル

データベース講習会

2.3. リレーショナルデータベース
管理システム15vRDBMS（Relational Data Base Management System）• リレーショナルデータベースをコンピュータ上で実現するためのソフトウェア。
– 有償のものと無償のものが存在。
> 有償：Oracle Database、Microsoft SQL Server、IBM DB2> 無償：MySQL、PostgreSQL、MariaDB• クライアント・サーバモデルを採⽤しており、クライア
ントから要求された検索を、データベースを持つサーバが実⾏。
• データベースへの検索要求にはSQL（⾔語）を使⽤。

2021年12⽉21―22⽇

データベース講習会

vRDBMSの系譜•

16

http://data-e-education.com/E107_History_of_RDBMS.html– 参照元はリンク切れで閲覧不可。

2021年12⽉21―22⽇

データベース講習会

2.4. SQL

17vSQL（Structured Query Language）• リレーショナルデータベースにおいて、データの定義や問い合わせを⾏うための⾔語。
• ⽶国規格協会（ANSI）と国際標準化機構（ISO）により
標準SQL規格が策定（最新： SQL:2016）。
• RDBMSは基本的に標準SQL規格に則るため、RDBMS間で使⽤するSQLが⼤きく変わることはない。
– 関数に関しては互換性が無いことが多々ある。

2021年12⽉21―22⽇

データベース講習会

3. PostgreSQLについて

2021年12⽉21―22⽇

データベース講習会

3.1. PostgreSQLとは

19

vPostgreSQLとは• オープンソースソフトウェア（OSS）のオブジェクトリ
レーショナルデータベース管理システム（ORDBMS）。
• PostgreSQL Global Development Group（PGDG）が開発。
– PGDG：7⼈のコアメンバー、39⼈の主要開発者（⽇本⼈5名）、数⼗名の貢献者で構成（2021年10⽉現在）。

• ⽇本では「ポストグレスキューエル」、「ポストグレス」、「ポスグレ」と呼ばれる。
• ⽇本では2009年前後までOSSのRDBMSの中でトップシェアだったが、現在はMySQLがトップシェア。
– 参考：第3回オープンソースソフトウエア活⽤ビジネス実態調査
2021年12⽉21―22⽇

データベース講習会

3.2. PostgreSQL⼩史

20

v PostgreSQL⼩史• PostgreSQLは最初期のRDBMSであるIngresが発端。
• 1986年にカリフォルニア⼤学バークレー校（UCB）でIngresに オ ブ ジ ェ ク ト 指 向 を 取 り ⼊ れ た POSTGRES （ PostIngres）の開発が開始され、1993年に公開されたPOSTGRES
4.2をもって開発が終了。
• 1995年にUCBの⼤学院⽣であったAndrew YuとJolly Chenに
よってSQL⾔語インタプリタが追加されたPostgres 95が公開。
• 1997年にSQLに対応したことを踏まえて名称がPostgreSQLに 改 称 さ れ 、 UCB 時 代 の バ ー ジ ョ ン 番 号 を 引 き 継 い だ
PostgreSQL 6.0が公開。同時期にPGDGが結成され開発体制が確⽴。
• 1999年に⽇本PostgreSQLユーザ会（JPUG）が設⽴。
• 2021年12⽉現在ではPostgreSQL 14が公開中。
2021年12⽉21―22⽇

データベース講習会

3.3. PostgreSQLの特徴

21v 個⼈・商⽤問わず無償で利⽤・配布・改変可能
v 豊富な資料•

公式ドキュメント– 基礎的な内容から応⽤的な内容まで多くの例題を使って解説がなされており、これを読めば分からないことはない（かもしれない）。
– JPUGによって全⽂⽇本語訳されている。

•

ウェブ資料
– 歴史的に⽇本ではコアなユーザが多く、資料が充実。

v 公開サービス向きの機能•

豊富なクライアントインターフェース– C、Java、JavaScript、Perl、PHP、Python、Ruby他、多くの⾔語からアクセス可能。

•

商⽤版のPowerGresが存在– ソフトウェアが出荷されてから7年間サポートが有るため、⻑期間同
じ環境を維持したい場合などに有⽤（PostgreSQLは5年間）。

2021年12⽉21―22⽇

データベース講習会

v天⽂データ検索向きの機能

22• 強⼒なユーザ定義関数– 複数の戻り値（⾏、列、⾏列）を返す関数を定義可能。
> ORDBMSであるPostgreSQLの⼤きな特徴。
> 通常RDBMSの関数は戻り値として1つの値しか返せない。

– 座標に対する複雑な計算を頻繁におこなう天⽂データ検索に有⽤。

• 強⼒なユーザ定義関数の開発環境– SQL ⾔ 語 、 PL/pgSQL 、 C ⾔ 語 、 PL/Tcl 、 PL/Perl 、PL/Python他、多くの⾔語でユーザ定義関数を作成可能。

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

2021年12⽉21―22⽇

データベース講習会

23

3.4. 対応するプラットフォーム
v以下のプラットフォームで動作する• CPUアーキテクチャ– X86、X86_64、IA64、PowerPC、PowerPC 64、S/390、
S/390x、Sparc、Sparc 64、ARM、MIPS、MIPSEL、PARISC等。

• OS– Linux（最近のディストリビューション全て）。
– Windows（Win2000 SP4以降）。
– FreeBSD、OpenBSD、NetBSD。
– OS X。
– AIX、HP/UX、Solaris 等。
2021年12⽉21―22⽇

データベース講習会

4. PostgreSQLのインストール

2021年12⽉21―22⽇

データベース講習会

25

4.1. 概要v LinuxへのPostgreSQLのインストール⽅法を紹介• 本講習会の実施環境へはインストール済み。

v インストール環境• OS：CentOS Stream release 8– ソフトウェアの選択：最⼩限のインストール + 標準

• おこなった設定– # dnf update– # dnf install gcc– # dnf install emacs– # localectl set-locale LANG=ja_JP.UTF-8; source /etc/locale.c
onfv 4章⽬次• 4.2. PostgreSQLのインストール• 4.3. データベースクラスタの作成• 4.4. PostgreSQLの起動と終了
2021年12⽉21―22⽇

データベース講習会

26

4.2. PostgreSQLのインストール
v本家ウェブサイトからPostgreSQLをダウンロード
1. 適当な環境でウェブブラウザを起動。
2. PostgreSQLの本家ウェブサイトにアクセス。
https://www.postgresql.org
3. ページ上部の「Download」をクリック。
4. 「Packages and Installers」で「Linux」をクリック。続いて「RedHat/Rocky/CentOS」をクリック。
5. 「PostgreSQL Yum Repository」の選択欄を次のように選択。
1. Select version：13
2. Select platform：Red Hat Enterprise, Rocky, CentOS or
Oracle Version 8
3. Select architecture：x86_64

2021年12⽉21―22⽇

データベース講習会

27

2021年12⽉21―22⽇

データベース講習会

vPostgreSQLのインストール

28

1. PostgreSQLをインストールする環境にリポジトリを登録。
[root]# dnf install https://download.postgresql.org/pub/repos/yum/reporp
ms/EL-8-ppc64le/pgdg-redhat-repo-latest.noarch.rpm
2. dnfのPostgreSQLのモジュールの無効化。
[root]# dnf module disable postgresql
3. PostgreSQLのパッケージをインストール。
[root]# dnf install postgresql13
postgresql13-server
postgresql13-devel
postgresql13-contrib
postgresql13-libs

2021年12⽉21―22⽇

データベース講習会

vインストールすべきパッケージ29• postgresql13– データベースサーバにアクセスするために必要なクライア
ントソフトウェア。

• postgresql13-server– データベースサーバを構築・管理するために必要なソフトウェア。

• postgresql13-devel– アプリケーション開発⽤ヘッダファイルやライブラリ。

• postgresql13-contrib– OSコマンドや関数など。

• postgresql13-libs– PostgreSQLのクライアントプログラム⽤のライブラリや
インターフェース。

2021年12⽉21―22⽇

データベース講習会

30

4.3. データベースクラスタの作成
vデータベースクラスタ• データベースを格納するディレクトリ。
• PostgreSQLサーバ⽤Linuxコマンド「initdb」で作成。
• 未作成だとPostgreSQL起動不可。

2021年12⽉21―22⽇

データベース講習会

vデータベースクラスタの作成

31

1. コマンド「initdb」でデータベースクラスタを作成。
[root]# su postgres
bash-4.4$ /usr/pgsql-13/bin/initdb--no-locale--encoding=UTF8-D /var/lib/pgsql/13/data• Linuxユーザ「postgres」– PostgreSQL13-serverインストール時に⾃動的に作成されるLinuxユーザ。
– ホームディレクトリ：「/var/lib/pgsql」。

• --no-locale– PostgreSQLサーバの地域化を解除。
– デフォルトはOSで設定されているロケール（講習会環境ではja_JP）。並び替え、
⽇付や通貨の表⽰、エラーメッセージの⾔語などが地域化。
– LIKE句（パターンマッチング）でインデックスが使⽤されず検索速度が悪化。

• データベースの初期化– データベースクラスタ（/var/lib/pgsql/13/data）を削除することでデータベースを完全に初期化可能。
2021年12⽉21―22⽇

データベース講習会

vデータベースクラスタの構造

32• 主要なディレクトリとファイルについて。
[root]# ls -la /var/lib/pgsql/13/data

data/

base/

Ø データベースを格納。

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

log/Ø PostgreSQLサーバのログを保管。

pg_wal/Ø WALファイル（データ変更情報を変更と同時に記録したもの）を格納。

pg_xact/

Ø データ変更操作の実⾏状態を記録。

pg_hba.conf

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

postgresql.conf Ø PostgreSQLサーバの制御ファイル。
2021年12⽉21―22⽇

データベース講習会

33

4.4. PostgreSQLの起動と終了
vPostgreSQLの起動
1. PostgreSQLの起動。
[root]# systemctl start postgresql-13.service
2. 状態確認。
[root]# systemctl status postgresql-13.service
3. ⾃動起動設定。
[root]# systemctl enable postgresql-13.service• CentOSでは⾃動起動設定をおこなわないと、OS起動時に⾃動的に
PostgreSQLが⽴ち上がらない。
• systemctl– CentOSにおけるサービス管理コマンド。
– stop：サービス終了。
– disable：⾃動起動設定解除。

2021年12⽉21―22⽇

データベース講習会

5. PostgreSQLの初期設定

2021年12⽉21―22⽇

データベース講習会

35

作業環境の確認v初期設定⽅法を紹介する前に、本講習会の作業環
境の確認をおこないますv【実習】 作業環境の確認
1. 事前にお知らせした⼿順に従い、SSH接続で講習会⽤サーバに接続。
2. 端末にコマンドを⼊⼒し環境を確認。
[schoolXX]$ ls DB
DB ┬ catalogue└ sql• catalogue：天⽂カタログを収めたディレクトリ。
• sql：作成したsqlファイルを収めるディレクトリ。

[schoolXX]$ su postgres
bash-4.4$ whoami
bash-4.4$ exit
• デフォルトではアカウントがロックされているため⼀般ユーザからLinu
xユーザ「postgres」にはスイッチできないが、本講習会環境ではコマンド「# passwd -u -f postgres」でロックを解除している。
2021年12⽉21―22⽇

データベース講習会

5.1. 概要36

vPostgreSQLの初期設定⽅法を紹介• 本講習会の実施環境へは設定済み。

v5章⽬次• 5.2. データベースロールの設定• 5.3. PostgreSQLサーバ制御ファイルの設定• 5.4. アクセス制御ファイルの設定

2021年12⽉21―22⽇

データベース講習会

37

5.2. データベースロールの設定
v データベースロール• PostgreSQLサーバ⽤のユーザのようなもの。
• 閲覧専⽤等、役割に応じて作成するのが⼀般的。

v 設定するロール• postgres– データベースクラスタ作成時に作成される特権ロール。
– 初期状態ではパスワードが設定されていないので設定。

• dbr– ⼀般権限のデータベースロールを作成。

v 5.2節⽬次• 5.2.1. ロール「postgres」のパスワード設定• 5.2.2. ロール「dbr」の作成
2021年12⽉21―22⽇

データベース講習会

5.2.1. ロール「postgres」のパス
ワード設定

38

vロール「postgres」のパスワードの設定
1. データベース「postgres」に接続。
[schoolXX]$ psql -U postgres postgres• psql：PostgreSQLの対話型インターフェース。7章で詳しく説明。
• -U postgres：特権ロール「postgres」で接続。
• postgres：データベースクラスタ作成時に⾃動的に作成されるデータベース。
主に特権が必要な操作を⾏うときに利⽤する。
2. 「ALTER ROLE」コマンドでパスワードを変更。
postgres=# ALTER ROLE postgres WITH PASSWORD ‘postgres’;• ALTER ROLE：ロールの属性を変更するためのSQLコマンド。
3. 「\q」コマンドでpsqlを終了。
postgres=# ¥q• 「\」はバックスラッシュ「＼」。
• アクセス制御ファイルが初期状態の場合無条件にデータベースへの接続を許可しているため、この時点ではパスワードを求められない。
2021年12⽉21―22⽇

データベース講習会

5.2.2. ロール「dbr」の作成

39

vロール「dbr」の作成
1. データベース「postgres」に接続。
[schoolXX]$ psql -U postgres• 接続先を省略するとロールと同名のデータベースへ接続される。
2. 「CREATE ROLE」コマンドでロールを作成。
postgres=# CREATE ROLE dbr LOGIN PASSWORD ’dbr’;• CREATE ROLE：ロールを新規作成するためのSQLコマンド。
3. ロールが作成できたか確認
postgres=# ¥du• \du：ロールの⼀覧を表⽰するpsqlメタコマンド。
4. 「\q」コマンドでpsqlを終了。
postgres=# ¥q

2021年12⽉21―22⽇

データベース講習会

v【実習】データベースへのログイン

40

1. データベース「postgres」にロール「postgres」で接続してみましょう。
[schoolXX]$ psql –U postgres
postgres=# ¥xpostgres=# SELECT * FROM pg_roles
WHERE rolname=‘postgres’;
postgres=# SELECT * FROM pg_roles
WHERE rolname=‘dbr’;
postgres=# ¥q• \x：表⽰形式を変更するpsqlメタコマンド。
• コマンドと任意値を区別するため「SELECT~」で⼤⽂字を使っているが、psqlでは⼤⽂字⼩⽂字の区別がないため⼩⽂字⼊⼒でOK。
• 上⽮印キーでコマンドの履歴を表⽰可能。
2. データベース「postgres」にロール「dbr」で接続してみましょう。
[schoolXX]$ psql –U dbr postgres
postgres=> CREATE ROLE tukurenaiyo PASSWORD ‘hoge’;
postgres=> ¥q• 本講習会の実施環境ではアクセス制御ファイルの設定が完了しているためパスワードの⼊⼒を求められる。
2021年12⽉21―22⽇

データベース講習会

5.3. PostgreSQLサーバ制御ファ
イルの設定

41

vpostgresql.conf• 在り処：「/var/lib/pgsql/13/data/postgresql.conf」。

v設定項⽬
•
•
•
•
•
•
•
•

FILE LOCATIONSCONNECTIONS AND
AUTHENTICATIONRESOURCE USAGE
WRITE-AHEAD LOG
REPLICATIONQUERY TUNINGREPORTING AND LOGGINGPROCESS TITLE

•
•
•
•
•
•
•

STATISTICS
AUTOVACUUM
CLIENT CONNECTION DEFAULTSLOCK MANAGEMENTVERSION AND PLATFORM
COMPATIBILITYERROR HANDLINGCONFIG FILE INCLUDES

v5.3節⽬次• 5.3.1. 設定変更すべき項⽬• 5.3.2 postgresql.confの設定
2021年12⽉21―22⽇

データベース講習会

5.3.1. 設定変更すべき項⽬

42v 設定変更すべき項⽬• ほとんどの項⽬は初期設定のままで問題ない。
• デフォルトのメモリ使⽤量は保守的で本来の性能を発揮できないため、現在のマシンスペックに合わせた設定変更が必要。
– 本講習会環境のメモリ量は4GB。

v RESOURCE USAGE• shared_buffers（初期値：128MB => 1024MB）– テーブルやインデックスのデータをキャッシュする領域。
– 膨⼤なデータから少数のデータを読み書きする処理が頻繁におこなわれる処理に影響。
– カ ー ネ ル が キ ャ ッ シ ュ し た デ ー タ を PostgreSQL のshared_buffresが読み込むため、shared_buffersに⼤きな値を割り当てるとキャッシュするデータのほとんどが重複し、メモリの利⽤効率が悪化する。
– 経験的にshared_buffersのサイズは「実メモリの20~30%程度、もしくは頻繁にアクセスするテーブルのデータがshared_buffersに載る程度」とされる。
2021年12⽉21―22⽇

データベース講習会• work_mem（初期値：4MB => 1024MB）30MB

43– 検索時にテーブル結合や並び替え操作をおこなうための領域。
– 多くのレコードにアクセスする統計処理や、多くのレコードからデータを取り出す処理に影響。
– work_mem の 値 は 最 ⼤ で も 「 （ 実 メ モ リ -shared_buffers ）/max_connections（初期値：100）」程度とし、スワップが発⽣しない様に設定する。

• maintenance_work_mem（初期値：64MB => 1024MB）– バキュームやインデックス作成等のメンテナンス時に使⽤される。

• effective_cache_size（初期値：4GB => 2GB）
– ディスクキャッシュに利⽤できるメモリ量を予想するために⽤いられる（実メモリは消費しない）。
– 実メモリの50%程度が良いとされる。

2021年12⽉21―22⽇

データベース講習会

5.3.2. postgresql.confの設定

44

vpostgresql.confの設定
1. 「su」コマンドでLinuxユーザ「postgres」にスイッチ。
[root]# su postgres
2. テキストエディタで「postgresql.conf」を開く。
bash-4.4$ emacs /var/lib/pgsql/13/data/postgresql.conf
3. 「postgresql.conf」を以下のように編集（⾏頭の「#」は消す）。
shared_buffers = 1024MB
work_mem = 1024MB 30MB
maintenance_work_mem = 1024MB
effective_cache_size = 2GB
4. Linuxユーザ「root」でPostgreSQLを再起動。
bash-4.4$ exit
[root]# systemctl restart postgresql-13.service[root]# exit

2021年12⽉21―22⽇

データベース講習会

45

5.4. アクセス制御ファイルの設定
vpg_hba.conf（hbaは「Host Based Authentication」の略）• 在り処：「/var/lib/pgsql/13/data/pg_hba.conf」。

v個⼈環境で利⽤することを想定した設定• 外部からの接続は不許可– PostgreSQLサーバをインストールしたサーバにログインしないとデータベースに接続できない• ローカルからの接続に対するパスワード認証を実施– サーバにログインしないとデータベースに接続できないので本来パスワード認証は不要だが、練習のため設定。

v5.4節⽬次• 5.4.1. pg_hba.confの書式• 5.4.2. pg_hba.confの設定
2021年12⽉21―22⽇

データベース講習会

46

5.4.1. pg_hba.confの書式
vpg_hba.confの書式# TYPE
local
host

DATABASE
all
db01

USER
all
dbr

ADDRESS
192.168.0.0/24

METHOD
trust
md5• TYPE– local：ローカルホストに対するアクセス制御。
– host：リモートホストに対するアクセス制御。

• DATABASE– データベース名: 指定したデータベースのみ接続可能（all指定可能）。

• USER••– ロール名: 指定したロールのみ接続可能（all指定可能）。
ADDRESS– 指定したネットワークアドレスの機器のみ接続可能（localの場合は不要）。
METHOD– trust：無条件に接続を許可。
– reject：無条件に接続を拒否。
– md5：md5で暗号化されたパスワードによる認証。

2021年12⽉21―22⽇

データベース講習会

5.4.2. pg_hba.confの設定

47

vpg_hba.confの設定
1. 「su」コマンドでLinuxユーザ「postgres」にスイッチ。
[root]# su postgres
2. テキストエディタで「pg_hba.conf」を開く。
bash-4.4$ emacs /var/lib/pgsql/13/data/pg_hba.conf
3. 「pg_hba.conf」を以下のように編集。
local allall
md5
host allall 127.0.0.1/32 md5
host allall ::1/128
md5
local replication all
rejecthost replication all 127.0.0.1/32 reject
host replication all ::1/128
reject• 初期状態ではMETHODが全てtrust。

2021年12⽉21―22⽇

データベース講習会

4. PostgreSQLを再起動。

48

bash-4.4$ exit
[root]# systemctl restart postgresql-13.service[root]# exit
5. パスワード認証が有効化されたか確認。
[schoolXX]$ psql –U postgres
ユーザー postgres のパスワード: postgres
postgres=> ¥q[schoolXX]$ psql -U dbr postgres
ユーザー dbr のパスワード: dbr
postgres=> ¥q

2021年12⽉21―22⽇

データベース講習会

6. データベースの作成

2021年12⽉21―22⽇

データベース講習会

50

6.1. 概要

v天体カタログ「PGC2003」と「NVSS」のデータベース化を実施
v6章⽬次
•
•
•
•
•

6.2. テーブル空間の作成
6.3. データベースの作成
6.4. テーブルの作成
6.5. インデックスの作成
6.6. 演習問題：NVSSカタログのデータベース化

2021年12⽉21―22⽇

データベース講習会

vデータベースの構造51

データベースクラスタ

テーブル空間1

データベース1
データベース2

データベース3

テーブル1

テーブル2

テーブル2

インデックス1

インデックス2

インデックス2• データベースクラスタ– PostgreSQLサーバの全情報が格納されたディレクトリ。

• データベース– オブジェクト（テーブル、インデックス等）の集合に名前を付けたもの。
– 物理的にはオブジェクトファイルが格納されたディレクトリ（data/base/）。
– データベースを跨いでのデータのやりとりはできない。

• テーブル•どのDBに対応しているのかは
SELECT * FROM pg_database;
で確認可能– データを格納する表。
– 物理的にはデータが記述されたファイル群。
テーブル空間– データベースやテーブルを格納できるディレクトリ。
– データベースクラスタ外にデータベースやテーブルを配置可能。

2021年12⽉21―22⽇

データベース講習会

v作成するデータベースの構造
データベース
クラスタ

52

テーブル空間「tscat」
データベース「cat」
テーブル「nvss」

テーブル「pgc2003」

インデックス

インデックス• PCに外付けHDDを備えた個⼈環境にデータベースを構築することを想定。
• ディレクトリ「/hdd/」以下にテーブル空間を作成し、テーブル空間内にデータベースを作成。

2021年12⽉21―22⽇

データベース講習会

6.2. テーブル空間の作成

53

vCREATE TABLESPACE⽂の書式
CREATE TABLESPACE <テーブル空間名>
OWNER <ロール名>
LOCATION <テーブル空間を作成する場所のパス>;v【実習】 テーブル空間の作成
n ディレクトリ「/hdd/」以下にロール「dbr」が所有者であるテーブル空間「tscat」を作成
1. ディレクトリ「/hdd/」の確認。
[schoolXX]$ ls -ld /hdd
drwx------. 2 postgres postgres

6 11月 24 16:41 hdd• テーブル空間⽤ディレクトリは、Linuxユーザ「postgres」が所有していなければならない。

2021年12⽉21―22⽇

データベース講習会

2. ロール「postgres」でデータベース「postgres」に接続。

54[schoolXX]$ psql -U postgres• ロール「dbr」にはテーブル空間の作成権限がない。
3. テーブル空間を作成。
postgres=# CREATE TABLESPACE tscat
OWNER dbr
LOCATION ‘/hdd’;
4. 作成したテーブル空間をpsqlメタコマンド「\db」で確認。
postgres=# ¥db
postgres=# ¥qv テーブル空間の削除⽅法
postgres=# DROP TABLESPACE tscat;• テーブル空間を所有するロールでもテーブル空間を削除可能。
• テーブル空間内にデータベースオブジェクトが存在する場合は削除できない。

2021年12⽉21―22⽇

データベース講習会

6.3. データベースの作成

55

vCREATE DATABASE⽂の書式
CREATE DATABASE <データベース名>
OWNER <ロール名>[TABLESPACE <テーブル空間名>];v【実習】 データベースの作成
n ロール「dbr」が所有するデータベース「cat」をテーブル空間「tscat」内に作成
1. ロール「postgres」でデータベース「postgres」に接続。
[schoolXX]$ psql -U postgres• ロール「dbr」にはデータベースの作成権限がない。

2021年12⽉21―22⽇

データベース講習会

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

56

postgres=# CREATE DATABASE cat
OWNER dbr
TABLESPACE tscat;

3. 作成したデータベースをpsqlメタコマンド「\l」で確認。
postgres=# ¥lpostgres=# ¥q
4. ロール「dbr」によるデータベースへの接続確認。
[schoolXX]$ psql -U dbr cat
cat=> ¥q
5. ディレクトリ「/hdd/」の確認。
[schoolXX]$ su postgres
bash-4.4$ ls /hdd

v データベースの削除⽅法
postgres=# DROP DATABASE cat;
• データベースを所有するロールでもデータベースを削除可能。
2021年12⽉21―22⽇

データベース講習会

6.4. テーブルの作成

57

v天体カタログ「PGC2003」を収めるテーブルの作
成とデータの登録を実施
v6.4節⽬次•••••

6.4.1. テーブルの構造
6.4.2. PostgreSQLがサポートするデータ型
6.4.3. PGC2003
6.4.4. テーブルの作成
6.4.5. データの登録

2021年12⽉21―22⽇

データベース講習会

58

6.4.1. テーブルの構造v リレーショナルデータベースのテーブルは⾏（row）
と列（column）から構成される• ⾏（row）– データベースにおけるデータそのもの。

• 列（column）– 列毎にデータ型が定義され、データ型と異なる値は⼊⼒できない。
– 列毎に制約を定義でき、制約に違反した値は⼊⼒できない。

• ⾏と列をRecordやFieldと⾔う場合もあるが定義が曖昧なので個⼈的に⾮推奨
columnrow

2021年12⽉21―22⽇

id

ra

dc

mag

1

83.625+22.0168.4

2

323.375-0.8166.3

3

205.550+31.716

6.3
データベース講習会

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

59

v PostgreSQLは以下のデータ型をサポート
•
•
•
•
•
•
•
•
•
•
•
•
•

数値データ型
⽂字データ型
⽇付・時刻データ型
ブーリアン型
幾何データ型
ネットワークアドレスデータ型
ビット列データ型
通貨データ型
疑似データ型
バイナリ列データ型
⽂字列検索型
XML型
Range型

2021年12⽉21―22⽇

データベース講習会

60

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

格納サイズ

説明

範囲

SMALLINT

2バイト

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

INTEGER

4バイト

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

BIGINT

8バイト

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

REAL

4バイト

単精度浮動小数点数型

6桁精度 -1E37から+1E+37

DOUBLE PRECISION

8バイト

倍精度浮動小数点数型

15桁精度 -1E308から+1E308

NUMERIC、
DECIMAL

可変長

任意精度固定小数点数型

小数点より上は131072桁まで、小数点より下は16383桁まで• SMALLINT、INTEGER、BIGINT– 必要に応じて使い分ける。

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

• NUMERIC[(p,s)]、DECIMAL[(p,s)]– DECIMALはNUMERICの別名。
– 精度（全体の桁数）と⼩数部の桁数が指定可能で、正確な計算が可能。
– 整数型及び浮動⼩数点数型に対し、計算が⾮常に遅い。
2021年12⽉21―22⽇

データベース講習会

61

v⽂字データ型
データ型名

格納文字数

説明

TEXT

無制限

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

CHARACTER VARYING(N)、VARCHAR(N)N文字以下最大N文字の可変長文字列型。

CHARACTER(N)、CHAR(N)

N文字

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

• CHARACTER VARYING(N)、VARCHAR(N)– VARCHARはCHARACTER VARYINGの別名。
– 最⼤n⽂字まで⽂字列を格納できる。
– PostgreSQL内部ではTEXT型と区別がなく、データ⼊⼒時に⽂字列⻑を確認する点だけが異なる。

• CHARACTER(N)、CHAR(N)– CHARはCHARACTERの別名。
– 最⼤n⽂字までの⽂字列を格納できる。
– ⼊⼒⽂字数がN⽂字に満たない場合は、残りの部分が空⽩⽂字で埋められる。
– 上記のデータ型よりも処理速度が遅く、理由がない限り使う必要はない。
2021年12⽉21―22⽇

データベース講習会

62v⽇付・時刻データ型
データ型名

格納サイズ

説明

精度

TIMESTAMP [(P)] [WITH TIME ZONE]

8バイト

YYYY-MM-DD hh:mm:ss [+/-hh]

1マイクロ秒

DATE

4バイト

YYYY-MM-DD

1日

TIME [(P)]8バイト

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

• TIMESTAMP、DATE、TIME– それぞれ「年⽉⽇時分秒」、「年⽉⽇」、「時分秒」のデータを格納可能。

• WITH TIME ZONE オプション– ⽇時をUTCで保存。
– ⽇時の⼊出⼒時に時間帯を指定可能。
– 時間帯を指定せずに⽇時を⼊⼒した場合、postgresql.confで設定された時間
帯を参照して⼊⼒値をUTCに変換して格納。
– 時間帯を指定せずに⽇時を出⼒した場合、格納されているUTC⽇時を
postgresql.confで設定された時間帯に変換して出⼒2021年12⽉21―22⽇

データベース講習会

63

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

v 講習会⽤PGC2003• 在 り 処 ： 「 /home/schoolXX/DB/catalogue/pgc2003.dat 」 。
• データベース化のため以下の加⼯を施している。（これが⼀番⼤変な作業）– ⾚経を時⾓から度に変換。
– ⾚緯を度分秒から度に変換。
– 直径、楕円率、位置⾓が不明の場合の値「9.99」及び「999」を空⽂字に置換。
– 区切り⽂字をʼ,ʼに変更。
2021年12⽉21―22⽇

データベース講習会

64

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」の略なので使わない。
2021年12⽉21―22⽇

データベース講習会

65

6.4.4. テーブルの作成
vCREATE TABLE⽂の書式
CREATE TABLE <テーブル名> (<列名1><データ型>[NOT NULL],<列名2><データ型>[NOT NULL],
︙
CONSTRAINT <制約名> PRIMARY KEY (<列名>));• 列名に⼤⽂字と「+*/-.ʼ:」は使わない– ⼤⽂字は⼩⽂字として解釈されてしまう。
– 列名を⼆重引⽤符で囲めば⼤⽂字と記号を使えるが、検索時も⼆重引⽤符をつけて列名を指定しなければならない。

• 主キー制約（PRIMARY KEY）
– テーブル内の各⾏を⼀意に識別できる列に対し設定する制約。
– 重複データの⼊⼒を拒否。
– 1テーブルに1つしか設定できない。
– 主キー制約を課した列にはインデックスと⾮NULL制約が設定される。

• ⾮NULL制約（NOT NULL）– NULL値が存在するデータの⼊⼒を拒否。
2021年12⽉21―22⽇

データベース講習会

66v【実習】 テーブル「pgc2003」の作成
n SQLを記述したファイルを作成しファイルをPostgreSQLに読み込ませる
1. SQLを記述したファイルを「/home/schoolXX/DB/sql」以下に作成。
[schoolXX]$ cd /home/shoolXX/DB/sql[schoolXX]$ emacs CreateTablePgc2003.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));
2021年12⽉21―22⽇

データベース講習会

67

3. 作成したSQLを実⾏。
[schoolXX]$ psql -U dbr cat -f CreateTablePgc2003.sql• -f：ファイルに記述されたSQLを実⾏するオプション。
4. 作成したテーブルをpsqlメタコマンド「\d」で確認。
[shoolXX]$ psql -U dbr cat
cat=> ¥dcat=> ¥d pgc2003
cat=> ¥qv テーブルの削除⽅法
cat=# DROP TABLE pgc2003;• データベースを所有するロールでもテーブルを削除可能。

2021年12⽉21―22⽇

データベース講習会

68

6.4.5. データの登録
v\copyコマンドの書式¥copy <テーブル名> FROM <ファイル名> [DELIMITER ‘<区切り文字>’] [NULL AS ‘<NULL値を表す文字>’]• ファイルの内容をテーブルへコピーするpsqlメタコマンド。
• デフォルトの区切り⽂字は「\t」（タブスペース）。
• デフォルトのNULL値を表す⽂字は「\N」。

v【実習】 データの登録
1. ロール「dbr」でデータベース「cat」に接続。
[schoolXX]$ psql -U dbr cat
2. /home/schoolXX/DB/catalogue/pgc2003.dat」をコピー。
cat=> ¥copy pgc2003 FROM /home/schoolXX/DB/catalogue/pgc
2003.dat DELIMITER ‘,’ NULL AS ‘’• 983261⾏コピーされる。
2021年12⽉21―22⽇

データベース講習会

3. テーブルの確認。

69

cat=> SELECT COUNT(*) FROM pgc2003;
cat=> SELECT * FROM pgc2003 LIMIT 10;
cat=> ¥qv データの削除⽅法
cat=# DELETE FROM pgc2003;• データベースを所有するロールでもデータを削除可能。

2021年12⽉21―22⽇

データベース講習会

v【コラム】「\copy」と「COPY」

70• ￥copy– psqlメタコマンド。
– クライアント側にあるファイルをデータベースにコピー。

• COPY– SQLコマンド。
– サーバ側にあるファイルをデータベースにコピー。
– サーバ側のファイルはLinuxユーザ「postgres」が読み込み可能である必要がある。
– ファイルは絶対パスで指定しなければならない。

2021年12⽉21―22⽇

データベース講習会

71

6.5. インデックスの作成
vテーブル「pgc2003」の列にインデックスを設定
v6.5節⽬次• 6.5.1. インデックスの仕組み• 6.5.2. インデックスの作成と検索、速度の⽐較

2021年12⽉21―22⽇

データベース講習会

6.5.1. インデックスの仕組み

72

vインデックス（索引）• ⽬的のデータまでの検索⼿順を最⼩化するためのデータ構造とその仕組み。
• インデックスを作成することでRDBMSは本来の性能を発揮し、インデックスがない場合と⽐べ格段に⾼速の検索を実現。
• 主キーを設定した列には⾃動的にインデックスが作成されるが、その他の列は必要に応じて作成する必要がある。
• PostgreSQLではデフォルトでB+TREEインデックスという⽅式が使⽤される。

2021年12⽉21―22⽇

データベース講習会

vB+TREEインデックス

73• 平衡探索⽊型のデータ構造を持つインデックス。
• ある順番でソート可能なデータに対する等価性や範囲の問い合わせを扱うことができる。

v例：1から10までのデータから3を探す場合。
シーケンシャルスキャン

B+TREEインデックススキャン

1
X<5

2
3
︙
8
9
10

2021年12⽉21―22⽇

X<3

1

2

3

X≧5

3回の計算
（最大~lnN回）

3

3回の計算（最大N回）

5

X≧3

X<7

4

5

7

9

7≦X<9

6

7

8

X≧9

9

10

データベース講習会

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

74

vCREATE INDEX⽂の書式
CREATE INDEX [<インデックス名>] ON <テーブル名（列名）>;• インデックス名を省略すると⾃動的に名前が設定される。

v【実習】 インデックスがない時の検索時間の測定
n テーブル「pgc2003」から⾚緯-5度から+5度の天体を検索してその検索時間を測定
1. ロール「dbr」でデータベース「cat」に接続。
[schoolXX]$ psql -U dbr cat
2. psqlメタコマンド「\timing」を実⾏し、実⾏時間が計測されるようにする。
cat=> ¥timing
タイミングは on です。
2021年12⽉21―22⽇

データベース講習会

3. 以下のSQLを3回以上実⾏し、実⾏時間の平均値を調べる。

75

cat=> SELECT count(*)
FROM pgc2003
WHERE dc BETWEEN -5 and 5;v【実習】 インデックスの設定
n テーブル「pgc2003」の列「ra」と「dc」にインデックスを設定
1. インデックスを作成。
cat=> CREATE INDEX ON pgc2003(ra);cat=> CREATE INDEX ON pgc2003(dc);
2. インデックスの確認。
cat=> ¥d pgc2003
3. 以下のSQLを3回以上実⾏し、実⾏時間の平均値を調べる。
cat=> SELECT count(*)
FROM pgc2003
WHERE dc BETWEEN -5 and 5;
cat=> ¥q
2021年12⽉21―22⽇

データベース講習会

v【実習】インデックスの追加

76

n テーブル「pgc2003」の残りのカラムにインデックスを設定
1. インデックスを作成。
cat=> CREATE INDEX ON pgc2003(otype);
cat=> CREATE INDEX ON pgc2003(mtype);
cat=> CREATE INDEX ON pgc2003(logd25);
cat=> CREATE INDEX ON pgc2003(e_logd25);
cat=> CREATE INDEX ON pgc2003(logr25);
cat=> CREATE INDEX ON pgc2003(e_logr25);
cat=> CREATE INDEX ON pgc2003(pa);cat=> CREATE INDEX ON pgc2003(e_pa);
cat=> CREATE INDEX ON pgc2003(o_anames);
cat=> CREATE INDEX ON pgc2003(anames);• インデックスを設定するとデータの更新処理が遅くなるため、本来は検索に使⽤する列のみに設定するのが正しい。

2021年12⽉21―22⽇

データベース講習会

6.6. 演習問題：NVSSカタログのデータベース化

77v The NRAO VLA Sky Survey (Condon+1998)•••

Very Large Array (VLA）を使って、⾚緯-40度以北の天域を周波数
1.4GHzでサーベイした結果をまとめたカタログ。
2.5 mJy 以上の電波源を約177万の収録。
各電波源の座標、フラックス密度、⻑軸⻑、短軸⻑、位置⾓、偏波強度等を掲載。

v 講習会⽤NVSSカタログ••

在り処：/home/schoolXX/DB/catalogue/nvss.dat
データベース化のため、以下の加⼯を⾏っている。
– ⾚経を時⾓から度に変換。
– ⾚緯を度分秒⾓から度に変換。
– Field列、Xpos列、Ypos列の削除。
> サーベイデータのイメージ番号と、天体のXYピクセル座標。

– 区切り⽂字をʼ,ʼに変更。
2021年12⽉21―22⽇

データベース講習会

78

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

単位

id

データ型

Null値

説明

TEXT

無

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

radegreeDOUBLE 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

無電波源の短軸長。

paarcdeg

REAL

有電波源の長軸の位置角。

e_majaxis

arcsecREAL

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

e_minaxis

arcsecREAL

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

e_pa

arcdeg

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

有偏波角の誤差。

2021年12⽉21―22⽇

データベース講習会

v【演習】NVSSカタログのデータベース化

79

1. テーブル「nvss」を作成せよ。
––

参：P66。
ファイル「~/DB/sql/CreateTableNvss.sql」を作成。

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

参：P68。

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

2021年12⽉21―22⽇

参：P75。

データベース講習会

v 【解答例】テーブル「nvss」の作成とデータの登録80

1. テーブル「nvss」を作成せよ。
[schoolXX]$ emacs /home/schoolXX/DB/sql/CreateTableNvss.sql
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));

2021年12⽉21―22⽇

データベース講習会

81

2. テーブル「nvss」にNVSSカタログをコピーせよ。
cat=> ¥copy nvss FROM /home/schoolXX/DB/catalogue/nvss.d
at DELIMITER ‘,’ NULL AS ‘’• 1773484⾏コピーされる。

3. テーブル「nvss」の全列にインデックスを設定せよ。
cat=> CREATE INDEX ON nvss(hoge);• psqlメタコマンド「\d nvss」でインデックスを作成できたか確認する。

2021年12⽉21―22⽇

データベース講習会

v【コラム】データ操作コマンド82• PostgreSQLには3つのデータ操作コマンドが存在• \copy (COPY)– ファイルからデータをテーブルにコピーするコマンド。
– ⼤量のデータを⾼速にコピーできる。
– ファイルからしかコピーできない。

• INSERT– テーブルに⾏を挿⼊するコマンド。
– 値を明⽰して挿⼊、或いは他テーブルの値をSQLで取得して挿⼊可能。
– 1⾏ずつ処理を⾏うため遅い。

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

2021年12⽉21―22⽇

データベース講習会

7. psqlの使⽤⽅法

2021年12⽉21―22⽇

データベース講習会

7.1. 概要84vpsql
• PostgreSQLの対話型インターフェース。
• ⼊⼒されたSQLをPostgreSQLサーバに送信し、その実⾏結果を受け取り表⽰。

v7章⽬次
•
•
•
•

7.2. Linuxコマンド「psql」
7.3. SQL
7.4. psqlメタコマンド
7.5. ログインパスワードの⼊⼒省略設定

2021年12⽉21―22⽇

データベース講習会

7.2. Linuxコマンド「psql」

85

vpsqlコマンドの書式$ psql [<オプション>] -U <ロール名> <データベース名>• 接続関連オプション– -h <ホスト名|IPアドレス>：接続先サーバを指定。
– -p <ポート番号>：PostgreSQLサーバのポート番号を指定。

• 問い合わせ結果の出⼒形式変更オプション– -t：列名や結果⾏数の出⼒を抑制。
– -A：位置揃えなしの出⼒モードに切り替え。
– -F 区切り⽂字：フィールドの区切り⽂字を指定。「-A -F,」でCSV形式。
– -H：HTML形式で出⼒。
– -P format=latex：LaTeX形式で出⼒。

• コマンドラインからのSQL実⾏時に使⽤するオプション– -c “<SQL>”：指定したSQLを実⾏。
– -f “<ファイル名>”：ファイルに記述されたSQLを実⾏。
– -o “<ファイル名>”：SQLの実⾏結果をファイルに出⼒。
– -L “<ファイル名>”：SQLの実⾏結果をファイルと標準出⼒の両⽅に出⼒。
2021年12⽉21―22⽇

データベース講習会

v実⾏例

86

n ホスト名とポート名を明⽰してデータベースに接続[schoolXX]$ psql -h localhost -p 5432 -U dbr cat
cat=> ¥q
• 5432：PostgreSQLサーバのデフォルトのポート番号。
n コマンドラインからSQLを実⾏し結果をCSV形式でファイルに出⼒[schoolXX]$ psql -t -A -F, -U dbr cat-o ~/DB/psql_result.txt-c “SELECT * FROM pgc2003 LIMIT 10;”[schoolXX]$ cat ~/DB/psql_result.txt

2021年12⽉21―22⽇

データベース講習会

87

7.3. SQLvSQLの書式
•
•
•
•
•
•

セミコロン「;」がコマンドの終端。
⽂字列を除き⼤⽂字と⼩⽂字は区別されない。
⽂字列は単⼀引⽤符「ʼ」で囲んで⼊⼒。
数値計算が可能。
単⼀⾏のコメントアウトは⼆重ダッシュ記号「--」。
複数⾏のコメントアウトは「/* コメント */」。

cat=> SELECT ‘Hello world!’; --文字列入力の例?column?
-------------Hello world!
cat=> SELECT 1+2*(3+4); /*数値計算の例*/?column?
-------------15
2021年12⽉21―22⽇

データベース講習会

88

7.4. psqlメタコマンド
vpsqlメタコマンド• psql⾃⾝が実⾏するコマンド。
• バックスラッシュから始まり、改⾏がコマンドの終端。
• ここではよく使われるメタコマンドを紹介。

vバッチファイル実⾏⽤メタコマンド
メタコマンド説明¥i “<ファイル名>”

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

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

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

2021年12⽉21―22⽇

データベース講習会

89

v情報表⽰⽤メタコマンド
メタコマンド説明¥db

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

¥du

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

¥l

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

¥d

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

¥d テーブル名

テーブルの情報を表示。

¥dS

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

¥di

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

¥df

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

¥dfS

全関数の一覧を表示。

¥dT

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

¥dTS

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

2021年12⽉21―22⽇

データベース講習会

90

v表⽰変更⽤メタコマンド
メタコマンド説明¥x

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

¥t

結果のみ表示へ切り替え。

¥a

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

¥f ‘<区切り文字>’

出力区切り文字を設定。¥aを設定しないと無効。

¥H

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

¥pset format ‘latex’

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

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

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

¥h <SQLコマンド>

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

¥s

入力履歴を表示。

¥timing

SQLの実行時間を計測。

¥cd ‘<ディレクトリ名>’

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

¥! <LINUXコマンド>

LINUXコマンドを実行。例：¥! ls

2021年12⽉21―22⽇

データベース講習会

7.5. ログインパスワードの⼊⼒省略
設定

91

vPostgreSQLサーバ接続時に求められるパスワードの⼊⼒省略設定を実施• データベースへの接続情報を記述したファイルをホーム
ディレクトリに配置。
• ⼊⼒省略設定はクライアント側の環境でおこなう。
– すなわちパスワード認証が有効な外部のデータベースに接続する場合でもパスワード⼊⼒を省略できる。

2021年12⽉21―22⽇

データベース講習会

92

v「.pgpass」の書式<ホスト名>:<ポート番号>:<データベース名>:<ロール名>:<パスワード>• アクセス権は「600」（所有者のみ読み書き可）。

v【実習】 パスワードの省略設定
1. ホームディレクトリに「.pgpass」を作成。
[schoolXX]$ cd[schoolXX]$ emacs .pgpass
localhost:5432:cat:dbr:dbr
2. アクセス権の変更。
[schooolXX]$ chmod 600 .pgpass
3. 接続の確認。
[schoolXX]$ psql -U dbr cat
cat=> ¥q

2021年12⽉21―22⽇

データベース講習会

8. テーブルへの問い合わせ

2021年12⽉21―22⽇

データベース講習会

8.1. 概要

94

vテーブルへの問い合わせコマンドであるSELECT⽂の使⽤⽅法を学習
v8章⽬次
•
•
•
•
•
•

8.2. SELECT⽂の基本形
8.3. 検索条件の指定
8.4. 列に対する演算
8.5. 副問い合わせ
8.6. テーブルの結合
8.7. 遅くならないWHERE句の書き⽅

2021年12⽉21―22⽇

データベース講習会

8.2. SELECT⽂の基本形

95

vSELECT⽂• テーブルから⾏を検索し、結果を表⽰するSQLコマンド。
• SELECT⽂には様々な「句」が存在し、「句」を組み合わせることで様々な検索実現。

v8.2節⽬次•••••

8.2.1. 基本形
8.2.2. 検索結果の出⼒件数の指定
8.2.3. 検索結果の並び替え
8.2.4. 列名・テーブル名の別名の定義
8.2.5. 演習問題

2021年12⽉21―22⽇

データベース講習会

8.2.1. 基本形

96vSELECT⽂の書式
SELECT <列名1,列名2,...,列名N]|*>FROM <テーブル名>;v実⾏例
n テーブル「pgc2003」から列「id, ra, dc」を検索
cat=> SELECT id,ra,dc
FROM pgc2003;
n テーブル「pgc2003」から全列を検索
cat=> SELECT *FROM pgc2003;• 検索結果画⾯の操作− 1⾏送り：「Enter」。
− ページ単位送り：「Space」。
− 検索結果の表⽰終了：「q」。
2021年12⽉21―22⽇

データベース講習会

97

8.2.2. 検索結果の出⼒件数の指定
vLIMIT句の書式
SELECT <列名>
FROM <テーブル名>
LIMIT <出力件数>;• LIMIT句の位置はSELECT⽂の⽂末。

v実⾏例
n テーブルの先頭から20⾏を出⼒
cat=> SELECT id,ra,dc
FROM pgc2003
LIMIT 20;2021年12⽉21―22⽇

データベース講習会

8.2.3. 検索結果の並び替え

98

vORDER BY句の書式
SELECT <列名>
FROM <テーブル名>
ORDER BY <列名> [ASC|DESC];• ASC：昇順で並び替え（デフォルト）。
• DESC：降順で並び替え。

v 実行例
n 検索結果を列「id」が⼩さい順に並び替えて出⼒
cat=> SELECT id,ra,dc
FROM pgc2003
ORDER BY id
LIMIT 20;2021年12⽉21―22⽇

データベース講習会

8.2.4. 列名・テーブル名の別名の定義

99

v別名の定義⽅法
SELECT <列名> AS <別名>
FROM <テーブル名> AS <別名>;• 列の別名はORDER BY句とGROUP BY句でのみ参照可能。

v実⾏例
n テーブル「pgc2003」と列「id, ra, dc」に別名を定義
cat=> SELECT p.id AS pgc_number,p.ra AS sekkei,
p.dc AS sekii
FROM pgc2003 AS p
LIMIT 20;
• 「テーブル名.列名」は列名の厳密な指定⽅法。
• テーブルに別名を定義した場合は、列名を厳密に指定しなければならない場合が多い。
2021年12⽉21―22⽇

データベース講習会

8.2.5. 演習問題

100v【演習】 以下の問い合わせを実施せよ
1. 【s825ex1.sql】 テ ー ブ ル 「 nvss 」 の 列 「 id, ra, dc,
flux」を、列「flux」が⼤きい順に20件表⽰せよ。

2021年12⽉21―22⽇

データベース講習会

v【解答例】以下の問い合わせを実施せよ

101

1. 【s825ex1.sql】 テ ー ブ ル 「 nvss 」 の 列 「 id, ra,dc ,flux」を、列「flux」が⼤きい順に20件表⽰せよ。
cat=> SELECT id,ra,dc,flux
FROM nvss
ORDER BY flux DESC
LIMIT 20;2021年12⽉21―22⽇

データベース講習会

102

8.3. 検索条件の指定
v 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. LIKE/SIMILAR TO演算⼦
8.3.6. 演習問題

2021年12⽉21―22⽇

データベース講習会

103

8.3.1. ⽐較演算⼦v⽐較演算⼦⼀覧
演算子

説明

<

小なり

>

大なり

<=

以下

>=

以上

=

等しい

<> or !=

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

v実⾏例
n 列「id」がPGC0077777の天体を検索
cat=> SELECT id,ra,dc
FROM pgc2003
WHERE id=‘PGC0077777’;• ⽂字列はシングルクォーテーションで囲む。
2021年12⽉21―22⽇

データベース講習会

104

8.3.2. IS演算⼦
vIS演算⼦の書式
SELECT <列名>
FROM <テーブル名>
WHERE <列名> IS [NOT] NULL;• NULL値を含む/含まない⾏を検索。
• ⽐較演算⼦でNULL値の⽐較演算をおこなうと戻り値として「NULL」を返すためNULL値の検索ができない。

v実⾏例
n 列「mtype」がNULLである⾏を⽐較演算⼦「=」を使って検索
cat=> SELECT id,ra,dc,mtype
FROM pgc2003
WHERE mtype=NULL
LIMIT 20;n 列「mtype」がNULLである⾏をIS演算⼦を使って検索。
cat=> SELECT id,ra,dc,mtype
FROM pgc2003
WHERE mtype IS NULL
LIMIT 20;2021年12⽉21―22⽇

データベース講習会

105

8.3.3. 論理演算⼦
v論理演算⼦⼀覧
演算子

説明

AND

論理積

OR

論理和

NOT

否定

v実⾏例
n ⾚経が359度から1度かつ列「mtype」がNULLではない天体を検索
cat=> SELECT id,ra,dc,mtype
FROM pgc2003
WHERE (ra>359 OR ra<1) AND(mtype IS NOT NULL)ORDER BY ra;

2021年12⽉21―22⽇

データベース講習会

106

8.3.4. BETWEEN句vBETWEEN句の書式
SELECT <列名>
FROM <テーブル名>
WHERE <列名> [NOT] BETWEEN <a> AND <b>;
• 列の値がa以上b以下の⾏を検索。

v実⾏例
n ⾚緯が-0.1度以上+0.1度以下の天体を検索
cat=> SELECT id,ra,dc
FROM pgc2003
WHERE dc BETWEEN -0.1 AND 0.1
ORDER BY dc;
n ⾚経が359度から1度かつ列「mtype」がNULLではない天体を検索
cat=> SELECT id,ra,dc,mtype
FROM pgc2003
WHERE (ra NOT BETWEEN 1 AND 359) AND(mtype IS NOT NULL)ORDER BY ra;
2021年12⽉21―22⽇

データベース講習会

107

8.3.5. LIKE/SIMILAR TO演算⼦
vLIKE/SIMILAR TO演算⼦の書式
SELECT <列名>
FROM <テーブル名>
WHERE <列名> [NOT] LIKE|SIMILAR TO ‘<パターン>’;

v使⽤可能な正規表現
演算子

%
_
*
+
[文字列]
[^文字列]|(パターン)

説明

0文字以上の文字に一致。
任意の1文字に一致。
直前の文字の0回以上の繰り返し。
直前の文字の1回以上の繰り返す。
指定した文字列、文字範囲に含まれるあらゆる文字に一致。
指定した文字列、文字範囲に含まれないあらゆる文字に一致。
二者択一。
パターンのグループ化。

LIKE
◯
◯
☓
☓
☓
☓
☓
☓

SIMILAR TO
◯
◯
◯
◯
◯
◯
◯
◯

• SIMILAR TOはPOSIX準拠の正規表現を使⽤できるが、正規表現に対す
る脆弱性攻撃に気を付ける必要がある。
2021年12⽉21―22⽇

データベース講習会

v実⾏例

108

n LIKE演算⼦を使って「NGC5194」を検索
cat=> SELECT id,ra,dc,anames
FROM pgc2003
WHERE anames LIKE ‘%NGC5194%’;• 列「anames」にはスペース区切りで複数の別名が羅列されているため、「%」で「NGC5194」前後の⽂字列を表現。
n SIMILAR TO演算⼦を使って「NGC1~9」を検索
cat=> SELECT id,ra,dc,anames
FROM pgc2003
WHERE anames SIMILAR TO ‘%NGC[1-9]_%’;• _：空⽩。

2021年12⽉21―22⽇

データベース講習会

8.3.6. 演習問題

109v【演習】 以下の問い合わせを実施せよ
1. 【s836ex1.sql】テーブル「pgc2003」から⾚経が（202<=ra<=204）かつ⾚緯が（46<=dc<=48）である天体を検索し、列「id,ra,dc,anames」を「anames」がアルファベット順となるように表⽰せよ。
2. 【s836ex2.sql】上記の問い合わせに列「anames」がNULLでないという条件を追加し検索せよ。

2021年12⽉21―22⽇

データベース講習会

110v【解答例】 以下の問い合わせを実施せよ
1. 【s836ex1.sql】テーブル「pgc2003」から⾚経が（202<=ra<=204）かつ⾚緯が（46<=dc<=48）である天体を検索し、列「id,ra,dc,anames」を「anames」がアルファベット順となるように表⽰せよ。
cat=> SELECT id,ra,dc,anames
FROM pgc2003
WHERE (ra BETWEEN 202 AND 204) AND(dc BETWEEN 46 AND 48)
ORDER BY anames;

2. 【s836ex2.sql】上記の問い合わせに「anames」がN
ULLでないという条件を追加し検索せよ。
cat=> SELECT id,ra,dc,anames
FROM pgc2003
WHERE (ra BETWEEN 202 AND 204) AND(dc BETWEEN 46 AND 48) AND(anames IS NOT NULL)ORDER BY anames;

2021年12⽉21―22⽇

データベース講習会

8.4. 列に対する演算111v演算⼦と関数• PostgreSQLには様々な演算⼦と関数が⽤意されている。
• SELECT⽂中で列に対し演算を⾏うことで、演算結果を検索結果として得ることができる。

v8.4節⽬次
•
•
•
•
•
•

8.4.1. 型変換関数
8.4.2. 算術演算⼦
8.4.3. 算術関数
8.4.4. 集約・統計関数
8.4.5. ⽂字列演算⼦・関数
8.4.6. 演習問題

2021年12⽉21―22⽇

データベース講習会

112

8.4.1. 型変換関数
v型変換関数⼀覧
関数

説明

例

結果

CAST(データ AS データ型）

データを指定したデータ型に変換。

CAST(1.2 AS INTEGER)

1

データ :: データ型

PostgreSQL独自の記法(演算子?)。

1.2 :: INTEGER

1v実⾏例
n 異なるデータ型の⽐較演算
cat=> SELECT id,polflux
FROM nvss
WHERE polflux=CAST(999.9 AS REAL);
cat=> SELECT id,polflux
FROM nvss
WHERE polflux=999.9;
cat=> SELECT pg_typeof(999.9);
cat=> SELECT 999.9::REAL=999.9::NUMERIC AS logical;• REAL型とNUMERIC型の999.9は異なる値。
2021年12⽉21―22⽇

データベース講習会

113

v実⾏例
n ⼊⼒値のデータ型
cat=> SELECT 3, 3.0, 3^3, 3.0^3;
3 | 3.0 | 27 | 27.0000000000000000
cat=> SELECT pg_typeof(3),pg_typeof(3.0),
pg_typeof(3^3),
pg_typeof(3.0^3);
integer | numeric | double precision | numeric• 字句解析をおこない⼊⼒引数にデータ型を割り当て（数値はINTEGERかNUMERIC）。
• 演算⼦ごとに決められたデータ型に型変換して演算。
• 演算⼦「^」の引数型と戻り値型：− DOUBLE PRECISION^DOUBLE PRECISION=>DOUBLE PRECISION− NUMERIC^NUMERIC=>NUMERIC

2021年12⽉21―22⽇

データベース講習会

114

8.4.2. 算術演算⼦
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

2021年12⽉21―22⽇

データベース講習会

115

v実⾏例
n 列「logd25」のlogを外してarcminに変換したものを値が⼤きい順に表⽰
cat=> SELECT id,(10^logd25)*0.1 AS d,anames
FROM pgc2003
WHERE logd25 IS NOT NULLORDER BY d DESC
LIMIT 20;
• 演算結果列に別名「d」を付与し、ORDER BY句で指定。
• 演算結果列は無名「?column?」なため、別名を付与しないとORDER BY句
で列名を指定ができない。

2021年12⽉21―22⽇

データベース講習会

116

8.4.3. 算術関数
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

2021年12⽉21―22⽇ ※DP：DOUBLE PRECISION

説明
絶対値
立方根
切り上げ
ラジアンに対応する度
指数
切り下げ
自然対数
常用対数
Bを底としたXの対数
Y/Xの剰余
円周率
AのB乗
AのB乗
度に対応するラジアン
0.0-1.0の乱数値
四捨五入
Sの桁で四捨五入RANDOM()で使用する種を設定
引数の符号
平方根
切り捨て
Vを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
1712.3

データベース講習会

117v三⾓関数⼀覧関数

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実⾏例
n ⾚経と⾚緯の⼩数点以下を切り捨て
cat=> SELECT id,
TRUNC(CAST(ra AS NUMERIC),0) AS tra,
TRUNC(CAST(dc AS NUMERIC),0) AS tdc
FROM pgc2003
LIMIT 20;
• TRUNC(V NUMERIC,S INTEGER)：VをSの桁で切り捨て。
• 列「ra」と「dc」のデータ型はDOUBLE PRECISION型であるため、CAST
関数でNUMERICに変更しないとエラーが発⽣する。
2021年12⽉21―22⽇

データベース講習会

119

8.4.4. 集約・統計関数
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（式）2021年12⽉21―22⽇

説明

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

120

v集約関数⼀覧
関数

AVG(X)
COUNT(*)
MAX(X)MIN(X)SUM(X)

戻り値型

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

説明

全ての入力値の平均値を返す
入力行の数
全ての入力値の中から最大値を返す
全ての入力値の中から最小値を返す
全ての入力値の和を返す• 複数の⼊⼒値から1つの結果を返す。
• WHERE句では使⽤できない。

v実⾏例
n テーブルのレコード数を算出
cat=> SELECT COUNT(*) FROM pgc2003;
n 列「logd25」の最⼤値を検索
cat=> SELECT MAX(logd25) FROM pgc2003;
n 列「logd25」の最⼤値がどのIDなのか知りたいとき（間違い例）
cat=> SELECT id FROM pgc2003 WHERE logd25=MAX(logd25);
2021年12⽉21―22⽇

データベース講習会

121

vGROUP BY句とHAVING句の書式
SELECT <列名> FROM <テーブル名>
GROUP BY <列名>
[HAVING <条件式>];• GROUP BY句：同じ値を持つ⾏をグループ化（して集約・統計）。
• HAVING句：グループの中から条件式を満たす⾏を返す。

v実⾏例
n 列「mtype」をグループ化してタイプが「SB%」の天体数と各グループ内で最⼤の列「logd25」の値を表⽰
cat=> SELECT mtype,COUNT(*),max(logd25)
FROM pgc2003
GROUP BY mtype
HAVING mtype LIKE ‘SB%’;n ⾚経10度ごとにグループ化して天体数を表⽰
cat=> SELECT TRUNC(ra::NUMERIC,-1) AS tra,COUNT(*)
FROM pgc2003
WHERE mtype IS NOT NULLGROUP BY tra
ORDER BY tra;2021年12⽉21―22⽇

データベース講習会

122

8.4.5. ⽂字列演算⼦・関数
v⽂字列演算⼦・関数⼀覧
演算子・関数

説明

例

結果

||

文字列の結合ho || gehoge

CHAR_LENGTH(‘text’)

文字数を返すCHAR_LENGTH(’hoge’)

4

OVERLAY(‘text1’ PLACING ‘text2’ FROM i)

text1のi番目以降の文字をtext2で上書き

OVERLAY(‘hoge’ PLACING ‘pp’ FOR 2)

hppe

POSITION(‘text1’ IN ‘text2’)

text2からtexｔ1の文字列が始まる位置を返す

POSITION(‘hoge’ IN ‘og’)

2

SUBSTRING(‘text’ FROM i FOR j)

textのi番目の文字からj文字分取り出す

SUBSTRING(‘hoge’ FROM 3 FOR 2)

ge

SUBSTRING(‘test’ FROM ‘pattern’)

正規表現にマッチする部分文字列を返す。

SUBSTRING(‘hoge’ FROM ‘..$’)ge

TRIM(‘text1’ FROM ‘text2’)

text2からtext1の文字を取り除く

TRIM(‘he’ FROM ’hoge’)

og

v実⾏例
n 列「id」から「PGC」を削除
cat=> SELECT TRIM（’PGC’ FROM id)
FROM pgc2003
LIMIT 20;2021年12⽉21―22⽇

データベース講習会

8.4.6. 演習問題

123v【演習】 以下の問い合わせを実⾏せよ
1. 【s846ex1.sql】 テ ー ブ ル 「 nvss 」 か ら 「 flux 」 が1000mJy以上の電波源を検索しその総数を表⽰せよ。
2. 【s846ex2.sql】 テ ー ブ ル 「 nvss 」 を 検 索 し 、 列「id,x,y,z」を表⽰せよ。
−−−

2021年12⽉21―22⽇

x = cos(ra) * cos(dc)y = sin(ra) * cos(dc)z = sin(dc)

データベース講習会

124v【解答例】 以下の問い合わせを実施せよ
1. 【s846ex1.sql】テーブル「nvss」から「flux」が1000mJy以上の電波源を検索しその総数を表⽰せよ。
cat => SELECT COUNT(*)
FROM nvss
WHERE flux>=1000;

2. 【s846ex2.sql】テーブル「nvss」を検索し、列「id,x,y,z」を表⽰せよ。
cat => SELECT id,
COS(RADIANS(ra))*COS(RADIANS(dc)) AS x,
SIN(RADIANS(ra))*COS(RADIANS(dc)) AS y,
SIN(RADIANS(dc)) AS z
FROM nvss
LIMIT 20;2021年12⽉21―22⽇

データベース講習会

v【コラム】SELECT分の評価順序

125• 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句

v例
cat => SELECT TRUNC(ra::NUMERIC,-1) AS tra,COUNT(*)
FROM pgc2003
WHERE mtype IS NOT NULLGROUP BY tra
ORDER BY tra;
1.
2.
3.
4.
5.
2021年12⽉21―22⽇

FROM：テーブル「pgc2003」から、
WHERE：「mtype」がNULL値の⾏を除外し、
GROUP BY：⾚経が同じ値の⾏をグループ化し、SELECT：グループ毎にCOUNT(*)を計算し、ORDER BY：⾚経の値が⼩さい順に並び替える。
データベース講習会

8.5. 副問い合わせ

126

v副問い合わせ• SELECT⽂中でSELECT⽂を⽤いることで、より複雑な検索が可能になる。
• SELECT句中、FROM句中、WHERE句中でSELECT⽂を⽤いることができる。

v8.5節⽬次•••••

8.5.1. SELECT句での副問い合わせ
8.5.2. FROM句での副問い合わせ
8.5.3. WHERE句での副問い合わせ
8.5.4. 共通テーブル式
8.5.5. 演習問題

2021年12⽉21―22⽇

データベース講習会

8.5.1. SELECT句での副問い合わ
せ127

vSELECT句での副問い合わせ書式
SELECT (SELECT <列名>
FROM <テーブル名>
WHERE <条件式>) AS <別名>
FROM <テーブル名>;• 副問い合わせが返した⼀⾏⼀列の値を問い合わせ結果に結合して表⽰。

v実⾏例
n 各電波源のFluxが平均値よりも⼤きいか検査
cat=> SELECT id,flux,flux > (SELECT AVG(flux)
FROM nvss) AS logical
FROM nvss
LIMIT 20;2021年12⽉21―22⽇

データベース講習会

128

8.5.2. FROM句での副問い合わせ
vFROM句での副問い合わせ書式
SELECT <副問い合わせの別名.列名>
FROM (
SELECT <列名>
FROM <テーブル名>
WHERE <条件式>) AS <副問い合わせの別名>;• 副問い合わせが返した複数⾏複数列の結果に対して問い合わせをおこなう。
• 副問い合わせの結果に別名を指定しなければならない。
• 主SELECT⽂ではインデックス検索ができない。

v実⾏例
n 列「mtype」が「SB%」の天体の中から赤緯が±0.1度内の天体を検索
cat => SELECT t.id,t.ra,t.dc,t.mtype
FROM (
SELECT *FROM pgc2003
WHERE mtype LIKE ‘SB%’) AS t
WHERE t.dc BETWEEN -0.1 AND 0.1;
2021年12⽉21―22⽇

データベース講習会

129

8.5.3. WHERE句での副問い合わせvWHERE句での副問い合わせ• ⼀⾏⼀列の値を返す副問い合わせ• 複数⾏複数列の値を返す副問い合わせ• 相関副問い合わせv⼀⾏⼀列の値を返す副問い合わせ書式
SELECT <列名>
FROM <テーブル名>
WHERE <列名> 比較演算子 (SELECT <列名>
FROM <テーブル名>
WHERE <条件式>);• 副問い合わせが返した⼀⾏⼀列の値を⽐較条件として問い合わせをおこなう。
2021年12⽉21―22⽇

データベース講習会

130

v実⾏例
n 列「logd25」の最⼤値がどのIDなのか知りたいとき
cat=> SELECT id,logd25
FROM pgc2003
WHERE logd25 = (SELECT MAX(logd25)
FROM pgc2003);
n Fluxが平均値よりも⼤きい電波源を検索
cat=> SELECT id,flux,(SELECT AVG(flux) FROM NVSS) AS a
FROM nvss
WHERE flux > (SELECT AVG(flux)
FROM nvss)
LIMIT 20;2021年12⽉21―22⽇

データベース講習会

131v複数⾏複数列の値を返す副問い合わせ書式
SELECT <列名>
FROM <テーブル名>
WHERE (<列名1>,<列名2>,...<列名n>) [NOT] IN (SELECT <列名1,>,<列名2>,...,<列名n>FROM <テーブル名>
WHERE <条件式>);• 副問い合わせが返した複数⾏複数列の値がIN句で指定した列にあるか評価。

v実⾏例
n テーブル「nvss」から⾚経⾚緯の値がテーブル「pgc2003」のそれと完全に⼀致する⾏を検索
cat=> SELECT id,ra,dc
FROM nvss
WHERE (ra,dc) IN (SELECT ra,dc
FROM pgc2003);

2021年12⽉21―22⽇

データベース講習会

132

v相関副問い合わせ書式
SELECT <列名>
FROM <テーブル名>
WHERE [NOT] EXISTS (SELECT <列名>
FROM <テーブル名>
WHERE <条件式>);• 副問い合わせの結果が真である場合のみ主SELECT⽂の結果を返す。
• 主SELECT⽂のテーブルを副問い合わせが参照する為「相関副問い合わせ」。

v実⾏例
n テーブル「nvss」から⾚経⾚緯の値がテーブル「pgc2003」のそれと完全に⼀致するレコードを検索
cat=> SELECT id,ra,dc
FROM pgc2003
WHERE EXISTS (SELECT *FROM nvss
WHERE (pgc2003.ra=nvss.ra) AND(pgc2003.dc=nvss.dc));• pgc2003のレコードを⼀⾏ずつ副問い合わせで評価し、真の場合結果を返す。
2021年12⽉21―22⽇

データベース講習会

133

8.5.4. 共通テーブル式
vWITH句の書式
WITH <別名1> AS (SELECT <列名>
FROM <テーブル名>
WHERE <条件式>) [,<別名N> AS (...)]• WITH句に記述した問い合わせの結果を⼀時的にテーブル化。
• 共通テーブル式は主SELECT⽂内でのみ有効（他の問い合わせには無影響）。

v実⾏例
n 列「mtype」が「SB%」の天体の中から⾚緯が±0.1度内の天体を検索
cat=> WITH cte AS (SELECT *FROM pgc2003
WHERE mtype LIKE ‘SB%’)
SELECT id,ra,dc,mtype
FROM cte
WHERE dc BETWEEN -0.1 AND 0.1;
2021年12⽉21―22⽇

データベース講習会

8.5.5. 演習問題

134v【演習】 以下の問い合わせを実⾏せよ。
1. 【s855ex1.sql】 副 問 い 合 わ せ を 使 っ て テ ー ブ ル「nvss」から⾚経が（202<=ra<=204）かつ⾚緯が（46<=dc<=48）である電波源を検索し、その中から
列「flux」が100mJy以上の電波源を検索せよ。
2. 【s855ex2.sql】 副 問 い 合 わ せ を 使 っ て テ ー ブ ル「 nvss 」 か ら 列 「 flux 」 が 最 も ⼤ き い 電 波 源 の「id,ra,dc,flux,最も⼤きいflux」を表⽰せよ。

2021年12⽉21―22⽇

データベース講習会

135v【解答例】 以下の問い合わせを実施せよ。
1. 【s855ex1.sql】FROM句での副問い合わせを使ってテーブル「nvss」から⾚経が（202<=ra<=204）かつ⾚緯が（46<=dc<=48）である電波源を検索し、その中から「flux」が100mJy以上の電波源を検索せよ。
cat => SELECT *FROM (
SELECT *FROM nvss
WHERE (ra BETWEEN 202 AND 204) AND(dc BETWEEN 46 AND 48)) AS t
WHERE t.flux>=100;

2021年12⽉21―22⽇

データベース講習会

136

2. 【s855ex2.sql】副問い合わせを使ってテーブル「nvss」から列「flux」が最も⼤きい電波源の「id,ra,dc,flux,最も⼤きいflux」を表⽰せよ。
cat => SELECT id,ra,dc,flux,(SELECT MAX(flux) FROM nvss)
FROM nvss
WHERE flux = (SELECT MAX(flux) FROM nvss);
n 共通テーブル式を使った場合
cat => WITH cte AS (SELECT MAX(flux)
FROM nvss)
SELECT id,ra,dc,flux,(SELECT * FROM cte)
FROM nvss
WHERE flux = (SELECT * FROM cte);• 共通テーブル式はテーブルなので、FROM句以外の場所で使う場合はSELECT⽂で値を引き出さなければならない。

2021年12⽉21―22⽇

データベース講習会

8.6. テーブルの結合

137

vテーブルの結合• テーブル同⼠を結合することで、複数のテーブルに対して同時に検索ができる。
• 結合条件を課すことでテーブル同⼠を関連づけられる。
• 交差結合、内部結合、外部結合という結合⽅法が存在。

v8.6節⽬次••••

8.6.1. 交差結合
8.6.2. 内部結合
8.6.3. 外部結合
8.6.4. 演習問題

2021年12⽉21―22⽇

データベース講習会

138

8.6.1. 交差結合
v交差結合の書式1
SELECT <列名>
FROM <テーブル1> CROSS JOIN <テーブル2>;• テーブル1の各⾏にテーブル2の全⾏を結合。全ての組み合わせが求まる。
• 内部・外部結合の結果は交差結合に結合条件を課したもの。

v図解
n テーブルAとBを交差結合した場合
A

B

ID

主食

種別

1

ごはん

和

2

パン

洋

3

炒飯

中

2021年12⽉21―22⽇

ID

ID

主食

種別

ID

おかず

種別

1

ごはん

和

1

納豆

和

1

ごはん

和

2

ハム

洋

おかず

種別

X 1

納豆

和

= 2

パン

洋

1

納豆

和

2

ハム

洋

2

パン

洋

2

ハム

洋

3

炒飯

中

1

納豆

和

3

炒飯

中

2

ハム

洋

データベース講習会

139

v実⾏例
n テーブル「pgc2003」と「nvss」を交差結合し、⾚経⾚緯の値が完全に⼀致する⾏を検索
cat=> SELECT a.id,
a.ra,
a.dc,
b.id,
b.ra,
b.dc
FROM pgc2003 AS a CROSS JOIN nvss AS b
WHERE a.ra=b.ra AND
a.dc=b.dc
ORDER BY a.id;• 内部結合と同等の結果を得られる。

2021年12⽉21―22⽇

データベース講習会

140v交差結合の書式2
SELECT <列>
FROM <テーブル1>,<テーブル2>,...,<テーブルn>v実⾏例
n テーブル「pgc2003」と「nvss」を交差結合し、⾚経⾚緯の値が完全に⼀致する⾏を検索
cat=> SELECT a.id,
a.ra,
a.dc,
b.id,
b.ra,
b.dc
FROM pgc2003 AS a,
nvss
AS b
WHERE a.ra=b.ra AND
a.dc=b.dc
ORDER BY a.id;• 内部結合と同等の結果を得られる。
• よく使う。
2021年12⽉21―22⽇

データベース講習会

141

8.6.2. 内部結合
v内部結合の書式
SELECT <列名>
FROM <テーブル1> INNER JOIN <テーブル2> ON <結合条件>;• テーブル1の各⾏に結合条件を満たすテーブル2の⾏を結合。
• 結合条件は論理値が求まる式（テーブル1.id=テーブル2.idなど）。

v図解
n テーブルAとBを「A.種別=B.種別」という条件で内部結合した場合
B

A
ID

主食

種別

1

ごはん

和

2

パン

洋

3

炒飯

中

2021年12⽉21―22⽇

ID

ID

おかず

種別

X 1

納豆

和

= 1

2

ハム

洋

2

主食

種別

ID

おかず

種別

ごはん

和

1

納豆

和

パン

洋

2

ハム

洋

データベース講習会

v実⾏例

142

n テーブル「pgc2003」と「nvss」を内部結合し、⾚経⾚緯の値が完全に⼀致する⾏を検索
cat=> SELECT a.id,
a.ra,
a.dc,
b.id,
b.ra,
b.dc
FROM pgc2003 AS a INNER JOIN nvss AS b
ON a.ra=b.ra AND
a.dc=b.dc
ORDER BY a.id;v複数のテーブルを結合する場合の例
SELECT t1.id,
t2.id,
t3.id
FROM (t1 INNER JOIN t2 ON t1.id = t2.id)
INNER JOIN ON t1.id = t3.id;

2021年12⽉21―22⽇

データベース講習会

143

8.6.3. 外部結合
v外部結合の書式
SELECT <列名>
FROM <テーブル1> LEFT|RIGHT|FULL OUTER JOIN <テーブル2>
ON <結合条件>;• 結合条件に真偽に拘らず結合。偽の場合NULL値が⼊⼒された⾏を結合。
• 左外部結合、右外部結合、完全外部結合が存在。
143v図解（左外部結合）
n テーブルAとBを「A.種別=B.種別」という条件で左外部結合した場合
A

B

ID

主食

種別

1

ごはん

和

2

パン

洋

3

炒飯

中

ID

おかず

種別

X 1

納豆

和

2

ハム

洋

=

ID

主食

種別

ID

おかず

種別

1

ごはん

和

1

納豆

和

2

パン

洋

2

ハム

洋

3

炒飯

中

• JOIN句の左側のテーブルを基準に右側のテーブルを結合。
2021年12⽉21―22⽇

データベース講習会

144

v図解（右外部結合）
n テーブルAとBを「A.種別=B.種別」という条件で右外部結合した場合
A
B
ID

主食

種別

1

ごはん

和

2

パン

洋

3

炒飯

中

ID

ID

おかず

種別

X 1

納豆

和

= 1

2

ハム

洋

2

主食

種別

ID

おかず

種別

ごはん

和

1

納豆

和

パン

洋

2

ハム

洋

• JOIN句の右側のテーブルを基準に左側のテーブルを結合。

v図解（完全外部結合）– テーブルAとBを「A.種別=B.種別」という条件で完全外部結合した場合
A
B
ID

主食

種別

1

ごはん

和

2

炒飯

中

ID

おかず

種別

X 1

納豆

和

2

ハム

洋

=

ID

主食

種別

ID

おかず

種別

1

ごはん

和

1

納豆

和

2

炒飯

中
2

ハム

洋

• 両テーブルの⾏を全て保持して結合。
2021年12⽉21―22⽇

データベース講習会

v実⾏例

145

n テーブル「pgc2003」から⾚緯が（10.0<=dc<=10.1）の天体を抽出したものとテーブル「nvss」を左外部結合し、⾚経⾚緯の値が完全に⼀致する⾏を検索
cat=> WITH a AS (SELECT *FROM pgc2003
WHERE dc BETWEEN 10.0 AND 10.1)
SELECT a.id,
a.ra,
a.dc,
b.id,
b.ra,
b.dc
FROM a LEFT OUTER JOIN nvss AS b
ON a.ra=b.ra AND
a.dc=b.dc
ORDER BY a.id;• ⼀⽅（あるいは両⽅）のテーブルの⾏を残したい場合に使う。
• 例えば⽇時を主キーとした温度、湿度、気圧テーブルを⽇時を結合条件として結合した場合、内部結合ではあるテーブルである⽇時にデータ⽋損が発⽣するとその⾏が結合条件を満たさない為表⽰されなくなる。
2021年12⽉21―22⽇

データベース講習会

8.6.4. 演習問題

146v【演習】以下の問い合わせを実⾏せよ。
1. 【s864ex1.sql】テーブル「pgc2003」と「nvss」を⾚経⾚緯の⼩数第三位までが⼀致するという条件で内部結合し、⾚緯が（-1<=dc<=1）である天体の数を調べよ。

2021年12⽉21―22⽇

データベース講習会

147v【解答例】 以下の問い合わせを実施せよ。
1. テーブル「pgc2003」と「nvss」を⾚経⾚緯の⼩数第三位までが⼀致するという条件で内部結合し、⾚緯が（-1<=dc<=1）である天体の数を調べよ。
cat => SELECT COUNT(*)
FROM pgc2003 AS a INNER JOIN nvss AS b
ON(
TRUNC(CAST(a.ra AS numeric),4)=
TRUNC(CAST(b.ra AS numeric),4))AND(
TRUNC(CAST(a.dc AS numeric),4)=
TRUNC(CAST(b.dc AS numeric),4))
WHERE a.dc BETWEEN -1 AND 1;• なぜ検索に数秒かかるのか？

2021年12⽉21―22⽇

データベース講習会

8.7. 遅くならないSELECT⽂の書
き⽅148

vRDBMSは問い合わせの結果が直ちに返ってくるよ
うに利⽤すべき• 以下の場合、結果がすぐに返ってこない。
– WHERE句・ON句の書き⽅が悪い。
– インデックスを張っていない列を検索している。
– 検索結果が膨⼤で、ディスクI/Oがボトルネックに。

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

v8.7節⽬次• 8.7.1. 問い合わせの実⾏計画の表⽰• 8.7.2. 「8.6.4. 演習問題」の⾼速化
2021年12⽉21―22⽇

データベース講習会

8.7.1. 問い合わせの実⾏計画の表⽰

149

vEXPLAIN句の書式
EXPLAIN [ANALYZE]
SELECT ...;
• 問い合わせの実⾏計画を表⽰。
• ANALYZE：問い合わせを実⾏して実際の実⾏時間も表⽰。

v実⾏例
n PGC0077777を検索した場合の実⾏計画
cat=> EXPLAIN ANALYZE
SELECT * FROM pgc2003 WHERE id=‘PGC0077777’;• 検索⽅式：Seq Scan、Index Scan、Bitmap Scan等が存在。
• cost：オプティマイザが最も効率的な問い合わせ⽅法を⾒つけるための指標。
1コスト=1ページ（テーブルファイルを構成する8192byteの固定⻑領域）のデータをシーケンシャルに読み込むためにかかる時間。
• rows：推定検索結果⾏数。
• width：1⾏あたりの推定平均幅（byte）。
2021年12⽉21―22⽇

データベース講習会

150v【実習】 実⾏結果は同じだがWHERE句の書き⽅が異なるSQLの実⾏時間の⽐較
1. 以下のSQLは「logd25」が1arcmin以上の天体の数を計測するものである。
SQLを3回以上実⾏し、実⾏時間の平均時間を調べよ。
n A
cat=> ¥timing
cat=> SELECT COUNT(*)
FROM pgc2003
WHERE (10^logd25)*0.1 >= 1;n B
cat=> SELECT COUNT(*)
FROM pgc2003
WHERE logd25 >= LOG(1*10);
2.

EXPLAIN ANALYZE句を使って実⾏計画を確認せよ。

2021年12⽉21―22⽇

データベース講習会

v列に対する演算

151• WHERE 句 ・ ON 句 で 列 に 対 す る 演 算 を お こ な う と 、
RDBMSは列の演算結果に対して⽐較演算を実⾏する。
• 列の演算結果にはインデックスが存在しないため、列の演算結果全⾏に対する⽐較演算（シーケンシャルスキャ
ン）がおこなわれる。
• WHERE句・ON句内での列に対する演算には要注意。

2021年12⽉21―22⽇

データベース講習会

152

8.7.2. 「8.6.4. 演習問題」の⾼速化
vON句で列に対する演算をしているため遅い
cat => SELECT COUNT(*)
FROM pgc2003 AS a INNER JOIN nvss AS b
ON(
TRUNC(CAST(a.ra AS numeric),4)=
TRUNC(CAST(b.ra AS numeric),4))AND(
TRUNC(CAST(a.dc AS numeric),4)=
TRUNC(CAST(b.dc AS numeric),4))
WHERE a.dc BETWEEN -1 AND 1;

v解決⽅法• 絞り込み検索− インデックス検索で絞り込んだ後シーケンシャル検索。

• 式インデックス− 計算式（の演算結果）に対してインデックスを設定。

• あらかじめ計算結果を表に格納してインデックスを設定− よく使われる値は計算しておくと効率的。
− DBの設計理念的にはX。⾏を更新したときに演算し忘れないよう注意が必要。
2021年12⽉21―22⽇

データベース講習会

v絞り込み検索の実施

153

cat => WITH a AS (SELECT *FROM pgc2003
WHERE dc between -1 and 1),
b AS (
SELECT *FROM nvss
WHERE dc between -1 and 1)
SELECT COUNT(*)
FROM a INNER JOIN b ON(
TRUNC(CAST(a.ra AS numeric),1)=
TRUNC(CAST(b.ra AS numeric),1))AND(
TRUNC(CAST(a.dc AS numeric),1)=
TRUNC(CAST(b.dc AS numeric),1));• 実⾏時間：~0.3秒• FROM句での副問い合わせを使って検索範囲を絞り込んだ後に結合。
2021年12⽉21―22⽇

データベース講習会

v【コラム】列名の重複

154

n 以下のSQLは「FROM句にテーブルaが存在しない」というエラーが出て失敗する。
cat => SELECT a.id
FROM (
SELECT *FROM pgc2003 AS a INNER JOIN nvss AS b ON
a.ra=b.ra AND a.dc=b.dc) AS t;• FROM句での副問い合わせ内のテーブル名は主SELECT⽂の参照範囲外。
n 以下のSQLも「列idの参照があいまいである」というエラーが出て失敗する。
cat => SELECT t.id
FROM (
SELECT *FROM pgc2003 AS a INNER JOIN nvss AS b ON
a.ra=b.ra AND a.dc=b.dc) AS t;• FROM句での副問い合わせ結果「t」内に列「id」が2列存在している。

2021年12⽉21―22⽇

データベース講習会

155

n 以下のようにSQLを修正すると成功する。
cat => SELECT aid
FROM (
SELECT a.id AS aid
FROM pgc2003 AS a INNER JOIN nvss AS b ON
a.ra=b.ra AND a.dc=b.dc) AS t;• FROM句内での副問い合わせ結果「t」内の列「a.id」と「b.id」を区別するためにそれぞれに別名を定義すれば、主SELECT⽂で列を特定できる。
• 全列指定「*」を使っているとよく発⽣するので注意が必要。

2021年12⽉21―22⽇

データベース講習会

9. ユーザ定義関数

2021年12⽉21―22⽇

データベース講習会

9.1. 概要

157

vSQL⾔語とPL/pgSQLを使ったユーザ定義関数の作成⽅法を紹介• ユーザ定義関数の作成にはSQL⾔語、C⾔語、⼿続き型
⾔語が使⽤できる。
• 標準で使⽤できる⼿続き型⾔語は以下の通り。
– PL/pgSQL、PL/Tcl、PL/Perl、PL/Python– ただし、PL/pgSQL以外は要インストール。

v9章⽬次• 9.2. SQL関数• 9.3. PL/pgSQL関数• 9.4. SQL関数とPL/pgSQL関数の使い分け
2021年12⽉21―22⽇

データベース講習会

9.2. SQL関数

158vSQL関数• SQL⽂で構成される関数。
• 関数内に記述されたSQL⽂を実⾏していき、最後に記述されたSQL⽂の実⾏結果を返す。

v9.2節⽬次••••

9.2.1. ⼀⾏⼀列の値を返すSQL関数
9.2.2. ⼀⾏複数列の値を返すSQL関数
9.2.3. 複数⾏複数列の値を返すSQL関数
9.2.4. 演習問題

2021年12⽉21―22⽇

データベース講習会

9.2.1. ⼀⾏⼀列の値を返すSQL関数

159

v⼀⾏⼀列の値を返すSQL関数の書式
CREATE FUNCTION <関数名>(<引数のデータ型>[,・・・])
RETURNS <戻り値のデータ型> AS $$<SQL>;$$ <IMMUTABLE|STABLE|VOLATILE> LANGUAGE SQL;v実⾏例n 【s921eg1.sql】「Hello, World!」を表⽰するSQL関数
CREATE FUNCTION s921eg1()
RETURNS TEXT AS $$SELECT ‘Hello, World!’;$$ IMMUTABLE LANGUAGE SQL;
cat=> SELECT s921eg1();• ユーザ定義関数の⼀覧はメタコマンド「\df」で確認可能。
2021年12⽉21―22⽇

データベース講習会

160

n 【s921eg2.sql】 引数を⽤いるSQL関数
CREATE FUNCTION s921eg2(INTEGER,INTEGER)
RETURNS INTEGER AS $$SELECT $1 + $2;$$ IMMUTABLE LANGUAGE SQL;
cat=> SELECT s921eg2(2000,21);n 【s921eg3.sql】条件式の値を変数としたSQL関数
CREATE FUNCTION s921eg3(TEXT)RETURNS TEXT AS $$SELECT anames FROM pgc2003 WHERE id=$1;$$ IMMUTABLE LANGUAGE SQL;
cat=> SELECT s921eg3(‘PGC0077777’);vSQL関数の削除⽅法
cat=> DROP FUNCTION <関数名[(引数のデータ型)]>;• 同じ名前の関数が複数存在する場合は引数のデータ型も指定。
2021年12⽉21―22⽇

データベース講習会

v⼀⾏⼀列の値を返すSQL関数の解説

161

CREATE FUNCTION <関数名>(<引数のデータ型>[,・・・])
RETURNS <戻り値のデータ型> AS $$<SQL>;$$ <IMMUTABLE|STABLE|VOLATILE> LANGUAGE SQL;
• ⼆重ドル引⽤符（$$）− SQLコマンド「CREATE FUNCTION」にとって「<SQL>」は⽂字列である
ため単⼀引⽤符で囲む必要がある。
− しかし単⼀引⽤符で囲んでしまうと「<SQL>」内で⽂字列を記述できなくなってしまうため、同じ働きをする⼆重ドル引⽤符で囲む。

• 関数の変動性分類（IMMUTABLE,STABLE,VOLATILE）− 関数の振る舞い⽅をオプティマイザに教えるためのオプション。
− WHERE句で関数を呼び出した時IMMUTALBLEのみインデックス検索が可能。
− IMMUTALBE：同⼀引数に対し常に同⼀の結果を返す関数。
− 例：SIN()− STABLE：同⼀問い合わせ内で同⼀引数に対し同⼀の結果を返す関数。
− 例：NOW()− VOLATILE：同⼀引数に対し異なる結果を返す関数。デフォルト設定。
− 例：RANDOM()• 関数の引数− 「<SQL>」内の列名や条件式の値に引数「$<引数番号>」を使⽤できる。
− テーブル名には引数を使⽤できない。

2021年12⽉21―22⽇

データベース講習会

9.2.2. ⼀⾏複数列の値を返すSQL関数

162

v⼀⾏複数列の値を返すSQL関数の書式
CREATE FUNCTION <関数名>(<引数データ型[,・・・])
RETURNS <複合型の戻り値データ型> AS $$<SQL>;$$ IMMUTABLE|STABLE|VOLATILE LANGUAGE SQL;
• 戻り値のデータ型として複合型を指定する必要がある。

v複合型の作成⽅法
CREATE TYPE <複合型名> AS (<要素名1> <データ型名1>,<要素名2> <データ型名2>,・・・);• 複合型：複数のデータ型を組み合わせて1つのデータ型として扱うデータ型。
• 列名とデータ型を単に列挙したもの。

2021年12⽉21―22⽇

データベース講習会

163

v実⾏例
n 【s922eg1.sql】 3つのデータ型要素を持つ複合型の定義
CREATE TYPE s922eg1 AS (a TEXT,b DOUBLE PRECISION,
c DOUBLE PRECISION);• ユーザー定義データ型は「\dT」「\d データ型」で確認可能。
n 【s922eg2.sql】 複数列の結果を返すSQL関数
CREATE FUNCTION s922eg2()
RETURNS s922eg1 AS $$SELECT id,ra,dc FROM pgc2003 LIMIT 20;$$ IMMUTABLE LANGUAGE SQL;
cat=> SELECT s922eg2();
cat=> SELECT * FROM s922eg2();• 関数の出⼒結果の列名は複合型の要素名になる。
• この⽅法だと1⾏しか返ってこない。

vユーザ定義データ型の削除⽅法
cat=> DROP TYPE <データ型名>;
2021年12⽉21―22⽇

データベース講習会

9.2.3. 複数⾏複数列の値を返す
SQL関数

164

v複数⾏複数列の値を返すSQL関数の書式
CREATE FUNCTION <関数名>(<引数のデータ型>)
RETURNS SETOF <複合型の戻り値のデータ型> AS $$<SQL>;$$ IMMUTABLE|STABLE|VOLATILE LANGUAGE SQL;
• SETOF：関数内の本⽂に記述された複数のSQLのうち、最も最後に記述されたSQLをSQLが終了するまで実⾏。。

v実⾏例

n 【s923eg1.sql】 複数⾏複数列の値を返すSQL関数
CREATE FUNCTION s923eg1()
RETURNS SETOF pgc2003 AS $$SELECT * FROM pgc2003 LIMIT 20;$$ IMMUTABLE LANGUAGE SQL;
cat=> SELECT * FROM s923eg1();• 戻り値のデータ型としてテーブル名を指定可能。
2021年12⽉21―22⽇

データベース講習会

9.2.4. 演習問題

165v【演習】次のSQL関数を作成せよ。
1. 【s924ex1.sql】テーブル「pgc2003」の列「anames」から任意の天体名の⾏を検索するSQL関数。
–

2021年12⽉21―22⽇

ヒント：結合演算⼦||を使う。

データベース講習会

v【解答例】

166

1. 【s924ex1.sql】テーブル「pgc2003」の列「anames」から任意の天体名の⾏を検索するSQL関数。
n 部分⼀致検索
CREATE FUNCTION s924ex1(text)
RETURNS SETOF pgc2003 AS $$SELECT * FROM PGC2003 WHERE anames LIKE ‘%’||$1||’%’$$ IMMUTABLE LANGUAGE SQL;
cat=> SELECT * FROM s924ex1(‘NGC1’);n 完全⼀致検索
CREATE FUNCTION s924ex1b(text)
RETURNS SETOF pgc2003 AS $$SELECT * FROM PGC2003 WHERE anames
SIMILAR TO ‘(^’||$1||’%|%_’||$1||’_%|’||$1||’$)’$$ IMMUTABLE LANGUAGE SQL;
• _：スペース。

2021年12⽉21―22⽇

データベース講習会

9.3. PL/pgSQL関数

167

vPL/pgSQL• ⾮⼿続き型⾔語であるSQLを拡張した、PostgreSQLで読み込み可能な⼿続き型⾔語。
• SQL関数よりも複雑な演算を⾏うことができる。
• PostgreSQL9.0以降ではデフォルトで使⽤可能。

v9.3節⽬次•••••

9.3.1. ⼀⾏⼀列の値を返すPL/pgSQL関数
9.3.2. ⼀⾏複数列の値を返すPL/pgSQL関数
9.3.3. 複数⾏複数列の値を返すPL/pgSQL関数
9.3.4. PL/pgSQLの制御構⽂
9.3.5. 演習問題

2021年12⽉21―22⽇

データベース講習会

9.3.1. ⼀⾏⼀列の値を返す
PL/pgSQL関数

168

v⼀⾏⼀列の値を返すPL/pgSQL関数の書式
CREATE FUNCTION <関数名>(<仮引数> <データ型>[,・・・])
RETURNS <戻り値のデータ型> AS $$DECLARE<変数名> <データ型>[:=<値>];
BEGIN
RETURN <式>;
END$$ IMMUTABLE|STABLE|VOLATILE LANGUAGE PLPGSQL;• DECLARE：変数の宣⾔をおこなえる。
• RETURN：式の内容を結果として返す。
• :=：変数へ値を代⼊。PostgreSQL9.4以降は「=」も使⽤可能。

2021年12⽉21―22⽇

データベース講習会

169

v実⾏例
n 【s931eg1.sql】「Hello, World!」を表⽰するPL/pgSQL関数
CREATE FUNCTION s931eg1() RETURNS TEXT AS $$DECLAREx TEXT;
BEGIN
x := ‘Hello, World!’;
RETURN x;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
cat=> SELECT s931eg1();n 【s931eg2.sql】 引数を⽤いるPL/pgSQL関数
CREATE FUNCTION s931eg2(x INTEGER,y INTEGER)
RETURNS INTEGER AS $$DECLAREz INTEGER;
BEGIN
z := x + y;
RETURN z;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
cat=> SELECT s931eg2(2000,21);
2021年12⽉21―22⽇

データベース講習会

170

n 【s931eg3.sql】条件式の値を変数としたSQL関数
CREATE FUNCTION s931eg3(x TEXT) RETURNS TEXT AS $$DECLAREz TEXT;
BEGIN
SELECT anames INTO z FROM pgc2003 WHERE id=x;
RETURN z;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
cat=> SELECT s931eg3(‘PGC0077777’);• SELECT <列名> INTO <変数>：指定した列の値を変数に代⼊。

2021年12⽉21―22⽇

データベース講習会

9.3.2. ⼀⾏複数列の値を返す
PL/pgSQL関数

171

v⼀⾏複数列の値を返すPL/pgSQL関数の書式
CREATE FUNCTION <関数名>(<引数> <データ型>[,・・・])
RETURNS <複合型の戻り値データ型> AS $$DECLARE<変数名> <複合型>[:=(<値>[,...])];BEGIN
RETURN 式;
END$$ IMMUTABLE|STABLE|VOLATILE LANGUAGE PLPGSQL;• ⾏変数：変数のデータ型として複合型を指定したもの。

2021年12⽉21―22⽇

データベース講習会

n 【s932eg1.sql】複数列の結果を返すPL/pgSQL関数
CREATE FUNCTION s932eg1()
RETURNS s922eg1 AS $$DECLARErow s922eg1; -- 行変数rowを宣言。
BEGIN
SELECT id,ra,dc INTO row FROM pgc2003 LIMIT 20;
RETURN row;
END$$ IMMUTABLE LANGUAGE PLPGSQL;

172

cat=> SELECT * FROM s932eg1();• s922eg1：(a TEXT, b DP, c DP）。
• この⽅法では1⾏しか返ってこない。

2021年12⽉21―22⽇

データベース講習会

9.3.3. 複数⾏複数列の値を返す
PL/pgSQL関数

173

v複数⾏複数列の値を返すPL/pgSQL関数の書式
CREATE FUNCTION <関数名>(<引数1> <データ型>[,...])
RETURNS SETOF <複合型の戻り値のデータ型> AS $$DECLARE<変数名> <複合型>[:=(<値>[,...])];BEGIN
RETURN QUERY <SQL>; | RETURN NEXT <変数名>;
RETURN;
END$$ IMMUTABLE|STABLE|VOLATILE LANGUAGE PLPGSQL;••••「SETOF」使⽤時には「RETURN QUERY」か「RETURN NEXT」が必要。
RETURN QUERY <SQL>：<SQL>の実⾏結果を戻り値テーブルに追加。
RETURN NEXT <変数名>：<変数名>の値を戻り値テーブルに追加。
RETURN：関数が終了したことを表す。

2021年12⽉21―22⽇

データベース講習会

v実⾏例

174

n 【s933eg1.sql】複数列の結果を返すPL/pgSQL関数（失敗例）
CREATE FUNCTION s933eg1()
RETURNS SETOF s922eg1 AS $$DECLARErow s922eg1; -- 行変数rowを宣言。
BEGIN
SELECT id,ra,dc INTO row FROM pgc2003 LIMIT 20;
RETURN row;
END$$ IMMUTABLE LANGUAGE PLPGSQL;ERROR: RETURN cannot have a parameter in function
returning set
行 7: RETURN row;
• 「SETOF」使⽤時は「RETURN QUERY」か「RETURN NEXT」を使わないと関数を定義できない。

2021年12⽉21―22⽇

データベース講習会

175

n 【s933eg2.sql】RETURN QUERYの使⽤例
CREATE FUNCTION s933eg2() RETURNSSETOF s922eg1 AS $$BEGIN
RETURN QUERY SELECT id,ra,dc FROM pgc2003 LIMIT 20;
RETURN;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
cat=> SELECT * FROM s933eg2();

2021年12⽉21―22⽇

データベース講習会

176

n 【s933eg3.sql】RETURN NEXTの使⽤例
CREATE FUNCTION s933eg3()
RETURNS SETOF pgc2003 AS $$DECLARErow pgc2003;
BEGIN
FOR i IN 1..20 LOOP
SELECT * INTO row FROM pgc2003 LIMIT 1 OFFSET i-1;
RETURN NEXT row;
END LOOP;
RETURN;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
cat=> SELECT * FROM s933eg3();• OFFSET：指定した⾏を⾶ばして表⽰。
• 検索結果を1⾏ずつ戻り値テーブルに追加している。

2021年12⽉21―22⽇

データベース講習会

9.3.4. PL/pgSQLの制御構⽂

177

vIF⽂
IF (<条件式>) THEN
<実行文>;ELSEIF (<条件式>) THEN
<実行文>;
ELSE<実行文>;END IF;vCASE⽂CASE <評価対象>
WHEN <値> then <実行文>;
ELSE <実行文>;
END CASE;

2021年12⽉21―22⽇

データベース講習会

v整数FORループ⽂

178

FOR <変数名> IN <整数値..整数値> LOOP
<実行文>;
END LOOP;• <変数>はINTEGER型として⾃動的に定義される。

v問い合わせ結果FORループ⽂
FOR <行変数|カンマ区切り変数リスト> IN <SQL> LOOP
<実行文>;
END LOOP;• 問い合わせ結果の各⾏を<実⾏⽂>で処理できるループ⽂。

vWHILE⽂WHILE <条件式> LOOP
<実行文>;
END LOOP;• 条件式の評価が真である限りループを繰り返す。

2021年12⽉21―22⽇

データベース講習会

179

9.3.5. 演習問題
v【演習】次のPL/pgSQL関数を作成せよ。
1. 【s935ex1.sql】⼊⼒された度分秒を度に変換する
PL/pgSQL関数。
––

⼊⼒引数の形式は「ʼ12d34m56sʼ」とする。
SUBSTRING関数で⽂字列の抽出が可能。
>

2021年12⽉21―22⽇

SUBSTRING(ʻhogeʼ FROM 3 FOR 2) => ge

データベース講習会

v【解答例】次のPL/pgSQL関数を作成せよ。

180

1. 【s935ex1.sql】⼊⼒された度分秒を度に変換する
PL/pgSQL関数。
CREATE FUNCTION s935ex1(dms TEXT)RETURNS DOUBLE PRECISION AS $$DECLAREd DOUBLE PRECISION;m DOUBLE PRECISION;s DOUBLE PRECISION;r DOUBLE PRECISION;
BEGIN
d:=SUBSTRING(dms FROM 1 FOR 2);
m:=SUBSTRING(dms FROM 4 FOR 2);
s:=SUBSTRING(dms FROM 7 FOR 2);
r:=d+(m*1/60)+(s*1/3600);
RETURN r;
END$$ IMMUTABLE LANGUAGE PLPGSQL;
cat=> SELECT s935ex1(‘12d34m56s’);
12.58222...
• 変数の型と代⼊値の型が異なる場合、代⼊値を変数の型に変換する型変換が⾃動的に試みられる。
2021年12⽉21―22⽇

データベース講習会

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

181

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

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

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

v 使い分け• テーブルを扱わない関数 => PL/pgSQL関数• テーブルを扱う関数 => SQL関数 or PL/pgSQL関数
2021年12⽉21―22⽇

データベース講習会

10. 指定座標を中⼼とした
任意⾓度範囲内の天体の検索

2021年12⽉21―22⽇

データベース講習会

10.1. 概要

183v コーンサーチ・ラジアルサーチ• 天球⾯上のある座標を中⼼とした任意⾓度範囲内にある天体を検索する⽅法。
• 天⽂業界ではコーンサーチの実装⼿法がいくつか確⽴されているが、本講習会では直交座標系を利⽤したコーンサーチの実装⼿法を紹介。

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

検索中⼼(x1,y1,z1)
天体(x2,y2,z2)• WHERE句内で列に対する演算をおこなうため検索に⾮常に時間がかかる。
• 絞り込み検索をおこなう必要がある。
2021年12⽉21―22⽇

データベース講習会

v絞り込み検索を⽤いたコーンサーチの実装⼿法

184

1. 天体の⾚道座標を直交座標に変換。
2. 検索半径がRの時、検索中⼼を中⼼とした1辺2Rの⽴⽅体の中にある天体をインデックス検索で抽出。
3. 2で取り出した天体に対して検索中⼼からの⾓度を計算
し、検索半径内にある天体を取り出す。

v10章⽬次• 10.2. 直交座標への変換• 10.3. コーンサーチの実装• 10.4. クロスマッチの実装

z
2R
R
2R

y

2R
x
2021年12⽉21―22⽇

データベース講習会

10.2 直交座標への変換

185

v天体の⾚道座標を直交座標に変換しビューに格納。
v10.2節⽬次• 10.2.1 座標変換⽤関数の作成• 10.2.2 直交座標系ビューの作成• 10.2.4 直交座標系ビューに対する検索の⾼速化

2021年12⽉21―22⽇

データベース講習会

10.2.1 座標変換⽤関数の作成

186v【演習】 座標変換⽤関数の作成
1. 【fEq2(X|Y|Z).sql】⾚道座標を直交座標に変換する
PL/pgSQL関数「fEq2X, fEq2Y, fEq2Z」をそれぞれ作
成せよ。
–
–

引数と戻り値の型：DOUBLE PRECISION
参：「8.4.6. 演習問題」

2. 次のSQLを実⾏し値が~1になることを確認せよ。
cat=> SELECT SQRT(
fEq2X(ra,dc)^2 +fEq2Y(ra,dc)^2 +fEq2Z(dc)^2)
FROM pgc2003
LIMIT 20;2021年12⽉21―22⽇

データベース講習会

v【解答例】座標変換⽤関数の作成

187

1. 【fEq2(X|Y|Z).sql】⾚道座標を直交座標に変換する
PL/pgSQL関数「fEq2X, fEq2Y, fEq2Z」をそれぞれ作
成せよ。
n fEq2X.sql
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;n 【fEq2Y.sql】
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;n 【fEq2Z.sql】
CREATE FUNCTION fEq2Z(dc DOUBLE PRECISION)
RETURNS DOUBLE PRECISION AS $$BEGIN
RETURN SIN(RADIANS(dc));
END$$ IMMUTABLE LANGUAGE PLPGSQL;
2021年12⽉21―22⽇

データベース講習会

188

10.2.2. 直交座標系ビューの作成
vCREATE VIEW⽂の書式
CREATE VIEW <ビュー名> (<列名>[,...]) AS
SELECT <列名>[,...] FROM <テーブル名>;v ビュー• テーブルから任意の列を取り出して仮想的なテーブルを作成する機能。
• SELECT⽂内でテーブルと同様に扱える。
• 内部的には「CREATE VIEW」で定義したSQLを問い合わせのたびに実⾏。
• 使⽤例：– 列から計算した値をテーブルのように扱いたい時。
– ユーザーに対しテーブルの⼀部の列を隠蔽したい時。

2021年12⽉21―22⽇

データベース講習会

v【実習】直交座標系ビューの作成

189

n 【CreateViewPgc2003_xyz.sql】テーブル「pgc2003」の⾚道座標を直交座標に変換するビュー「pgc2003_xyz」を作成せよ。
cat=> CREATE VIEW pgc2003_xyz(id,x,y,z) AS
SELECT id,
fEq2X(ra,dc),
fEq2Y(ra,dc),
fEq2Z(dc)
FROM pgc2003;
n 【CreateViewNvss_xyz.sql】テーブル「nvss」の⾚道座標を直交座標に変換するビュー「nvss_xyz」を作成せよ。
cat=> CREATE VIEW nvss_xyz(id,x,y,z) AS
SELECT id,
fEq2X(ra,dc),
fEq2Y(ra,dc),
fEq2Z(dc)
FROM nvss;
n 【上記ビューが機能しているか確認せよ。
cat=> ¥dcat=> SELECT * FROM pgc2003_xyz LIMIT 20;
cat=> SELECT id,SQRT(x^2+y^2+z^2)
FROM pgc2003_xyz LIMIT 20;
2021年12⽉21―22⽇

データベース講習会

10.2.4. 直交座標系ビューに対する検索の⾼速化190v【実習】直交座標系ビューの検索時間の測定
n 【s1024eg1.sql】ビュー「nvss_xyz」から適当な⽴⽅体領域にある天体を検索
cat=> EXPLAIN ANALYZE
SELECT COUNT(*)
FROM nvss_xyz
WHERE (x BETWEEN 0.8 AND 0.9) AND(y BETWEEN 0 AND 0.1) AND(z BETWEEN -0.6 AND -0.5);

n 上記SQLの実態
SELECT COUNT(*)
FROM nvss_xyz
WHERE(COS(RADIANS(ra))*COS(RADIANS(dc)) BETWEEN 0.8 AND 0.9) AND(SIN(RADIANS(ra))*COS(RADIANS(dc)) BETWEEN 0 AND 0.1) AND(COS(RADIANS(dc)) BETWEEN -0.6 AND -0.5);• 列「ra,dc」はインデックスが設定されているが、WHERE句内で列に対する演算をおこなっているためシーケンシャル検索となり遅い。
• だからといって⾚経⾚緯で⽴⽅体内天球⾯の範囲を表すわけにもいかない。
• 式インデックスと複合インデックスを活⽤する。
2021年12⽉21―22⽇

データベース講習会

v式インデックス

191• 列を⽤いた式や関数に対して設定するインデックス。
• 演算結果に基づいた⾼速な検索が可能。
• ⾏が挿⼊・更新される度に式の計算が必要なため、通常のインデックスよりも運⽤コストが⾼い。

v実⾏例
n 列「id」から’PGC’を削除した値にインデックスを設定
cat=> CREATE INDEX ON pgc2003(TRIM(‘PGC’ FROM id));n 検索速度の比較
cat=> SELECT *FROM pgc2003
WHERE TRIM(‘PGC’ FROM id)=‘0077777’;• 式インデックス無し：~150 ms• 式インデックス有り：~0.5 ms

2021年12⽉21―22⽇

データベース講習会

v複合インデックス

192• 複数の列の組み合わせに設定するインデックス。
• 複数で⼀つの情報を表す列に設定。
– ⾚経と⾚緯、苗字と名前など。

• 通常のインデックスよりも検索速度が向上する可能性が
ある。

v実⾏例
n 列「ra,dc」に複合インデックスを設定
cat=> CREATE INDEX ON pgc2003(ra,dc);n 検索速度の比較
cat=> SELECT *FROM pgc2003
WHERE (ra BETWEEN 90 AND 100) AND(dc BETWEEN -5 and 5);• インデックス有り：~8 ms• 複合インデックス有り：~1.5 ms
2021年12⽉21―22⽇

データベース講習会

v複合インデックスの注意点

193• インデックスの定義順序が検索性能に影響。
– 列1、列2の順番で絞り込むのが最適な場合は、この順番で複合インデックスを定義すべき。

• 1つの列に対してのみ検索を⾏なった時、1番⽬に定義された列以外はインデックス検索を⾏えない。
– pgc2003(ra,dc)設定時、dcのみの検索はシーケンシャルに。

v【実⾏例】
n 検索速度の比較
cat=> SELECT *FROM pgc2003
WHERE dc BETWEEN -5 and 5;• インデックス有り：~40ms• 複合インデックス有り：~120 ms• 検索⽅法が定型化されていない限り、複合インデックスの採⽤は慎重に。

2021年12⽉21―22⽇

データベース講習会

v【実習】複合インデックスの作成

194

n テーブル「pgc2003」に複合インデックスを設定せよ。
cat=> CREATE INDEX ON pgc2003(
fEq2X(ra,dc),
fEq2Y(ra,dc),
fEq2Z(dc));• なおx、y、zの検索優先度は同⼀であるため列の定義順序は何でも良い。
n テーブル「nvss」に複合インデックスを設定せよ。
cat=> CREATE INDEX ON nvss(
fEq2X(ra,dc),
fEq2Y(ra,dc),
fEq2Z(dc));
n 【s1024eg1.sql 】SQLの実⾏時間が短くなったか確認せよ。
cat=> EXPLAIN ANALYZE
SELECT COUNT(*)
FROM nvss_xyz
WHERE (x BETWEEN 0.8 AND 0.9) AND(y BETWEEN 0 AND 0.1)
AND(z BETWEEN -0.6 AND -0.5);
2021年12⽉21―22⽇

データベース講習会

10.3. コーンサーチの実装

195

vコーンサーチの実装⼿法
1. 天体の⾚道座標を直交座標に変換（済）。
2. 検索半径がRの時、検索中⼼を中⼼とした1辺2Rの⽴⽅体の中にある天体をインデックス検索でとりだす。
3. 2で取り出した天体に対して検索中⼼からの⾓度を計算
し、検索半径内にある天体を取り出す。

v10.3節⽬次• 10.3.1. コーンサーチに必要な関数の作成• 10.3.2. コーンサーチ⽤SQLの作成• 10.3.3. コーンサーチの実装

2021年12⽉21―22⽇

データベース講習会

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

196

v 必要な関数• 天球⾯上の2点間の⾓度（分⾓）を求める関数• 分⾓をラジアンに変換する関数v 【演習】2点間の⾓度を求める関数
1. 【fDistanceArcminXyz.sql】天球⾯上の点1（x1, y1, z1）と点2（x2, y2, z2）の間の⾓度（分⾓）を求めるPL/pgSQL
関数を作成せよ。
––

引数と戻り値の型：DOUBLE PRECISION
関数の内容：

a := x1*x2+y1*y2+z1*z2;
IF (a>1) THENa := 1;ELSEIF (a<-1) THENa := -1;
END IF;
RETURN DEGREES(ACOS(a))*60;• 丸め誤差を警戒してACOSの定義域を制限。
2021年12⽉21―22⽇

データベース講習会

197

2. 次のSQLを実⾏し、結果が「5400」になることを確認せよ。
cat=> SELECT fDistanceArcminXyz(1,0,0,0,1,0);

3. 【fArcmin2Rad.sql】 分 ⾓ を ラ ジ ア ン に 変 換 す る
PL/pgSQL関数を作成せよ。
––

引数と戻り値の型：DOUBLE PRECISION
度をラジアンに変換する関数：RADIANS(DP)4. 次のSQLを実⾏し、結果が「3.14...」になることを確認せよ。
cat=> SELECT fArcmin2Rad(180*60);

2021年12⽉21―22⽇

データベース講習会

v【解答例】

198

1. 【fDistanceArcminXyz.sql】天球⾯上の点1（x1, y1,z1）と点2（x2, y2, z2）の間の⾓度（分⾓）を求める
PL/pgSQL関数を作成せよ。
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;
2021年12⽉21―22⽇

データベース講習会

199

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

2021年12⽉21―22⽇

データベース講習会

200

10.3.2. コーンサーチ⽤SQLの作成
v【実習】⾚経200度、⾚緯5度を中⼼とした半径60分内にある天体を検索する場合のSQLの作成
n 【s1032eg1.sql】1辺2Rの⽴⽅体内にある天体のインデックス検索
cat=> SELECT t1.id,t1.ra,t1.dc
FROM pgc2003 AS t1 INNER JOIN pgc2003_xyz AS t2
ON t1.id=t2.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));• fEq2(X|Y|Z)+/-farcmin2Rad(60)で検索中⼼+/-Rを表現。
• pgc2003の情報を得るためにpgc2003とpgc2003_xyzを内部結合。

2021年12⽉21―22⽇

データベース講習会

201

n 【s1032eg2.sql】1辺2Rの⽴⽅体内にある天体に対して検索中⼼との離⾓を計算し指定した半径（60度）以内にあるものを表⽰
SELECT t.id,t.ra,t.dc,t.distance
FROM (
SELECT t1.id,t1.ra,t1.dc,
fDistanceArcminXyz(
fEq2X(200,5),
fEq2Y(200,5),
fEq2Z(5),x,y,z) AS distance
FROM pgc2003 AS t1 INNER JOIN pgc2003_xyz AS t2
ON t1.id=t2.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;• ｆDestanceArcminXyzで離⾓を測定。
• 主SELECT分のWHERE句で離⾓が条件を満たすか判定。
2021年12⽉21―22⽇

データベース講習会

10.3.3. コーンサーチの実装

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

複合型の要素>>>>id TEXT
ra DOUBLE PRECISION
dc DOUBLE PRECISION
distance DOUBLE PRECISION

2. 【fConesearchPgc2003.sql】 テーブル「pgc2003」
に対しコーンサーチをおこなうSQL関数を作成せよ。
–––
2021年12⽉21―22⽇

引数は「ra,dc,r」
引数の型：DOUBLE PRECISION
戻り値の型：SETOF TypeCone
データベース講習会

203

3. 以下のSQLの検索件数がSQL「s1032eg2.sql」と同じ結果になるか確認せよ。
cat=> SELECT * FROM fConesearchPgc2003(200,5,60);

4. 【fConesearchNvss.sql】 テーブル「nvss」⽤のコー
ンサーチ⽤SQL関数を作成せよ。

2021年12⽉21―22⽇

データベース講習会

204v【解答例】 コーンサーチ⽤SQL関数の作成
1. 【TypeCone.sql】コーンサーチ⽤ユーザ定義関数の実⾏結果を返すために必要な複合データ型「TypeCone」
を作成せよ。
CREATE TYPE TypeCone AS (
id
TEXT,
ra
DOUBLE PRECISION,
dc
DOUBLE PRECISION,
distance DOUBLE PRECISION);

2021年12⽉21―22⽇

データベース講習会

205

2. 【fConesearchPgc2003.sql】テーブル「pgc2003」
に対しコーンサーチをおこなうSQL関数を作成せよ。
CREATE FUNCTION fConesearchPgc2003 (DOUBLE PRECISION,DOUBLE PRECISION,DOUBLE PRECISION) RETURNS SETOF TypeCone AS $$SELECT t.id,t.ra,t.dc,t.distance
FROM (
SELECT t1.id,t1.ra,t1.dc,
fDistanceArcminXyz(
fEq2X($1,$2),
fEq2Y($1,$2),
fEq2Z($2),x,y,z) AS distance
FROM pgc2003 AS t1 INNER JOIN pgc2003_xyz AS t2
ON t1.id=t2.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;
2021年12⽉21―22⽇

データベース講習会

206v【コラム】 コーンサーチ⽤PL/pgSQL関数の例• 関数中のFROM句で仮引数や変数を使うことはできない。
• テーブル名を引数で指定したい場合はPL/pgSQL関数でSQL⽂を⽂字列として動的に⽣成し、EXECUTE⽂で⽂字列の内容を実⾏する。
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 t1.id,t1.ra,t1.dc,
fDistanceArcminXyz(
fEq2X(‘||ra||’,’||dc||’),
fEq2Y(‘||ra||’,’||dc||’),
fEq2Z(‘||dc||’),
x,y,z) AS distance
FROM ‘||tbl||’ AS t1 INNER JOIN ‘||tbl||’_xyz AS t2 ON t1.id=t2.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 <= ‘||r ;RETURN QUERY EXECUTE sql;
RETURN;
END$$ IMMUTABLE LANGUAGE PLPGSQL;

2021年12⽉21―22⽇

データベース講習会

10.4. クロスマッチの実装

207

vクロスマッチの実装• コーンサーチを利⽤すれば、複数のカタログ間での天体のクロスマッチを簡単に実装できる。
• 実装⼿法1. カタログAから天体の⾚経⾚緯を取り出す。
2. カタログBから1で取り出した⾚経⾚緯を中⼼にコーン
サーチをおこない、最も近傍の天体を取り出す。

• 必要なもの– 検索半径内で最も検索中⼼に近い天体を取り出すクロス
マッチ⽤関数v10.4節⽬次• 10.4.1. クロスマッチの実装• 10.4.2. クロスマッチの実⾏
2021年12⽉21―22⽇

データベース講習会

10.4.1. クロスマッチの実装

208v【実習】 クロスマッチ⽤関数の作成
n 【fGetNearestNvssObjID.sql】テーブル「nvss」に対してコーンサーチをおこない、検索中⼼に最も近い天体の「id,ra,dc,distance」を返す関数
CREATE FUNCTION fGetNearestNvssObjID (DOUBLE PRECISION,DOUBLE PRECISION,DOUBLE PRECISION) RETURNS SETOF TypeCone AS $$SELECT t.id,t.ra,t.dc,t.distance
FROM (
SELECT t1.id,t1.ra,t1.dc,
fDistanceArcminXyz(
fEq2X($1,$2),
fEq2Y($1,$2),
fEq2Z($2),x,y,z) AS distance
FROM nvss AS t1 INNER JOIN nvss_xyz AS t2 ON t1.id=t2.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;
2021年12⽉21―22⽇

データベース講習会

209

10.4.2. クロスマッチの実⾏
v【実習】 クロスマッチの実⾏

n 【s1042eg1.sql】テーブル「pgc2003」から「NGC5194」を検索し、テーブル「NVSS」から1分⾓でクロスマッチ
WITH t1 AS (SELECT id,ra,dc
FROM pgc2003
WHERE anames LIKE ’%NGC5194%’)
SELECT * FROM t1 CROSS JOIN
fGetNearestNvssObjID(t1.ra,t1.dc,1) AS t2;

2021年12⽉21―22⽇

データベース講習会

おわりに

210

vADCの講習会ウェブページには過去のSQL講習会の資料も公開されています。
v特に「2016年度SQL講習会[中級編：データベース
構築編]資料（⼭内 千⾥）」では本講習会の内容に加え、以下のような⾼度な⼿法も紹介されています。
• C⾔語でのユーザー定義関数の作成⽅法• テーブルパーティショニングの⽅法• 画像ファイルの検索⽅法

2021年12⽉21―22⽇

データベース講習会