`
w800927
  • 浏览: 118128 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

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

  • 大小: 12.3 KB
  • 大小: 68.6 KB
分享到：
| map遍历问题
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    ORACLE多条件统计查询的简单方法

    前几天要做一个统计查询的功能，因为涉及多张表，多种条件的统计分析。一开始便想到了UNION和IF语句，然后写了1000多行代码，就为了查30条数据觉得不应该。 然后就开始百度，多种条件下的统计。然后有一种语法让我...

    Oracle技术人才在各个行业分布岗位

    Oracle技术人才在各个行业分布岗位、薪金情况：（本资料统计来源于前程无忧、中华英才网、智联招聘网）

    Oracle数据库管理员技术指南

    4.13 分类和划分数据 4.14 划分表空间的优先次序 4.15 如何配置高可用性的 TEMP 表空间 4.16 确保在归档日志目标位置有足够的 可用空间 4.17 如何调整联机重做日志 4.18 通过优化归档速度避免 LGWR 等待 4.19...

    ORACLE9i_优化设计与系统调整

    §3.4.29 分类区的大小(SORT_AREA_SIZE) 79 §3.4.30 用户卸出文件的路径(USER_DUMP_DEST) 79 §3.5 SQL脚本文件 79 §3.5.1 建立数据字典的脚本 79 §3.5.2 建立附加的数据字典 80 §3.5.3 带“NO”的脚本 81 §...

    Oracle SQL高级编程(资深Oracle专家力作，OakTable团队推荐)--随书源代码

    6.2.3 收集执行计划统计信息 151 6.2.4 标识SQL语句以便以后取回计划 153 6.2.5 深入理解DBMS_XPLAN的细节 156 6.2.6 使用计划信息来解决问题 161 6.3 小结 169 第7章 高级分组 170 7.1 基本的GROUP BY用法 ...

    Oracle数据库、SQL

    8.1报表统计常用 16 8.2缺省情况组函数处理什么值 16 8.3当组函数要处理的所有值都为null时 16 8.4行级信息和组级信息 16 九、 group by子句 17 9.1语法和执行顺序 17 9.2分组过程 17 9.3常见错误 17 9.4多列分组 17...

    收获不知Oracle

    5.2.2.1 统计条数奋勇夺冠297 5.2.2.2 即席查询一骑绝尘302 5.2.2.3 遭遇更新苦不堪言306 5.2.2.4 重复度低一败涂地309 5.2.2.5 了解结构真相大白311 5.2.3 小心函数索引步步陷阱 315 5.2.3.1 列运算让索引失去作用...

    行运2002 Oracle论坛

    此论坛适用于Oracle 8.05、Oracle 9i,使用jdbc:thin的oracle驱动; 在tomcat、TongWeb、weblogic下测试成功; 主要功能: ===================================================== 1、论坛分类：...

    OCPOCA认证考试指南全册：Oracle Database 11g（1Z0-051，1Z0-052，1Z0-053）--详细书签版(第2/2部分)

    7.1 分类主要的数据库对象 214 7.1.1 对象类型 214 7.1.2 命名模式对象 215 7.1.3 对象名称空间 216 7.2 列举列可用的数据类型 217 7.3 创建简单的表 219 7.3.1 使用列规范创建表 220 7.3.2 使用子查询创建...

    oracle数据库笔记

    5.统计函数——列名应指定别名 35 6. Group By分组 36 7.Having子句 36 8.练习：表的查询 36 五． 在SQL *Plus中使用函数 37 1.字符串函数 37 2.数字函数 37 3.日期时间函数 38 4.转换函数 38 第五讲 修改SQL数据与...

    Oracle 10g 开发与管理

    5.统计函数——列名应指定别名 35 6. Group By分组 36 7.Having子句 36 8.练习：表的查询 36 五． 在SQL *Plus中使用函数 37 1.字符串函数 37 2.数字函数 37 3.日期时间函数 38 4.转换函数 38 第五讲 修改SQL数据与...

    oracle教案(doc)+SQL Reference 10g(chm).rar

    3.2.8 组函数及分组统计 (重点) 52 3.2.9 子查询 58 3.2.10 ROWNUM伪列(重点) 63 3.2.11 集合操作 65 3.2.12 连接查询及分组查询强化训练 68 3.3 数据库的更新操作 69 3.3.1 添加数据 69 3.3.2 添加数据的语法 69 ...

    oracle-ERP表结构培训资料.doc

    如果设置了"不允许负库存"，这样就不可能出现负 数 物品分类 MTL_CATEGORY_SETS_V 需要找顾问询问实际操作， PO 请购单([审批](采购订单(采购接收（供应商）(MTL_transaction/AP发票 请购单 头表 Po_Requisition_...

    （E文）基于成本的Oracle优化法则.pdf

    7.2.2 Oracle何时忽略直方图 149 7.3 频率直方图 152 7.3.1 伪造频率直方图 155 7.3.2 注意事项 156 7.4 “高度均衡”直方图 157 7.5 重新审视数据问题 163 7.5.1 愚蠢的数据类型 163 7.5.2 危险的默认值 166 7.6 本...

    OCPOCA认证考试指南全册：Oracle Database 11g（1Z0-051，1Z0-052，1Z0-053）--详细书签版(第1/2部分)

    7.1 分类主要的数据库对象 214 7.1.1 对象类型 214 7.1.2 命名模式对象 215 7.1.3 对象名称空间 216 7.2 列举列可用的数据类型 217 7.3 创建简单的表 219 7.3.1 使用列规范创建表 220 7.3.2 使用子查询创建...

Magicbox
Global site tag (gtag.js) - Google Analytics