SQLite中根据rarity字段值加权计数统计owner记录的方法

需求规则 #

统计每个owner（用户ID）的记录加权总得分，按得分倒序生成排行榜，权重规则：

• rarity值为1：单条记录计1分
• rarity值为2：单条记录计10分
• rarity值为3：单条记录计100分
  固定筛选条件：
• usercards.group模糊匹配指定分组参数
• customCard字段值为0

此前方案的问题 #

• 拼接多段独立SELECT加IF分支的写法不符合SQL语法逻辑，无法正常执行
• 调用POWER()函数的方案不适配SQLite，该数据库无此内置函数
• 按owner + rarity双字段分组后再加权的写法，会把同一个owner的不同稀有度记录拆成多行返回，没有做同owner的得分汇总，不符合单用户单条排行记录的要求

可直接运行的正确SQL #

SELECT
  owner,
  SUM(
    CASE rarity
      WHEN 1 THEN 1
      WHEN 2 THEN 10
      WHEN 3 THEN 100
      ELSE 0
    END
  ) AS count
FROM usercards
WHERE
  `group` LIKE '%$group%'
  AND customCard = 0
GROUP BY owner
ORDER BY count DESC;

实现说明 #

• 逐行匹配符合筛选条件的记录时，先根据当前行的rarity值计算单条记录的权重分，再通过SUM()聚合函数按owner分组累加总分，不需要额外按rarity字段拆分分组
• 所有逻辑均在SQL聚合层完成，执行效率与最初的无加权基础查询一致，比在Node.js层拉取全量数据再计算的方案性能高很多
• 写法完全兼容SQLite语法，无特殊函数依赖，其中ELSE 0分支代表非1/2/3的rarity值不计入得分，可根据实际业务规则调整对应权重

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