Comment les fonctions de fenêtre peuvent-elles résoudre les problèmes de numérotation en série dans les clés composées ?

Résoudre les problèmes de numérotation de série dans les clés composées

La gestion des bases de données nécessite souvent l'attribution de numéros séquentiels au sein des groupes de lignes, en particulier lorsqu'il s'agit de clés composées. Cela peut s'avérer délicat, en particulier lorsqu'il s'agit de garantir une numérotation séquentielle sur plusieurs transactions ou après la suppression de données. Examinons un scénario courant : un tableau d'historique d'adresses qui suit les déménagements résidentiels d'une personne. Chaque entrée comprend un identifiant de personne, un numéro de séquence, un horodatage et des détails d'adresse. Le but est de générer automatiquement un numéro de séquence commençant à 1 pour chaque personne unique.

Les méthodes traditionnelles et leurs limites

Les approches traditionnelles, telles que les colonnes calculées ou les générateurs de séquences, peuvent échouer. Les transactions simultanées ou les suppressions de lignes peuvent facilement créer des lacunes dans la séquence de numérotation.

Une approche supérieure : les fonctions de fenêtre

Une solution plus robuste exploite les fonctions de fenêtre. La fonction row_number() génère efficacement des nombres séquentiels dans des partitions définies. En partitionnant en fonction de l'ID de la personne, nous garantissons des numéros de séquence uniques pour chaque individu.

Mise en œuvre des numéros de série avec row_number()

Pour mettre en œuvre cela, ajoutez une nouvelle colonne, adr_nr, à votre table address_history. Remplissez-le en utilisant cette requête :

<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>

Cela génère le adr_nr pour chaque ligne, en tenant compte de son person_id et de sa position dans la partition de ce person_id. Cela garantit des numéros de série uniques, quels que soient les changements dans l'ordre des lignes.

Bonnes pratiques pour l'intégrité des données

Au-delà de la numérotation, n'oubliez pas ces bonnes pratiques :

• Utilisez les types de données appropriés pour les horodatages (par exemple, timestamp ou timestamptz).
• Minimisez la redondance. Évitez de stocker des données redondantes comme original_address si elles peuvent être dérivées de champs existants.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!