清洗数据

DELL

2024-08-01 b650468138546705246bc28ad8165f8205211c57

清洗数据

 BattMonitor_FBS9100S_ForXinJiang/bin/com/battmonitor/data_store/InsertDataToMysql_Task.class

Binary files differ

 BattMonitor_FBS9100S_ForXinJiang/bin/com/battmonitor/sql/Sql_Mysql.class

Binary files differ

 BattMonitor_FBS9100S_ForXinJiang/src/com/battmonitor/data_store/InsertDataToMysql_Task.java

@@ -167,6 +167,12 @@
                        inserTestDataToDB(sql, m_batt_rt.mTestData.test_stoptype);

                    }*/

                }

                /**

                 *    清理数据

                 */

                clearDisTestDataEndData(m_Conn_Pool,m_batt_rt.BattGroupId,m_batt_rt.mTestData.testRecordCount);

				
				
                if(m_batt_rt.mTestData.testTimeLong < m_batt_rt.mTestTimeLongMin) {

                    try {

                        sql.mysql_con.setAutoCommit(false);

@@ -207,4 +213,218 @@
            sql_v.close_con();

        }

    }

    
    /**

     *    格式化刚结束的放电数据

     *        放电数据末尾的 清除  {测试电流<[平均电流*0.8]}=>清除

     * @param pool

     * @param BattGroupId

     * @param testRecordCount

     */

    public static void clearDisTestDataEndData(MysqlConnPool pool,int BattGroupId,int testRecordCount) {

        //1.判断当前这一笔数据是否是放电数据=>结果可分析出平均电流

        BattStatData battStatData = queryTestDataInf(pool,BattGroupId,testRecordCount);

        if(battStatData.battTestType_For_DataSave == BattStatData.BATTSTATE_DISCHARGE && battStatData.testTimeLong > 0) {

            System.out.println("testCap:"+battStatData.testCap+"\ttestTimeLong:"+battStatData.testTimeLong);

            float avg_curr = (float)Math.abs(battStatData.testCap*3600/(battStatData.testTimeLong))*0.8f;    		
            System.out.println("AvgCurr:" + avg_curr + "总记录数：" + battStatData.recordNum);

            //2.查询放电过程中最后一笔测试电流 绝对值>[平均电流*0.8],的位置=>record_num

            int max_record_num = queryMaxTestRecordNum(pool,BattGroupId,testRecordCount,avg_curr);

            System.out.println("需要删除的起始record_num:" + max_record_num);

            //3.删除记录中  > record_num 的数据结束

            if(max_record_num < battStatData.recordNum) {

                deleteData(pool,BattGroupId, testRecordCount,max_record_num);

                //4.同步 tb_batt_testdata_inf 和 tb_batt_testdata_id中的record_num

                synchTestDataRecordNum(pool, BattGroupId, testRecordCount, max_record_num);

            }

        }

    }

    
    /**

     *     查询本次测试的信息

     * @param pool

     * @param BattGroupId

     * @param testRecordCount

     * @return

     */

    public static BattStatData queryTestDataInf(MysqlConnPool pool,int BattGroupId,int testRecordCount) {

        BattStatData battData = new BattStatData();

        String sql_str_sel = "SELECT * FROM " + Sql_Mysql.BattTestDataInf_Table + " WHERE BattGroupId = " + BattGroupId + " AND test_record_count = " + testRecordCount;

        ResultSet res = null;

        Sql_Mysql sql = new Sql_Mysql(pool);

        res = sql.sqlMysqlQuery(sql_str_sel);

        try {

            if(res.next()) {

                battData.testCap = res.getFloat("test_cap");

                battData.testTimeLong = res.getInt("test_timelong");

                battData.recordNum = res.getInt("record_num");

                battData.battTestType_For_DataSave = (byte)res.getInt("test_type");

            }

        } catch (SQLException e) {

            sql.logger.error(e.toString(), e);

        } finally {

            if(null != res) {

                try {

                    res.close();

                } catch (SQLException e) {

                    sql.logger.error(e.toString(), e);

                }

            }

            sql.close_con();

        }

        return battData;

    }

    
    /**

     *    查询需要删除的数据

     * @param pool

     * @param BattGroupId

     * @param testRecordCount

     * @param avg_curr

     * @return

     */

    public static int queryMaxTestRecordNum(MysqlConnPool pool,int BattGroupId,int testRecordCount,float avg_curr) {

        int Max_Record_Num = 65535;

        String sql_str_sel =  " SELECT * "

                            + " FROM db_batt_testdata.tb_batttestdata_" + BattGroupId

                            + " WHERE  test_record_count = " + testRecordCount + " and abs(test_curr) >= " + avg_curr

                            + " ORDER BY record_num DESC";

        ResultSet res = null;

        Sql_Mysql sql = new Sql_Mysql(pool);

        res = sql.sqlMysqlQuery(sql_str_sel);

        try {

            if(res.next()) {

                Max_Record_Num = res.getInt("record_num");

            }

        } catch (SQLException e) {

            sql.logger.error(e.toString(), e);

        } finally {

            if(null != res) {

                try {

                    res.close();

                } catch (SQLException e) {

                    sql.logger.error(e.toString(), e);

                }

            }

            sql.close_con();

        }

        return Max_Record_Num;

    }

    
    
    /**

     *     删除记录

     * @param pool

     * @param battGroupId

     * @param testRecordCount

     * @param num

     */

    public static void deleteData(MysqlConnPool pool,int battGroupId, int testRecordCount, int record_num) {

        createBattTestData_Clear_Table(pool, battGroupId);

        ArrayList<String> al_str = new ArrayList<>();

    	
        String sql_str_ins = "INSERT INTO " + Sql_Mysql.BattTestData_Clear_Table + battGroupId

                + "(BattGroupId,test_record_count,test_type,data_new,data_available,record_num,test_starttime,record_time,test_timelong,online_vol,group_vol,test_curr,test_cap,mon_num,mon_vol,mon_tmp) "

                + "(SELECT BattGroupId,test_record_count,test_type,data_new,data_available,record_num,test_starttime,record_time,test_timelong,online_vol,group_vol,test_curr,test_cap,mon_num,mon_vol,mon_tmp from db_batt_testdata.tb_batttestdata_" + battGroupId

                     + " WHERE test_record_count = " + testRecordCount

                     + " and record_num > " + record_num 
                + ")";    ;

    	
        String sql_str_del = " delete FROM db_batt_testdata.tb_batttestdata_"+battGroupId+

                         " WHERE test_record_count = " +testRecordCount+

                         " and record_num > " + record_num;

        Sql_Mysql sql = new Sql_Mysql(pool.getConn());

        try {

            al_str.add(sql_str_ins);

            //al_str.add(sql_str_del);

            sql.mysql_con.setAutoCommit(false);

            for(int n=0; n<al_str.size(); n++) {

                sql.sqlMysqlExecute(al_str.get(n));

            }

            sql.mysql_con.commit();

            System.out.println("清除成功。。。");

            sql.sqlMysqlExecute(sql_str_del);

        } catch (SQLException e) {

            try {

                sql.mysql_con.rollback();

            } catch (SQLException e1) {

                sql.logger.error(e.toString(), e);

            }

            sql.logger.error(e.toString(), e);

        } finally {

            sql.close_con();

        }

    }

    
    /**

     *     删除记录

     * @param pool

     * @param battGroupId

     * @param testRecordCount

     * @param num

     */

    public static void synchTestDataRecordNum(MysqlConnPool pool,int battGroupId, int testRecordCount, int record_num) {

        String sql_str = "  update "+Sql_Mysql.BattTestDataInf_Table+

                          "  Set record_num = " + record_num

                         + " WHERE test_record_count = " + testRecordCount

                         + " and BattGroupId = " + battGroupId;        
        Sql_Mysql sql = new Sql_Mysql(pool.getConn());

        try {

            sql.sqlMysqlExecute(sql_str);

        } catch (SQLException e) {

            sql.logger.error(e.toString(), e);

        } finally {

            sql.close_con();

        }

    }

   
    /**

     *     创建数据缓存清空表

     * @param pool

     * @param battGroupId

     */

    public static void createBattTestData_Clear_Table(MysqlConnPool pool,int battGroupId) {

        String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BattTestData_Clear_Table + battGroupId + " (" + 
                "  `num` bigint(20) NOT NULL AUTO_INCREMENT," + 
                "  `BattGroupId` int(11) NOT NULL DEFAULT '0'," + 
                "  `test_record_count` int(11) NOT NULL DEFAULT '0'," + 
                "  `test_type` int(11) NOT NULL DEFAULT '0'," + 
                "  `data_new` tinyint(1) NOT NULL DEFAULT '0'," + 
                "  `data_available` tinyint(1) NOT NULL DEFAULT '0'," + 
                "  `record_num` int(11) NOT NULL DEFAULT '0'," + 
                "  `test_starttime` datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," + 
                "  `record_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," + 
                "  `test_timelong` int(11) NOT NULL DEFAULT '0'," + 
                "  `online_vol` float NOT NULL DEFAULT '0'," + 
                "  `group_vol` float NOT NULL DEFAULT '0'," + 
                "  `test_curr` float NOT NULL DEFAULT '0'," + 
                "  `test_cap` float NOT NULL DEFAULT '0'," + 
                "  `mon_num` int(11) NOT NULL DEFAULT '0'," + 
                "  `mon_vol` float NOT NULL DEFAULT '0'," + 
                "  `mon_tmp` float NOT NULL DEFAULT '0'," + 
                "  PRIMARY KEY (`num`)," + 
                "  KEY `index_test_record_count` (`test_record_count`)" + 
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        Sql_Mysql sql = new Sql_Mysql(pool.getConn());

        try {

            sql.sqlMysqlExecute(sql_str);

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            sql.close_con();

        }

    }

    
    
    
    public static void main(String[] args) {

    	
        MysqlConnPool pool = new MysqlConnPool("118.89.139.230", 3360, 5);

        clearDisTestDataEndData(pool,1005279,63);

		
		
		
        //MysqlConnPool pool = new MysqlConnPool("192.168.10.79", 3360, 5);

        //clearDisTestDataEndData(pool,1000012,170);

    }

}


 BattMonitor_FBS9100S_ForXinJiang/src/com/battmonitor/sql/Sql_Mysql.java

@@ -104,6 +104,8 @@
    public final static String BattTestDataInf_Table = DB_BATT_TESTDATA + ".`tb_batttestdata_inf`";

    public final static String BattResData_Table = DB_BATT_TESTDATA + ".`tb_BattResData`";

    public final static String BattResDataInf_Table = DB_BATT_TESTDATA + ".`tb_battresdata_inf`";



    public final static String BattTestData_Clear_Table = DB_BATT_TESTDATA + ".tb_batttestdata_clear_";

    

    public final static String UserInfTable = DB_USER + ".`tb_user_inf`";

    public final static String UserLogTable = DB_USER + ".`tb_user_log`";