public class VipStatistics {
private Log logger = LogFactory.getLog(VipStatistics.class);
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
private SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMdd");
private Connection con =
ConnectionFactory.getInstance().getConnection(Constant.DATABASE_STATISTICS1);
private Connection orderCon =
ConnectionFactory.getInstance().getConnection(Constant.DATABASE_ORDER_SELECT);
/*
* vip分为红钻黄钻(vipType)，vip有包年vip和普通vip(isYearVip)，vip有0-7个等级(vipLevel)
*
* [0：普通，1：包年][vip等级-1]
*/
private int[][] yellowDiamondActiveCount = new int[2][20];
private int[][] redDiamondActiveCout = new int[2][20];
private int[][] yellowDiamondRegistCount = new int[2][20];
private int[][] redDiamondRegistCout = new int[2][20];
private int[][] yellowDiamondPayCount = new int[2][20];
public void handle(Date date) {
// 初始化
for (int i = 0; i < 2; i++) {
for (int j = 0; j < 8; j++) {
yellowDiamondActiveCount[i][j] = 0;
redDiamondActiveCout[i][j] = 0;
yellowDiamondRegistCount[i][j] = 0;
redDiamondRegistCout[i][j] = 0;
yellowDiamondPayCount[i][j] = 0;
}
}
try {
logger.info("================VipStatistics======" + sdf.format(date) + "======begin===");
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
PreparedStatement pstmt = con.prepareStatement("select count(*) from vip_level where date=?");
pstmt.setDate(1, new java.sql.Date(calendar.getTimeInMillis()));
ResultSet rs = pstmt.executeQuery();
if (rs.next() && rs.getInt(1) != 0) {
logger.info(
"================VipStatistics======"
+ sdf.format(date)
+ "======record exist end===");
return;
}
// 从active表中统计vip用户数量
countVipInfo(calendar);
// 黄钻各等级活跃用户
saveRecord(date, 0, 0, yellowDiamondActiveCount);
// 红钻各等级活跃用户
// saveRecord(date, 1, 0, redDiamondActiveCout);
// 黄钻各等级新增用户
saveRecord(date, 0, 1, yellowDiamondRegistCount);
// 红钻各等级新增用户
// saveRecord(date, 1, 1, redDiamondRegistCout);
// 黄钻付费用户
saveRecord(date, 0, 2, yellowDiamondPayCount);
logger.info("================VipStatistics======" + sdf.format(date) + "======end===");
} catch (Exception e) {
logger.error(e.getMessage(), e);
} finally {
if (con != null) {
try {
con.close();
} catch (Exception ex) {
logger.error("", ex);
}
}
}
}
private void countVipInfo(Calendar calendar) throws SQLException {
List<Long> registUserIds = getRegistUsers(calendar);
List<Long> payUserIds = getPayUsers(calendar);
long queryBeginTime = System.currentTimeMillis();
PreparedStatement pstmt =
con.prepareStatement(
"select user_id,y_vip_type,y_vip_level,r_vip_type,r_vip_level from user_active_"
+ sdf2.format(calendar.getTime())
+ " where y_vip_type > 0 or r_vip_type > 0 ");
try {
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
long userId = rs.getLong(1);
int yVipType = rs.getInt(2);
int yVipLevel = rs.getInt(3);
int rVipType = rs.getInt(4);
int rVipLevel = rs.getInt(5);
if (yVipType > 0) {
yellowDiamondActiveCount[yVipType - 1][yVipLevel]++;
}
if (rVipType > 0) {
redDiamondActiveCout[rVipType - 1][rVipLevel]++;
}
// 是新增用户
if (registUserIds.contains(userId)) {
if (yVipType > 0) {
yellowDiamondRegistCount[yVipType - 1][yVipLevel]++;
}
if (rVipType > 0) {
redDiamondRegistCout[rVipType - 1][rVipLevel]++;
}
}
// 是付费用户
if (payUserIds.contains(userId)) {
if (yVipType > 0) {
yellowDiamondPayCount[yVipType - 1][yVipLevel]++;
}
}
}
rs.close();
} catch (Exception e) {
logger.error("", e);
}
pstmt.close();
long queryEndTime = System.currentTimeMillis();
logger.info("--statistics vipInfo cost:" + (queryEndTime - queryBeginTime));
}
/**
* @param calendar
* @return
* @throws SQLException
*/
private List<Long> getRegistUsers(Calendar calendar) throws SQLException {
List<Long> registUserIds = new ArrayList<Long>();
for (int i = 0; i < 10; i++) {
try {
PreparedStatement pstmt =
con.prepareStatement("select uid from user_register" + i + " where date =?");
pstmt.setDate(1, new java.sql.Date(calendar.getTimeInMillis()));
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
registUserIds.add(rs.getLong(1));
}
} catch (Exception e) {
logger.error("", e);
}
}
return registUserIds;
}
private List<Long> getPayUsers(Calendar calendar) {
List<Long> payUserIds = new ArrayList<Long>();
Calendar calendar1 = Calendar.getInstance();
calendar1.setTime(calendar.getTime());
calendar1.set(Calendar.HOUR_OF_DAY, 0);
calendar1.set(Calendar.MINUTE, 0);
calendar1.set(Calendar.SECOND, 0);
Calendar calendar2 = Calendar.getInstance();
calendar2.setTime(calendar.getTime());
calendar2.set(Calendar.HOUR_OF_DAY, 23);
calendar2.set(Calendar.MINUTE, 59);
calendar2.set(Calendar.SECOND, 59);
try {
PreparedStatement pstmt =
orderCon.prepareStatement(
"select distinct(uid) from ordersqq where time between ? and ? and cmd=7");
pstmt.setLong(1, calendar1.getTimeInMillis());
pstmt.setLong(2, calendar2.getTimeInMillis());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
payUserIds.add(rs.getLong(1));
}
} catch (Exception e) {
logger.error("", e);
}
return payUserIds;
}
/**
* @param date
* @param vipType 黄钻为0，红钻为1
* @param type 活跃用户为0，新增用户为1
* @param list
* @throws SQLException
*/
private void saveRecord(Date date, int vipType, int type, int[][] list) throws SQLException {
PreparedStatement pstmt;
for (int isYearVip = 0; isYearVip < 2; isYearVip++) {
for (int vipLevel = 0; vipLevel < 8; vipLevel++) {
pstmt =
con.prepareStatement(
"delete from vip_level where date=? and type=? and is_year_vip=? and vip_level=? and vip_type=?");
pstmt.setDate(1, new java.sql.Date(date.getTime()));
pstmt.setInt(2, type);
pstmt.setInt(3, isYearVip);
pstmt.setInt(4, vipLevel);
pstmt.setInt(5, vipType);
pstmt.execute();
pstmt.close();
pstmt =
con.prepareStatement(
"insert into vip_level(date,type,is_year_vip,vip_level,vip_type,count) values(?,?,?,?,?,?)");
pstmt.setDate(1, new java.sql.Date(date.getTime()));
pstmt.setInt(2, type);
pstmt.setInt(3, isYearVip);
pstmt.setInt(4, vipLevel);
pstmt.setInt(5, vipType);
pstmt.setInt(6, list[isYearVip][vipLevel]);
pstmt.execute();
pstmt.close();
}
}
}
}
public void handle(Date date, int days) {
Connection con = null;
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMdd");
logger.info(
"================LevelUserLostStatistics======"
+ sdf.format(date)
+ "===="
+ days
+ "======begin===");
con = ConnectionFactory.getInstance().getConnection(Constant.DATABASE_STATISTICS1);
HashMap<Integer, Integer> levelActiveUserMap = new HashMap<Integer, Integer>();
HashMap<Integer, Integer> levelLostUserMap = new HashMap<Integer, Integer>();
for (int i = 0; i < 100; i++) {
HashMap<Long, Integer> levelMap1 = new HashMap<Long, Integer>();
HashMap<Long, Integer> levelMap2 = new HashMap<Long, Integer>();
// HashMap<Long,Date> dateMap1 = new HashMap<Long,Date>();
// HashMap<Long,Date> dateMap2 = new HashMap<Long,Date>();
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
long time1 = System.currentTimeMillis();
for (int j = 0; j < days; j++) {
logger.info("i:" + i + ",date:" + sdf2.format(calendar.getTime()));
PreparedStatement pstmt =
con.prepareStatement(
"select user_id,user_level from user_active_"
+ sdf2.format(calendar.getTime())
+ " where user_id%100="
+ i);
try {
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
if (levelMap1.get(rs.getLong(1)) == null) {
levelMap1.put(rs.getLong(1), rs.getInt(2));
// dateMap1.put(rs.getLong(1), calendar.getTime());
}
levelMap2.put(rs.getLong(1), rs.getInt(2));
// dateMap2.put(rs.getLong(2), calendar.getTime());
}
rs.close();
} catch (Exception e) {
}
pstmt.close();
calendar.add(Calendar.DAY_OF_YEAR, 1);
}
long time2 = System.currentTimeMillis();
logger.info(
"-----"
+ i
+ "---levelMap1.size:"
+ levelMap1.size()
+ "---levelMap2.size:"
+ levelMap2.size()
+ "--cost:"
+ (time2 - time1));
HashMap<Long, Integer> levelMap = new HashMap<Long, Integer>();
levelMap.putAll(levelMap1);
long time3 = System.currentTimeMillis();
logger.info("put cost:" + (time3 - time2));
for (int j = days; j < days * 2; j++) {
PreparedStatement pstmt =
con.prepareStatement(
"select user_id,user_level from user_active_"
+ sdf2.format(calendar.getTime())
+ " where user_id%100="
+ i);
try {
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
if (levelMap.get(rs.getLong(1)) != null) {
levelMap.remove(rs.getLong(1));
}
}
rs.close();
} catch (Exception e) {
}
pstmt.close();
calendar.add(Calendar.DAY_OF_YEAR, 1);
}
logger.info(
"-----"
+ i
+ "---levelMap.size:"
+ levelMap.size()
+ "--cost:"
+ (System.currentTimeMillis() - time3));
Set<Long> uidSet = levelMap1.keySet();
Iterator<Long> ite = uidSet.iterator();
while (ite.hasNext()) {
long uid = ite.next();
int level1 = levelMap1.get(uid);
int level2 = levelMap2.get(uid);
if (levelActiveUserMap.get(level1) == null) {
levelActiveUserMap.put(level1, 1);
} else {
levelActiveUserMap.put(level1, levelActiveUserMap.get(level1) + 1);
}
for (int j = (level1 + 1); j <= level2; j++) {
if (levelActiveUserMap.get(j) == null) {
levelActiveUserMap.put(j, 1);
} else {
levelActiveUserMap.put(j, levelActiveUserMap.get(j) + 1);
}
}
if (levelMap.get(uid) != null) {
if (levelLostUserMap.get(level2) == null) {
levelLostUserMap.put(level2, 1);
} else {
levelLostUserMap.put(level2, levelLostUserMap.get(level2) + 1);
}
}
}
logger.info(
"-------"
+ i
+ "--levelActiveUserMap.size:"
+ levelActiveUserMap.size()
+ "---levelLostUserMap.size:"
+ levelLostUserMap.size());
}
Set<Integer> levelSet = levelLostUserMap.keySet();
Iterator<Integer> levelIte = levelSet.iterator();
Calendar cal = Calendar.getInstance();
cal.setTime(date);
cal.add(Calendar.DAY_OF_YEAR, days);
while (levelIte.hasNext()) {
int level = levelIte.next();
PreparedStatement pstmt =
con.prepareStatement(
"insert into user_level_lost(date1,date2,level,active,lost,type) values(?,?,?,?,?,?)");
pstmt.setDate(1, new java.sql.Date(date.getTime()));
pstmt.setDate(2, new java.sql.Date(cal.getTimeInMillis()));
pstmt.setInt(3, level);
pstmt.setInt(4, levelActiveUserMap.get(level));
pstmt.setInt(5, levelLostUserMap.get(level));
pstmt.setInt(6, 0);
pstmt.execute();
pstmt.close();
}
logger.info(
"================LevelUserLostStatistics======"
+ sdf.format(date)
+ "===="
+ days
+ "======end===");
} catch (Exception e) {
logger.error(e.getMessage(), e);
} finally {
if (con != null) {
try {
con.close();
} catch (Exception ex) {
logger.error("", ex);
}
}
}
}
