DB

안드로이드 SQLite 통계쿼리문 작성예시

떠돌이개발자

개발을 하다보면 통계정보가 필요할 경우가 많다. 이러한 경우 DB에서 데이터를 조회하는데 내가 원하는 데이터에 맞게 스크립트를 작성해야한다. 나중에 기억나지 않을 것을 대비하여 기록을 남긴다. 

 

[스크립트1]

    public static Cursor selectData(SQLiteDatabase database, String tabId){
        String pDate ="";

        if("tab1".equals(tabId)) {
            pDate = DateUtil.getCurrentDate("yyyy-MM-dd");
        }else if("tab2".equals(tabId)) {
            //Calendar cal = new GregorianCalendar();
            Calendar cal = Calendar.getInstance();
            //cal.setTime(new Date());
            //cal.add(Calendar.YEAR, 1); // 1년을 더한다.
            //cal.add(Calendar.MONTH, 1); // 한달을 더한다.
            //cal.add(Calendar.DAY_OF_YEAR, 1); // 하루를 더한다.
            //cal.add(Calendar.HOUR, 1); // 시간을 더한다

            cal.add(Calendar.DATE, -1); // 하루전날짜 구한다.
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                pDate = sdf.format(cal.getTime());
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        
        Cursor cursor =  database.rawQuery(
                "SELECT * FROM ( SELECT _id, _start_date, _end_date, _charge_time, _etc "
//                        +" ,COUNT(CASE WHEN _type = 'FU' THEN 1 ELSE 0 END) OVER() AS full_cnt "
//                        +" ,COUNT(CASE WHEN _type = 'NO' THEN 1 ELSE 0 END) OVER() AS normal_cnt "
//                        +" ,COUNT(CASE WHEN _type = 'OC' THEN 1 ELSE 0 END) OVER() AS overcharge_cnt "
//                        +" ,COUNT(CASE WHEN _type = 'OH' THEN 1 ELSE 0 END) OVER() AS overheat_cnt "
//                        +" ,COUNT(CASE WHEN _type = 'OV' THEN 1 ELSE 0 END) OVER() AS over_voltage_cnt "
//                        +" ,COUNT(CASE WHEN _type = 'CO' THEN 1 ELSE 0 END) OVER() AS cold_cnt "
                        +" FROM TB_CHARGE_HISTORY"
                        + " WHERE _start_date >="  + "'" + pDate + " 00:00:00'"
                        + " AND _start_date <="  + "'" + pDate + " 23:59:59' )"
                ,null);

        return cursor;
    }

[스크립트2]

    public static Cursor selectMonthStatForYear2(SQLiteDatabase database, String sYear){
        String startDate = sYear +"-01-01";
        String endDate = sYear +"-12-31";
        
        Cursor cursor =  database.rawQuery(
                "SELECT '01' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-01' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-01' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-01' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-01' AND  _event_type = 4) AS bed_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '02' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-02' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-02' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-02' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-02' AND  _event_type = 4) AS bed_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '03' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-03' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-03' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-03' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-03' AND  _event_type = 4) AS bed_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '04' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-04' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-04' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-04' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-04' AND  _event_type = 4) AS bed_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '05' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-05' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-05' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-05' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-05' AND  _event_type = 4) AS bed_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '06' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-06' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-06' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-06' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-06' AND  _event_type = 4) AS bed_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '07' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-07' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-07' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-07' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-07' AND  _event_type = 4) AS bed_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '08' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBERTB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-08' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-08' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-08' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-08' AND  _event_type = 4) AS bed_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '09' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-09' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-09' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-09' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-09' AND  _event_type = 4) AS bed_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '10' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-10' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-10' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-10' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-10' AND  _event_type = 4) AS bed_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '11' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-11' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-11' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-11' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-11' AND  _event_type = 4) AS bed_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '12' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-12' AND  _event_type = 1) AS good_cnt "
                        + ",(SELECT COUNT(1) AS critical_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-12' AND  _event_type = 2) AS critical_cnt "
                        + ",(SELECT COUNT(1) AS normal_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-12' AND  _event_type = 3) AS normal_cnt "
                        + ",(SELECT COUNT(1) AS bed_cnt  FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear +"-12' AND  _event_type = 4) AS bed_cnt "
                        +")"

                ,null);
        return cursor;
    }

[스크립트3]

  public static Cursor selectMonthStatForYear(SQLiteDatabase database, String sYear){
        String startDate = sYear +"-01-01";
        String endDate = sYear +"-12-31";
        
        Cursor cursor =  database.rawQuery(
                "SELECT '01' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-01' ) AS good_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '02' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-02' ) AS good_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '03' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-03' ) AS good_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '04' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-04' ) AS good_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '05' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-05' ) AS good_cnt "  // AND  date(_event_date) <= date('now')
                        +")"
                        +" UNION ALL "
                        +"SELECT '06' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-06' ) AS good_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '07' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-07') AS good_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '08' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-08' ) AS good_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '09' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-09' ) AS good_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '10' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-10') AS good_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '11' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-11') AS good_cnt "
                        +")"
                        +" UNION ALL "
                        +"SELECT '12' AS sMonth, * FROM ("
                        + "(SELECT COUNT(1) AS good_cnt FROM TB_LOTTO_WINNER_NUMBER"
                        + " WHERE substr(_event_date,1,7) ='" + sYear + "-12' ) AS good_cnt "
                        +")"

                ,null);
        return cursor;
    }

 사람의 기억력은 오래가지않으니까………

Related Posts

Leave a Reply

error: Content is protected !!