The example here uses 2 different approaches one using JOIN and another using LAG analytic function. In the table below I want to find the months that A store profiting more than B store. What I need is to first calculate the sum of monthly sales amount of each store and then subtract them. Note this table is only for demonstration hence it’s not normalized.
How I did it was primitive and the performance was terrible with numerous records. I calculated the sums of both stores and then joined them together so I can calculate the result by subtracting 2 columns:
SELECT t.SaleMonth, netamount FROM (SELECT A.SaleMonth, A.Profit - B.Profit netamount FROM (SELECT Sales.SaleMonth, SUM(Amount) AS Profit FROM Sales WHERE Sales.Store = 'A' GROUP BY Sales.SaleMonth) A LEFT JOIN (SELECT Sales.SaleMonth, SUM(Amount) AS Profit FROM Sales WHERE Sales.Store = 'B' GROUP BY Sales.SaleMonth) B ON A.SaleMonth= B.SaleMonth) t WHERE t.netamount > 0;
A better approach is using the Oracle LAG analytic function. First sum the Amount grouping by SaleMonth & Store and then subtract between rows using LAG. This is roughly 5 times faster than the previous one in other test case:
SELECT s.SaleMonth, netamount FROM (SELECT t.SaleMonth, t.Amount- LAG(Amount, 1) OVER(PARTITION BY Sales.SaleMonth ORDER BY Sales.SaleMonth, Sales.Store) netamount FROM (SELECT Sales.SaleMonth, Sales.Store, SUM(Amount) AS Amount FROM Sales GROUP BY Sales.SaleMonth, Sales.Store) t) s WHERE s.netamount > 0;
The query result:
Reference:LAG analytic function in Oracle SQL