Sélection de la version la plus récente et spécifique dans chaque groupe d'enregistrements, pour plusieurs groupes

Le problème:
J'ai une table qui enregistre les lignes de données dans foo. Chaque fois que la ligne est mise à jour, une nouvelle ligne est insérée avec un numéro de révision. Le tableau ressemble à:

id  rev field
1   1   test1
2   1   fsdfs
3   1   jfds
1   2   test2

Noter que dans le tableau du dernier enregistrement est une version plus récente de la première ligne.

Personne ne sait d'une manière efficace à la requête pour la version la plus récente des lignes, et une version spécifique d'enregistrements? Par exemple, une requête pour rev=2 serait de retour le 2, 3 et 4 rangs (pas remplacé le 1er rang), alors que les requêtes pour rev=1 les rendements de ces lignes avec rev <= 1 et en cas de duplication de l'ids, l'une avec le plus haut numéro de révision est choisi (record: 1, 2, 3).

Je ne suis pas sûr si cela est encore possible dans SQL Server...

Je préférerais pas de retourner le résultat de façon itérative.

source d'informationauteur orange

29

Pour obtenir uniquement des dernières révisions:
```
SELECT * from t t1
WHERE t1.rev = 
  (SELECT max(rev) FROM t t2 WHERE t2.id = t1.id)
```
Pour obtenir une révision spécifique, dans ce cas, 1 (et si un article n'est pas de la révision mais la prochaine plus petite révision):
```
SELECT * from foo t1
WHERE t1.rev = 
  (SELECT max(rev) 
   FROM foo t2 
   WHERE t2.id = t1.id
   AND t2.rev <= 1)
```
Il pourrait ne pas être le moyen le plus efficace pour ce faire, mais pour l'instant je ne peut pas la figure d'une meilleure façon de le faire.

C'est comment j'allais le faire. ROW_NUMBER() nécessite SQL Server 2005 ou plus tard

Des données de l'échantillon:

DECLARE @foo TABLE (
    id int,
    rev int,
    field nvarchar(10)
)

INSERT @foo VALUES
    ( 1, 1, 'test1' ),
    ( 2, 1, 'fdsfs' ),
    ( 3, 1, 'jfds' ),
    ( 1, 2, 'test2' )

La requête:

DECLARE @desiredRev int

SET @desiredRev = 2

SELECT * FROM (
SELECT 
    id,
    rev,
    field,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rn
FROM @foo WHERE rev <= @desiredRev 
) numbered
WHERE rn = 1

L'intérieur SELECT retourne tous les documents pertinents, et à l'intérieur de chaque id groupe (c'est le PARTITION BY), calcule le nombre de ligne au moment de la commande par ordre décroissant d' rev.

L'extérieur SELECT juste sélectionne le premier membre (ainsi, l'un avec la plus haute rev) de chaque id groupe.

De sortie lors de l' @desiredRev = 2 :

id          rev         field      rn
----------- ----------- ---------- --------------------
1           2           test2      1
2           1           fdsfs      1
3           1           jfds       1

De sortie lors de l' @desiredRev = 1 :

id          rev         field      rn
----------- ----------- ---------- --------------------
1           1           test1      1
2           1           fdsfs      1
3           1           jfds       1

Si vous voulez toutes les dernières révisions de chaque champ, vous pouvez utiliser

SELECT C.rev, C.fields FROM (
  SELECT MAX(A.rev) AS rev, A.id
  FROM yourtable A
  GROUP BY A.id) 
AS B
INNER JOIN yourtable C
ON B.id = C.id AND B.rev = C.rev

Dans le cas de votre exemple, qui serait de retour

 rev field
 1   fsdfs   
 1   jfds   
 2   test2

SELECT
  MaxRevs.id,
  revision.field
FROM
  (SELECT
     id,
     MAX(rev) AS MaxRev
   FROM revision
   GROUP BY id
  ) MaxRevs
  INNER JOIN revision 
    ON MaxRevs.id = revision.id AND MaxRevs.MaxRev = revision.rev

SELECT foo.* from foo 
left join foo as later 
on foo.id=later.id and later.rev>foo.rev 
where later.id is null;

Comment à ce sujet?

select id, max(rev), field from foo group by id

Pour des requêtes spécifiques de révision révision 1,

select id, max(rev), field from foo where rev <= 1 group by id

0

Voici une solution alternative a subit une mise à jour des coûts, mais est beaucoup plus efficace pour sélectionner les données les plus récentes lignes qu'il évite le calcul de MAX(rev). Il fonctionne également lorsque vous êtes en train de faire en vrac les mises à jour des ensembles ou sous-ensembles de la table. J'avais besoin de ce modèle pour s'assurer que je efficace pourrait passer à un nouveau jeu de données qui a été mis à jour via une longue mise à jour en bloc, sans aucune des fenêtres de temps où l'on avait été partiellement mise à jour des données visibles.

Vieillissement
- Remplacer le rev colonne avec un age colonne
- Créer une vue de l'actuel dernières données avec filtre: age = 0
- Pour créer une nouvelle version de vos données ...
  - INSÉRER de nouvelles lignes avec age = -1 Dans mon cas, cela a été un lent processus d'exécution long.
  - UPDATE table-name age = age + 1 pour toutes les lignes mises à jour. Cette commute l'affichage de la nouvelle données les plus récentes.
  - Des données en option de purge: SUPPRIMER des lignes ayant age > N
Indexation
- Créer un indice composite avec age et puis id donc, la vue y est agréable et rapide et peut également être utilisé pour la recherche par id. Bien que cette clé est effectivement unique, ses temporairement non-uniques quand vous êtes vieillissement les lignes (en cours de UPDATE SET age=age+1) de sorte que vous aurez besoin afin de le rendre unique et idéalement l'index cluster. Si vous avez besoin de trouver toutes les versions d'un idvous aurez besoin d'un index supplémentaire sur id.
Enfin ... disons que vous êtes dans un mauvais jour, et le traitement par lots des pauses. Vous pouvez rapidement revenir à une ancienne version du jeu de données en exécutant:
- UPDATE table-name SET age = age - 1 -- Restaurer une version
- DELETE table-name WHERE age < 0 -- Nettoyer les mauvaises choses
Note: je vous recommande de nommer le nom de la colonne RowAge au lieu de age pour indiquer ce modèle est utilisé car il est plus clair que le son d'une base de données sur la valeur et complète de SQL Server RowVersion nom de la colonne et de la convention de nommage. Maintenant, il n'entre pas en conflit avec une colonne ou de la vue qui doit ramener l'âge de la personne.

Contrairement à d'autres solutions, ce modèle fonctionne pour les non bases de données SQL Server, par conséquent, moins l'enfermement dans.

Vous devez vous connecter pour publier un commentaire.

Vieillissement

Indexation