varying(64) NOT NULL, "価格" numeric NOT NULL DEFAULT 0, "売上日時" timestamp NOT NULL DEFAULT now(), "売上月" character varying(6) NOT NULL ) PARTITION BY LIST ("売上月"); -- 文字列指定の場合のパーティション demo=# CREATE TABLE "2018年10月" PARTITION OF "販売履歴" FOR VALUES IN ('201810'); demo=# CREATE TABLE "2018年9月" PARTITION OF "販売履歴" FOR VALUES IN ('20189'); demo=# CREATE TABLE "2018年8月" PARTITION OF "販売履歴" FOR VALUES IN ('20188'); demo=# CREATE TABLE "2018年7月" PARTITION OF "販売履歴" FOR VALUES IN ('20187'); demo=# CREATE TABLE "2018年6月" PARTITION OF "販売履歴" FOR VALUES IN ('20186');
NULL, "価格" numeric NOT NULL DEFAULT 0, "売上日時" timestamp NOT NULL DEFAULT now(), ) PARTITION BY RANGE ("売上日時"); demo=# CREATE TABLE "2018年10月RANGE" PARTITION OF "販売履歴2" FOR VALUES FROM ('2018-10-01 00:00:00') TO ('2018-11-1 00:00:00 ');
(5), (5), (6)) SELECT value, array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS current_row, array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS group, array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS ties, array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) AS no_others FROM t WINDOW obj AS (ORDER BY value); value | current_row | group | ties | no_others -------+-------------+-------+---------+----------- 1 | {1} | | {1} | {1,1} 1 | {1,3} | {3} | {1,3} | {1,1,3} 3 | {1,5} | {1,5} | {1,3,5} | {1,3,5} 5 | {3,5} | {3} | {3,5} | {3,5,5} 5 | {5,5} | | {5} | {5,5,5} 5 | {5,6} | {6} | {5,6} | {5,5,6} 6 | {5} | {5} | {5,6} | {5,6} (7 rows)