RSS

Select values calculated between rows

02 Jul

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.

Sales:

ID Store SaleMonth Amount
1 A Jan 120.00
2 A Feb 80.50
3 A Feb 200.00
4 B Jan 160.50
5 B Feb 70.00
6 A Feb 130.00
7 B Mar 180.00
8 A Mar 110.50

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:

SaleMonth netamount
Feb 340.50

Reference:LAG analytic function in Oracle SQL

Advertisements
 
Leave a comment

Posted by on July 2, 2012 in Database, Oracle, SQL

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: