Détection des changements entre les lignes ayant le même ID

Question

J'ai un tableau contenant des noms et leur ID associé, ainsi qu'un instantané :

snapshot, systemid, name[, some, other, columns]

J'ai besoin d'identifier toutes les name s qu'un systemid a eu dans tous les instantanés, mais seulement lorsqu'il y a eu au moins un changement.

Par exemple, avec les données :

'DR1', 0, 'MOUSE_SPEED'
'DR1', 1, 'MOUSE_POS'
'DV8', 0, 'MOUSE_BUTTONS'
'DV8', 1, 'MOUSE_POS'
'DR6', 0, 'MOUSE_BUTTONS'
'DR6', 1, 'MOUSE_POS'
'PP2', 0, 'MOUSE_SPEED'
'PP2', 1, 'MOUSE_POS'

...je voudrais une requête qui renvoie (dans n'importe quel ordre) :

0, 'MOUSE_SPEED'
0, 'MOUSE_BUTTONS'

En outre, il serait utile de disposer de l'inverse - une liste de systemid qui sont restés stables dans l'ensemble de l'Union européenne. tous snapshot (c'est-à-dire là où le name n'a jamais changé).

J'utilise PostgreSQL v8.4.2.

EDITAR: Mise à jour pour tenir compte des commentaires (désolée pour l'article original qui n'était pas parfait, je suis nouvelle ici !) .

Answer 1

PostgreSQL dispose de l'opérateur EXCEPT, qui, si je me souviens bien, est à peu près le même que MINUS (comme dans Oracle), donc peut-être que quelque chose comme cela fonctionnerait ?

select id, name
from some_table
where snapshot = '1' and id in ('1', '2', '0')
except
select id, name
from some_table
where snapshot = '2' and id in ('1', '2', '0')

Si vous avez plusieurs shapshots, vous pouvez essayer de les concaténer tous en une longue séquence de EXCEPT ou vous pouvez écrire une procédure pour les traiter de manière itérative, comme (pseudocode) :

for i = 1 to maX(snapshot)-1 loop
    results := diff_query(i, i+1)  //the query above, but inside a procedure or something
    forall records in results loop
        /* do your processing  here */
    end loop
end loop

Cela semble vraiment être le genre de choses pour lesquelles il faut utiliser les opérateurs de set.

Answer 2

La suite est avec SQL Server mais elle n'utilise pas de constructions spécifiques à SQL Server. Il devrait être transférable à postgresql.

Déclaration SQL

SELECT  DISTINCT t1.id, t1.name
FROM    @Table t1
        INNER JOIN (
          SELECT  t.id 
          FROM    (
                    SELECT  DISTINCT id, name
                    FROM    @Table
                  ) t
          GROUP BY t.id 
          HAVING COUNT(*) > 1
        ) t2 ON t2.id = t1.id

Données d'essai

DECLARE @Table TABLE (snapshot INTEGER, id INTEGER, name VARCHAR(32))

INSERT INTO @TABLE
SELECT 1, 0, 'MOUSE_SPEED'
UNION ALL SELECT 1, 1, 'MOUSE_POS'
UNION ALL SELECT 1, 2, 'KEYBOARD_STATE'
UNION ALL SELECT 2, 0, 'MOUSE_BUTTONS'
UNION ALL SELECT 2, 1, 'MOUSE_POS'
UNION ALL SELECT 2, 2, 'KEYBOARD_STATE'
UNION ALL SELECT 3, 0, 'MOUSE_SPEED'
UNION ALL SELECT 3, 1, 'MOUSE_POS'
UNION ALL SELECT 3, 2, 'KEYBOARD_STATE'

Answer 3

select distinct s1.snapshot, s1.id, s1.name from snapshot s1, snapshot s2   
where s1.snapshot != s2.snapshot   
and s1.id = s2.id   
and s1.name != s2.name

Answer 4

Pour ceux qui ont changé :

SELECT t1.snapshot, t1.systemid
FROM table t1
GROUP BY t1.snapshot, t1.systemid
HAVING min(t1.name) <> max(t1.name)

vous donnerait l'instantané et l'identifiant de ceux qui ont changé.

Pour ceux qui sont restés les mêmes

SELECT t1.snapshot, t1.systemid
FROM table t1
GROUP BY t1.snapshot, t1.systemid
HAVING min(t1.name) = max(t1.name)

L'association des valeurs à la première ou à la dernière requête peut se faire au moyen d'une sous-requête jointe ou d'une sous-requête corrélée.

Rejoint (exemple avec les noms qui ont changé)

SELECT t2.snapshot, t2.systemid, t2.name
FROM table t2
     JOIN (
           SELECT snapshot, systemid
           FROM table 
           GROUP BY snapshot, systemid
           HAVING min(name) <> max(name) ) t1
     ON t2.snapshot = t1.snapshot AND t2.systemid = t1.systemid

Corrélés (exemple avec des noms qui sont restés les mêmes)

SELECT t2.snapshot, t2.systemid, t2.name
FROM table t2
WHERE t2.name IN (
           SELECT t1.name
           FROM table t1
           WHERE t2.snapshot = t1.snapshot AND t2.systemid = t1.systemid
           GROUP BY t1.name
           HAVING COUNT(DISTINCT t1.name) = 1 ) 

Si vous n'avez pas besoin de l'instantané pour la requête inverse, alors

SELECT DISTINCT t2.systemid, t2.name

et se reposer de la même manière.

Les requêtes ne sont pas validées, mais j'espère que les approches sont claires.