oralce分类统计
@Transactional@Component("statisDAO")@Scope("prototype")public class StatisDAO extends AbstractDAO { private static final String SQL_STATIS_CHANNELTYPE = "INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange) " + "SELECT DBMS_RANDOM.STRING('A', 32) AS uuid, count(*) AS counter, channeltype, accountarea,sysdate AS statistime,to_char(?)||'/'||to_char(?) AS STATISRANGE " + "FROM ACTIVITY WHERE txtime>=? AND txtime<=? GROUP BY ROLLUP (channeltype,accountarea)"; @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class) public boolean statisChanneltype(String startDateStr,String endDateStr) { try { DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date startDate=format.parse(startDateStr+" 00:00:00"); Date endDate=format.parse(endDateStr+" 23:59:59"); Object[] values = {startDateStr,endDateStr,startDate,endDate}; this.getSmartDAO().update("", SQL_STATIS_CHANNELTYPE, values); return true; } catch (Exception e) { e.printStackTrace(); return false; } }public static void main(String[] args) { ApplicationContext applicationContext = new FileSystemXmlApplicationContext("file:E:\\CommonContext.xml"); StatisDAO statisDAO = (StatisDAO) applicationContext.getBean("statisDAO"); System.out.println(statisDAO.statisChanneltype("2011-04-01", "2011-08-02")); }}按日期时长统计：
?
?
统计每个时间段得条目数，txtime是date类型，sql如下：
按小时---select to_char(txtime, 'YYYY-MM-dd hh24') a,count(*) from mytable group by to_char(txtime,'YYYY-MM-dd hh24');
按天---select to_char(txtime, 'YYYY-MM-dd') a,count(*) from mytable group by to_char(txtime,'YYYY-MM-dd');
按月---select to_char(txtime, 'YYYY-MM') a,count(*) from mytable group by to_char(txtime,'YYYY-MM');
按年---select to_char(txtime, 'YYYY') a,count(*) from mytable group by to_char(txtime,'YYYY');
按季度---select to_char(txtime, 'YYYY-q') a,count(*) from mytable group by to_char(txtime,'YYYY-q');
按周---select to_char(txtime, 'ww') a,count(*) from mytable group by to_char(txtime,'ww');
?
参考http://hi.baidu.com/qq5910225/blog/item/4a8c91d7ef0ec514a08bb74e.html
?
同字符串类型统计
?
select
SUM(CASE
WHEN city = '海口市' THEN
1
ELSE
0
END) haikou_num
,SUM(CASE
WHEN city = '广州市' THEN
1
ELSE
0
END) guangzhou_num
FROM ACTIVITY_HIS
decode方式
SQL> select id,num from test1;
ID NUM
---------- ----------
1 3
1 4
2 2
2 5
3 1
3 8
6 rows selected
SQL> select decode(grouping(id),1, '总计 ',id) id,sum(num) num
2 from test1
3 group by rollup(id);
ID NUM
---------------------------------------- ----------
1 7
2 7
3 9
总计 23
<!--StartFragment -->
最终大神：select count(*), province, city from ACTIVITY_HIS where to_char(txtime, 'YYYY-MM-dd')='2011-06-01' group by rollup (province,city);
rollup是数据挖掘中的上卷操作，运行效果截图
?
另外，将取出来得数据直接插入表中：
select ...into 用在存储过程里面的,保存变量
insert ...select 就是插入语句,插入的部分是表中的数据
?
举例来说：
insert 表 select * from 表的方法主要有两种：
1、若两张表完全相同：
insert into table1
select * from table2
where condition(条件)
2、若两张表字段有不同的：
insert into table1(字段1，字段2，字段3....)
select 字段1，字段2，字段3....
from table2
where condition(条件)
上述两种方法均不需要写values.
综上，我的sql是：
INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange)
SELECT DBMS_RANDOM.STRING('A', 32) AS uuid, count(*) AS counter, channeltype, accountarea,sysdate AS statistime,TO_CHAR(?,'yyyy-mm-dd') AS STATISRANGE
FROM ACTIVITY
WHERE where to_char(txtime, 'yyyy-mm-dd')>'?' AND TO_CHAR(txtime, 'yyyy-mm-dd')<'?'
GROUP BY ROLLUP (channeltype,accountarea)
?
channeltypestatis数据字典：
CHANNELTYPESTATIS UUID VARCHAR2
CHANNELTYPESTATIS COUNTER NUMBER
CHANNELTYPESTATIS CHANNELTYPE VARCHAR2
CHANNELTYPESTATIS ACCOUNTAREA VARCHAR2
CHANNELTYPESTATIS STATISTIME DATE
CHANNELTYPESTATIS STATISRANGE VARCHAR2
?
在编写过程中还有问题：
?
代码片段
@Transactional@Component("statisDAO")@Scope("prototype")public class StatisDAO extends AbstractDAO { private static final String SQL_STATIS_CHANNELTYPE = "INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange) SELECT DBMS_RANDOM.STRING('A', 32) AS uuid, count(*) AS counter, channeltype, accountarea,sysdate AS statistime,TO_CHAR(?/?) AS STATISRANGE FROM ACTIVITY WHERE txtime>=to_date(?, 'yyyy-mm-dd') AND txtime<=to_date(?, 'yyyy-mm-dd') GROUP BY ROLLUP (channeltype,accountarea)"; @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class) public boolean statisChanneltype(String startDate,String endDate) { try { Object[] values = {startDate,endDate,startDate,endDate}; this.getSmartDAO().update("", SQL_STATIS_CHANNELTYPE, values); return true; } catch (Exception e) { e.printStackTrace(); return false; } } public static void main(String[] args) { ApplicationContext applicationContext = new FileSystemXmlApplicationContext("file:E:\\CommonContext.xml"); StatisDAO statisDAO = (StatisDAO) applicationContext.getBean("statisDAO"); System.out.println(statisDAO.statisChanneltype("2011-04-01", "2011-08-02")); }}?
如果 报无效的列索引，原因是在sql语句中 ？不能加' 而应如上所示
?
但上述代码依旧报错，错误的数字格式，因为是我传 2011-08-02 参数的时候，解析sql时，会产生错误，最终改成了如下格式
?
@Transactional@Component("statisDAO")@Scope("prototype")public class StatisDAO extends AbstractDAO { private static final String SQL_STATIS_CHANNELTYPE = "INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange) SELECT DBMS_RANDOM.STRING('A', 32) AS uuid, count(*) AS counter, channeltype, accountarea,sysdate AS statistime,TO_CHAR(?-?) AS STATISRANGE FROM ACTIVITY WHERE txtime>=to_date(?, 'yyyymmdd') AND txtime<=to_date(?, 'yyyymmdd') GROUP BY ROLLUP (channeltype,accountarea)"; @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class) public boolean statisChanneltype(String startDate,String endDate) { try { Object[] values = {startDate,endDate,startDate,endDate}; this.getSmartDAO().update("", SQL_STATIS_CHANNELTYPE, values); return true; } catch (Exception e) { e.printStackTrace(); return false; } } public static void main(String[] args) { ApplicationContext applicationContext = new FileSystemXmlApplicationContext("file:E:\\CommonContext.xml"); StatisDAO statisDAO = (StatisDAO) applicationContext.getBean("statisDAO"); System.out.println(statisDAO.statisChanneltype("20110401", "20110802")); }}?
但是TO_CHAR(?-?) 这个函数给我解析成数学符号后，全给我相减了NND,应该用oracle中的字符串拼接。其他常用oracle函数见下一篇
?
带拼接的，还应注意时间，前面的不能满足当天查询
?
@Transactional@Component("statisDAO")@Scope("prototype")public class StatisDAO extends AbstractDAO { private static final String SQL_STATIS_CHANNELTYPE = "INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange) " + "SELECT DBMS_RANDOM.STRING('A', 32) AS uuid, count(*) AS counter, channeltype, accountarea,sysdate AS statistime,to_char(?)||'/'||to_char(?) AS STATISRANGE " + "FROM ACTIVITY WHERE txtime>=? AND txtime<=? GROUP BY ROLLUP (channeltype,accountarea)"; @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class) public boolean statisChanneltype(String startDateStr,String endDateStr) { try { DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date startDate=format.parse(startDateStr+" 00:00:00"); Date endDate=format.parse(endDateStr+" 23:59:59"); Object[] values = {startDateStr,endDateStr,startDate,endDate}; this.getSmartDAO().update("", SQL_STATIS_CHANNELTYPE, values); return true; } catch (Exception e) { e.printStackTrace(); return false; } }public static void main(String[] args) { ApplicationContext applicationContext = new FileSystemXmlApplicationContext("file:E:\\CommonContext.xml"); StatisDAO statisDAO = (StatisDAO) applicationContext.getBean("statisDAO"); System.out.println(statisDAO.statisChanneltype("2011-08-01", "2011-08-01")); }} ?
?
但是上述sql语句是采用SELECT DBMS_RANDOM.STRING('A', 32)随机数的，这个是可能相同的，因此要求使用sequence，但是sequence和group by一块使用会报错：ORA-02287: 此处不允许序号，
INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange) SELECT SEQ_PK.nextVal as uuid , count(*) AS counter, channeltype, accountarea,sysdate AS statistime,to_char('2011-08-01')||'/'||to_char('2011-08-01') AS STATISRANGE FROM ACTIVITY WHERE txtime>=to_date('2011-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND txtime<=to_date('2011-08-01 23:59:59','yyyy-mm-dd hh24:mi:ss') GROUP BY ROLLUP (channeltype,accountarea)报错：oracle sequence ORA-02287: 此处不允许序号
采用
INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange) SELECT SEQ_PK.nextVal as uuid , P.* from (select count(*) AS counter, channeltype, accountarea,sysdate AS statistime,to_char('2011-08-01')||'/'||to_char('2011-08-01') AS STATISRANGE FROM ACTIVITY WHERE txtime>=to_date('2011-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND txtime<=to_date('2011-08-01 23:59:59','yyyy-mm-dd hh24:mi:ss') GROUP BY ROLLUP (channeltype,accountarea)) P ?
进行使用即可
最终代码为：
?
?
@Transactional@Component("statisDAO")@Scope("prototype")public class StatisDAO extends AbstractDAO { private static final String SQL_STATIS_CHANNELTYPE ="INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange) " + "SELECT SEQ_PK.nextVal as uuid , P.* from " + "(select count(*) AS counter, channeltype, accountarea,sysdate AS statistime,to_char(?)||'/'||to_char(?) AS STATISRANGE " + "FROM ACTIVITY WHERE txtime>=? AND txtime<=? " + "GROUP BY ROLLUP (channeltype,accountarea)) P"; @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class) public boolean statisChanneltype(String startDateStr,String endDateStr) { try { DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date startDate=format.parse(startDateStr+" 00:00:00"); Date endDate=format.parse(endDateStr+" 23:59:59"); Object[] values = {startDateStr,endDateStr,startDate,endDate}; this.getSmartDAO().update("", SQL_STATIS_CHANNELTYPE, values); return true; } catch (Exception e) { e.printStackTrace(); return false; } } }?
哎，又有新问题出现了，当用rollup进行统计时，如果原有数据中有null，在所有时它统计也过也是null于是就杯具了，如同第一个图，经过搜索
?
将上述sql更改为
INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange) SELECT SEQ_PK_CHANNELTYPESTATIS.nextVal as uuid , P.* from (select count(*) AS counter, Decode(Grouping(channeltype),1,'所有渠道',channeltype) channeltype,Decode(Grouping(accountarea),1,'所有地区',accountarea) accountarea,sysdate AS statistime,to_char('2011-08-01')||'/'||to_char('2011-08-01') AS STATISRANGE FROM ACTIVITY WHERE txtime>=to_date('2011-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND txtime<=to_date('2011-08-01 23:59:59','yyyy-mm-dd hh24:mi:ss') GROUP BY ROLLUP (channeltype,accountarea)) P ?
即可，其中搜索出的结果如图2所示
?
问题还在：就是accountarea区域不能就合计，就是不能求得北京的all值，因此sql再次修改为
?
INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange) SELECT SEQ_PK_CHANNELTYPESTATIS.nextVal AS uuid , P.* FROM(SELECT count(*) AS counter, Decode(Grouping(channeltype),1,'ALL',channeltype) channeltype,Decode(Grouping(accountarea),1,'ALL',accountarea) accountarea,sysdate AS statistime,to_char('2011-08-01')||'/'||to_char('2011-08-01') AS STATISRANGE FROM activity WHERE txtime>=to_date('2011-04-01','yyyy-mm-dd') AND txtime<=to_date('2011-08-01','yyyy-mm-dd')GROUP BY CUBE (channeltype,accountarea)) P?
注意将rollup换成了cube即可,关于cube和rollup的区别详见：
http://space.itpub.net/519536/viewspace-610997