title | subtitle | author | date |
---|---|---|---|
それPostgreSQLでできるよ |
@Rails Developer Meetup 2018 Day 1 |
Daisuke Fujimura |
2018-03-24 |
- PostgreSQLには便利な機能がたくさんあります。業務の中で「これできないかな?」と思って調べると関数や拡張が用意されていた、ということも多々ありました。ActiveRecordおよび標準SQLを使って仕事をしていると見えてこない「レールの外」のPostgreSQLの世界をご紹介しようと思います。
- PostgreSQLの便利な機能をそれぞれユースケースを交えて解説します。
- 原則、AWS RDS、Google Cloud SQL for PosterSQLで使える機能のみ紹介します。
- 利用したPostgreSQLのバージョンは10.1、PostGISのバージョンは2.4.3です。
- 発表で登場するコード例は https://github.com/fujimura/railsdm_2018_postgresql にあります。
藤村大介
<img src="./fujimura.png"/ style='width: 60px'>
ご近所SNSマチマチを運営する株式会社マチマチのCTO。Railsは2.2位から仕事で使っている。フロントエンド開発も得意。
- twitter.com/ffu_
- fujimuradaisuke.com
SQL歴は足掛け10年程度。PostgreSQL歴は4年程度。内部構造の詳しい知識などはありません。
<img src="./machimachi.png"/ style='width: 95%; height: 90%'>
<img src="./fujimura.png"/ style='width: 60px'> @fujimura <img src="./knu.png"/ style='width: 60px'> @knu <img src="./imaz.png"/ style='width: 60px'> @imaz(フリーランス)
- バックエンド:Rails, PostgreSQL
- フロントエンド:React, Flowtype
- ネイティブアプリ:これからReact Nativeで作る
エンジニア募集中です!藤村まで気軽にお声かけ下さい。
最近MySQLにも入ったWindow関数ですが皆さん使っていますか?データ分析ではよく使いますが、アプリケーション開発ではあまり使わないかもしれません。
このテーブルの各行のposition
を重複なしタイムスタンプ順で振りなおしたい
name | position | updated_at ------+----------+---------------------------- A | 1 | 2018-03-21 22:17:49.215978 B | 2 | 2018-03-21 22:17:49.215978 F | 3 | 2018-03-23 10:17:49.220138 C | 3 | 2018-03-21 22:17:49.215978 D | 4 | 2018-03-21 22:17:49.215978 E | 5 | 2018-03-21 22:17:49.215978
Window関数は「現在の行」の情報を使った値を計算する機能です。例えばROW_NUMBER
を使うと、特定のカラムでソートした際の現在の行番号がわかります。
例えばさきほどのテーブルにこのクエリを実行すると
SELECT name, position,
ROW_NUMBER() OVER ( ORDER BY position, updated_at DESC) AS i
FROM categories;
position
、updated_at
の順にソートした場合の行番号が取得できます。
name | position | i | updated_at ------+----------+---+---------------------------- A | 1 | 1 | 2018-03-21 22:17:49.215978 B | 2 | 2 | 2018-03-21 22:17:49.215978 F | 3 | 3 | 2018-03-21 22:17:49.215978 C | 3 | 4 | 2018-03-21 22:17:49.215978 D | 4 | 5 | 2018-03-21 22:17:49.215978 E | 5 | 6 | 2018-03-21 22:17:49.215978
実際にposition
を振り直すには、下記のSQLを実行します。新たにposition
を振った表をWindow関数を使って用意して、それを元のテーブルと結合してUPDATEしています。
UPDATE categories
SET POSITION = c.row_number
FROM
(SELECT name,
ROW_NUMBER() OVER (ORDER BY POSITION, updated_at DESC) AS row_number
FROM categories) AS c
WHERE c.name = categories.name;
name | position | updated_at ------+----------+---------------------------- A | 1 | 2018-03-21 22:17:49.215978 B | 2 | 2018-03-21 22:17:49.215978 F | 3 | 2018-03-23 10:17:49.220138 C | 4 | 2018-03-21 22:17:49.215978 D | 5 | 2018-03-21 22:17:49.215978 E | 6 | 2018-03-21 22:17:49.215978
アクセスログから30分以内の連続したアクセスを一つのセッションとして、セッションの一覧を出したい。
ユーザーIDとしてid
、タイムスタンプとしてtime
を持った簡単なログテーブルがあるとします。
id | time
----+----------------------------
1 | 2018-03-10 06:14:06.265533
1 | 2018-03-10 07:04:06.265533
2 | 2018-03-10 07:31:06.265533
1 | 2018-03-10 07:35:06.265533
2 | 2018-03-10 08:02:06.265533
2 | 2018-03-10 08:03:06.265533
2 | 2018-03-10 08:04:06.265533
1 | 2018-03-10 08:05:06.265533
2 | 2018-03-10 08:14:06.265533
1 | 2018-03-10 08:14:06.265533
LAG
で前の行が取得できます。この例ではid
ごとに前の行のtime
を取得、現在の行のtime
との差を求めています。
SELECT
id,
time as current_row,
LAG(time) OVER (PARTITION BY id ORDER BY time) as previous_row,
(time - (LAG(time) OVER (PARTITION BY id ORDER BY time))) / 60 as difference
FROM access_logs
ORDER BY id, current_row ASC
id | current_row | previous_row | difference ----+----------------------------+----------------------------+------------ 1 | 2018-03-10 06:14:06.265533 | ¤ | ¤ 1 | 2018-03-10 07:04:06.265533 | 2018-03-10 06:14:06.265533 | 00:00:50 1 | 2018-03-10 07:35:06.265533 | 2018-03-10 07:04:06.265533 | 00:00:31 1 | 2018-03-10 08:05:06.265533 | 2018-03-10 07:35:06.265533 | 00:00:30 1 | 2018-03-10 08:14:06.265533 | 2018-03-10 08:05:06.265533 | 00:00:09 2 | 2018-03-10 07:31:06.265533 | ¤ | ¤ 2 | 2018-03-10 08:02:06.265533 | 2018-03-10 07:31:06.265533 | 00:00:31 2 | 2018-03-10 08:03:06.265533 | 2018-03-10 08:02:06.265533 | 00:00:01 2 | 2018-03-10 08:04:06.265533 | 2018-03-10 08:03:06.265533 | 00:00:01 2 | 2018-03-10 08:14:06.265533 | 2018-03-10 08:04:06.265533 | 00:00:10
さきほどのSQLより、前回との差がNULL(初回アクセス)、前回との差が30分以上(新しいセッション)の行を取得すると、セッション一覧になります。
SELECT
id, s.time as timestamp
FROM (
SELECT
CASE
WHEN LAG(time) OVER (PARTITION BY id ORDER BY time) IS NULL THEN time
WHEN EXTRACT(epoch FROM time - (LAG(time) OVER (PARTITION BY id ORDER BY time))) > 60 * 30 THEN time
ELSE NULL
END AS time,
id
FROM
access_logs
ORDER BY
time ) as s
WHERE
time IS NOT NULL
order by timestamp, id
id | current_row | previous_row | difference ----+----------------------------+----------------------------+------------ 1 | 2018-03-10 06:14:06.265533 | ¤ | ¤ 1 | 2018-03-10 07:04:06.265533 | 2018-03-10 06:14:06.265533 | 00:00:50 1 | 2018-03-10 07:35:06.265533 | 2018-03-10 07:04:06.265533 | 00:00:31 1 | 2018-03-10 08:05:06.265533 | 2018-03-10 07:35:06.265533 | 00:00:30 1 | 2018-03-10 08:14:06.265533 | 2018-03-10 08:05:06.265533 | 00:00:09 2 | 2018-03-10 07:31:06.265533 | ¤ | ¤ 2 | 2018-03-10 08:02:06.265533 | 2018-03-10 07:31:06.265533 | 00:00:31 2 | 2018-03-10 08:03:06.265533 | 2018-03-10 08:02:06.265533 | 00:00:01 2 | 2018-03-10 08:04:06.265533 | 2018-03-10 08:03:06.265533 | 00:00:01 2 | 2018-03-10 08:14:06.265533 | 2018-03-10 08:04:06.265533 | 00:00:10
id | timestamp ----+---------------------------- 1 | 2018-03-10 06:14:06.265533 1 | 2018-03-10 07:04:06.265533 2 | 2018-03-10 07:31:06.265533 1 | 2018-03-10 07:35:06.265533 2 | 2018-03-10 08:02:06.265533
ActiveRecord::Connection.execute
ActiveRecord::Querying#find_by_sql
ActiveRecord::QueryMethods#select
PostgreSQLではTriggerという仕組みを使って、行への操作(挿入・更新・削除)があった時に特定の関数を実行することができます。
テーブルの変更履歴をアプリケーション側ではなくデータベース側のみで自動で記録したい。
公式ドキュメントによると、
CREATE TRIGGERは新しいトリガを作成します。 作成したトリガは指定したテーブルまたはビューと関連付けられ、特定のイベントが発生した時に指定した関数function_nameを実行します
https://www.postgresql.jp/document/10/html/sql-createtrigger.html
とのこと。
関数はPL/pgSQLという手続き型プログラミング言語で記述し、コードの中にSQLを書いて行の操作をすることができます。
下記のトリガーでレコードに更新があった際にchanges
というテーブルに変更を保存します。変更前、変更後の行をOLD
とNEW
で参照することができるので、それをrow_to_json
でJSONに変換して保存しています。
CREATE OR REPLACE FUNCTION audit_changes() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO changes (table_name, operation, old_content, new_content, created_at)
VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), now());
RETURN NEW;
END IF;
END;
$$;
CREATE TRIGGER
でトリガーをテーブルに適用します。
CREATE TRIGGER audit_items_changes BEFORE UPDATE ON items FOR EACH ROW EXECUTE PROCEDURE audit_changes();
このテーブルを
name | type
-----------+-------
Apple | Fruit
Wine | Drink
Beer | Drink
Chocolate | Food
変更すると
UPDATE items SET name = 'Orange' WHERE name = 'Apple';
履歴が保存されます。
table_name | operation | old_content | new_content | created_at
------------+-----------+-------------------+--------------------+----------------------------
items | UPDATE | {"name": "Apple", | {"name": "Orange", | 2018-03-10 09:27:05.213614
| | "type": "Fruit"} | "type": "Fruit"} |
- マイグレーション内で
ActiveRecord::Connection.execute
要はキャッシュされたビューです。
下記のように、「ワイン」テーブルと「ビール」テーブルがあるとします。これを「飲み物」テーブルとして横断して検索したくなりました。
CREATE EXTENSION pgcrypto; -- gen_random_uuid()のために必要
CREATE TABLE wines (
id uuid DEFAULT gen_random_uuid() NOT NULL,
name varchar,
color varchar,
price integer
);
CREATE TABLE beers (
id uuid DEFAULT gen_random_uuid() NOT NULL,
name varchar,
type varchar,
price integer
);
ビューはSELECT文を保存してあたかもテーブルかのように扱える機能です。
通常のビューは毎回その定義のSELECT文を実行します。マテリアライズド・ビューは通常のビューとは違い、SELECT文の結果が保存されます。
結果が保存されているので通常のビューよりも多くの場合高速ですが、データの更新は手動で行う必要があります。また、インデックスを貼ることも可能です。
「ワイン」テーブルと「ビール」テーブルをまとめた、「飲み物」マテリアライズド・ビューを定義してみましょう。
改めて先程の「ワイン」テーブルと「ビール」テーブルの定義です。
CREATE TABLE wines (
id uuid DEFAULT gen_random_uuid() NOT NULL,
name varchar,
color varchar,
price integer
);
CREATE TABLE beers (
id uuid DEFAULT gen_random_uuid() NOT NULL,
name varchar,
type varchar,
price integer
);
「ワイン」テーブルと「ビール」テーブルの型を揃えてUNION
したものを「飲み物」ビューとして定義します。
beverages.id
の型はUUIDなので重複がありません。なのでユニークインデックスを貼ることができます。
CREATE MATERIALIZED VIEW beverages AS
SELECT w.id AS id,
w.name AS name,
w.price AS price,
'wines' AS type
FROM wines AS w
UNION ALL
SELECT b.id AS id,
b.name AS name,
b.price AS price,
'beers' AS type
FROM beers AS b
;
CREATE UNIQUE INDEX index_beverages_id ON beverages USING btree (id);
データを投入した後、REFRESH MATERIALIZED VIEW
でデータを更新します。ユニークインデックスがあるとCONCURRENTLY
オプションを使うことができます。これを指定すると更新中のビューへの読み込みロックが回避できます。
INSERT INTO wines
(name, color, price)
VALUES
('Rotten Highway', 'White', 10000),
('yellow tail Chardonnay', 'White', 1000)
;
INSERT INTO beers
(name, type, price)
VALUES
('Old Rasputin', 'Imperial Stout', 1300),
('Ichiban Shibori', 'Lager', 300)
;
REFRESH MATERIALIZED VIEW CONCURRENTLY beverages;
マテリアライズド・ビューを使って1000円以下の「ビール」と「ワイン」を一度に検索することができました😇
SELECT * FROM beverages
WHERE price <= 1000;
id | name | price | type
--------------------------------------+------------------------+-------+-------
1f17edd2-f3df-4cd7-b72e-faff06df6130 | Ichiban Shibori | 300 | beers
73afb8eb-9f6f-4e1c-a3ef-5f4ee36ff3af | yellow tail Chardonnay | 1000 | wines
- https://github.com/thoughtbot/scenic でマイグレーションとして管理できます。とても便利!
- ディスクスペースが必要になる、リフレッシュが必要、リフレッシュのコストがかかるというデメリットがあります。詳しくは https://www.slideshare.net/SoudaiSone/postgre-sql-54919575 を参照ください。
- 個人的には 1) リフレッシュが遅れても問題なく 2) 更新頻度が低い 場合のみマテリアライズド・ビュー使用可としています。
- ソースとなるテーブルの主キー(
id
)をRailsでよくあるincremental idにするとビューで主キー(的なもの)が作れません。- キーが衝突するのでユニークインデックスが貼れないのでリフレッシュ時に
CONCURRENTLY
オプションが使えず、更新中にソースとなっているテーブルにロックがかかってしまいます。 id
をUUIDにするとこれを回避できます。- ちなみにRails 5からActiveRecordでprimary keyをUUIDにできるようになりました。詳しく http://blog.bigbinary.com/2016/04/04/rails-5-provides-application-config-to-use-UUID-as-primary-key.html を参照ください。
- キーが衝突するのでユニークインデックスが貼れないのでリフレッシュ時に
東京都の近くに公園が多い駅ランキングを出したい。
地理空間情報を扱うための拡張です。そもそも地理空間情報って何?という問いについては、これからお話しする実例を通してお答えできればと思います。
PostGISが具体的に提供する機能としては、1) ジオメトリカラムの定義 2) ジオメトリ関数 の2つです。
ジオメトリは点、線、面など、空間上を占める何らかの情報のことです。
駅のテーブル(stations
、定義はのちほど)を使って、代々木公園駅から距離が近い駅ランキングを出してみましょう。
下記のようにST_Distance_Sphere
で二点間の距離を出すことができます。
SELECT s1.name, s2.name,
ST_Distance_Sphere(s1.geom, s2.geom) AS distance
FROM stations s1
CROSS JOIN stations s2
WHERE s1.name = '代々木公園' AND s1.name <> s2.name
ORDER BY distance
LIMIT 5;
+------------+------------+------------+
| name | name | distance |
|------------+------------+------------|
| 代々木公園 | 代々木上原 | 0.00894604 |
| 代々木公園 | 参宮橋 | 0.0104074 |
| 代々木公園 | 駒場東大前 | 0.0115478 |
| 代々木公園 | 初台 | 0.0123519 |
| 代々木公園 | 神泉 | 0.0127556 |
+------------+------------+------------+
「駅の最寄りエリア」の面を作って、その中にある公園を数えるという方向で挑みます。
<img src="./parks.png"/ style='width: 85%'>
geometry(Point, 4326)
※ という設定で空間上の「点」を表すジオメトリ型のカラムを定義できます。要は緯度経度です。geometry(Polygon, 4326)
でポリゴン、つまり「面」を定義できます。
公園(parks
)にはgeom
として公園を代表する地点を、駅(stations
)にはgeom
として駅の地点、nearby
として最寄りエリアを定義しました。
CREATE EXTENSION postgis;
CREATE TABLE parks (
prefecture varchar,
city varchar,
name varchar,
type varchar,
geom geometry(Point, 4326)
);
CREATE TABLE stations (
code integer,
name varchar,
prefecture_code integer,
address varchar,
geom geometry(Point, 4326),
nearby geometry(Polygon, 4326)
);
※: 4326
って何?と思う方へ: 緯度経度の測り方は何種類もあって、それぞれ測地系と呼ばれています。4326
は世界測地系WGS84というよく使われている測地系を表すコードです。
平面上にいくつか点があって、その平面を「どの点に一番近いか」で分割した図がボロノイ図です。これを使うと「最寄りエリア」のポリゴンを作れます。
下記のように駅の地点を使ってボロノイ図を描画し、それを最寄りエリアテーブル(polygons
)として保存します。
CREATE TABLE polygons (geom geometry(Polygon, 4326));
CREATE INDEX index_polygons_geom ON polygons USING gist (geom);
INSERT INTO polygons (geom)
SELECT g.geom
FROM
(
SELECT (
-- ST_Dump: 複数のジオメトリを行に展開する。ボロノイ図は面の集合として返されるので、展開が必要
ST_Dump(
-- ST_VoronoiPolygons: 複数のジオメトリ(駅の地点の集合)からボロノイ図を描画する
ST_VoronoiPolygons(
-- ST_Union: 複数のジオメトリ(ここでは駅の地点)を一つのジオメトリ(駅の地点の集合)にまとめる
ST_Union(geom)
)
)
).geom AS geom
FROM stations
) as g;
地図上に表示すると、このように区分けができました。がしかし、これだとどこがどの駅がわかりません。 <img src="./shinjuku_polygons.png"/ style='width: 85%'>
最寄りエリアにどの駅(の地点)が含まれているかがわかれば、エリアのポリゴンと駅の対応がわかるはず。
PostGISの関数ST_Contains
で包含関係を判定できるので、これを使って駅テーブルと駅の最寄りエリアテーブルを結合し、更新します。
UPDATE stations
SET nearby = polygons.geom
FROM polygons
WHERE ST_Contains(polygons.geom, stations.geom);
🎉 🎉 🎉
<img src="./shinjuku_polygons_with_name.png"/ style='width: 85%'>
さきほど登場したST_Contains
を使って、最寄りエリアに入っている公園を数えます。
ジオメトリの面積をST_Area
で求められるので※、それを使って平方キロメートルあたりの公園数をp_sqkm
として計算しています。
SELECT stations.name,
stations.address,
COUNT(parks.name) p,
FLOOR(ST_Area(ST_Transform(stations.nearby, 4326)::geography)) area,
FLOOR(COUNT(parks.name) / ST_Area(ST_Transform(stations.nearby, 4326)::geography) / (1000 * 1000)) p_sqkm
FROM stations
LEFT OUTER JOIN parks ON st_contains(stations.nearby, parks.geom)
WHERE prefecture_code = 13
GROUP BY stations.address, stations.name, stations.nearby
ORDER BY p_sqkm DESC
※ メートル単位での計算はジオグラフィ型で行う必要があるので、ST_Transform
を変換しています。
まさかの「新大久保」駅がナンバーワンでした!
name | address | p | area | p_sqkm
--------------------------+-------------------------------------+-----+-----------+--------
新大久保 | 東京都新宿区百人町一丁目10-15 | 12 | 513173 | 23
京成曳舟 | 墨田区京島1-39-1 | 13 | 712581 | 18
大鳥居 | 大田区西糀谷3-37-18 | 31 | 1638396 | 18
大森町 | 大田区大森西3-24-7 | 25 | 1464670 | 17
千鳥町 | 大田区千鳥1-20-1 | 14 | 844929 | 16
松陰神社前 | 世田谷区若林4-21-16 | 10 | 604776 | 16
志村三丁目 | 板橋区志村3-23-1 | 27 | 1615272 | 16
結果をビジュアライズしました。ポケットパークという公園が沢山あります。 <img src="./shinookubo.png"/ style='width: 85%'>
多いような気もします。 <img src="./hikifune.png"/ style='width: 85%'>
面積を考慮しない公園の数ではここがトップです。広い。 <img src="./tamagawagakuenmae.png"/ style='width: 85%'>
ジオメトリ型がマイグレーションで定義できます。
class CreateStations < ActiveRecord::Migration[5.1]
def change
create_table :stations do |t|
t.string :name
t.string :line_name
t.st_point :geom
t.timestamps
end
add_index :welfare_facilities, :geom, using: :gist
end
end
- 公園: 国土数値情報 都市公園データ
- 駅: 駅データ.jp
- 地図: OSM Standard on QGIS, ©OpenStreetMap contributors
- PostgreSQLには便利な機能が沢山
- 少しRailsのレールを外れると面白い世界が広がっている
- ご利用は計画的に