Grouping Rows

1 Before and after Grouping

1. Before grouping, rows can be filtered. That is, objects of grouping are filtered rows.

2. In order to implement sorting and statistic with database system, data are converted as temporary database table automatically before grouping.

3. Sorting condition and maximum data number of each group can be set for grouped data.

2 Usages of grouping

With grouped data, following can be handled:

1. Add grouping information in source data, which are 2 new columns: Group Number and Group Parameter.

2. Split data into multiple files of groups.

3. Calculate descriptive statistic of groups.

4. Display following types of charts in sequence of groups dynamically: XY chart, pie chart, comparison bars chart, self comparison bars chart, box-and-whisker chart.

3 Ways of Data Grouping

MyBox supports six ways to group data:

Following are their descriptions.

4 Group by Equal Values

4.1 Way of Grouping

Select several columns and put rows into same group when they have same values of these columns.

4.2 Implementation of Grouping

1. Convert data as a temporary database table

2. Read each row and group them by equal values of selected columns. The results like executing SQL of “group by”.

4.3 Results of Grouping

• Complete: Each row belongs to a group.

• Non-intersect: Each row only belongs to one group.

5 Group by Value Ranges

5.1 Way of Grouping

Select one column and divide data by value ranges of this column.

Following 3 selections can be for the split:

• Interval of values

• Number of split

• List of “StartValue-EndValue”

5.2 Split Date/Era

When split rows of date/era:

1. Values of the column are converted as milliseconds.

2. Time unit can be set.

3. For items of “Start-End”, ranges should be in valid formats of date/era.

5.3 Edit List of “Start-End”

1. Add/Delete/Update start-end items in table.

2. Set whether includes “start”/“end” for “start-end” items.

5.4 Implementation of Grouping

• Convert data as a temporary database table.

• Ignore type of selected column, and parse its values as double.

• Calcuate maximu value and minimum value of this column.

• Count ranges of each group.

• Compose query statements for each group. The conditions are like “column-name >= start-value-of-range AND column-name < end-value-of-range”.

5.5 Results of Grouping

• If split as size or number, then:

  • Complete: Each row belongs to a group.

  • Non-intersect: Each row only belongs to one group.

• If split as start-end list, then:

  • Possible incomplete: Some rows may not belong to any group.

  • Possible intersect: Some rows may belongs to several groups.

6 Group by Time

6.1 Way of Grouping

Only when data include date/time columns, this way can be taken:

1. Select a column of date/time type.

2. Select time unit: century/year/month/day/hour/minute/second.

Examples:

• When time unit is “century”, data are calculated to get century values and grouped by them.

• When time unit is “month”, data are divided as same groups when they have same values of year and month.

• When time unit is “hour”, data are divided as same groups when they have same values of year, month, day, and hour.

• When time unit is “second”, data are divided as same groups when they have same values of year, month, day, hour, minute, and second.

6.2 Implementation of Grouping

1. Convert data as a temporary database table.

2. As time unit, save values are in same groups.

6.3 Results of Grouping

• Complete: Each row belongs to a group.

• Non-intersect: Each row only belongs to one group.

7 Group by Expression

7.1 Way of Grouping

Define a row expresion, and rows are in same groups when they have same values of this expression.

Examples:

• Expression “new Date(‘#{StartTime}’).getDay()”, group as the week values of column “StartTime”.

• Expression “#{description}’.charAt(2)”, group as the 2th character of column “description”.

• Expression “‘#{StartTime}’.substring(0, 7)”, group as the first 7 characters of column “StartTime”.

7.2 Implementation of Grouping

1. Convert data as a temporary database table.

2. Calculate the expression against each row, save values are in same groups.

7.3 Results of Grouping

• Complete: Each row belongs to a group.

• Non-intersect: Each row only belongs to one group.

8 Group by Conditions

8.1 Way of Grouping

Define some row filters and data rows are divided in groups by these row filters.

8.2 Implementation of Grouping

1. Convert data as a temporary database table.

2. For each row filter, scan data rows to pick out group members which satisfy condition.

8.3 Results of Grouping

• Possible incomplete: Some rows may not belong to any group.

• Possible intersect: Some rows may belongs to several groups.

9 Group by Row Number

9.1 Way of Grouping

Divide data rows by their row numbers. Following 3 selections can be for the split:

• Size of split(interval of row numbers)

• Number of split

• List of “StartNumber-EndNumber”

9.2 Implementation of Grouping

1. Convert data as a temporary database table.

2. Count range of row numbers for each group.

3. Scan data rows, and set groups as row numbers.

9.3 Results of Grouping

• If split as size or number, then:

  • Complete: Each row belongs to a group.

  • Non-intersect: Each row only belongs to one group.

• If split as start-end list, then:

  • Possible incomplete: Some rows may not belong to any group.

  • Possible intersect: Some rows may belongs to several groups.