package com.go;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Scanner;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
public class yjshujv {
//createnewtable nt;
String kkid;
String dfs;
Date df;
String dd;
int interval;
public yjshujv() {
// TODO 自动生成的方法存根
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","8621903");
Statement sm=ct.createStatement();
Scanner s=new Scanner(System.in);
System.out.println("输入卡口号");
kkid = s.next();
System.out.println("输入日期");
dfs = s.next();
System.out.println("输入统计间隔");
interval = s.nextInt();
//将string类型转变为Data类型
SimpleDateFormat df1 = new SimpleDateFormat("yyyy/MM/dd");
try {
df = df1.parse(dfs);
SimpleDateFormat df2 = new SimpleDateFormat("yyyy_MM_dd");
dd = df2.format(df);
System.out.println("dd="+dd);
} catch (ParseException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
/*
* SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date now = new Date();
System.out.println("当前时间:" + sdf.format(now));
方法一:
Date afterDate = new Date(now .getTime() + 300000);
System.out.println(sdf.format(afterDate ));
方法二:
Calendar nowTime = Calendar.getInstance();
nowTime.add(Calendar.MINUTE, 5);
System.out.println(sdf.format(nowTime.getTime()));
*
*/
//System.out.println(df+kkid+interval);
//提交语句
String sqltj="commit";
//创建子表
String sqlct="CREATE TABLE "+"cltx_"+dd+"(xh NUMBER NOT NULL,jgsj DATE NOT NULL,clsd NUMBER(4,1) NOT NULL)" ;
System.out.println(sqlct);
sm.executeUpdate(sqlct);
System.out.println(sqltj);
sm.executeUpdate(sqltj);
//插入数据
String sqlin="insert into "+"cltx_"+dd+" (xh,jgsj,clsd)"+" select xh,jgsj,clsd"+" FROM cltx_201203 where kkid='"+kkid+"' and jgsj>=to_date('"+dfs+" 00:00:00','yyyy/mm/dd/ hh24:mi:ss') and jgsj<=(to_date('"+dfs+" 00:00:00','yyyy/mm/dd/ hh24:mi:ss')+1)" ;
System.out.println(sqlin);
sm.executeUpdate(sqlin);
System.out.println(sqltj);
sm.executeUpdate(sqltj);
//处理数据
//处理1分钟分组数据
String sqlcl="select to_char(jgsj,'yyyy/mm/dd hh24:mi'),count(jgsj),avg(clsd) from "+"cltx_"+dd+" group by to_char(jgsj,'yyyy/mm/dd hh24:mi') order by to_char(jgsj,'yyyy/mm/dd hh24:mi')" ;
System.out.println(sqlcl);
ResultSet rs=sm.executeQuery(sqlcl);
// String sql="select * from"+" cltx_"+dd+"where xh=220338989 " ;
// ResultSet rs=sm.executeQuery("select * from emp");
// ResultSet rs1=sm.executeQuery(sql);、
//显示1分钟数据
PrintWriter out = null;
String str = "";
while(rs.next()){
// System.out.println("用户名"+rs.getString(2));
// System.out.println(rs.getString("count(jgsj)")+","+rs.getString("avg(clsd)"));
//改成csv输出
try {
out = new PrintWriter(new FileWriter("f:\cltx_"+dd+"_1.csv"));
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
str+=rs.getString("count(jgsj)")+","+rs.getString("avg(clsd)");
str+='n';
}
out.append(str);
System.out.println("输出一分钟数据文件以_1结尾");
out.close();
//处理5分钟分组数据
//一个循环处理0-1440分钟,t=1440/interval(math.ceil)
PrintWriter out1 = null;
String str1 = "";
ResultSet rs1 = null;
for (int i = 0; i < Math.ceil(1440/(double)interval); i++) {
String sqlcl1="select count(xh),avg(clsd) from "+"cltx_"+dd+" t where jgsj>=(to_date('"+dfs+" 00:00:00','yyyy/mm/dd/ hh24:mi:ss')+"+(i*interval)+"/(24*60)) and jgsj
//System.out.println(sqlcl1);//测试
rs1=sm.executeQuery(sqlcl1);
//select rownum rn,a.* from (select count(xh),avg(clsd) from CLTX_2012_03_19 t where jgsj>=(to_date('2012/3/19 00:00:00','yyyy/mm/dd/ hh24:mi:ss')+1/(24*60)) and jgsj
while(rs1.next()){
//System.out.println(rs1.getString("count(xh)")+","+rs1.getString("avg(clsd)"));//测试
//改成csv输出
try {
out1 = new PrintWriter(new FileWriter("f:\cltx_"+dd+"_"+interval+".csv"));
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
str1+=rs1.getString("count(xh)")+","+rs1.getString("avg(clsd)");
str1+='n';
}
out1.append(str1);
out1.close();
}
System.out.println("输出指定间隔数据以下划线_间隔时间结尾");
rs1.close();
rs.close();
sm.close();
ct.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
public static void main(String[] args) {
yjshujv yjsj=new yjshujv();
}
}
转载本文请联系原作者获取授权,同时请注明本文来自倪升华科学网博客。
链接地址:http://blog.sciencenet.cn/blog-785542-723741.html
上一篇:九月十号,老师们节日快乐!
下一篇:该程序用于2个csv文件的合并