6.8 Alignment grouping by ordinal number: keep all matching members for each group
Sort data by the order of specified ordinal numbers and keep all matching members for each group. This is suitable for computing scenarios where we want to know information of all members in each group or where we need to use these member records to perform further statistical analysis.
List total sales amount in each month the year 2014 based on the sales table. Below is part of the table:
| ID | CustomerID | OrderDate | Amount |
|---|---|---|---|
| 10248 | VINET | 2013/07/04 | 2440 |
| 10249 | TOMSP | 2013/07/05 | 1863.4 |
| 10250 | HANAR | 2013/07/08 | 1813.0 |
| 10251 | VICTE | 2013/07/08 | 670.8 |
| 10252 | SUPRD | 2013/07/09 | 3730.0 |
| … | … | … | … |
SPL provides @a option to use in A.align(n,y) function to keep all matching members for each group during an alignment grouping operation.
SPL script:
| A | |
|---|---|
| 1 | =T(“Sales.csv”) |
| 2 | =A1.select(year(ORDERDATE)==2014) |
| 3 | =A2.align@a(12,month(ORDERDATE)) |
| 4 | =A3.new(#:Month,~.sum(AMOUNT):AMOUNT) |
A1 Query Sales table.
A2 Select records of the year 2014 from the Sales table.
A3 Use A.align@a(n,y) function to divide records of Sales table into 12 groups according to the order of months from January to December; @a option enables keeping all matching members for each group.
A4 Calculate total sales amount in each month.
Execution result:
| Month | Amount |
|---|---|
| 1 | 66692.8 |
| 2 | 52207.2 |
| 3 | 39979.9 |
| 4 | 60699.39 |
| … | … |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
