OLAP function: CSUM / SUM with Reset value

Database

OLAP function: CSUM / SUM with Reset value

Hi All,

Can I have the teradata query for the below requirement:

Input:

day dt SRC_CREATE_DT CURR_VAL
3/1/2016 ? 0
3/2/2016 ? 0
3/3/2016 ? 0
3/4/2016 ? 0
3/5/2016 ? 0
3/6/2016 ? 0
3/7/2016 ? 0
3/8/2016 ? 0
3/9/2016 ? 0
3/10/2016 ? 0
3/11/2016 ? 0
3/12/2016 ? 0
3/13/2016 03/13/2016 22:48:23 366
3/14/2016 ? 0
3/15/2016 ? 0
3/16/2016 ? 0
3/17/2016 ? 0
3/18/2016 ? 0
3/19/2016 ? 0
3/20/2016 03/20/2016 22:47:33 700
3/21/2016 ? 0
3/22/2016 ? 0
3/23/2016 ? 0
3/24/2016 ? 0
3/25/2016 03/25/2016 22:47:33 920
3/26/2016 ? 0
3/27/2016 ? 0
3/28/2016 ? 0
3/29/2016 ? 0
3/30/2016 03/30/2016 21:01:00 1,021.00
3/31/2016 ? 0

Output/Expected Result:

day dt SRC_CREATE_DT CURR_VAL
3/1/2016 ? 0
3/2/2016 ? 0
3/3/2016 ? 0
3/4/2016 ? 0
3/5/2016 ? 0
3/6/2016 ? 0
3/7/2016 ? 0
3/8/2016 ? 0
3/9/2016 ? 0
3/10/2016 ? 0
3/11/2016 ? 0
3/12/2016 ? 0
3/13/2016 03/13/2016 22:48:23 366
3/14/2016 ? 366
3/15/2016 ? 366
3/16/2016 ? 366
3/17/2016 ? 366
3/18/2016 ? 366
3/19/2016 ? 366
3/20/2016 03/20/2016 22:47:33 700
3/21/2016 ? 700
3/22/2016 ? 700
3/23/2016 ? 700
3/24/2016 ? 700
3/25/2016 03/25/2016 22:47:33 920
3/26/2016 ? 920
3/27/2016 ? 920
3/28/2016 ? 920
3/29/2016 ? 920
3/30/2016 03/30/2016 21:01:00 1,021.00
3/31/2016 ? 1,021.00

Thanks

4 REPLIES
Senior Apprentice

Re: OLAP function: CSUM / SUM with Reset value

If CURR_VALUE is actually always increasing you only need a MAX, which results a single STAT-step.

MAX(NULLIF(CURR_VAL, 0))
OVER (--partition by ...
ORDER BY day_dt
ROWS UNBOUNDED PRECEDING)

Otherwise it depends on your Teradata release.

Since TD14.10 there's FIRST/LAST_VALUE, single STAT-step, too:

COALESCE(LAST_VALUE(NULLIF(CURR_VAL, 0) IGNORE NULLS)
OVER (--partition by ...
ORDER BY day_dt
ROWS UNBOUNDED PRECEDING)
,0)

Before you can use RESET WHEN, but this will require two STAT-steps:

MAX(CURR_VAL)
OVER (--partition by ...
ORDER BY day_dt RESET WHEN CURR_VAL <> 0
ROWS UNBOUNDED PRECEDING)

Re: OLAP function: CSUM / SUM with Reset value

You are awesome Dieter,  3rd one is working for me.

Re: OLAP function: CSUM / SUM with Reset value

Hi Dieter,

I am able to gererate rows for one ID but not more than one ID. Is it possible to generate the rows as below:

I am using points table and date lookup table in the below query:

SELECT 
DAY_DT,
MAX(ID)
OVER (--partition by ...
ORDER BY day_dt RESET WHEN POINTS <> 0
ROWS UNBOUNDED PRECEDING) ID,
MAX(POINTS)
OVER (--partition by ...
ORDER BY day_dt RESET WHEN POINTS <> 0
ROWS UNBOUNDED PRECEDING) POINTS
FROM TIME_DAY_LKP

LEFT OUTER JOIN

(SELECT ID,SOURCE_DT, POINTS FROM POINT_TBLE
) DT
ON CAST(SOURCE_DT AS DATE)=DAY_DT
WHERE MTH_IDNT=201602
Source table
ID source_dt POINTS
12,953 3/24/2016 366
12,953 3/29/2016 541
12,953 3/30/2016 1,021.00
12,953 3/31/2016 1,620.00
13,046 3/2/2016 700
13,046 3/17/2016 720
13,046 3/22/2016 760
13,046 3/24/2016 770

