[急急急]oracle有没有个功能可以从数据库表中取出数据然后通过计算然后创建一个新表？

ys_yinqin

lhqmccn 发表于 2016-10-31 17:10
按版主的方法，建个物化视图吧，可以定时更新。刚建的普通视图，等于每次查询，都是执行那个查询sql，性能 ...

好的，谢谢！！

物化视图的目标表是个统计表，那更新的时候 我用什么方式比较好呢？

FAST
COMPLETE
FORCE

选择fast，oracle会重新计算吗？

ys_yinqin

lhqmccn 发表于 2016-10-31 17:10
按版主的方法，建个物化视图吧，可以定时更新。刚建的普通视图，等于每次查询，都是执行那个查询sql，性能 ...

物化视图中不能包括ROWNUM、SYSDATE等不可重复的表达式；

创建物化视图的时候 会出现 ora-30353报错（物化视图查询重写的限制）

newkid

ys_yinqin 发表于 2016-10-31 18:26
物化视图中不能包括ROWNUM、SYSDATE等不可重复的表达式；

创建物化视图的时候 会出现 ora-30353报错 ...

你的结果中只用到SYSDATE。
在创建物化视图的时候可以先建表CREATE TABLE AS SELECT，然后在表上面创建一个ON PREBUILT TABLE的物化视图，此时这个物化视图就是一个自动刷新的壳子，这个物化视图的SELECT语句可以和建表语句稍微不同，把 "SYSDATE 修改时间" 变成 "TO_DATE(NULL) 修改时间"
然后在这个物化视图上创建一个触发器来设置这个修改时间。
不要把月日分开存放，直接存日期，输出的时候再转换成月日。

ys_yinqin

谢谢@newkid版主回复，针对你的建议：
我用代码复述一遍，看是否正确，

在创建物化视图的时候可以先建表CREATE TABLE AS SELECT，然后在表上面创建一个ON PREBUILT TABLE的物化视图，此时这个物化视图就是一个自动刷新的壳子，这个物化视图的SELECT语句可以和建表语句稍微不同，把 "SYSDATE 修改时间" 变成 "TO_DATE(NULL) 修改时间"
然后在这个物化视图上创建一个触发器来设置这个修改时间。
不要把月日分开存放，直接存日期，输出的时候再转换成月日。

--源数据表
CREATE TABLE TEST (ID VARCHAR2(32),DATEs DATE,a NUMBER);

--插入源数据
insert into test (ID, DATES, A) values ('50201281', to_date('01-01-2015', 'dd-mm-yyyy'), 1);
insert into test (ID, DATES, A)values ('50201281', to_date('02-01-2015', 'dd-mm-yyyy'), 2);
insert into test (ID, DATES, A)values ('50201281', to_date('03-01-2015', 'dd-mm-yyyy'), 3);
insert into test (ID, DATES, A)values ('50201281', to_date('01-01-2016', 'dd-mm-yyyy'), 4);
insert into test (ID, DATES, A)values ('50201281', to_date('02-01-2016', 'dd-mm-yyyy'), 5);
insert into test (ID, DATES, A)values ('50201281', to_date('03-01-2016', 'dd-mm-yyyy'), 6);
insert into test (ID, DATES, A)values ('50201281', to_date('01-01-2017', 'dd-mm-yyyy'), 7);
insert into test (ID, DATES, A)values ('50201281', to_date('02-01-2017', 'dd-mm-yyyy'), 8);
insert into test (ID, DATES, A)values ('50201281', to_date('03-01-2017', 'dd-mm-yyyy'), 9);
insert into test (ID, DATES, A)values ('50201282', to_date('01-01-2011', 'dd-mm-yyyy'), 10);
insert into test (ID, DATES, A)values ('50201282', to_date('02-01-2011', 'dd-mm-yyyy'), 11);
insert into test (ID, DATES, A)values ('50201282', to_date('03-01-2011', 'dd-mm-yyyy'), 12);
insert into test (ID, DATES, A)values ('50201282', to_date('01-01-2016', 'dd-mm-yyyy'), 13);
insert into test (ID, DATES, A)values ('50201282', to_date('02-01-2016', 'dd-mm-yyyy'), 14);
insert into test (ID, DATES, A)values ('50201282', to_date('03-01-2016', 'dd-mm-yyyy'), 15);
insert into test (ID, DATES, A)values ('50201282', to_date('01-01-2017', 'dd-mm-yyyy'), 16);
insert into test (ID, DATES, A)values ('50201282', to_date('02-01-2017', 'dd-mm-yyyy'), 17);
insert into test (ID, DATES, A)values ('50201282', to_date('03-01-2017', 'dd-mm-yyyy'), 18);

然后建一个新表test01
CREATE TABLE test01 AS
SELECT id 序号,
       to_char(dates, 'mm') 月,
       to_char(dates, 'dd') 日,
       AVG(a) a均值,
       MIN(to_char(dates, 'YYYY')) 开始年份,
       MAX(to_char(dates, 'YYYY')) 结束年份,
       MAX(to_char(dates, 'YYYY')) - MIN(to_char(dates, 'YYYY'))+1 间隔年份,
        ' null' 备注,
       TO_DATE(NULL) 修改时间  这个地方有按你说的修改
  FROM test
