視窗函數如何解決複合鍵中的序號挑戰？

解複合鍵中的序號挑戰

資料庫管理通常需要在行組內分配序號，特別是在處理複合鍵時。 這可能很棘手，特別是在確保多個事務之間或資料刪除後的順序編號時。 讓我們來看看一個常見的場景：追蹤一個人的住宅搬遷的地址歷史表。 每個條目包括人員 ID、序號、時間戳記和地址詳細資料。目標是為每個唯一的人自動產生一個從 1 開始的序號。

傳統方法及其限制

傳統方法，例如計算列或序列產生器，可能會達不到要求。 並發交易或行刪除很容易在編號序列中造成間隙。

高階方法：視窗函數

更強大的解決方案利用視窗函數。 row_number() 函數在定義的分區內有效地產生序號。 透過根據人員 ID 進行分區，我們保證每個人的序號都是唯一的。

使用 row_number()

要實現此目的，請將新列 adr_nr 新增到您的 address_history 表中。 使用以下查詢填充它：

<code class="language-sql">CREATE VIEW address_history_nr AS
SELECT *, row_number() OVER (PARTITION BY person_id
                             ORDER BY address_history_id) AS adr_nr
FROM   address_history;</code>

這會根據每行的 adr_nr 以及該 person_id 分區中的位置產生 person_id。無論行順序如何變化，這都可以確保唯一的序號。

資料完整性的最佳實務

除了編號之外，請記住以下最佳實踐：

• 對時間戳記使用適當的資料類型（例如，timestamp 或 timestamptz）。
• 最小化冗餘。 如果可從現有欄位派生，請避免儲存像 original_address 這樣的冗餘資料。

以上是視窗函數如何解決複合鍵中的序號挑戰？的詳細內容。更多資訊請關注PHP中文網其他相關文章！