Result I am getting
DAY_DT ID POINTS
2/28/2016 ? ?
2/29/2016 ? ?
3/1/2016 ? ?
3/2/2016 13,046 700
3/3/2016 13,046 700
3/4/2016 13,046 700
3/5/2016 13,046 700
3/6/2016 13,046 700
3/7/2016 13,046 700
3/8/2016 13,046 700
3/9/2016 13,046 700
3/10/2016 13,046 700
3/11/2016 13,046 700
3/12/2016 13,046 700
3/13/2016 13,046 700
3/14/2016 13,046 700
3/15/2016 13,046 700
3/16/2016 13,046 700
3/17/2016 13,046 720
3/18/2016 13,046 720
3/19/2016 13,046 720
3/20/2016 13,046 720
3/21/2016 13,046 720
3/22/2016 13,046 760
3/23/2016 13,046 760
3/24/2016 13,046 770
3/24/2016 12,953 366
3/25/2016 12,953 366
3/26/2016 12,953 366
3/27/2016 12,953 366
3/28/2016 12,953 366
3/29/2016 12,953 541
3/30/2016 12,953 1,021.00
3/31/2016 12,953 1,620.00
4/1/2016 12,953 1,620.00
4/2/2016 12,953 1,620.00

Expected results
DAY_DT ID POINTS
2/28/2016 ? ?
2/29/2016 ? ?
3/1/2016 ? ?
3/2/2016 13,046 700
3/3/2016 13,046 700
3/4/2016 13,046 700
3/5/2016 13,046 700
3/6/2016 13,046 700
3/7/2016 13,046 700
3/8/2016 13,046 700
3/9/2016 13,046 700
3/10/2016 13,046 700
3/11/2016 13,046 700
3/12/2016 13,046 700
3/13/2016 13,046 700
3/14/2016 13,046 700
3/15/2016 13,046 700
3/16/2016 13,046 700
3/17/2016 13,046 720
3/18/2016 13,046 720
3/19/2016 13,046 720
3/20/2016 13,046 720
3/21/2016 13,046 720
3/22/2016 13,046 760
3/23/2016 13,046 760
3/24/2016 13,046 770
3/24/2016 13,046 770
3/25/2016 13,046 770
3/26/2016 13,046 770
3/27/2016 13,046 770
3/28/2016 13,046 770
3/29/2016 13,046 770
3/30/2016 13,046 770
3/31/2016 13,046 770
4/1/2016 13,046 770
4/2/2016 13,046 770
2/28/2016 ? ?
2/29/2016 ? ?
3/1/2016 ? ?
3/2/2016 ? ?
3/3/2016 ? ?
3/4/2016 ? ?
3/5/2016 ? ?
3/6/2016 ? ?
3/7/2016 ? ?
3/8/2016 ? ?
3/9/2016 ? ?
3/10/2016 ? ?
3/11/2016 ? ?
3/12/2016 ? ?
3/13/2016 ? ?
3/14/2016 ? ?
3/15/2016 ? ?
3/16/2016 ? ?
3/17/2016 ? ?
3/18/2016 ? ?
3/19/2016 ? ?
3/20/2016 ? ?
3/21/2016 ? ?
3/22/2016 ? ?
3/23/2016 ? ?
3/24/2016 ? ?
3/25/2016 12,953 366
3/26/2016 12,953 366
3/27/2016 12,953 366
3/28/2016 12,953 366
3/29/2016 12,953 541
3/30/2016 12,953 1,021.00
3/31/2016 12,953 1,620.00
4/1/2016 12,953 1,620.00
4/2/2016 12,953 1,620.00
Senior Apprentice

Re: OLAP function: CSUM / SUM with Reset value

You can't use this approach when you need multiple IDs, the only workaround is

TIME_DAY_LKP CROSS JOIN (SELECT DISTINCT ID FROM POINT_TBLE)

to create all possible combinations of date/id and then LEFT JOIN POINT_TBLE using both date&id, which is not very efficient.

A better approach utilizes EXPAND ON.

This is an example:

create table inventory(product varchar(10), inv_date date, quantity int);

insert into inventory('bottle', date '2011-04-01', 10);
insert into inventory('bottle', date '2011-04-06', 50);
insert into inventory('bottle', date '2011-04-07', 40);
insert into inventory('bottle', date '2011-04-13', 30);

insert into inventory('bottle2', date '2011-04-03', 15);
insert into inventory('bottle2', date '2011-04-06', 55);
insert into inventory('bottle2', date '2011-04-12', 35);

-- only for a single product:
SELECT
calendar_date,
MIN(quantity) OVER
(ORDER BY calendar_date
RESET WHEN quantity IS NOT NULL
ROWS UNBOUNDED PRECEDING) AS new_quantity
FROM sys_calendar.calendar AS c LEFT JOIN inventory AS i
ON c.calendar_date = i.inv_date
AND product = 'bottle'
WHERE calendar_date BETWEEN DATE '2011-03-31' AND DATE '2011-04-13'
ORDER BY calendar_date;

-- TD13.10+: easier, more efficient, multiple products possible
SELECT product, BEGIN(expd), quantity
FROM
(
SELECT product, quantity, inv_date,
MIN(inv_date)
OVER (PARTITION BY product ORDER BY inv_date
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_date
FROM inventory
WHERE inv_date < DATE '2011-04-14'
-- AND product = 'bottle'
) AS i
EXPAND ON PERIOD(inv_date,COALESCE(next_date,DATE '2011-04-14')) AS expd BY INTERVAL '1' DAY
FOR PERIOD(DATE '2011-03-31', DATE '2011-04-14')
ORDER BY 1,2;