Group Collapse Modeling in Java

Question

I have an ArrayList<Map<String, Object>> that has data such as a database table. I want to calculate the total total number of participants in Java. The same functionality can be achieved in SQL GroupBY Rollup / Cube. I wrote a function to compute GroupBy Cube.

 public static List<Map<String, Object>> applyGroupBy(List<LevelDetail> dimensionColumns, List<MeasureDetail> numericColumns, List<Map<String, Object>> resultset) { List<Map<String, Object>> resultsetNew = new ArrayList<Map<String, Object>>(); Map<String, List<Double>> grandTotalMap = new HashMap<String, List<Double>>(); for (LevelDetail levelDetail : dimensionColumns) { Stream<Map<String, Object>> stream = resultset.stream(); stream.collect(Collectors.groupingBy(m -> m.get(levelDetail.getName()), LinkedHashMap::new, Collectors.mapping(m -> m, Collectors.toList()))).forEach((g, r) -> { Map<String, Object> row = new HashMap<String, Object>(); row.put(levelDetail.getName(), g); for (MeasureDetail measureDetail : numericColumns) { DoubleStream valueStream = r.stream().mapToDouble(p -> { p.put(levelDetail.getName() + "_nb_grouping", 0); resultsetNew.add(p); return Double.parseDouble(p.get(measureDetail.getName()) != null ? p.get(measureDetail.getName()).toString() : "0"); }); Double value = getAggregatedResult(valueStream, measureDetail.getMeasure_rollup_expr()); row.put(measureDetail.getName(), value); if (dimensionColumns.indexOf(levelDetail) == 0) { if (!grandTotalMap.containsKey(measureDetail.getName())) { grandTotalMap.put(measureDetail.getName(), new ArrayList<Double>()); } grandTotalMap.get(measureDetail.getName()).add(value); } } row.put(levelDetail.getName() + "_nb_grouping", 0); for (LevelDetail ld : dimensionColumns) { if (!ld.getName().equals(levelDetail.getName())) { row.put(ld.getName() + "_nb_grouping", 1); row.put(ld.getName(), null); } } resultsetNew.add(row); }); } Map<String, Object> row = new HashMap<String, Object>(); for (LevelDetail levelDetail : dimensionColumns) { row.put(levelDetail.getName(), null); row.put(levelDetail.getName() + "_nb_grouping", 1); } for (MeasureDetail measureDetail : numericColumns) { Double value = getAggregatedResult(grandTotalMap.get(measureDetail.getName()).stream().mapToDouble((i) -> (Double) i), measureDetail.getMeasure_rollup_expr()); row.put(measureDetail.getName(), value); } resultsetNew.add(row); resultset = new ArrayList<Map<String, Object>>(new LinkedHashSet<Map<String, Object>>(resultsetNew)); resultsetNew.clear(); resultsetNew.addAll(resultset); return resultsetNew; } private static final String AVG = "avg"; private static final String MIN = "min"; private static final String MAX = "max"; private static Double getAggregatedResult(DoubleStream ds, String agg) { switch (agg.toLowerCase()) { case AVG: return ds.average().getAsDouble(); case MIN: ds.min().getAsDouble(); case MAX: ds.max().getAsDouble(); default: return ds.sum(); } } 

I want to write a method that will be grouped using Rollup. It can be parameterized for the same function, regardless of whether Rollup or Cube should be grouped.

Answer 1

When working with free software SGBDs, which are not as strong as commercial ones, sometimes you have to implement some functions inside Java code. The summary seems to be a subset of the cube request. Therefore, to get the summary, you will need to remove some lines from your query for the cube and some partial totals. In a particular case, this can be quite easy, but to generalize, it can take some time to determine exactly which lines should be deleted, and which algorithm can execute it in each case.