Oracle 数据处理

1. 对维度按照度量值的排名进行统计得分，第一名100分，第二名99分，第三名98……可以先进行排名，然后用得分值+1，减去排名既是所得分数。

 -- 建表

 create table province_people(

 city varchar2(100),

 people_name varchar2(100), --姓名

 sex varchar2(1)  --性别 1 男  2 女  0 未知

 )

 -- 插入数据

 insert into province_people (city, people_name, sex)values ('西安', '大王', '');

 insert into province_people (city, people_name, sex)values ('西安', '刘一', '');

 insert into province_people (city, people_name, sex)values ('西安', '陈二', '');

 insert into province_people (city, people_name, sex)values ('西安', '张三', '');

 insert into province_people (city, people_name, sex)values ('西安', '李四', '');

 insert into province_people (city, people_name, sex)values ('宝鸡', '王五', '');

 insert into province_people (city, people_name, sex)values ('宝鸡', '赵六', '');

 insert into province_people (city, people_name, sex)values ('宝鸡', '孙七', '');

 insert into province_people (city, people_name, sex)values ('宝鸡', '周八', '');

 insert into province_people (city, people_name, sex)values ('汉中', '吴九', '');

 insert into province_people (city, people_name, sex)values ('汉中', '郑十', '');

 commit;

 --查询结果

 select * from province_people;

 select city, px, 101 - px

   from (select city, rank() over(order by count(people_name) desc) px

           from province_people

         having count(people_name) > 0

          group by city) t1;

 --按照人数排名，第一名100分，第二名99分……

2. 相同维度下，不同度量值的数据进行统计个数，可以对度量值进行稍微的转换，将所需要的度量值转换为1，然后进行汇总。

 -- 建表

 create table province_people(

 city varchar2(100),

 people_name varchar2(100), --姓名

 sex varchar2(1)  --性别 1 男  2 女  0 未知

 )

 -- 插入数据

 insert into province_people (city, people_name, sex)values ('西安', '大王', '');

 insert into province_people (city, people_name, sex)values ('西安', '刘一', '');

 insert into province_people (city, people_name, sex)values ('西安', '陈二', '');

 insert into province_people (city, people_name, sex)values ('西安', '张三', '');

 insert into province_people (city, people_name, sex)values ('西安', '李四', '');

 insert into province_people (city, people_name, sex)values ('宝鸡', '王五', '');

 insert into province_people (city, people_name, sex)values ('宝鸡', '赵六', '');

 insert into province_people (city, people_name, sex)values ('宝鸡', '孙七', '');

 insert into province_people (city, people_name, sex)values ('宝鸡', '周八', '');

 insert into province_people (city, people_name, sex)values ('汉中', '吴九', '');

 insert into province_people (city, people_name, sex)values ('汉中', '郑十', '');

 commit;

 --查询结果

 select city,

        count(people_name) num,

        sum(decode(sex, 1, 1, 0)) man_num,

        sum(decode(sex, 2, 1, 0)) femalel_num,

        sum(decode(sex, 0, 1, 0)) null_num

   from province_people

  group by city;

 --数据经过转换，可以一次性查出各种类型的数据数量

巴特西

Oracle 数据处理

最新文章

热门文章