oralce分類統計

14883 ワード
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(CASEWHEN city='海口市'THEN 1 ELSE 0 END)haikou_num,SUM(CASEWHEN city='広州市'THEN 1 ELSE 0 END)guangzhou_numFROM ACTIVITY_HIS
decode方式
SQL> select id,num from test1; ID NUM-----------------1 3 3 1 4 2 5 3 1 3 8 6 rows selected SQL>select decode(grouping(id)、1,'合計',id)id,sum(num)num 2 from test 1 3 group by rollup(id);ID NUM----------------------------------------------------1 7 2 7 3 9合計23

最終大神: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テーブルの方法は主に2つあります:1、2つのテーブルが完全に同じである場合:insert into table 1 select*from table 2 where condition(条件)2、2つのテーブルフィールドが異なる場合:insert into table 1(フィールド1、フィールド2、フィールド3...)selectフィールド1、フィールド2、フィールド3....from table 2 where condition(条件)上記の2つの方法は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 VARCHAR2CHANNELTYPESTATIS COUNTER NUMBERCHANNELTYPESTATIS CHANNELTYPE VARCHAR2CHANNELTYPESTATIS ACCOUNTAREA VARCHAR2CHANNELTYPESTATIS STATISTIME DATECHANNELTYPESTATIS 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とgroupbyを一緒に使用するとエラーが発生します.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
SwingでのJRadioButtonマルチセレクションボックスの使い方
1.sparksqlのcase classによるDataFramesの作成(反射)