Rank database elements python-CodePudding

I have a snippet dataset of chemical elements shown below

I want to create a new field called "abundance_rank", where a rank of 1 refers to the highest abundant value (Referring to 'Abundance in Earth's crust' column)

For example, (symbol, abundance_in_earth_crust) with rows (A, 100), (B, 100), (C, 100), (D, 50), the expected records of (symbol, abundance_in_earth_crust, abundance_rank) will be (A, 100, 1), (B, 100, 1), (C, 100, 1), (D, 50, 4). Note that the abundance_rank of D is 4.

I created a new field abundance_rank with the code below

import sqlite3

conn = sqlite3.connect('chemicals.sqlite')
cur = conn.cursor()
cur.execute('ALTER TABLE chemicalElements ADD COLUMN abundance_rank INTEGER')
conn.commit()

CodePudding user response：

The RANK() analytic function would seem to be what you want here:

SELECT Symbol, abundance,
       RANK() OVER (ORDER BY abundance DESC) AS abundance_rank
FROM elements
ORDER BY abundance_rank DESC;

Your Python code might look like this:

import sqlite3

conn = sqlite3.connect('chemicals.sqlite')
cur = conn.cursor()
sql = """SELECT Symbol, abundance,
    RANK() OVER (ORDER BY abundance DESC) AS abundance_rank
FROM elements
ORDER BY abundance_rank DESC"""
for row in cur.execute(sql):
    print(row)