7.3 Rank a sequence

1. Single-field ranking

2. Multi-field ranking

SPL

SQL

1. Single-field ranking

SELECT RANK() OVER (ORDER BY BIRTHDAY) AS rank
FROM EMPLOYEE
ORDER BY ROWNUM;

2. Multi-field ranking

SELECT RANK() OVER (ORDER BY BIRTHDAY ASC,HIREDATE DESC) AS rank
FROM (SELECT * FROM EMPLOYEE ORDER BY EID)
ORDER BY ROWNUM;

Python

df = pd.read_csv('../EMPLOYEE.csv')
#Rank by birthday in ascending order
birthday_ascending_rank = df['BIRTHDAY'].rank(ascending=True,method='min').values 

Python doesn’t support multi-field ranking, so it has to hard code to do this calculation.

7.5 Transpose
Example codes for comparing SPL, SQL, and Python