7.3 序列排名
1. 升序排名
2. 降序排名
3. 去掉重复成员后再排名
4. 重复成员算均值后再排名
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. 升序排名
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. 降序排名
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. 去掉重复成员后再排名
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. 重复成员算均值后再排名
SQL 没有现成的方法来完成。
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. ]
