SQL sum over partition for preceding period

Question

I have the following table, which represent Customers for each day:

+----------+-----------+
|   Date   | Customers |
+----------+-----------+
| 1/1/2014 |         4 |
| 1/2/2014 |         7 |
| 1/3/2014 |         5 |
| 1/4/2014 |         5 |
| 1/5/2014 |        10 |
| 2/1/2014 |         7 |
| 2/2/2014 |         4 |
| 2/3/2014 |         1 |
| 2/4/2014 |         5 |
+----------+-----------+

I would like to add 2 additional columns:

Summary of the customers for the current month
Summary of the customers for the preceding month

here's the desired outcome:

+----------+-----------+----------------------+------------------------+
|   Date   | Customers | Sum_of_Current_month | Sum_of_Preceding_month |
+----------+-----------+----------------------+------------------------+
| 1/1/2014 |         4 |                   31 |                      0 |
| 1/2/2014 |         7 |                   31 |                      0 |
| 1/3/2014 |         5 |                   31 |                      0 |
| 1/4/2014 |         5 |                   31 |                      0 |
| 1/5/2014 |        10 |                   31 |                      0 |
| 2/1/2014 |         7 |                   17 |                     31 |
| 2/2/2014 |         4 |                   17 |                     31 |
| 2/3/2014 |         1 |                   17 |                     31 |
| 2/4/2014 |         5 |                   17 |                     31 |
+----------+-----------+----------------------+------------------------+

I have managed to calculate the 3rd column by a simple sum over partition function:

Select 
  Date,
  Customers, 
  Sum(Customers) over (Partition by (Month(Date)||year(Date) Order by 1) as Sum_of_Current_month
From table

However, I can't find a way to calculate the Sum_of_preceding_month column.

Appreciate your support.

Asaf

Check out this post. Seems like its the same topic. http://stackoverflow.com/questions/8124756/teradata-equivalent-for-lead-and-lag-function-of-oracle — Amir Pelled, Oct 01 '15 at 10:55

score 3 · Answer 1 · answered Oct 01 '15 at 14:36

The previous month is a bit tricky. What's your Teradata release, TD14.10 supports LAST_VALUE:

SELECT 
   dt,
   customers,
   Sum_of_Current_month,
   -- return the previous sum
   COALESCE(LAST_VALUE(x ignore NULLS) 
            OVER (ORDER BY dt 
                  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
           ,0) AS Sum_of_Preceding_month
FROM 
 (
   SELECT 
     dt,
     Customers, 
     SUM(Customers) OVER (PARTITION BY TRUNC(dt,'mon')) AS Sum_of_Current_month,
     CASE -- keep the number only for the last day in month
       WHEN ROW_NUMBER()
            OVER (PARTITION BY TRUNC(dt,'mon')
                  ORDER BY dt)
          = COUNT(*) 
            OVER (PARTITION BY TRUNC(dt,'mon'))
       THEN Sum_of_Current_month
     END AS x
   FROM tab
 ) AS dt

Gordon Linoff · Accepted Answer · 2015-10-01T18:28:14.613

I think this might be easier by using lag() and an aggregation sub-query. The ANSI Standard syntax is:

Select t.*, tt.sumCustomers, tt.prev_sumCustomers
From table t join
     (select extract(year from date) as yyyy, extract(month from date) as mm,
             sum(Customers) as sumCustomers,
             lag(sum(Customers)) over (order by extract(year from date), extract(month from date)
                                      ) as prev_sumCustomers
      from table t
      group by extract(year from date), extract(month from date)
     ) tt
     on extract(year from date) = tt.yyyy and extract(month from date) = t.mm;

In Teradata, this would be written as:

Select t.*, tt.sumCustomers, tt.prev_sumCustomers
From table t join
     (select extract(year from date) as yyyy, extract(month from date) as mm,
             sum(Customers) as sumCustomers,
             min(sum(Customers)) over (order by extract(year from date), extract(month from date)
                                       rows between 1 preceding and 1 preceding
                                      ) as prev_sumCustomers
      from table t
      group by extract(year from date), extract(month from date)
     ) tt
     on extract(year from date) = tt.yyyy and extract(month from date) = t.mm;

I don't believe Teradata has implemented support for LAG yet. I believe it relies on ROWS PRECEDING and ROWS FOLLOWING clauses in the OVER() windowing function to accomplish LEAD and LAG. — Rob Paller, Oct 01 '15 at 15:30
Thank you Gordon and Rob. I beleive Rob is right. Any idea how can this be acheived with "rows preceding" syntax? — Asaf Lahav, Oct 01 '15 at 16:32
TD14.10 implements `FIRST_VALUE` & LAST_VALUE`, which are more generic versions on `LEAD`& `LAG` — dnoeth, Oct 01 '15 at 21:33

score 0 · Answer 3 · answered Oct 01 '15 at 10:57

0

Try this:

 SELECT
     [Date],
     [Customers],
     (SELECT SUM(customers) FROM table WHERE MONTH(dte) = MONTH(tbl.dte)),
     ISNULL((SELECT SUM(customers) FROM table WHERE MONTH(dte) = MONTH(DATEADD(MONTH, -1, tbl.dte))), 0)
 FROM table tbl

answered Oct 01 '15 at 10:57

Hisham Maudarbocus

610
8
18

1

I don't this this is Teradata syntax. – Gordon Linoff Oct 01 '15 at 11:32

SQL sum over partition for preceding period

3 Answers3