Python操作SQLite为化学元素表添加丰度排名字段实现咨询

你需要的排名属于标准SQL中的跳跃并列排名，即相同值排名一致，后续排名跳过并列占用的位次，以下是两种可直接使用的实现方式：

方案一：使用窗口函数实现（推荐，SQLite 3.25.0及以上版本支持） #

SQLite 3.25版本开始支持窗口函数，直接调用内置的RANK()函数即可匹配你的排名规则，完整Python代码如下：

import sqlite3

conn = sqlite3.connect('chemicals.sqlite')
cur = conn.cursor()

# 执行排名更新
cur.execute('''
UPDATE chemicalElements
SET abundance_rank = (
    SELECT rank FROM (
        SELECT 
            symbol,
            RANK() OVER (ORDER BY abundance_in_earth_crust DESC) AS rank
        FROM chemicalElements
    ) t
    WHERE t.symbol = chemicalElements.symbol
)
''')
conn.commit()

# 可选：验证更新结果
cur.execute('SELECT symbol, abundance_in_earth_crust, abundance_rank FROM chemicalElements ORDER BY abundance_in_earth_crust DESC LIMIT 10')
print(cur.fetchall())

conn.close()

方案二：低版本SQLite兼容实现（无窗口函数场景） #

如果你的SQLite版本低于3.25，不支持窗口函数，可以用子查询手动计算排名，逻辑完全匹配规则：

import sqlite3

conn = sqlite3.connect('chemicals.sqlite')
cur = conn.cursor()

cur.execute('''
UPDATE chemicalElements
SET abundance_rank = (
    SELECT COUNT(*) + 1 
    FROM chemicalElements ce
    WHERE ce.abundance_in_earth_crust > chemicalElements.abundance_in_earth_crust
)
''')
conn.commit()

conn.close()

该方案的逻辑是统计比当前元素丰度高的记录总数，加1即为当前元素的排名，以你给出的示例验证：D的丰度为50，比它高的记录有3条，所以D的排名为3+1=4，和预期完全一致。

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