...

7.6 Association query: full join

 
spl-cookbook(224)

Perform a full join on two associated tables and then cross-table computations.
Compare the product sales in each month of the year 2014 based on the associated Sales table and Product table.

imagepng

Use @f option in join() function to perform a full join.

SPL script:

A
1 =connect(“db”)
2 =A1.query(“select ProductID, month(Date) as Month from Sales where year(Date)=2014”)
3 =A1.query@x(“select * from Product”)
4 =A2.switch(ProductID ,A3:ID)
5 =A4.group(Month)
6 =A5.(~.group@1(ProductID).new(ProductID.Name:Product, count(~):Count))
7 =A6.(“A6(”+string(#)+“):”+string(#)+“,Product”).concat(“;”)
8 =join@f(${A7})

A1 Connect to the database.
A2 Select records of 2014 from Sales table.
A3 Query Product table.
A4 Use switch function to convert ProductID field values in Sales table into corresponding records of Product table.
A5 Group and sort the associated records by Month.
A6 Perform distinct count on product IDs while retaining the product name.
A7 Concatenate up the parameter string for join@f function.
A8 Use join@f() function to perform a full join on the 12 months of records.

Execution result:

1 2 3 4 5 6 7 8 9 10 11 12
(null) [Cheese,3] (null) (null) (null) (null) (null) (null) (null) [Cheese,6] (null) (null)
(null) [Coffee,7] (null) [Coffee,6] [Coffee,9] (null) [Coffee,9] (null) (null) (null) (null) [Coffee,8]
[Milk,3] (null) [Milk,5] [Milk,7] (null) [Milk,6] [Milk,8] [Milk,3] (null) [Milk,6] [Milk,4] (null)
… … … … … … … … … … … …

 

spl-cookbook(224)
 
 •  179 View  •