儲存歷史記錄並從給定時期提取數據
這是一個關於數據庫建模和 PostgreSQL 的問題。這篇文章部分回答了這個問題，但我需要更多技術方面的建議：列和查詢以提取數據。
我需要在給定區域內儲存行政邊界歷史，以便能夠跟踪過去幾年的邊界運動。
以下是邊界如何移動的範例：
Martin Land地區一直存在
Buwers Land地區也一直存在
這些領域在 2015 年合併在一起：
- 新區域更名為Martin-Bulwers
- 新區域保留了 Bulwers Land程式碼（Martin Land程式碼已棄用）
這是我儲存數據的方式：
gid | code | name | change_date -----+--------+-----------------+------------- 1 | 86001 | Martin Land | 2000-01-01 2 | 86002 | Bulwers Land | 2000-01-01 3 | 86002 | Martin-Bulwers | 2015-01-01我已經為一個區域的歷史情況設置了預設值
change_date，2000-01-01即在第一次更改發生之前。然後，我有許多不同的案例：尚未合併的區域，在不同日期與其他區域合併的區域。這可以產生以下範例：
CREATE TEMPORARY TABLE foo AS SELECT gid,code,area,change_date::date FROM ( VALUES ( 1,86001,'Martin Land' ,'2000-01-01' ), ( 2,86002,'Bulwers Land' ,'2000-01-01' ), ( 3,86002,'Martin-Bulwers','2015-01-01' ), ( 4,86003,'Coveral Land' ,'2000-01-01' ), ( 5,86004,'Big Tom Area' ,'2000-01-01' ), ( 6,86005,'Small Tom Area','2000-01-01' ), ( 7,86004,'Tom Land' ,'2016-01-01' ) ) AS t(gid,code,area,change_date);然後我很難編寫一個可以返回給定年份區域列表的查詢。我試圖依靠
DISTINCT ON()條款，但這不是我需要的工作……例如，以下查詢將返回下表：
SELECT DISTINCT ON (code) code, area, change_date FROM myTable WHERE change_date < '2016-01-01' ORDER BY code, change_date DESC ; -- Result: code | area | change_date -------+-----------------+------------- 86001 | Martin Land | 2000-01-01 86002 | Martin-Bulwers | 2015-01-01 86003 | Coveral Land | 2000-01-01 86004 | Big Tom Area | 2000-01-01 86005 | Small Tom Area | 2000-01-01這和以前一樣，部分正確
2016-01-01，大湯姆地區和小湯姆地區沒有合併在一起，但Martin Land和Bulwers Land有！2014年有5個，2015年有4個，2016年有3個。事實上，我需要以下結果：
code | area | change_date -------+-----------------+------------- 86002 | Martin-Bulwers | 2015-01-01 86003 | Coveral Land | 2000-01-01 86004 | Big Tom Area | 2000-01-01 86005 | Small Tom Area | 2000-01-01是否有另一種方法來儲存這些資訊或編寫查詢？
這是我最終採用的一個解決方案，它被證明足以滿足我的需求，並且避免使用
infinity日期語句。gid | code | name | start_date | end_date | parent -----+------+-----------------+------------+------------+----------- 1 | 86001| Martin Land | 2000-01-01 | 2014-12-31 | 2 | 86002| Bulwers Land | 2000-01-01 | 2014-12-31 | 3 | 86002| Martin-Bulwers | 2015-01-01 | 2100-01-01 | {86001,86002}我添加了一個
parent列來儲存合併以產生新區域的先前區域的程式碼。我也使用 astart_date和end_date列。然後，以下查詢可以選擇給定年份的區域：
-- Year 2015 SELECT * FROM table WHERE start_date <= '2015-01-01' AND end_date >= '2015-01-01' ; -- Result: gid | code | name | start_date | end_date | parent -----+------+-----------------+------------+------------+----------- 3 | 86002| Martin-Bulwers | 2015-01-01 | 2100-01-01 | {86001,86002} -- Year 2012 SELECT * FROM table WHERE start_date <= '2012-01-01' AND end_date >= '2012-01-01' ; -- Result: gid | code | name | start_date | end_date | parent -----+------+-----------------+------------+------------+----------- 1 | 86001| Martin Land | 2000-01-01 | 2014-12-31 | 2 | 86002| Bulwers Land | 2000-01-01 | 2014-12-31 |當然，我設置了
start_date等於，2000-01-01因為我知道在 2000 年之前沒有合併區域。然後我可以認為我的區域故事的起源是 2000 年。另一件事是關於end_date我設置2100-01-01為避免使用infinity.這當然不是最好的選擇（對不起@Evan Carroll！），但它對我來說非常完美，我沒有足夠的時間深入重新組織我的數據（不幸的是……）。
無論如何，感謝大家提供的幫助！
問題是當一個區域被合併並且它的程式碼被重用時，你有那個資訊，但是當一個區域被合併並且它的程式碼被棄用時，你沒有它。換句話說，您不知道某個區域已被棄用。
現在，我看到了兩種解決方案。首先，保持模式不變，每當一個區域被棄用時，添加一個新行，因為它是一個新區域並聲明它已被棄用，例如
INSERT INTO myTable(code, area, change_date) VALUES(86001, 'deprecated', '2015-01-01')因此，在您的查詢中，它將顯示為
'deprecated'或者您將其過濾掉。第二個也是可能的最佳選擇是有一個新列來說明何時（以及是否）棄用了某個區域：
ALTER TABLE myTable ADD deprecated DATE; UPDATE myTable SET deprecated = '2015-01-01' WHERE gid = 1;因此，在您的查詢中，您可以簡單地添加過濾器：
(deprecated IS NULL OR deprecated >= '2016-01-01')完整程式碼：
SELECT DISTINCT ON (code) code, area, change_date FROM myTable WHERE change_date < '2016-01-01' AND (deprecated IS NULL OR deprecated >= '2016-01-01') ORDER BY code, change_date DESC;作為非棄用區域的另一個選項，您可以將它們設置為
'infinity'而不是NULL，因此查詢不需要deprecated IS NULL條件，因為'infinity'它將始終高於或等於任何其他非空值。