1. 对维度按照度量值的排名进行统计得分,第一名100分,第二名99分,第三名98……可以先进行排名,然后用 得分值+1,减去排名既是所得分数。
1 -- 建表 2 3 create table province_people( 4 city varchar2(100), 5 people_name varchar2(100), --姓名 6 sex varchar2(1) --性别 1 男 2 女 0 未知 7 ) 8 9 -- 插入数据 10 11 insert into province_people (city, people_name, sex)values ('西安', '大王', '2'); 12 insert into province_people (city, people_name, sex)values ('西安', '刘一', '1'); 13 insert into province_people (city, people_name, sex)values ('西安', '陈二', '2'); 14 insert into province_people (city, people_name, sex)values ('西安', '张三', '0'); 15 insert into province_people (city, people_name, sex)values ('西安', '李四', '1'); 16 insert into province_people (city, people_name, sex)values ('宝鸡', '王五', '0'); 17 insert into province_people (city, people_name, sex)values ('宝鸡', '赵六', '1'); 18 insert into province_people (city, people_name, sex)values ('宝鸡', '孙七', '2'); 19 insert into province_people (city, people_name, sex)values ('宝鸡', '周八', '2'); 20 insert into province_people (city, people_name, sex)values ('汉中', '吴九', '1'); 21 insert into province_people (city, people_name, sex)values ('汉中', '郑十', '1'); 22 commit; 23 24 --查询结果 25 26 select * from province_people; 27 28 select city, px, 101 - px 29 from (select city, rank() over(order by count(people_name) desc) px 30 from province_people 31 having count(people_name) > 0 32 group by city) t1; 33 34 --按照人数排名,第一名100分,第二名99分……
2. 相同维度下,不同度量值的数据进行统计个数,可以对度量值进行稍微的转换,将所需要的度量值转换为1,然后进行汇总。
1 -- 建表 2 3 create table province_people( 4 city varchar2(100), 5 people_name varchar2(100), --姓名 6 sex varchar2(1) --性别 1 男 2 女 0 未知 7 ) 8 9 -- 插入数据 10 11 insert into province_people (city, people_name, sex)values ('西安', '大王', '2'); 12 insert into province_people (city, people_name, sex)values ('西安', '刘一', '1'); 13 insert into province_people (city, people_name, sex)values ('西安', '陈二', '2'); 14 insert into province_people (city, people_name, sex)values ('西安', '张三', '0'); 15 insert into province_people (city, people_name, sex)values ('西安', '李四', '1'); 16 insert into province_people (city, people_name, sex)values ('宝鸡', '王五', '0'); 17 insert into province_people (city, people_name, sex)values ('宝鸡', '赵六', '1'); 18 insert into province_people (city, people_name, sex)values ('宝鸡', '孙七', '2'); 19 insert into province_people (city, people_name, sex)values ('宝鸡', '周八', '2'); 20 insert into province_people (city, people_name, sex)values ('汉中', '吴九', '1'); 21 insert into province_people (city, people_name, sex)values ('汉中', '郑十', '1'); 22 commit; 23 24 --查询结果 25 26 select city, 27 count(people_name) num, 28 sum(decode(sex, 1, 1, 0)) man_num, 29 sum(decode(sex, 2, 1, 0)) femalel_num, 30 sum(decode(sex, 0, 1, 0)) null_num 31 from province_people 32 group by city; 33 34 --数据经过转换,可以一次性查出各种类型的数据数量