如何单条SQL删除SQLite长格式矩阵的对称重复行

存储结构说明 #

对称矩阵采用长格式存储的表结构定义：

create table mat_long(a varchar, b varchar, val float)

全量存储对称矩阵时，非对角线元素会同时存储(a,b)和(b,a)两条对称重复数据，以3阶矩阵为例，全量共9行数据，其中3条对角线数据、6条重复的非对角线数据。目标是删除冗余数据，仅保留包含对角线的上三角半幅数据，最终留存6行有效记录。

原有写法问题 #

之前编写的自关联子查询写法会产生逐行匹配的嵌套遍历，时间复杂度为O(n²)，数据量稍大时就会出现查询卡死、执行超时的问题，执行效率极低。

高效单条SQL实现 #

不需要表关联、不需要复杂子查询，直接通过字段比较即可筛选出所有冗余行执行删除，SQL完全兼容SQLite及通用SQL语法：

DELETE FROM mat_long
WHERE a > b;

逻辑说明 #

• 保留规则完全匹配上三角存储要求：所有满足a <= b的行都会留存，其中a = b的对角线数据不会被误删，对称的非对角线数据对(m,n)/(n,m)中只会保留字典序更小的维度在前的一行
• 执行效率极高：语句仅做一次全表扫描，时间复杂度O(n)，即使表中存储十万、百万级矩阵元素也不会出现卡顿
• 支持执行前校验：可先运行预览语句查看待删除数据，确认逻辑符合预期后再执行删除操作，避免误删数据，预览语句如下：

SELECT * FROM mat_long WHERE a > b;

针对示例数据，预览返回的待删除行恰好是3条冗余记录：

删除后查询全表，得到的结果和预期的上三角半幅数据完全一致：

如果实际业务中维度值的排序规则和字符串字典序不一致，只需要把a > b替换为对应维度排序规则下的判断条件即可，整体逻辑不变。

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