SQLite单表专辑多版本记录匹配筛选及保留标记方案问询

完全可以只用SQLite实现，比Python遍历更简洁高效，不需要额外写脚本逻辑，数据库原生的集算能力就能覆盖你的所有规则需求，实现代码如下：

前置操作 #

先重置所有记录的保留标记，避免历史脏数据干扰：

UPDATE versions SET keepversion = 0;

核心标记逻辑 #

WITH album_groups AS (
    SELECT
        rowid AS rid,
        __bitspersample * 1000000 + __frequency_num AS resolution_val,
        -- 规则1：按 不区分大小写的艺人+专辑+声道数 分组对比
        MAX(__bitspersample * 1000000 + __frequency_num) OVER w AS max_resolution,
        MAX(dynamicrange) OVER w AS max_dr,
        MAX(track_count) OVER w AS max_tracks,
        -- 规则6：完全相同属性的记录按rowid排序取第一条
        ROW_NUMBER() OVER (
            PARTITION BY albumartist COLLATE nocase, album COLLATE nocase, __channels, 
                         track_count, dynamicrange, __bitspersample, __frequency_num
            ORDER BY rowid ASC
        ) AS duplicate_rn
    FROM versions
    WINDOW w AS (PARTITION BY albumartist COLLATE nocase, album COLLATE nocase, __channels)
),
to_keep AS (
    SELECT rid FROM album_groups
    WHERE
        -- 规则2：保留最高分辨率版本
        resolution_val = max_resolution
        -- 规则3：保留最高动态范围版本
        OR dynamicrange = max_dr
        -- 规则4：保留最高轨数版本
        OR track_count = max_tracks
        -- 规则5：其余属性相等时自动过滤低轨数版本（低轨数不会命中max_tracks）
        -- 规则6：完全重复的仅保留第一条
        AND duplicate_rn = 1
)
UPDATE versions SET keepversion = 1
WHERE rowid IN (SELECT rid FROM to_keep);

说明 #

• 这里用__bitspersample * 1000000 + __frequency_num计算分辨率组合值，系数选100万是因为民用音频采样率最高不会超过192000，不会出现数值叠加溢出或冲突，能准确代表分辨率高低
• 所有规则已经全部覆盖，执行后keepversion = 0的记录就是你需要后续处理的非留存条目，直接筛选即可
• 如果你的SQLite版本低于3.33.0不支持UPDATE FROM语法，再考虑用Python遍历实现：先查询所有唯一的艺人+专辑+声道数分组，再逐个分组查询所有记录按规则排序后标记保留项，实现逻辑也不复杂，但性能远低于原生SQL方案

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