Oracle数据库高效sql语句的整理
业务需求说明:由于之前公司后台APP端有一个document表,该表中包含了所有的信息,新的需求就是通过该表创建出一个新的用户表(usertable)和一个档案表(document,该表只保留原document的部分字段)。
ps:原document表的数据有25万条的数据,因此在处理的时候,需要考虑到性能的问题。
首先当然是对两个表进行字段的设计,然后是再将原document表中的数据导入到这两个表中。
方案1:第一次采用的是最简单,最直接的方法,但是忽视了效率的问题。
方法是通过查询原document表,依次处理每一条记录,并将处理后的结果再插入到需要新建的两个表。这个方法效率极其的低下,无法接受。于是寻找优化的方法。
方案2:采用oracle数据库自身的sql语句来实现。
1、由于在导入到usertable和document表中需要用到一个字段birthyear,而原document表中没有birthyear字段,因此首先需要对原document表进行添加一个字段birthyear,为了能够添加一个字段,并且不修改原documen表中的信息,我们重新将原document表的数据全部导入一个临时的表document_temp中。因此,这里涉及到两步操作:
(1)将原document导入到一个临时的document_temp表中
create table document_temp as select * from document;
(2)将document_temp表的数据增加一个字段birthyear
alter table document_temp add birthyear;
update document_temp set birthyear=to_char(createtime,'yyyy')-age;
update document_test_temp set birthyear =-1 where birthyear is null;
2、根据document_temp表创建新的usertable。
这里需要说明一下:由于一个用户可能对应的多个document记录,并且会存在同名同姓的情况,因此我们做了一步初步处理,处理的方式是:如果一个人的姓名(name)和出生年份(birthyear)都相同的话,我们认为他们就是同一个人。由于usertable中还有包含该用户拥有的档案总数(record_num)的字段。因此这一步设计到三步。
(1)将document_temp的表数据部分字段导入到usertable中,此时是有重复的。
insert into USERTABLE_TEST(ID ,ANONAME ,APPID ,BIRTHYEAR ,SEX,HOSPITALNAME ,HOSPITALCODE )
select ID ,NAME ,BOOKPOP , BIRTHYEAR ,SEX ,HOSPITALNAME ,HOSPITALCODE
from document_temp
(2)去除重复(在这一步中,由于上面产生的usertable是有25万条记录,对该表进行去除重复是需要考虑性能的问题。)
1)//删除重复,这种方法对于大批量的数据来说效率非常的慢,这当时不可取的方法。
delete from usertable_test a where (a.anoname,a.birthyear) in (select anoname,birthyear from usertable_test group by anoname,birthyear having count(*) > 1)
and rowid not in (select min(rowid) from usertable_test group by Id,seq having count(*)>1)
2)这种方法跟第一种方法本质上是一样的
delete usertable_test where rowid not in
(select min(rowid) from usertable_test group by anoname,birthyear);
3)后来,受我同学的启发,说查出重复后,不能删除,已删除效率就非常低,于是有下面的方法,但是下面的方式只能产生某些字段,因为我重复的记录是根据这个字段来决定的。
create table usertable_test_temp select distinct anoname ,birthyear from usertable_test
4)//针对大量的数据,通过这个可以高效的去除重复
create table usertable_test_temp as select * from usertable_test where rowid in
(select min(rowid) from usertable_test group by anoname,birthyear);
(3)统计字段recordnum的值。
select count(*) as recordnum ,anoname,birthyear from usertable_test group by anoname,birthyear;
(4)为了简化步骤,可以将第(2)和(3)步进行合并成一步:
create table usertable_test_temp_temp as select X.*,Y.recordnum from (select * from usertable_test_temp where rowid in
(select min(rowid) from usertable_test_temp group by name,birthyear) ) X left join (select count(*) as recordnum ,name,birthyear from usertable_test_temp group by name,birthyear) Y on X.anoname=Y.anoname and X.birthyear=Y.birthyear;
此时可以得到一个已经去除重复的usertable表。
3、根据document_temp创建一个新的document的表。
业务要求是:在创建新的document表的时候,通过外键的形式关联userid,即新的document中添加一个字段userid的方式来关联USERTABLE表。所以在导入到新的document中需要关联usertable的id。
实现的方法:由于usertable也是根据document_temp表导入而来,因此,去重后的usertable与document_temp是有一个对应关系 ,也就是一个用户对应多个document记录。即每一个document_temp记录都有一个用户与之对应。因此可在导入新的document的时候,就将这个对应关系关联起来即可。
insert into document_test (ID,DOCID,USERID,HOSPITALNAME,HOSPITALCODE,IMAGENUM,ANONAME,SEX,AGE,DEPARTMENT,DOCUMENTDATE,DOCUMENTURL,CREATETIME,CHECKPART,CHECKPRO,DOCUMENTPATH,birthyear)
select a.ID,a.ID,b.ID,a.HOSPITALNAME,a.HOSPITALCODE,a.IMAGENUM,a.NAME,a.SEX,a.AGE,a.DEPARTMENT,
a.DOCUMENTDATE,a.DOCUMENTURL,a.CREATETIME,a.CHECKPART,a.CHECKPRO,a.DOCUMENTPATH, a.BIRTHYEAR
from document_temp a , usertable b where a.anoname = b.anoname and a.birthyear = b.birthyear
最新文章
- C#面向编程的三大特征
- eBay 消息发送(2)
- DB层面上的设计 分库分表 读写分离 集群化 负载均衡
- 从APP消息推送所理解的观察者模式
- java之集合类特性对比分析列表
- Android中View绘制优化二一---- 使用<;include />;标签复用布局文件
- CentOS tripwire-文件指纹
- Spring接口编程_设值注入和构造注入
- 新生命组件XAgent使用心得
- 微信公众号调用JS-SDK
- version.go
- DNS服务器 知识点
- python3安装pcap遇到的问题
- [JOISC2014]スタンプラリー
- org.apache.hadoop.security.AccessControlException
- Daily Scrumming* 2015.12.21(Day 13)
- log4net引用了Mysql.Data.dll,但是就是不能写到mysql数据库的解决办法
- C#编程(二十九)----------泛型接口
- super限定,子类中系统查找变量的顺序:
- mac编辑器vim美化
热门文章
- 如何知道btree树的高度
- servlet定义
- Linux C Programing - Arguments(2)
- HDU 5686:2016";百度之星"; - 资格赛 Problem B
- min.css----全世界最快的CSS框架
- 【leetcode❤python】 414. Third Maximum Number
- O(1) Check Power of 2 - LintCode
- Statement returned more than one row, where no more than one was expected
- 使用nodewebx进行前后端开发环境分离
- 【CSU1812】三角形和矩形 【半平面交】