IoTDB线性随机区间填充缺失数据DEMO

1、背景

甲方网络异常，采集断了5天，按甲方要求，填补能源月报中数据

2、说明

版本：0.12.2-SNAPSHOT

从本月17号17:35分断 到 22号9点恢复

缺失数据为本月18、19、20、21、22零点首条记录

现对17号首条0点数据，与23号首条0点数据做整合 ，填充缺失5天零点数据。

全部平均太假，浮动区间为平均数的0.8-1.2。各位可自定义

本产品中，数据保留2位小数

3、代码

/**
     * 生成总和确定 一组随机数
     * @param total
     * @param splitCount
     * @param min
     * @param max
     * @return
     */
    public static List<Integer> randomAllocation(int total, int splitCount, int min, int max){
        List<Integer> al = new ArrayList<Integer>();
        Random random = new Random();

        if ((splitCount & 1) == 1) {
            System.out.println("个数" + splitCount + "是奇数，单独生成一个随机数");
            int num = 0;
            do {
                num = random.nextInt(max);

                System.out.println("单个的随机为：" + num);
            } while (num >= max || num <= min);

            total = total - num;
            al.add(num);
        }
        int couples = splitCount >> 1;
        int perCoupleSum = total / couples;

        if ((splitCount & 1) == 1) {
            System.out.println("处理后剩余为：" + total);
        }
        System.out.println("将" + total + "拆分为" + couples + "对，每对总数：" + perCoupleSum);

        for (int i = 0; i < couples; i++) {
            Boolean finish = true;
            int num1 = 0;
            int num2 = 0;
            do {
                num1 = random.nextInt(max);
                num2 = perCoupleSum - num1;
                if (!al.contains(num1) && !al.contains(num2)) {
                    if (i == 0) {
                        num1 = (total - couples * perCoupleSum) + num1;
                    }
                }
            } while (num1 < min || num1 > max || num2 < min || num2 > max);
            al.add(num1);
            al.add(num2);
        }

        int check_num = 0;
        Integer.compare(1, 2);
        al.sort(new Comparator<Integer>() {
            @Override
            public int compare(Integer o1, Integer o2) {
                return Integer.compare(o1, o2);
            }
        });

        System.out.println(Arrays.toString(al.toArray()));

        for (int x : al) {
            check_num = check_num + x;
        }
        System.out.println("验证总和：" + check_num);

        return al;
    }

    public static Map<String, List<BigDecimal>> read(long start, long end) throws Exception{
        Connection connection = null;
        Class.forName("org.apache.iotdb.jdbc.IoTDBDriver");
        connection = DriverManager.getConnection("jdbc:iotdb://XXXXX:6667/", "root", "XXXX");
        Statement statement = connection.createStatement();

        ResultSet resultSet = statement.executeQuery("select first_value(s0) from root.xinsaike.wf01.we* group by (["+start+","+end+"),10m,1d)");

        Map<String, List<BigDecimal>> map = new HashMap<String, List<BigDecimal>>(150);
        List<String> title = new ArrayList<String>(150);
        List<List<BigDecimal>> content = new ArrayList<List<BigDecimal>>(150);

        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        for (int i = 1; i < columnCount; i++) {
            title.add(metaData.getColumnLabel(i+1));
            content.add(new ArrayList<BigDecimal>(3));
        }

        while (resultSet.next()) {
            //迭代当前行，列值
            for (int i = 2;i <= columnCount ; i++) {
                String resultStr = resultSet.getString(i);
                if(null != resultStr){
                    content.get(i-2).add(new BigDecimal(resultStr));
                }else {
                    content.get(i-2).add(new BigDecimal("0"));
                }
            }
        }

        for (int i = 0; i < title.size(); i++) {
            map.put(title.get(i),content.get(i));
        }




        statement.close();
        connection.close();

        return map;

    }

    public static void insert( List<ManyDaysBean> list,long[] timestamps) throws Exception{
        Connection connection = null;
        Class.forName("org.apache.iotdb.jdbc.IoTDBDriver");
        connection = DriverManager.getConnection("jdbc:iotdb://XXXXX:6667/", "root", "XXXXX");


        Statement statement = connection.createStatement();

        for (ManyDaysBean bean : list) {
            String s = bean.getPath();

            BigDecimal temp = new BigDecimal(bean.getStartValue()).divide(new BigDecimal(100),2,BigDecimal.ROUND_DOWN);
            for (int i = 0; i < timestamps.length; i++) {
                long timestamp = timestamps[i];

                temp = temp.add(new BigDecimal(bean.getResult().get(i)).divide(new BigDecimal(100),2,BigDecimal.ROUND_DOWN));;
                String sql = "insert into "+s.substring(s.indexOf("(")+1,s.lastIndexOf("."))+"(timestamp,s0) values("+timestamp+","+temp+");";
                System.out.println(sql);
                statement.addBatch(sql);
            }

        }
        statement.executeBatch();
        statement.clearBatch();


    }

@Data
public class ManyDaysBean {

    private String path;
    /**
     * bigdecimal 转 Int
     */
    private Integer startValue;
    /**
     * bigdecimal 转 Int
     */
    private Integer endValue;


    /**
     * end - start
     */
    private Integer total;
    private Integer count;
    private Integer min;
    private Integer max;

    private List<Integer> result;

}

主函数

    public static void main(String[] args) throws Exception{

        //缺失天数的 凌晨2秒时间戳
        long[] timestamps = new long[]{1631894402000L,1631980802000L,1632067202000L,1632153602000L,1632240002000L};
        //切割数量为 缺失天数+1
        int count = timestamps.length+1;


        long start1 = 1631808000000L;
        long end1 = 1631808600000L;

        long start2 = 1632326400000L;
        long end2 = 1632327000000L;


        Map<String, List<BigDecimal>> startMap = read(start1,end1);
        Map<String, List<BigDecimal>> endMap = read(start2,end2);

        List<ManyDaysBean> list = new ArrayList<>(startMap.size());
        for (String s : startMap.keySet()) {

            ManyDaysBean bean = new ManyDaysBean();
            bean.setPath(s);
            bean.setStartValue(DecimalUtil.mul(startMap.get(s).get(0),100).intValue());
            bean.setEndValue(DecimalUtil.mul(endMap.get(s).get(0),100).intValue());

            bean.setTotal(bean.getEndValue() - bean.getStartValue());
            bean.setCount(count);
            bean.setMin((int)(bean.getTotal() /count *0.8));
            bean.setMax((int)(bean.getTotal() /count *1.2)+2);

            System.out.println(bean.getPath());
            System.out.println(bean.getStartValue());
            System.out.println(bean.getEndValue());
            System.out.println(bean.getTotal());
            System.out.println(bean.getMin());
            System.out.println(bean.getMax());

            System.out.println("-------------------------");
            if(bean.getStartValue().equals(0)){
                System.out.println("开始为空");
                continue;
            }
            if(bean.getEndValue() - bean.getStartValue() == 0){
                System.out.println("开始和结束相等");
                continue;
            }

            bean.setResult(randomAllocation(bean.getTotal(),bean.getCount(),bean.getMin(),bean.getMax()));

            list.add(bean);
        }


        insert(list,timestamps);

    }

造假成功，完美解决