Обнаружение изменений между строками с одинаковым идентификатором

У меня есть таблица, содержащая некоторые имена и связанные с ними идентификаторы, а также моментальный снимок:

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

Мне нужно идентифицировать все уникальные name, которые a systemid имел во всех моментальных снимках, но только там, где было хотя бы одно изменение.

Например, с данными:

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

... Мне нужен запрос, который вернется (в любом порядке):

0, 'MOUSE_SPEED'
0, 'MOUSE_BUTTONS'

Кроме того, было бы полезно иметь обратный - список systemid, который оставался стабильным в все snapshot (то есть, когда name никогда не изменялся).

Я использую PostgreSQL v8.4.2.

РЕДАКТИРОВАТЬ: Обновлено, чтобы отражать комментарии (извините за оригинальную запись, отличную от совершенства, я новичок здесь!).

4 ответа

Ниже приведен пример SQL Server, но он не использует никаких конкретных конструкций SQL Server. Он должен быть переносимым для postgresql.

Заявление 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

Данные тестирования

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'


Для измененных:

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

предоставит вам снимок и идентификатор тех, которые изменили

Для тех, которые остались теми же

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

Присоединение значений к первому или последнему запросу может быть выполнено с помощью объединенного подзапроса или коррелированного подзапроса

Присоединился (пример с измененными именами)

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

Коррелированный (пример с именами, которые остались прежними)

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 )

Если вам нужен снимок для обратного запроса, то

SELECT DISTINCT t2.systemid, t2.name

и оставайтесь одинаково.

Запросы не проверяются, но я надеюсь, что подходы понятны


PostgreSQL имеет оператор EXCEPT, который, как я помню, почти такой же, как MINUS (например, в Oracle), возможно, что-то вроде этого работает?

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

Если у вас есть несколько снимков, вы можете попытаться объединить их все в одну длинную последовательность EXCEPT s, или вы можете написать процедуру для обработки их итеративно, например (псевдокод):

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

Это действительно похоже на то, как использовать операторы set для.


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

licensed under cc by-sa 3.0 with attribution.