Azure SQL中相似车牌字符串识别与去重的技术方案问询

我正在做车牌识别项目，识别后的车牌存储在Azure SQL Server上的MS SQL数据库中。我们有两台摄像头，能捕获同一车辆的前后两张图像，但识别器偶尔会识别错误——比如实际车牌是AA1111的车辆，一台摄像头识别成AA111，另一台识别成AA1111。

现有表中记录如下：

现在客户端会为每条记录创建事件，但这不合理——AA11、AA111和AA1111实际属于同一车牌，我只想为唯一车牌创建事件，示例中应该只生成AA1111和BB2222的事件。

另外，我自己写了一段SQL代码，但遇到了递归限制问题，求解决建议。代码如下：

CREATE TABLE Plates(
  id INT not null,
  plate NVARCHAR(10),
  CreatedAtTime NVARCHAR(20),
  Confidence DECIMAL(18,5)
  )

INSERT INTO Plates
VALUES
(1,'LK2873','13:00:00',100),
(2,'LK287','13:00:10',70),
(3,'LK287','13:00:12',65),
(4,'AZ4875','14:00:00',100),
(5,'TR3345','14:15:32',100),
(6,'TR33','14:15:36',45),
(7,'TR334','14:15:40',70),
(8,'AA76','14:12:36',100),
(9,'DF324','14:13:00',100),
(9,'LK28','13:00:09',64)

;WITH tmp(plate,lvl,snd,ln)  as(
    SELECT Plate,1 lvl,soundex(Plate),LEN(Plate) FROM Plates 
        WHERE LEN(Plate)=(SELECT max(LEN(Plate)) FROM Plates)
    UNION ALL
    SELECT tb.plate, lvl+1,SOUNDEX(tb.Plate),LEN(tb.Plate)
        FROM Plates tb
        INNER JOIn tmp  t ON tb.Plate =LEFT(tb.plate,LEN(t.plate))
)
SELECT * FROM tmp

一、修复递归CTE的问题 #

你的递归CTE有两个核心问题：

1. 连接条件tb.Plate = LEFT(tb.plate,LEN(t.plate))完全无效，等价于tb.Plate = tb.Plate，会导致无限循环，触发SQL Server默认的递归深度限制（默认100）。
2. 逻辑上没有正确关联短车牌和对应长车牌的归属关系。

修正后的递归CTE（解决递归限制） #

调整逻辑，让短车牌匹配到对应的最长前缀长车牌，同时避免无限递归：

;WITH PlateGroups AS (
    -- 锚点成员：取所有最长的车牌作为每组的根
    SELECT 
        plate AS RootPlate,
        plate AS CurrentPlate,
        Confidence,
        CreatedAtTime,
        1 AS Level
    FROM Plates
    WHERE LEN(plate) = (SELECT MAX(LEN(plate)) FROM Plates)

    UNION ALL

    -- 递归成员：找所有以RootPlate为前缀的更短车牌
    SELECT 
        pg.RootPlate,
        p.plate AS CurrentPlate,
        p.Confidence,
        p.CreatedAtTime,
        pg.Level + 1 AS Level
    FROM Plates p
    INNER JOIN PlateGroups pg 
        ON pg.RootPlate LIKE p.plate + '%'  -- 短车牌是长车牌的前缀
        AND LEN(p.plate) = LEN(pg.RootPlate) - pg.Level  -- 控制递归深度，避免重复匹配
)
SELECT * FROM PlateGroups
ORDER BY RootPlate, Level
OPTION (MAXRECURSION 32767); -- 手动设置最大递归深度，覆盖默认100的限制

关键调整点 #

• 用pg.RootPlate LIKE p.plate + '%'关联短车牌和对应长车牌（短车牌是长车牌的前缀）。
• 用LEN(p.plate) = LEN(pg.RootPlate) - pg.Level控制递归深度，避免同一车牌被重复匹配。
• 通过OPTION (MAXRECURSION 32767)设置最大递归深度，支持最多32767层递归，满足大部分场景需求。

二、直接获取唯一有效车牌 #

如果你的目标是直接得到每组的最终有效车牌（置信度最高或最长的），可以不用递归CTE，用更高效的分组匹配方式：

方案1：按最长前缀分组，取置信度最高的车牌 #

SELECT 
    -- 找到每个车牌对应的最长匹配前缀车牌
    (SELECT TOP 1 p2.plate 
     FROM Plates p2 
     WHERE p.plate LIKE LEFT(p2.plate, LEN(p.plate)) + '%'
     ORDER BY LEN(p2.plate) DESC, p2.Confidence DESC) AS ValidPlate,
    MAX(p.Confidence) AS MaxConfidence,
    MIN(p.CreatedAtTime) AS FirstSeenTime
FROM Plates p
GROUP BY 
    (SELECT TOP 1 p2.plate 
     FROM Plates p2 
     WHERE p.plate LIKE LEFT(p2.plate, LEN(p.plate)) + '%'
     ORDER BY LEN(p2.plate) DESC, p2.Confidence DESC)
ORDER BY ValidPlate;

方案2：用窗口函数标记每组的主车牌 #

WITH PlateMatches AS (
    SELECT 
        p.plate,
        p.Confidence,
        p.CreatedAtTime,
        -- 找到当前车牌所属的最长匹配车牌
        MAX(CASE WHEN p2.plate LIKE p.plate + '%' THEN p2.plate END) OVER (
            PARTITION BY SOUNDEX(p.plate)  -- 用SOUNDEX过滤发音相似的车牌，减少误匹配
        ) AS RootPlate
    FROM Plates p
    LEFT JOIN Plates p2 ON p2.plate LIKE p.plate + '%'
)
SELECT DISTINCT
    RootPlate AS ValidPlate,
    MAX(Confidence) OVER (PARTITION BY RootPlate) AS MaxConfidence,
    MIN(CreatedAtTime) OVER (PARTITION BY RootPlate) AS FirstSeenTime
FROM PlateMatches
WHERE RootPlate IS NOT NULL
ORDER BY ValidPlate;

三、优化建议 #

1. 索引优化：为plate字段创建非聚集索引，同时包含Confidence和CreatedAtTime，大幅提升匹配查询速度：

   CREATE NONCLUSTERED INDEX IX_Plate_Confidence_CreatedAt ON Plates(plate) INCLUDE(Confidence, CreatedAtTime);
   

2. 时间窗口限制：如果是实时处理，增加CreatedAtTime过滤条件（比如只处理最近5分钟内的记录），避免全表扫描，减少递归压力。
3. 自定义前缀规则：SOUNDEX适合英文发音匹配，对于车牌（字母+数字），可以自定义前缀匹配规则（比如只匹配前4位相同的车牌），减少不必要的分组。

内容的提问来源于stack exchange，提问作者SBarkovskis