oracle在groupby时间时,时间间断问题处理

来源：互联网发布：密立根油滴学生数据编辑：程序博客网时间：2024/05/17 06:35

直接贴代码:

package com.groupby;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;/** * 主要是在oracle中groupby time时,时间间断,也需要统计出来时.需要在代码中处理 *  *     实际统计的结果                      预期结果 * count       groupByTime          count   groupByTime *    2        2014-12-12             2        2014-12-12 *    5        2014-12-11             5        2014-12-11 *    9        2014-12-10             9        2014-12-10 *    3        2014-12-07             0        2014-12-09 *                                    0        2014-12-08 *                                    3        2014-12-07 *  * @author WeiJun Hu * */public class GroupByUtil {public static void main(String[] args) {try {String format = "yyyy-MM-dd";List<User> list = new ArrayList<User>();SimpleDateFormat sdf = new SimpleDateFormat(format);User user = null;user = new User();user.setCount(2);user.setCreateTime(sdf.parse("2014-12-12"));list.add(user);user = new User();user.setCount(5);user.setCreateTime(sdf.parse("2014-12-11"));list.add(user);user = new User();user.setCount(9);user.setCreateTime(sdf.parse("2014-12-10"));list.add(user);user = new User();user.setCount(3);user.setCreateTime(sdf.parse("2014-12-07"));list.add(user);GroupByUtil util = new GroupByUtil();list = util.getList(util.listToMap(list, format), 6, sdf.parse("2014-12-07"), format);for (User user2 : list) {System.out.println("时间:"+sdf.format(user2.getCreateTime())+"  数量:"+user2.getCount());}/**运行结果如下: *      时间:2014-12-07  数量:3时间:2014-12-08  数量:0时间:2014-12-09  数量:0时间:2014-12-10  数量:9时间:2014-12-11  数量:5时间:2014-12-12  数量:2 */} catch (Exception e) {e.printStackTrace();}}/** * 集合处理方法 * @param map 数据Map对象 * @param maxResult 集合大小 * @param startDate 开始时间 * @param format 时间格式化字符串 * @return */private  List<User> getList(Map<String, Integer> map,Integer maxResult,Date startDate,String format){List<User> list = new ArrayList<User>();Calendar calendar = Calendar.getInstance();calendar.setTime(startDate);SimpleDateFormat sdf = new SimpleDateFormat(format);for(Integer i=0;i<maxResult;i++){String dataStr = sdf.format(calendar.getTime());User user = new User();Integer countNum = map.get(dataStr);if(countNum==null){user.setCount(0);}else{user.setCount(countNum);}user.setCreateTime(calendar.getTime());list.add(user);if(format.equals("yyyy-MM-dd")){calendar.add(Calendar.DAY_OF_MONTH,1);}else if(format.equals("yyyy-MM")){calendar.add(Calendar.MONTH, 1);}}return list;}/** * List集合存储至以时间字符为key值的Map对象 * @param list 集合 * @param format 时间格式化字符串 * @return 以时间字符为key值的Map对象 */private Map<String, Integer> listToMap(List<User> list,String format){Map<String, Integer> map = new HashMap<String, Integer>();SimpleDateFormat sdf = new SimpleDateFormat(format);for (User user : list) {map.put(sdf.format(user.getCreateTime()),user.getCount());}return map;}}

User类:

package com.groupby;import java.util.Date;public class User {private Integer id;private String name;private Integer count;private Date createTime;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Date getCreateTime() {return createTime;}public void setCreateTime(Date createTime) {this.createTime = createTime;}public Integer getCount() {return count;}public void setCount(Integer count) {this.count = count;}}

0 0