Sample Code for implementing several common memory groups using a set Calculator

The Set calculator can easily implement several common memory groups, such as equivalent groups, bitwise groups, and enumeration groups. The following describes the examples.

Equivalent Group

The equivalent grouping is based on the fields (or calculated columns derived from fields) of the current dataset. Each group is a subset of the original dataset.

Example description: Group sales orders by order year.

Data Description: The order data is as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/49/C6/wKioL1QaT3XC3r4OAAE-JTg2ciQ626.jpg "style =" float: none; "Title =" 2014-09-18_111838.jpg "alt =" wKioL1QaT3XC3r4OAAE-JTg2ciQ626.jpg "/>

The preceding dataset (sequence table) can be read from a database or file, for example:

A1 = file ("E:/sales.txt") [email protected] ()

Set calculator code

A2 = a1.group (Year (orderdate ))

Calculation Result

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/49/C4/wKiom1QaT1zRxuiDAAIsaEgEhQU947.jpg "style =" float: none; "Title =" 2014-09-18_111848.jpg "alt =" wkiom1qat1zrxuidaaisaegehqu947.jpg "/>

Code explanation

1. In this example, the Group is based on the order date (orderdate). The year (orderdate) can be used to calculate the order date into a year, and the data of each year after the group is grouped into a group.

2. The Group fields can be multiple. For example, you can group the data of different salespeople in different years into a group by year and salesperson group. The Code is as follows:

A1.group (Year (orderdate), sellerid)

3. The grouped data is often aggregated. For example, the annual sales volume is calculated based on A2. The Code is as follows:

A2.new (Year (orderdate): Y ,~. Sum (amount):)

The calculation result is:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/49/C6/wKioL1QaT3XxxgJFAABcaHWNq00161.jpg "style =" float: none; "Title =" 2014-09-18_111900.jpg "alt =" wkiol1qat3xxxgjfaabcahwnq00161.jpg "/>

You can also use a group function to group and aggregate the two steps:

A1.groups (Year (orderdate): Y; sum (amount):)

Of course, sometimes we have to separate groups and summaries to improve code reuse and computing efficiency. For example, we have to filter one group of A2 and perform associated computing for another group. For example, if we find that the data of a group is abnormal after aggregation, it is worth further research. At this time, we can continue to use the original group for calculation, instead of filtering out the data of this group again.

4. by default, the group function of the cube uses the hash algorithm to group data. However, the adjacent comparison scheme can improve the performance of ordered data, the @ o option can be used in the group function to implement this grouping scheme, for example:

[Email protected] (Year (orderdate), sellerid)

Peer Group

The grouping of equivalent groups is based on the fields of the current dataset, but sometimes the grouping is based on external sources, such as fields of other datasets, user-constructed arrays, and parameter lists, this grouping method is a forward grouping.

Unlike an equi-type grouping, an empty subset may appear in the grouping, that is, no member corresponds to a group entry. In addition, incomplete groups may appear, that is, Members cannot appear in any group. This is not possible for equivalent groups.

Example description:You have calculated a list of the top 10 salespeople with performance scores. Please group the orders in the order of this list.

Datasets before grouping:

The order follows the previous example, where data is stored in A1.

The list of the top 10 salespeople with performance scores is stored in B1, as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/49/C4/wKiom1QaT1yhO61yAABDpt_sRkU372.jpg "style =" float: none; "Title =" 2014-09-18_111_7.jpg "alt =" wkiom1qat1yho61yaabdpt_srku372.jpg "/>

The salesperson list can be generated from an intermediate table or a piece of code. Its generation process is not the focus of the example.

Set calculator code

[Email protected] (B1: empid, sellerid)

Calculation Result

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/49/C6/wKioL1QaT3Wh-CHEAAJNtQJ2pdE039.jpg "style =" float: none; "Title =" 2014-09-18_111914.jpg "alt =" wKioL1QaT3Wh-CHEAAJNtQJ2pdE039.jpg "/>

Code explanation

1. In this example, the grouping basis (salesperson list) comes from outside the grouped dataset. After grouping, the data of each salesperson is grouped into a group, and the groups are arranged according to the grouping basis.

2. The number of sales personnel in an order is more than that in the list. This will prevent some orders from appearing in any group. If you want to divide these orders into multiple groups, you can use the function option @ n, as shown below:

[Email protected] @ n (B1: empid, sellerid)

A group with multiple points will be placed at the end, as shown below:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/49/C6/wKioL1QaT3WRr-7qAAB2WMKq034560.jpg "style =" float: none; "Title =" 2014-09-18_111923.jpg "alt =" wKioL1QaT3WRr-7qAAB2WMKq034560.jpg "/>

3. Sometimes grouping is based on data that is not in the group. For example, grouping is based on the "List of new sales personnel ". In this case, an empty group is displayed, which is normal. For example, if you change the first entry in the list to empid = 100, the calculation result is as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/49/C4/wKiom1QaT1zxd0b7AABnO5M2P-c573.jpg "style =" float: none; "Title =" 2014-09-18_111930.jpg "alt =" wKiom1QaT1zxd0b7AABnO5M2P-c573.jpg "/>

Enumeration Group

The grouping of enumeration groups is more flexible, and can be any Boolean expression. Records that match the expression are classified as the same group.

Similar to a forward grouping, an enumeration group is not a complete group, and a null subset or a member does not belong to any group, an enumeration group may have multiple members in multiple groups.

Example description:The order is divided into four groups: a. The order amount is less than 1000, B. The amount is less than 2000, c. The amount is less than 3000, and D. The amount is less than 10000. Special requirements: data cannot be grouped repeatedly. That is, if an order already belongs to Group A, it cannot be assigned to group B, group C, and Group D.

Datasets before grouping:

The order follows the previous example, where data is stored in A1.

Set calculator code

A2 = ["? <= 1000 ","? <= 2000 ","? <= 3000 ","? <= 10000 "]

A3 = a1.enum (A2, amount)

Calculation Result

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/49/C4/wKiom1QaUEeD8p-CAAI3utN4wt4129.jpg "Title =" 2014-09-18_111938.jpg "alt =" wKiom1QaUEeD8p-CAAI3utN4wt4129.jpg "/>

Code explanation:

1. In this example, the group is based on multiple flexible expressions. Each record matches the expression. If the record matches the expression, it becomes a member of the group. Similarly, groups are arranged according to the group order.

2. By default, the grouping result of Enum does not produce duplicates, that is, after dividing the data in group A, it will judge from the remaining data whether it meets the expression B, the preceding example is the grouping method. If the function option @ r is used, it indicates judging whether expression B is correct from all the data, which will generate repeated grouping results. For example: [email protected] (A2, amount), the calculation result is:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/49/C6/wKioL1QaT3qyxiTmAAIMGz-j5ss317.jpg "style =" float: none; "Title =" 2014-09-18_111946.jpg "alt =" wKioL1QaT3qyxiTmAAIMGz-j5ss317.jpg "/>

3. Similar to the forward grouping, if the enumerated expression is out of the range of data to be grouped, the group is empty. In addition, some data does not conform to any expression. In this case, you can use the @ n function option to classify these records into redundant groups.

Sample Code for implementing several common memory groups using a set Calculator