统计sendpay维度下，的有效订单量， 用户数， 总金额，优惠后金额，

1、在sendpay的维度下，统计有效订单量， 用户数， 总金额，优惠后金额，

select count(id) 有效订单量，
sum(totalprice) 优惠金额，
sum(totalprice-youhui)  优惠后金额，
count(distinct(memberid)) 用户数，
sendpay   手机客户端下单标记
from realtime_orders
where createtime>='2018-06-30'
groop by sendpay

测试样例数据
订单号 用户id    原始金额    优惠价 标识字段    下单时间
id  memberid    totalprice  youhui  sendpay createdate
7453513 79782   941 20  2   2018/6/30
4849686 79988   924 65  2   2018/6/30
5657879 32072   350 90  1   2018/6/30
300005  71384   996 24  0   2018/6/30
8316515 20977   774 88  0   2018/6/30
8074152 40884   532 10  2   2018/6/30
5932832 9184    922 37  1   2018/6/30
3817736 84880   903 99  2   2018/6/30
2546517 33109   965 94  0   2018/6/30
731608  40369   337 9   1   2018/6/30

1.1、为了对用户去重，将每个用户存到内存中， 供判断是否已经存在

//为了统计在sendpay维度下的用户数， 将sendpay+"_"+memberid存入map中，方便判断是否已经存在该用户
String key = sendpay+"_"+memberid;
String vx = memCache.get(key);
boolean isHasMem = false;
if(StringUtils.isNotEmpty(vx)){
    isHasMem = true;
    //不存在，放入map中。
    memCache.put(key, "1");
}

1.2、将统计数据存入map中

            //检查memberMap， 为空，将会创建一个map
            checkMap();
            //memberMap存储sendpay维度下的用户指标
            //key=sendpay;value = count(id),sum(totalPrice),sum(totalPrice - youhui),count(distinct memberid)
            String value = memberMap.get(sendpay);

            if(value!=null){
                //指标的统计
                String[] vals = value.split(",");
                int id_num = Integer.valueOf(vals[0])+1;
                double tp = Double.valueOf(vals[1])+Double.valueOf(totalprice);
                double etp = Double.valueOf(vals[2])+(Double.valueOf(totalprice)-Double.valueOf(youhui));
                //在1.1中已经判断了是否已经有了该用户， isHasMem决定是否累加。
                int counter_member = Integer.valueOf(vals[3])+(isHasMem?0:1);
                value =  id_num+","+tp+","+etp+","+counter_member;
            }else{//初次存入
                value =  1+","+totalprice+","+(Double.valueOf(totalprice)-Double.valueOf(youhui))+","+(isHasMem?0:1);
            }
            System.out.println("sendpay = "+sendpay +"  value = "+value);
            memberMap.put(sendpay,value);

1.3、控制定时写入

    /**
     * @Description

     * 定时器，控制定时写入数据库中, 
     * 
     */
    class cacheTimer extends TimerTask{

        @Override
        public void run() {
            Map tmpMap = new HashMap();
            //将memberMap中数据拷贝到tmpMap中， 用于写入数据库， 
            tmpMap.putAll(memberMap);
            memberMap = null;//清空
            saveMysql(tmpMap);

        }
    }


    /**
     * 将数据插入数据库
     * @param tmpMap
     */
    private void saveMysql(Map tmpMap){
        try{

            for(Map.Entry entry:tmpMap.entrySet()){
                //order_nums,p_total_price,y_total_price,order_members,sendpay

                //sendpay
                String key = entry.getKey();

                //order_nums,p_total_price,y_total_price,order_members
                String value = entry.getValue();
                String[] vals = value.split(",");
                int id_num = Integer.valueOf(vals[0]);
                double tp = Double.valueOf(vals[1]);
                double etp = Double.valueOf(vals[2]);
                int counter_member = Integer.valueOf(vals[3]);

                //select ...
                String sql = "select order_nums,p_total_price,y_total_price,order_members "
                        + "from total_order where sendpay='"+key+"'";
                ResultSet set = new DBTools().query(sql);
                int order_nums = 0;
                double p_total_price = 0;
                double y_total_price = 0;
                int order_member = 0;

                while(set.next()){
                    order_nums = set.getInt(1);
                    p_total_price = set.getDouble(2);
                    y_total_price = set.getDouble(3);
                    order_member = set.getInt(4);
                }

                order_nums += id_num;
                p_total_price += tp;
                y_total_price += etp;
                order_member += counter_member;

                StringBuffer sBuffer = new StringBuffer();
                if(order_nums==id_num){//insert
                    sBuffer.append("insert into total_order(order_nums,p_total_price,y_total_price,order_members,sendpay) values(")
                    .append(order_nums+","+p_total_price+","+y_total_price+","+order_member+",'"+key+"')");
                }else{//update
                    sBuffer.append("update total_order set order_nums="+order_nums)
                    .append(",p_total_price="+p_total_price)
                    .append(",y_total_price="+y_total_price)
                    .append(",order_members="+order_member)
                    .append(" where sendpay="+key);

                }

                System.out.println("sql = "+sBuffer.toString());

                new DBTools().executeCommit(sBuffer.toString());
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

2、在sendpay的维度下，统计某天的有效订单量， 用户数， 总金额，优惠后金额， 这是在sendpay，creatime两个维度

select
 createtime  统计时间
 count(id) 有效订单量，
sum(totalprice) 优惠金额，
sum(totalprice-youhui)  优惠后金额，
count(distinct(memberid)) 用户数，
sendpay   手机客户端下单标记
from realtime_orders
where createtime>='2018-06-30'
groop by sendpay，

对1.1中的去重用户增加时间维度

key改为

String key = sendpay+"_"+memberid+"_"+createtime;

memberMap的key就是memberid+”_”+createtime