の順にソートした場合の行番号が取得できます。 name | position | i | updated_at +++ A | 1 | 1 | 20180321 22:17:49.215978 B | 2 | 2 | 20180321 22:17:49.215978 F | 3 | 3 | 20180321 22:17:49.215978 C | 3 | 4 | 20180321 22:17:49.215978 D | 4 | 5 | 20180321 22:17:49.215978 E | 5 | 6 | 20180321 22:17:49.215978 SELECT name, position, ROW_NUMBER() OVER ( ORDER BY position, updated_at DESC) AS i FROM categories;
| 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;
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
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; $$;
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 );
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 );
貼ることができます。 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);
※ メートル単位での計算はジオグラフィ型で行う必要があるので、ST_Transform を変換しています。 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
fujimura
soracom
upura
quiver
inomasosan
koba04
tagucch
hatena
hanafusa1991
110y
hololab
chao2suke
rmakiyama
lauravandoore
morganepeng
michaelherold
kneath
carmenhchung
jnunemaker
bryan
chrislema
sachag
jrom
qrush
tammielis
![PostGIS - Rails では？ https://github.com/rgeo/activerecord-postgis-adapter ジオメトリ型がマイグレーションで定義できます。 class CreateStations < ActiveRecord::Migration[5.1]](https://files.speakerdeck.com/presentations/491fbd3e73d64d6fb620101c05429230/slide_48.jpg){kind=link}
