select edu 学历,age 年龄,count(1) 人数, to_char(count(1)/sum(count(1))over(partition by 1)*100,'99.99')||'%' 占总人口百分比 from student group by edu,age; 漏了个人数 百分比那里显示要调整精度的话,改动to_char后的'99.99'
可以使用这个: select edu, age, count(edu)over(partition by edu), round(count(edu)over(partition by edu)*100/count(edu)over(),4)||'%' from student
上面哪个还是有点问题; 这个测试过了,可以使用 select distinct edu, age, count(edu)over(partition by edu,age), round(count(edu)over(partition by edu,age)*100/count(edu)over(),2)||'%' from st order by edu,age
这样子写不对!如果学历中 大专,有多个年龄段! count(1)就不对!
to_char(count(1)/sum(count(1))over(partition by 1)*100,'99.99')||'%' 占总人口百分比
from student
group by edu,age;
select edu, age, cnt/sum from (select edu, age, count(1) cnt from student group by edu, age) a, (select sum(1) sum from student) b
--TEST DATA:
本科以下 21 15
本科 19 9
本科以上 25 126--SQL:
select t01 学历, t02 年龄,t03 人数,t03/rn*100||'%' 百分比 from
(
select t.*,sum(t03) over(order by 1) rn from t
)--RESULT:
本科以下 21 15 10%
本科 19 9 6%
本科以上 25 126 84%--你现在只要把人数统计出来就可以用上面的SQL实现!
to_char(count(1)/sum(count(1))over(partition by 1)*100,'99.99')||'%' 占总人口百分比
from student
group by edu,age;
漏了个人数
百分比那里显示要调整精度的话,改动to_char后的'99.99'
select
edu,
age,
count(edu)over(partition by edu),
round(count(edu)over(partition by edu)*100/count(edu)over(),4)||'%'
from student
select
distinct
edu,
age,
count(edu)over(partition by edu,age),
round(count(edu)over(partition by edu,age)*100/count(edu)over(),2)||'%'
from st order by edu,age