This section focuses on larger volume of data. We store data of Orders table in multiple tables by year. The table name format is Orders+year, such as Orders2021 and Orders2022. Each table has same structure as Orders table.

SQL

SELECT ProductID,City,sum(Amount) Amount
FROM Customers
INNER JOIN Orders2021
ON Customers.CustomerID= Orders2021.CustomerID
WHERE Orders2021.Quantity>5
GROUP BY ProductID,City 

SPL

This task involves association between primary table and sub table. Both tables contain data that cannot fit into the memory and cursor is needed to retrieve data. In this case we should sort both tables by join field and associate them through order-based merge in order to get the highest efficiency.

In practice, we can store data according to join field in advance, like we did in the section “Storing Data in Account Order”. All the following examples in this section are assumed to have been ordered by the primary table’s primary key field.

SQL

SELECT Customers.CustomerID,CustomerName,sum(Amount) Amount
FROM Customers
LEFT JOIN Orders2021
ON Customers.CustomerID= Orders2021.CustomerID
GROUP BY Customers.CustomerID,CustomerName

SPL

A4 Here we have a large grouping result set that cannot fit into memory. But as data is ordered by grouping field, group function is used to perform aggregation and return result as a cursor.

SQL

WITH a AS(
    SELECT CustomerID,sum(Amount) Amount2020 
    FROM Orders2020 
    GROUP BY CustomerID),
b AS(
    SELECT CustomerID,sum(Amount) Amount2021 
    FROM Orders2021 
    GROUP BY CustomerID)
SELECT 
    coalesce ( a.CustomerID, b.CustomerID ) CustomerID, 
    Amount2020, Amount2021,Amount2021/Amount2020-1 GrowthRate
FROM a FULL JOIN b ON a.CustomerID= b.CustomerID

SPL

SQL

WITH a AS(
    SELECT CustomerID,sum(Amount) Amount2020 
    FROM Orders2020 
    GROUP BY CustomerID),
b AS(
    SELECT CustomerID,sum(Amount) Amount2021 
    FROM Orders2021 
    GROUP BY CustomerID)
SELECT City,sum(Amount2020) Amount2020,sum(Amount2021) Amount2021, 
    sum(Amount2021)/sum(Amount2020)-1 GrowthRate
FROM Customers
INNER JOIN a ON Customers.CustomerID= a.CustomerID
INNER JOIN b ON Customers.CustomerID= b.CustomerID
GROUP BY City

SPL

SQL

WITH a AS(
    SELECT CustomerID,sum(Amount) Amount2020 
    FROM Orders2020 
    GROUP BY CustomerID),
b AS(
    SELECT CustomerID,sum(Amount) Amount2021 
    FROM Orders2021 
    GROUP BY CustomerID)
SELECT City,sum(Amount2020) Amount2020,sum(Amount2021) Amount2021, 
    sum(Amount2021)/sum(Amount2020)-1 GrowthRate
FROM Customers
LEFT JOIN a ON Customers.CustomerID= a.CustomerID
LEFT JOIN b ON Customers.CustomerID= b.CustomerID
GROUP BY City

SPL

SQL

WITH a AS(
    SELECT CustomerID,sum(Amount) Amount2019
    FROM Orders2019 
    GROUP BY CustomerID),
b AS(
    SELECT CustomerID,sum(Amount) Amount2020 
    FROM Orders2020 
    GROUP BY CustomerID),
c AS(
    SELECT CustomerID,sum(Amount) Amount2021
    FROM Orders2021 
    GROUP BY CustomerID)
SELECT coalesce (a.CustomerID, b.CustomerID, c.CustomerID ) CustomerID,
    Amount2019, Amount2020, Amount2021
FROM a
FULL JOIN b ON b.CustomerID= a.CustomerID
FULL JOIN c ON c.CustomerID= b.CustomerID or c.CustomerID= a.CustomerID
GROUP BY CustomerID

SPL