2011年7月27日
存储过程来更新表的记录
摘要： CREATE OR REPLACE procedure DWUSER.update_DW_Amount as begin execute immediate 'truncate table DW_AMOUNT'; insert into DW_AMOUNTselect login_date, login_amount, login_fail_amount, register_amount, login_fail_amount/login_amount as LOGIN_FAIL_RATE FROM (select login_date, count(user_id) login 阅读全文
posted @ 2011-07-27 13:51 八神吻你 阅读(8581) 评论(0) 推荐(0) 编辑
最近七天的数据每天相互对比
摘要： select tb7.MM,cpu7,cpu6,cpu5,cpu4,cpu3,cpu2,cpu1 from(select substr(mi,-5) mm,cpu7 from(select to_char(SAMPLE_TIMESTAMP_UTC,'yyyy/mm/dd hh24:mi') mi,avg(PARAMETER_VALUE) cpu7 from SANDBOXUSER.BMC_CPU where to_char(SAMPLE_TIMESTAMP_UTC,'yyyy/mm/dd hh24:mi') BETWEEN TO_CHAR ( trunc(SYS 阅读全文
posted @ 2011-07-27 12:23 八神吻你 阅读(300) 评论(0) 推荐(0) 编辑
在like 后面用系统变量来完成模糊查询
摘要： select * from(select to_char(SAMPLE_TIMESTAMP_UTC,'yyyy/mm/dd hh24:mi') mi,avg(PARAMETER_VALUE) cpu7 from SANDBOXUSER.BMC_CPU where to_char(SAMPLE_TIMESTAMP_UTC,'yyyy/mm/dd hh24:mi') BETWEEN TO_CHAR ( trunc(SYSDATE - 18), 'yyyy/mm/dd hh24:mi' )AND TO_CHAR ( trunc(SYSDATE - 17 阅读全文
posted @ 2011-07-27 11:35 八神吻你 阅读(400) 评论(0) 推荐(0) 编辑
查询昨天的数据
摘要： //昨天0点到今天0点select * from tablename where create_time>= trunc(SYSDATE-1) and create_time <trunc(SYSDATE )//昨天8点到今天8点select * from tablename where create_time> trunc(SYSDATE-1)+ 8/24 and create_time < trunc(SYSDATE) + 8/24 阅读全文
posted @ 2011-07-27 09:40 八神吻你 阅读(177) 评论(0) 推荐(0) 编辑
建立新表：用as关键字+select语句
摘要： create table temp_cup asselect to_char(SAMPLE_TIMESTAMP_UTC,'yyyy/mm/dd hh24:mi') a,avg(PARAMETER_VALUE) val from SANDBOXUSER.BMC_CPU where to_char(SAMPLE_TIMESTAMP_UTC,'yyyy/mm/dd hh24:mi') BETWEEN TO_CHAR ( SYSDATE - 10, 'yyyy/mm/dd hh24:mi' )AND TO_CHAR ( SYSDATE - 9, ' 阅读全文
posted @ 2011-07-27 08:55 八神吻你 阅读(688) 评论(0) 推荐(0) 编辑