`
w800927
  • 浏览: 119976 次
  • 性别: 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
分享到:
评论

相关推荐

    oracle函数分类.rar

    下面将详细讲解在"oracle函数分类.rar"压缩包中的主要函数类别及其应用。 首先,我们关注的是**数值型函数**。在处理数字数据时,Oracle提供了多种操作函数,如`ROUND`用于四舍五入,`TRUNC`用于截断小数部分,`MOD...

    Oracle数据库统计应用的结构设计与维护技巧.pdf

    Oracle数据库的统计应用结构设计涵盖了逻辑结构和物理结构两个层面。逻辑结构由表、索引、段、视图、存储过程和触发器等数据库对象组成,提供了数据管理和操作的抽象层。物理结构由数据文件、日志文件、控制文件和...

    oracle函数大全(分类显示).zip_Oracle 函数分类_oracle_oracle函数分类_oracle函数查阅用文档

    这份"oracle函数大全(分类显示).zip"压缩包显然是一份详细的Oracle函数参考手册,对于初学者而言,是一个非常宝贵的资源。下面,我们将深入探讨Oracle函数的分类以及一些重要的函数类型。 1. **数学函数**: 包括...

    Oracle数据库统计应用的结构设计与维护技巧 (1).pdf

    【Oracle数据库统计应用的结构设计与维护技巧】 Oracle数据库作为全球广泛使用的关系型数据库系统,以其卓越的兼容性、可移植性、可联接性、高生产率和开放性著称。在处理海量数据和瞬间入库的需求方面,Oracle展现...

    oracle函数大全(chm格式分类显示)

    本资源“oracle函数大全(chm格式分类显示)”详细介绍了Oracle中的主要函数类别,帮助用户更好地理解和运用这些功能。 1. **数值型函数**: Oracle提供了多种数值处理函数,如`ROUND()`用于四舍五入,`TRUNC()`用于...

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

    根据来自前程无忧、中华英才网及智联招聘网的数据统计,我们可以详细探讨Oracle技术人才在不同行业的分布情况以及他们的薪酬水平。 #### 软件业 **岗位名称**:在软件业中,主要分为Oracle开发类和Oracle维护类两...

    Oracle课程设计.doc

    - 查询和修改学生的基本信息,按系、班级和科目分类。 - 分析各科成绩,计算平均分、及格率和优秀率。 - 对课程成绩进行统计,展示平均分、最高分和最低分。 - 排名功能,按系别或班级对单科成绩进行排名,对...

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

    在Oracle数据库中,进行多条件统计查询是一项常见的任务,尤其在复杂的业务场景下,需要对多张表的数据进行综合分析。传统的做法可能涉及到大量的UNION和IF语句,但这不仅可能导致代码冗长,还可能影响查询效率。在...

    Oracle中的树状查询(递归查询)

    Oracle数据库系统在处理层次数据或树形结构时,提供了强大的工具——递归查询。递归查询允许我们在数据表中处理嵌套级别的数据,这在权限查询、组织结构、产品分类等场景中尤其常见。本文将深入探讨Oracle中的树状...

    Oracle逻辑读写深入分析

    本文将围绕“Oracle逻辑读写深入分析”这一主题,结合描述及部分示例内容,全面解析Oracle数据库中逻辑读的概念、分类及其统计方法。 #### 逻辑读的定义与分类 逻辑读是指Oracle在执行查询或事务处理过程中,从...

    Oracle优化常用概念.pptx

    统计信息是 Oracle 优化器计算成本所需要的信息,包括表的行数、块数、空块数、块的平均剩余空间等、表的列的唯一值数、null 值数等、索引的级数、叶子块数、唯一值数等。可以使用 `analyze table tb_name compute|...

    Oracle9i数据挖掘分类算法的分析与比较.pdf

    Oracle9i数据库通过内嵌的ODM(Oracle Data Mining)工具,使得用户可以直接在数据库中进行数据挖掘,无需深入的统计分析训练,即可进行趋势预测。 ODM由数据挖掘API和数据挖掘服务器(DMS)两部分组成。API允许...

    《Oracle Wait Interface性能诊断与调整实践指南》

    OWI提供了一种机制来记录这些等待事件,并对它们进行分类统计,帮助我们分析哪些等待事件消耗了大量时间,进而找出性能瓶颈。 #### 三、OWI的配置与启用 1. **配置参数**:OWI的配置主要涉及两个初始化参数:`...

    ORACLE11G性能分析及优化对策

    - **Oracle锁分类**: - **Enqueues**:通常与业务逻辑紧密相关。 - **Latches**:主要涉及系统资源的锁定,例如内存结构、SQL解析等。 - **锁的原因**:并发操作需要锁来避免数据冲突。 - **锁的原则**: - 只有...

    Mysql+Oracle函数文档

    这里,我们主要探讨的是它们的函数用法,通过提供的文档资源——"MySQL_5.1_zh.chm" 和 "oracle函数大全(分类显示).chm",我们可以深入了解这两个数据库系统的函数功能和应用场景。 首先,MySQL 是一款开源、免费的...

    Oracle数据库课程大作业.pdf

    这些任务涵盖基本的SQL操作,以及更复杂的查询,如计算平均价格、按季度分类的销售额报表等。同时,还需要编写存储过程以生成汇总报表,展示销售额和订单统计信息。 总的来说,Oracle数据库课程涵盖了数据库的基础...

    oracle9i官方文档9.2

    9. **Data Mining**:Oracle 9i首次引入了数据挖掘功能,包括分类、聚类、关联规则和预测等,为企业分析和预测业务趋势提供了工具。 10. **Advanced Queuing (AQ)**:Oracle AQ是内置的消息队列服务,支持异步处理...

    Oracle数据库优化详解

    - 探讨了CBO的选择性、统计信息的收集方法,以及如何查看和收集对象的统计信息。 - 介绍了直方图(Histogram)的概念、收集方法及其对选择性的影响。 - 讨论了表连接的不同方式,如排序-合并连接(SMJ)、嵌套...

    基于Oracle复杂油藏数据统计查询方法与实际案例.pdf

    2. 利用Group By和Having语句对数据进行分组和筛选,确保能够按照统计需求对数据进行准确的分类和汇总。 3. 运用表达式嵌套在Select语句中进行复杂的计算,以满足统计计算的需求。 4. 采用聚合函数如SUM, AVG等来对...

Global site tag (gtag.js) - Google Analytics