Привет!
Имеется таблица:
CREATE TABLE `or_participate_at` (
`participate_id` int(20) NOT NULL auto_increment,
`participant_id` int(20) default NULL,
`experiment_id` int(20) default NULL,
`invited` char(1) default 'n',
`registered` char(1) default 'n',
`shownup` char(1) default 'n',
`participated` char(1) default 'n',
`session_id` int(20) default '0',
PRIMARY KEY (`participate_id`),
UNIQUE KEY `uindex` (`participate_id`),
UNIQUE KEY `uindex2` (`participant_id`,`experiment_id`),
KEY `tindex` (`session_id`),
KEY `tindex2` (`experiment_id`),
KEY `tindex3` (`participant_id`),
KEY `tindex4` (`session_id`,`registered`),
KEY `tindex5` (`experiment_id`,`invited`),
KEY `tindex6` (`experiment_id`,`shownup`),
KEY `tindex7` (`experiment_id`,`participated`),
KEY `tindex8` (`session_id`,`participated`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Чтобы было понятнее, таблица отражет историю участия в экспериментах (`experiment_id`) участников (`participant_id`). Каждый участник может участвовать в одном из экспериментов лишь один раз (UNIQUE KEY `uindex2` (`participant_id`,`experiment_id`)). Поля `invited`, `registered`, `shownup` и `participated`отражают этапы участия в эксперименте - приглашение, регистрация, посещение, участие. Вышеперечисленные поля заполняются по порядку событий начиная с `invited`. Скажем, участник не может быть только `shownup`='y', если `shownup` стоит в значении 'y' значит и предыдущие два поля тоже в 'y'. Предположим для некоторых участников по ошибке была создана еще одна учетная запись со своим уникальным `participant_id`, некоторое время она просуществовала и имеет историю участия. При выявлении такого случая вся история должна комбинироваться в одну из учетных записей, после чего пустая (без истории) учетная запись удаляться.
Проблема в том что за обе учетные записи могут иметь историю участия в одних и тех же экспериментах, таким образом простое решение
UPDATE IGNORE or_participate_at set participant_id = 400711420 where participant_id = 1979714289;
сделает только половину работы и не изменит записи с совпадающим `experiment_id`. Критерий же объединения таких записей лежит в полях `invited`, `registered`, `shownup` и `participated`отражающих этапы участия от приглашения до участия - должна сохраниться та запись где больше информации в этих полях а другие удалиться.
132412, 400711420, 1503025322, 'y', 'n', 'n', 'n', 0
141488, 400711420, 1453000548, 'y', 'y', 'n', 'n', 0
133685, 1979714289, 1503025322, 'y', 'y', 'y', 'y', 581435840
139654, 1979714289, 1453000548, 'y', 'y', 'y', 'y', 47490904
В таком примере должны сохраниться записи '133685' и '139654', а '132412' и '141488' удалены.
Я вижу решение этой задачи следующим образом:
1. Делаю выборку дублирующихся записей (для примеры выше):
select t1.* from or_participate_at as t1, (select experiment_id, count(experiment_id) as cnt_exp from or_participate_at where participant_id in (1979714289,400711420) group by experiment_id) as t2 where participant_id in (1979714289,400711420) and t1.experiment_id = t2.experiment_id and t2.cnt_exp > 1
Разбираю ее на стороне клиента, вычисляю какая из повторяющихся записей имеет наибольший вес (более информативная по числу полей с 'y'), и удаляю записи с меньшим весом (для примера выше '132412' и '141488' будут удалены).
2. Выполняю
UPDATE or_participate_at set participant_id = 400711420 where participant_id = 1979714289;
который должен выполниться гладко ввиду отсутствия дублирующихся ключей, но и наконец удаляю саму учетную запись из соответствующей таблицы.
Вопрос - существует ли более оптимальное решение этой задачи? Спасибо!
Отредактированно rulan (29.05.2009 01:12:58)
