oracle 中有没有类似sql中percent的东东

我想取记录集里的前10%的纪录,怎么实现?
---------------------------------------------------------------

对PERCENT_RANK()函数的学习:

SQL> select * from t_percent;
COL1 COL2
---------- ----------
A 11
A 12
A 13
A 14
A 15
A 16
A 17
A 18
A 19
A 20
B 11
B 12
B 13
B 14
B 15
B 16
B 17
B 18
B 19
B 20
20 rows selected.

不分组获得每行在整个纪录集中位置的百分比(从0到1):
----------------------------------------------
SQL> SELECT x.*,
2 percent_rank() over (partition by 1 order by rownum) AS pr
3 FROM t_percent x;
COL1 COL2 PR
---------- ---------- ----------
A 11 0
A 12 .052631579
A 13 .105263158
A 14 .157894737
A 15 .210526316
A 16 .263157895
A 17 .315789474
A 18 .368421053
A 19 .421052632
A 20 .473684211
B 11 .526315789
B 12 .578947368
B 13 .631578947
B 14 .684210526
B 15 .736842105
B 16 .789473684
B 17 .842105263
B 18 .894736842
B 19 .947368421
B 20 1
20 rows selected.

分组获得每行纪录在相应分组中位置的百分比:
----------------------------------------------
SQL> SELECT x.*,
2 percent_rank() over (partition by col1 order by rownum) AS pr
3 FROM t_percent x;
COL1 COL2 PR
---------- ---------- ----------
A 11 0
A 12 .111111111
A 13 .222222222
A 14 .333333333
A 15 .444444444
A 16 .555555556
A 17 .666666667
A 18 .777777778
A 19 .888888889
A 20 1
B 11 0
B 12 .111111111
B 13 .222222222
B 14 .333333333
B 15 .444444444
B 16 .555555556
B 17 .666666667
B 18 .777777778
B 19 .888888889
B 20 1
20 rows selected.

我想这样的结果应该足够回答贴主的问题了.

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus