У меня есть таблица, состоящая из номеров транспортных средств, где все номера всегда содержат 7 символов. Иногда номерной знак был написан неправильно, поэтому мой запрос SELECT должен учитывать, что «AAU1234» и «AAV1234» должны быть одним и тем же транспортным средством. Я не хочу создавать правила между конкретными символами, такими как «U» и «V» или «I» и «1», а хочу создавать правило, позволяющее группировать таблички, в которых 6 из 7 символов равны и находятся в одном положении.
например:
В этом случае идентификаторы 1, 2 и 5 должны появляться только один раз.
Было бы здорово, если бы при группировке сообщались все сгруппированные таблички, объединенные в другой столбец.
Дополнительная информация: Это большая таблица, и в настоящее время идентичные пластины группируются, когда они находятся в одном и том же диапазоне дат и времени в 15 минут.
Мой запрос выглядит следующим образом:
select plate, floor(unix_timestamp(date)/(15 * 60)) as timekey
from table
group by plate, timekey
order by date desc
Следуя приведенному выше примеру изображения, моя цель — сгруппировать идентификаторы 1, 2 и 5 в одной строке, поскольку идентификаторы 1 и идентификатор 2 соответствуют 6 из 7 строк, а значит, идентификаторы 1 и идентификатор 5.
Результат может быть примерно таким:
Или:
Конечная информация о дате здесь не важна, главное – сгруппировать похожие таблички.
Update1 Вопросы и ответы по «плацдарму»:
РафаэльZG
Эта таблица получает данные от программного обеспечения камеры распознавания номеров, и у меня есть интерфейс, который показывает все записи, группируя одинаковые номера в одном и том же временном диапазоне, поскольку автомобиль может иметь 1, 2 или 10 фотографий. Проблема в том, что иногда программное обеспечение не может правильно прочитать символ и генерирует отдельные записи в моем интерфейсе, поэтому моя цель — максимально уменьшить эту проблему, сгруппировав эти номера, где был ошибочно зарегистрирован только 1 символ. Это правило не требует устранения 100% неправильных записей.
Тень
Строка с id=5 отличается всего на один символ от id=1, но отличается на 2 символа от строки с id=2. Таким образом, эти строки будут сгруппированы вместе только в том случае, если начальная строка имеет идентификатор = 1. Как определить, какая строка является начальной с точки зрения группировки? Кстати, вам следует посмотреть на расстояние Левенштейна, но этот вопрос намного сложнее, чем вы думаете, и MySQL, возможно, не лучший инструмент для выполнения вашей задачи!
РафаэльZG
@shadow Отличный момент! Все становится еще сложнее, но поскольку идентификаторы 1 и 2 сгруппированы, а идентификатор 5 имеет 6 совпадений символов с идентификатором 1, его также следует добавить в группу. Ограничивающей точкой должен быть диапазон дат, поэтому запросу потребуется бесконечно обрабатывать всю таблицу.
РафаэльZG
Я попытаюсь ответить еще раз более объективно: если идентификаторы 1 и идентификатор 2 сгруппированы, а идентификатор 5 имеет 6 символов, аналогичный идентификатору 1, то все 3 должны быть сгруппированы. например: ABC1234, ABG1234, ABO1234, ABOI234, ABOI284 должны быть сгруппированы, поскольку каждый из них имеет 6 символов, похожих как минимум на другой.
Пожалуйста, отмечайте вопрос только той маркой базы данных, которую вы действительно используете. MySQL и MariaDB — это разные базы данных, которые становятся все более несовместимыми. Решение для одного может оказаться неприменимым для другого. Также уточните, какую версию соответствующей базы данных вы используете, поскольку решение может зависеть от функций той или иной версии.
нет, вы ищете такие случайные буквы, поскольку у вас есть фиксированные правила, такие как V TO U, вы можете записать все правила во вложенном паре REPLACE и сгруппировать их, например GROUP BY REPLACE (REPLACE(plate,'V','U'),'1 ', 'Я') и так далее.
Я оспорил даже отправную точку, что ваш вопрос неясен, поскольку в нем не указано, как обрабатывать случаи, когда у вас есть 3 пластины, где 1-я и 2-я отличаются на 1 символ, 2-я и 3-я отличаются на 1 символ, а 1-я и 3-я отличаются на 1 характер. Следует ли поместить этих 3 в одну группу или в несколько групп? Если вы посмотрите ответ ВалНика ниже, это попытка решить проблему, но в конечном итоге вы можете сгруппировать значительно разные пластины. Вам необходимо указать бизнес-правила для обработки цепочек, если разница составляет 1 символ.
Я также подчеркнул, что sql, возможно, не лучший инструмент для такого сравнения, особенно если у вас большой набор данных, поскольку вам приходится начинать с декартова произведения.
вы можете вычислить количество различий между двумя строками из 8 или менее символов, используя сложное выражение, включающее подзапрос объединения, bit_xor, unhex, conv, replace и char_length, но делать это для каждой пары в вашей таблице не будет исполнитель. сделайте это в своем клиенте. (и Шэдоу прав, вам нужно какое-то правило, как обращаться с цепочками различий)
Я отмечаю, что вы не группируете даже одинаковые тарелки с интервалом в 1 секунду, если они находятся в разных фиксированных 15-минутных окнах.
Вы можете использовать REGEXP_REPLACE()
, чтобы заменить начальную букву AAV
на AAU
при группировке.
select
REGEXP_REPLACE(plate, '^AAV', 'AAU') AS fixed_plate,
FROM_UNIXTIME(floor(unix_timestamp(date)/(15 * 60)), * 15 * 60) as timekey,
GROUP_CONCAT(id) AS concat_ids,
GROUP_CONCAT(plate) AS concat_plates
from table
group by fixed_plate, timekey
order by timekey desc
См. пример с тестовыми данными. Данные несколько расширены по сравнению с примерами в вопросе.
create table test (id int, plate varchar(7));
insert into test values
(1,'AAU1234')
,(2,'AAV1234')
,(3,'BKP5678')
,(4,'CMD9081')
,(5,'A4U1234')
,(6,'ABC1234')
,(7,'ABG1234')
,(8,'ABO1234')
,(9,'ABOI234')
,(10,'ABOI284')
,(11,'ABGI234')
,(12,'ABGI284')
,(14,'CMD9031')
;
Сначала давайте просто сравним plate
и найдем строки, в которых совпадают 6 символов. В условии JOIN and (t1.s1=t2.s1 or t1.s2=t2.s2)
- должно совпадать хотя бы 1.
Подождите matchn=6
with recursive
tn as(
select *,substring(plate,1,1) s1,substring(plate,2,1) s2,substring(plate,3,1) s3
,substring(plate,4,1) s4,substring(plate,5,1) s5,substring(plate,6,1) s6,substring(plate,7,1) s7
from test
)
,cmp as(
select t1.id,t1.plate,t2.id id2,t2.plate plate2
select t1.id,t1.plate,t2.id id2,t2.plate plate2
,case when (t1.s1=t2.s1) then 1 else 0 end +case when (t1.s2=t2.s2) then 1 else 0 end
+case when (t1.s3=t2.s3) then 1 else 0 end +case when (t1.s4=t2.s4) then 1 else 0 end
+case when (t1.s5=t2.s5) then 1 else 0 end +case when (t1.s6=t2.s6) then 1 else 0 end
+case when (t1.s7=t2.s7) then 1 else 0 end matchn
from tn t1
left join tn t2 on t1.id<>t2.id and (t1.s1=t2.s1 or t1.s2=t2.s2)
)
Выход (часть результата)
Эту таблицу можно рассматривать как описание графика.
Полученный граф не ориентирован, поскольку мы установили условие (t1.id <> t2.id).
(6)ABC1234 (1)AAU1234
/ \ / \
C->G C->O A->4 U->V
/ \ / \
(7)ABG1234--G->O--(8)ABO1234 / (2)AAV1234
\ (5)A4U1234
1->I
\
(9)ABOI234 (4)CMD9081
/ \ |
O->G 3->8 8->3
/ \ |
/ (10)ABOI284 (14)CMD9031
/ \
/ O->G
/ \
(11)ABGI234-3->8-(12)ABGI284
Затем мы рекурсивно обходим ориентированный граф, чтобы найти все узлы, начиная с вершины графа.
,r as(
select distinct 0 lvl,id,plate p0,plate,id id2,plate plate2
,cast('' as char(1000)) as path
from cmp
where matchn=6
union all
select lvl+1,r.id,p0,r.plate2,t.id2 id2,t.plate2
,concat(r.path,',',r.plate) as path
from r inner join cmp t on t.plate=r.plate2 -- find_in_set(t.plate,r.list)>0
where t.matchn=6
and find_in_set(t.plate,r.path)=0
-- and lvl<9 -- for debug only
)
,d as(
select distinct id,p0,id2,plate2
from r
)
,chains as(
select min(id2) groupid,id,p0 plate
,group_concat(id2 order by id2) ids
,group_concat(plate2 order by id2) plates
from d
group by id,p0
)
Выход цепей
Теперь мы можем объединить строки или просто назначить каждой строке группу.
,newGr as(
select t.*,coalesce(c.groupid,t.id) groupid
,c.ids,c.plates
from test t
left join chains c on t.id=c.id
)
-- select * from cmp order by id,id2;
-- select * from r order by id,lvl,id2;
-- select * from d order by id,id2;
-- select * from chains order by groupid;
-- select * from newGr order by id;
select groupid,min(id) id -- ,grPlate
,group_concat(id) ids
,group_concat(plate) plates
from newGr
group by groupid
Выход
Новые группы для строк
Этот ответ довольно хороший, но некоторые результаты не соответствуют вопросу. В вопросе говорится сгруппировать пластины, различающиеся по своему характеру. К сожалению, ФП не смог разъяснить, что должно произойти, если у вас есть цепочка из 1 различия в символе (оспорил ФП по этому поводу на плацдарме, но ФП проигнорировал это). Итак, вы помещаете ABC1234 и ABOI234 в одну группу, хотя они отличаются двумя символами.
Проблема в том, что если у вас есть серия табличек, отличающихся друг от друга на 1 символ, помещенных в одну группу, но таблички в начале цепочки существенно отличаются от табличек в конце цепочки.
Верно. Я думаю, что это соответствует задаче ОП. eg: ABC1234, ABG1234, ABO1234, ABOI234, ABOI284 should be all grouped
Что ж, ваша группировка противоречит определению ОП: номера с разницей в 1 символ должны быть сгруппированы вместе. Не говоря уже о том, что если мы продолжим добавлять другие номера с разницей в 1 символ, все они будут сгруппированы вместе, а это определенно не то, чего хочет ФП. Помните, что это автомобильные номерные знаки, и ОП хочет сгруппировать очень похожие номера, только сгруппированные вместе. На всякий случай, если признание ошиблось.
Ваши рассуждения ясны и обоснованы. Однако в ФП явно указана группировка ABC1234 и ABOI284 с 3-мя различиями в символах, поскольку через цепочку различий в 1 символе они попадают в одну группу. Возможно, он считает маловероятным, что в скором времени в данных появятся две реальные таблички, отличающиеся на 1 символ.
Нет, в ФП прямо не написано, что целые цепочки должны быть сгруппированы вместе. И группировать такие разные номерные знаки вместе не имеет никакого смысла.
Вы участвовали в обсуждении вопроса на этапе «Площадка». Посмотрите последний комментарий ОП. Там он ясно указывает на такую группировку. Более того, другого решения у него быть не может. В противном случае противоречие не может быть разрешено, когда (id 1,code 1)<->(id 2,code 2), а также (id 2,mode 2)<->(id3,code3) и существует(более 1 разницы ) нет совпадения (id1,code1)<x>(id3,code3). Там id1 и id2 должны быть сгруппированы, а id2 и id3 должны быть сгруппированы. Что за id1,id3?
ОП не включил ничего подобного в вопрос. Вопрос как стоид не объяснил цепочек, и в вашем ответе также не было ссылок на комментарии. Между вопросом и вашим ответом был резкий разрыв. Кстати, я думаю, что подход ОП неправильный.
Ваше решение превосходно! Вы принесли тот результат, который я искал! Большое спасибо вам и другим друзьям, которые помогли мне здесь. Извините, если я не так ясно выразился в своем вопросе, но я новичок на этом форуме, и понимание этой проблемы было для меня несколько сложной задачей.
Вот простой способ создать оценку с помощью временной таблицы. Это необходимо для того, чтобы строки, уже включенные в более раннюю группировку, не появлялись снова в другой группе.
Вот запросы и пример:
Не стесняйтесь корректировать по мере необходимости!
Запросы:
CREATE TEMPORARY TABLE `tmp_ids` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`rec_id` INT DEFAULT NULL,
`group_id` INT DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ext_id` (`rec_id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT IGNORE INTO tmp_ids(rec_id,group_id)
SELECT v2.id,v1.id -- v1.*, v2.* ,
FROM vehicle v1
LEFT JOIN vehicle v2 ON
v1.`date`+ INTERVAL + 15 MINUTE > v2.`date`
AND
IF( MID(v1.plate,1,1) = MID(v2.plate,1,1), 0, 1) +
IF( MID(v1.plate,2,1) = MID(v2.plate,2,1), 0, 1) +
IF( MID(v1.plate,3,1) = MID(v2.plate,3,1), 0, 1) +
IF( MID(v1.plate,4,1) = MID(v2.plate,4,1), 0, 1) +
IF( MID(v1.plate,5,1) = MID(v2.plate,5,1), 0, 1) +
IF( MID(v1.plate,6,1) = MID(v2.plate,6,1), 0, 1) +
IF( MID(v1.plate,7,1) = MID(v2.plate,7,1), 0, 1) < 2;
SELECT MIN(v.id) AS FirstID
, GROUP_CONCAT(v.id ORDER BY v.id) AS IDs
, GROUP_CONCAT(v.plate ORDER BY v.id) AS PLATEs
, MIN(v.`date`) AS Start_date
FROM tmp_ids r
JOIN vehicle v ON v.id = r.rec_id
GROUP BY r.group_id;
DROP TABLE tmp_ids;
все в одном
WITH tmp_ids AS (
SELECT v2.id AS rec_id, v1.id AS group_id
FROM vehicle v1
LEFT JOIN vehicle v2 ON
v1.`date` + INTERVAL 15 MINUTE > v2.`date`
AND
IF(MID(v1.plate,1,1) = MID(v2.plate,1,1), 0, 1) +
IF(MID(v1.plate,2,1) = MID(v2.plate,2,1), 0, 1) +
IF(MID(v1.plate,3,1) = MID(v2.plate,3,1), 0, 1) +
IF(MID(v1.plate,4,1) = MID(v2.plate,4,1), 0, 1) +
IF(MID(v1.plate,5,1) = MID(v2.plate,5,1), 0, 1) +
IF(MID(v1.plate,6,1) = MID(v2.plate,6,1), 0, 1) +
IF(MID(v1.plate,7,1) = MID(v2.plate,7,1), 0, 1) < 2
),
distinct_ids AS (
SELECT rec_id, MIN(group_id) AS group_id
FROM tmp_ids
GROUP BY rec_id
)
SELECT
MIN(v.id) AS FirstID,
GROUP_CONCAT(v.id ORDER BY v.id) AS IDs,
GROUP_CONCAT(v.plate ORDER BY v.id) AS PLATEs,
MIN(v.`date`) AS Start_date
FROM distinct_ids r
JOIN vehicle v ON v.id = r.rec_id
GROUP BY r.group_id;
образец
mysql> SELECT * FROM vehicle;
+----+---------+---------------------+
| id | plate | date |
+----+---------+---------------------+
| 1 | AAU1234 | 2024-07-09 10:00:00 |
| 2 | AAV1234 | 2024-07-09 10:03:00 |
| 3 | BAV1234 | 2024-07-09 10:07:00 |
| 4 | AAV1245 | 2024-07-09 10:55:00 |
| 5 | AAV1234 | 2024-07-09 10:04:00 |
| 6 | AAV1234 | 2024-07-09 10:04:00 |
| 7 | AAV1234 | 2024-07-09 10:45:00 |
+----+---------+---------------------+
7 rows in set (0.00 sec)
mysql>
mysql>
mysql> CREATE TEMPORARY TABLE `tmp_ids` (
-> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `rec_id` INT DEFAULT NULL,
-> `group_id` INT DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `ext_id` (`rec_id`)
-> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT IGNORE INTO tmp_ids(rec_id,group_id)
-> SELECT v2.id,v1.id -- v1.*, v2.* ,
-> FROM vehicle v1
-> LEFT JOIN vehicle v2 ON
-> v1.`date`+ INTERVAL + 15 MINUTE > v2.`date`
-> AND
-> IF( MID(v1.plate,1,1) = MID(v2.plate,1,1), 0, 1) +
-> IF( MID(v1.plate,2,1) = MID(v2.plate,2,1), 0, 1) +
-> IF( MID(v1.plate,3,1) = MID(v2.plate,3,1), 0, 1) +
-> IF( MID(v1.plate,4,1) = MID(v2.plate,4,1), 0, 1) +
-> IF( MID(v1.plate,5,1) = MID(v2.plate,5,1), 0, 1) +
-> IF( MID(v1.plate,6,1) = MID(v2.plate,6,1), 0, 1) +
-> IF( MID(v1.plate,7,1) = MID(v2.plate,7,1), 0, 1) < 2;
Query OK, 7 rows affected, 23 warnings (0.01 sec)
Records: 30 Duplicates: 23 Warnings: 23
mysql>
mysql>
mysql> SELECT MIN(v.id) AS FirstID
-> , GROUP_CONCAT(v.id ORDER BY v.id) AS IDs
-> , GROUP_CONCAT(v.plate ORDER BY v.id) AS PLATEs
-> , MIN(v.`date`) AS Start_date
-> FROM tmp_ids r
-> JOIN vehicle v ON v.id = r.rec_id
-> GROUP BY r.group_id;
+---------+---------+---------------------------------+---------------------+
| FirstID | IDs | PLATEs | Start_date |
+---------+---------+---------------------------------+---------------------+
| 1 | 1,2,5,6 | AAU1234,AAV1234,AAV1234,AAV1234 | 2024-07-09 10:00:00 |
| 3 | 3 | BAV1234 | 2024-07-09 10:07:00 |
| 4 | 4 | AAV1245 | 2024-07-09 10:55:00 |
| 7 | 7 | AAV1234 | 2024-07-09 10:45:00 |
+---------+---------+---------------------------------+---------------------+
4 rows in set (0.00 sec)
mysql>
mysql> DROP TABLE tmp_ids;
Query OK, 0 rows affected (0.01 sec)
mysql>
Добавьте в таблицу дополнительный столбец. Заполните этот дополнительный столбец «канонической строкой», в которой 1->I, U->v, 5->S и т. д. Пример: «AB1UV5» сохранит «ABIVVS» в дополнительном столбце. Проиндексируйте этот дополнительный столбец.
При поиске тарелки выполните то же преобразование с тестовой строкой. Обратите внимание, что иногда это может привести к нескольким попаданиям.
Я бы выполнил преобразование в клиентском коде. В SQL для этого потребуется несколько десятков вложенных вызовов функций REPLACE(), что-то вроде:
REPLACE(
REPLACE(
...
REPLACE(str,
'1', 'I'),
'U', 'V'),
... ))
Вы не используете какие-либо функции агрегирования, использовать
GROUP BY
нет смысла.