Window 関数(1 )position を振り直す 実際にposition を振り直すには、下記のSQLを実行します。新たにposition を振っ た表をWindow関数を使って用意して、それを元のテーブルと結合してUPDATEしてい ます。 name | position | updated_at ++ A | 1 | 20180321 22:17:49.215978 B | 2 | 20180321 22:17:49.215978 F | 3 | 20180323 10:17:49.220138 C | 4 | 20180321 22:17:49.215978 D | 5 | 20180321 22:17:49.215978 E | 6 | 20180321 22:17:49.215978 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;
Window 関数(2) セッション一覧 さきほどの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
Trigger - トリガーをテーブルに適用する CREATE TRIGGER でトリガーをテーブルに適用します。 CREATE TRIGGER audit_items_changes BEFORE UPDATE ON items FOR EACH ROW EXECUTE PROCEDURE audit_changes();
Materialized view - テーブル定義(おさらい) 改めて先程の「ワイン」テーブルと「ビール」テーブルの定義です。 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 );
Materialized view - マテリアライズド・ビュー の定義 「ワイン」テーブルと「ビール」テーブルの型を揃えて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);