RaqForum 41 No.
8 View  • 
     ...

How to copy the field values within a subgroup in order to other subgroups with esProc

 

The first two fields of a certain database table are grouping fields, and the last two fields are details within the group. After sorting by the first three fields, the data follows the following pattern: the number of records in each subgroup of the same large group is the same; In each large group, the fourth field of only the last subgroup has values, while all other subgroups are null.

Group1

Group2

LineID

TargetField

1

1

5

1

1

6

1

2

3

1

2

4

1

3

1

1

1

3

2

2

2

4

11

2

4

12

2

5

9

2

5

10

2

6

7

3

2

6

8

4

Now, within each large group, update or copy the fourth column of the last subgroup to other subgroups in sequential order.

Group1

Group2

LineID

TargetField

1

1

5

1

1

1

6

2

1

2

3

1

1

2

4

2

1

3

1

1

1

3

2

2

2

4

11

3

2

4

12

4

2

5

9

3

2

5

10

4

2

6

7

3

2

6

8

4

SQL grouping must aggregate immediately after grouping, lacking natural sequence numbers, and can only indirectly implement ordered calculation after grouping, with complex code. SPL can retain grouped subsets and provides natural sequence numbers, facilitating ordered calculations after grouping：https://try.esproc.com/splx?4Bs

　A

1

$select * from lines.txt order by Group1, Group2, LineID

2

=A1.group(Group1).(~.group(Group2))

3

=A2.(last=~.m(-1).(TargetField),~.(~.(TargetField=last(#))))

4

return A1

A1: Load data and sort it by the first three fields.

A2: Group by the first field, and then group by the second field within each group, but do not aggregate. Each large group and subgroup are a set. The function group is used for grouping, where ~ represents the current group. After clicking to expand the first large group, it’s shown in the figure:

Picture1png

A3=A2.(last=~.m(-1).(TargetField), …) Loop through each large group in A2, first take the sequence of the TargetField of the last subgroup in the current large group and name it last. The function m represents taking members by sequence number, and -1 represents the last member. The last of the first large group is shown in the figure:

Picture2png

A3=A2.(…, ~.(~.(TargetField=last(#)))) Then loop through each record of each subgroup in the current large group, and change the fourth field to the value with the same position in last. ”~.(TargetField)” means retrieving the sequence of the field by name; ”last(#)” means to retrieve members by sequence number and is a shorthand for last.m(#)”; The symbol # represents the current sequence number. After execution, A1 is modified to the following figure:

Picture3png
The above code has modified each subgroup of each large group. In fact, the last subgroup can be left unchanged, or the first to second to last subgroup should be selected. Code: ~.m(1:-2), abbreviated as ~.m(:-2)

A4: Finally return A1.

 
 •  8 View  •