关键字
KingbaseES ,ratio_to_report
问题描述
如何计算统计值的百分比?
解决方案
KingbaseES数据库有一个非常简便易用的分析函数,可以计算一个值与一组值之和的百分比,组是通过over窗口来确定。其语法如下:
ration_to_report(expr) over(query_partition_clause)
如果query_partition_clause 没有指定,则组为整个查询结果集。
示例:
1、建表并插入数据
create table student_score (name varchar(10) , grade int ,score numeric);
insert into student_score values ('joy',1,100);
insert into student_score values ('mary',2,98);
insert into student_score values ('tony',3,97);
insert into student_score values ('sun',2,89);
insert into student_score values ('jack',1,95);
2、执行语句
select name , score,ratio_to_report(score) over () from student_score;
返回结果:
name | score | ratio_to_report
------+-----+------------------------
joy | 100 | 0.20876826722338204593
mary | 98 | 0.20459290187891440501
tony | 97 | 0.20250521920668058455
sun | 89 | 0.18580375782881002088
jack | 95 | 0.19832985386221294363
(5行记录)
3、执行语句
select name,score,ratio_to_report(score) over (partition by grade) from student _score;
返回结果:
name | score | ratio_to_report
------+-------+----------------------
joy | 100 | 0.51282051282051282051
jack | 95 | 0.48717948717948717949
mary | 98 | 0.52406417112299465241
Sun | 89 | 0.47593582887700534759
Tony | 97 | 1.0o000000000000000000
(5行记录)