7.3 Rank a sequence
1. Rank in ascending order
2. Rank in descending order
3. Rank after de-duplicating members
4. Rank after calculating the average of duplicate members
SPL
| A | B | |
|---|---|---|
| 1 | [2,1,3,4,8,5,2,0] | |
| 2 | =A1.ranks() | /[3,2,5,6,8,7,3,1] |
| 3 | =A1.ranks@z() | /[5,7,4,3,1,2,5,8] |
| 4 | =A1.ranks@i() | /[3,2,4,5,7,6,3,1] |
| 5 | =A1.ranks@s() | /[3.5,2.0,5.0,6.0,8.0,7.0,3.5,1.0] |
SQL
1. Rank in ascending order
SELECT RANK() OVER (ORDER BY COLUMN_VALUE) AS rank
FROM TABLE(sys.odcinumberlist(2,1,3,4,8,5,2,0))
ORDER BY ROWNUM;
2. Rank in descending order
SELECT RANK() OVER (ORDER BY COLUMN_VALUE DESC) AS rank
FROM TABLE(sys.odcinumberlist(2,1,3,4,8,5,2,0))
ORDER BY ROWNUM;
3. Rank after de-duplicating members
SELECT DENSE_RANK() OVER (ORDER BY COLUMN_VALUE) AS rank
FROM TABLE(sys.odcinumberlist(2,1,3,4,8,5,2,0))
ORDER BY ROWNUM;
4. Rank after calculating the average of duplicate members
SQL doesn’t provide a ready-made method for this calculation.
Python
ss = pd.Series([2, 1, 3, 4, 8, 5, 2, 0])
seq_rank_ascending = ss.rank(ascending=True,method='min').values #[3. 2. 5. 6. 8. 7. 3. 1.]
seq_rank_descending = ss.rank(ascending=False,method='min').values # [5. 7. 4. 3. 1. 2. 5. 8.]#
seq_rank_unique = ss.rank(ascending=True,method='dense').values # [3. 2. 4. 5. 7. 6. 3. 1.]
seq_rank_repeated_mean = ss.rank(ascending=True,method='average').values
# [3.5 2. 5. 6. 8. 7. 3.5 1. ]
7.4 Rank the structured data
Example codes for comparing SPL, SQL, and Python
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
