Tutorial :Math with previous row in SQL, avoiding nested queries?



Question:

I want to do some math on the previous rows in an SQL request in order to avoid doing it in my code.

I have a table representing the sales of two entities (the data represented here is doesn't make much sense and it's just an excerpt) :

YEAR    ID      SALES             PURCHASE         MARGIN  2009    1       10796820,57       2662369,19       8134451,38  2009    2        2472271,53       2066312,34        405959,19  2008    1        9641213,19       1223606,68       8417606,51  2008    2        3436363,86       2730035,19        706328,67  

I want to know how the sales, purchase, margin... have evolved and compare one year to the previous one.

In short I want an SQL result with the evolutions pre-computed like this :

YEAR    ID    SALES         SALES_EVOLUTION    PURCHASE      PURCHASE_EVOLUTION  MARGIN        MARGIN_EVOLUTION  2009    1     10796820,57   11,99              2662369,19    117,58              8134451,38     -3,36  2009    2      2472271,53   -28,06             2066312,34    -24,31               405959,19    -42,53  2008    1      9641213,19                      1223606,68                        8417606,51   2008    2      3436363,86                      2730035,19                         706328,67   

I could do some ugly stuff :

SELECT *, YEAR, ID, SALES , (SALES/(SELECT SALES FROM TABLE WHERE YEAR = OUTER_TABLE.YEAR-1 AND ID = OUTER_TABLE.ID) -1)*100 as SALES_EVOLUTION (...)   FROM TABLE as OUTER_TABLE   ORDER BY YEAR DESC, ID ASC  

But I have arround 20 fields for which I would have to do a nested query, meaning I would have a very huge and ugly query.

Is there a better way to do this, with less SQL ?


Solution:1

Using sql server (but this should work for almost any sql), with the table provided you can use a LEFT JOIN

DECLARE @Table TABLE(          [YEAR] INT,          ID INT,          SALES FLOAT,                     PURCHASE FLOAT,               MARGIN FLOAT  )    INSERT INTO @Table ([YEAR],ID,SALES,PURCHASE,MARGIN) SELECT 2009,1,10796820.57,2662369.19,8134451.38   INSERT INTO @Table ([YEAR],ID,SALES,PURCHASE,MARGIN) SELECT 2009,2,2472271.53,2066312.34,405959.19   INSERT INTO @Table ([YEAR],ID,SALES,PURCHASE,MARGIN) SELECT 2008,1,9641213.19,1223606.68,8417606.51   INSERT INTO @Table ([YEAR],ID,SALES,PURCHASE,MARGIN) SELECT 2008,2,3436363.86,2730035.19,706328.67       SELECT  cur.*,          ((cur.Sales / prev.SALES) - 1) * 100  FROM    @Table cur LEFT JOIN          @Table prev ON cur.ID = prev.ID AND cur.[YEAR] - 1 = prev.[YEAR]  

The LEFT JOIN will allow you to still see values from 2008, where an INNER JOIN would not.


Solution:2

Old skool solution:

SELECT c.YEAR, c.ID, c.SALES, c.PURCHASE, c.MARGIN  ,      p.YEAR, p.ID, p.SALES, p.PURCHASE, p.MARGIN  FROM       tab AS c -- current  INNER JOIN tab AS p -- previous  ON         c.year = p.year - 1  AND        c.id   = p.id  

If you have a db with analytical functions (MS SQL, Oracle) you can use the LEAD or LAG analytical functions, see http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php

I think this would be the correct application:

SELECT     c.YEAR, c.ID, c.SALES, c.PURCHASE, c.MARGIN  ,          LAG(c.YEAR, 1, 0) OVER (ORDER BY ID,YEAR)  ,          LAG(c.ID, 1, 0) OVER (ORDER BY ID,YEAR)  ,          LAG(c.SALES, 1, 0) OVER (ORDER BY ID,YEAR)  ,          LAG(c.PURCHASE, 1, 0) OVER (ORDER BY ID,YEAR)  ,          LAG(c.MARGIN, 1, 0) OVER (ORDER BY ID,YEAR)  FROM       tab AS c -- current  

(not really sure, haven't played with this enough)


Solution:3

You can do it like this:

SELECT t1.*, t1.YEAR, t1.ID, t1.SALES , ((t1.sales/t2.sales) -1) * 100 as SALES_EVOLUTION   (...)   FROM Table t1 JOIN Table t2 ON t1.Year = (t2.Year + 1) AND t1.Id = t2.Id  ORDER BY t1.YEAR DESC, t1.ID ASC  

Now, if you want to compare more years, you'd have to do more joins, so it is a slightly ugly solution.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »