7.26 Outer Apply operation
Perform Outer Apply on a table sequence and a sequence of values, and generate a new table sequence.
Find the label each author uses most based on the post record table.
| ID | TITLE | Author | Label |
|---|---|---|---|
| 1 | Easy analysis of Excel | Ashley | Excel,ETL,Import,Export |
| 2 | Early commute: Easy to pivot excel | Rachel | Excel,Pivot,Python |
| 3 | Initial experience of SPL | Rebecca | |
| 4 | Talking about set and reference | Emily | Set,Reference,Dispersed,SQL |
| 5 | Early commute: Better weapon than Python | Emily | Python,Contrast,Install |
| … | … | … | … |
A.news() function works with @1 option to perform an Outer Apply operation.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from PostRecord”) |
| 3 | =A2.news@1(A2.Label.split@c(); ID,Title,Author,~:Label) |
| 4 | =A3.groups(Author,Label;count(~):Count) |
| 5 | =A4.group(Author).conj(~.maxp@a(Count)) |
A1 Connect to the database.
A2 Query PostRecord table.
A3 A.news() function operates with @1 function to perform Outer Apply operation on the PostRecord table and the sequence of split labels, during which a post record is retained even if it does not have a corresponding label.
A4 Group A3’s records by Author and Label and count labels under each record.
A5 Group A5 by Author again and find the label each author uses most and concatenate them together.
Execution result:
| Author | Label | Count |
|---|---|---|
| Rebecca | (null) | 1 |
| Ashley | Excel | 3 |
| Ashley | SPL | 3 |
| Rachel | Python | 4 |
| … | … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