GROUP BY id, to_char(dates, 'mm'), to_char(dates, 'dd')

然后创建物化视图

create materialized view test01 on prebuilt table as select * from test01;

这一步就报错了。。ORA-32349,

然后在这个物化视图上创建一个触发器来设置这个修改时间。
不要把月日分开存放，直接存日期，输出的时候再转换成月日。

这两句怎么实施啊？谢谢回复！

丸喵喵

ys_yinqin 发表于 2016-11-1 09:43
谢谢@newkid版主回复，针对你的建议：
我用代码复述一遍，看是否正确，

回复问题

newkid

alter table test add constraint test_pk primary key (a);

CREATE MATERIALIZED VIEW LOG ON test WITH SEQUENCE, ROWID(id,dates),PRIMARY KEY INCLUDING NEW VALUES;


CREATE TABLE test01 AS
SELECT id SEQ,
       dates,
       AVG(a) avg_val,
       COUNT(a) cnt_a,
       count(*) cnt,
       CAST( ' null' AS VARCHAR2(100)) notes,
       sysdate modified_time
  FROM test
GROUP BY id, dates;

--- 原来的开始年份,结束年份完全没有意义，因为你的数据是按天分组的

---- test01只需保存dates, 需要年月的时候就在SELECT 里面完成计算: SELECT to_char(dates, 'mm') 月,  to_char(dates, 'dd') 日, ..... FROM TEST01

-- 然后创建物化视图, 每十分钟自动刷新一次

create materialized view test01 on prebuilt table
REFRESH FAST
START WITH SYSDATE+ 10/(24*60) NEXT  SYSDATE + 10/(24*60)  WITH ROWID
AS
SELECT id SEQ,
       dates,
       AVG(a) avg_val,
       COUNT(a) cnt_a,
       count(*) cnt,
       CAST( ' null' AS VARCHAR2(100)) notes,
       TO_DATE(NULL) modified_time
  FROM test
GROUP BY id, dates;


CREATE OR REPLACE TRIGGER trg_test01 BEFORE INSERT OR UPDATE  ON test01 FOR EACH ROW
BEGIN
   :NEW.modified_time := SYSDATE;
END;
/

ys_yinqin

谢谢@newkid版主，
我马上去试试！
感谢这些热心的朋友！

ys_yinqin

newkid 发表于 2016-11-1 10:31
alter table test add constraint test_pk primary key (a);

CREATE MATERIALIZED VIEW LOG ON test ...

@newkid 版主你好

针对你的建议，我还有几个问题咨询下
1. alter table test add constraint test_pk primary key (a);
a 很多都是重复的，不能做主键啊
2. 根据你的代码，我在plsql里面执行后，怎么test01表就不见了，是由于建立了触发器吗？
3.这个物化视图后续 取数据的效率会比 直接建一个 完整的物化视图 高吗？后续有sql语句想直接从这里取年，月。。。

newkid

ys_yinqin 发表于 2016-11-1 19:52
@newkid 版主你好

针对你的建议，我还有几个问题咨询下

你的表一定要有主键。这是个测试表，你建立的时候没有主键，我随便挑了个不重复的A列。
你自己完全可以改成其它的主键列，这个没问题吧？
创建物化视图之后，表还是在的，你所谓“不见了”是什么意思？能够 SELECT * FROM TEST01 吗？
如果有些工具要把它隐藏起来，只显示在物化视图列表中，那也没什么问题，数据还在。
从DATES中把年月算出来是小意思，完全不用考虑计算量。你可以加虚拟列，可以再建一层视图把这两个列弄出来。

ys_yinqin

newkid 发表于 2016-11-1 22:18
你的表一定要有主键。这是个测试表，你建立的时候没有主键，我随便挑了个不重复的A列。
你自己完全可以 ...

谢谢版主回复；
你的表一定要有主键。这是个测试表，你建立的时候没有主键，我随便挑了个不重复的A列。
你自己完全可以改成其它的主键列，这个没问题吧？
我可以用源表本来的主键做主键吗？比如联合主键（序号，日期一起）
alter table test add constraint test_pk primary key (序号，日期);


创建物化视图之后，表还是在的，你所谓“不见了”是什么意思？能够 SELECT * FROM TEST01 吗？
如果有些工具要把它隐藏起来，只显示在物化视图列表中，那也没什么问题，数据还在。

经查询，数据还在，只是表里被隐藏了。

从DATES中把年月算出来是小意思，完全不用考虑计算量。你可以加虚拟列，可以再建一层视图把这两个列弄出来。
这样一来，新建的物化视图会根据源表TEST 10分钟已更新，后面取值的时候在将最初年，最终年，年间隔算出来就行了，是这个意思吗？