Эффективно получить разность большого набора данных?
Мне нужно иметь возможность различать результаты двух запросов, показывая строки, которые находятся в «старом» наборе, но не в «новом» … и затем показывая строки, которые находятся в «новом» наборе, но не старый.
Прямо сейчас я собираю результаты в массив, а затем выполняю array_diff (). Но я затрагиваю некоторые проблемы с ресурсами и временем, так как наборы близки к 1 миллиону строк в каждом.
Схема одинакова в обоих наборах результатов (за исключением номера setId и номера автоинкремента таблицы), поэтому я предполагаю, что есть хороший способ сделать это непосредственно в MySQL … но я не нашел, как это сделать.
Example Table Schema:
rowId,setId,userId,name
Example Data:
1,1,user1,John
2,1,user2,Sally
3,1,user3,Tom
4,2,user1,John
5,2,user2,Thomas
6,2,user4,Frank
Что мне нужно сделать, это выяснить, добавляет / удаляет между setId 1 и setId 2.
Итак, результат diff должен (для примера) показать:
Rows that are in both setId1 and setId2
1,1,user1,John
Rows that are in setId 1 but not in setId2
2,1,user2,Sally
3,1,user3,Tom
Rows that are in setId 2 but not in setId1
5,2,user2,Thomas
6,2,user4,Frank
Я думаю, что это все детали. И я думаю, что я правильно понял пример. Любая помощь будет оценена. Решения в MySQL или PHP меня устраивают.
Решение
Ты можешь использовать exists или же not exists чтобы получить строки, которые находятся в обоих или только в 1 наборе.
Пользователи в наборе 1, но не в наборе 2 (просто переверните таблицы для обратного):
select * from set1 s1
where set_id = 1
and not exists (
select count(*) from set1 s2
where s1.user1 = s2.user1
)
Пользователи, которые находятся в обоих наборах
select * from set2 s2
where set_id = 2
and exists (
select 1 from set1 s1
where s1.setId = 1
and s2.user1 = s1.user1
)
Если вам нужны только отдельные пользователи в обеих группах, тогда group by user1:
select min(rowId), user1 from set1
where set_id in (1,2)
group by user1
having count(distinct set_id) = 2
или для пользователей в группе, но не другой
select min(rowId), user1 from set1
where set_id in (1,2)
group by user1
having count(case when set_id <> 1 then 1 end) = 0
Другие решения
В итоге мы добавили столбец контрольной суммы в необходимые таблицы. Таким образом, вместо того, чтобы выбирать несколько столбцов для сравнения, сравнение может быть выполнено для одного столбца (значение контрольной суммы).
Значение контрольной суммы представляло собой простой md5-хэш сериализованного массива, который содержал столбцы, которые необходимо преобразовать. Итак … это было так в PHP:
$checksumString = serialize($arrayOfColumnValues);
$checksumValue = md5($checksumString);
Затем $ checkumValue будет вставлен / обновлен в таблицы, и тогда мы сможем более легко выполнить объединения / объединения и т. Д. В одном столбце, чтобы найти различия. В итоге это выглядело примерно так:
SELECT i.id, i.checksumvalue
FROM SAMPLE_TABLE_I i
WHERE i.checksumvalue not in(select checksumvalue from SAMPLE_TABLE_II)
UNION ALL
SELECT ii.id, ii.checksumvalue
FROM SAMPLE_TABLE_II ii
WHERE ii.checksumvalue not in(select checksumvalue from SAMPLE_TABLE_I);
Это работает достаточно быстро для моих целей, по крайней мере сейчас 🙂