Teradata新特性之reset when使用

一同事問如何用SQL獲取各區域使用者數連續增長的天數。想到Teradata新特性中的reset when正好可以實現此功能。下面是我的測試案例，分享給大家：[@more@]

Step 1: CREATE the FOLLOWING TABLE:

CREATE TABLE Test.Reset_when_Monthly_Sales
(
SalesMonth INTEGER
, Sales INTEGER
)
UNIQUE PRIMARY INDEX (SalesMonth)
;
-- Completed. 0 rows processed.

Step 2: Populate the TABLE AS follows:

INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (1, 4500)
;
INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (2, 4550)
;
INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (3, 4250)
;
INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (4, 4225)
;
INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (5, NULL)
;
INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (6, 5124)
;
INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (7, NULL)
;
INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (8, 5500)
;
INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (9, 4750)
;
INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (10, 4850)
;
INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (11, 5000)
;
INSERT INTO Test.Reset_when_Monthly_Sales
VALUES (12, 5200)
;
Step 3: The FOLLOWING QUERY shows sales BY MONTH. We would LIKE the third COLUMN
(LastKnownSales) TO carry the LAST non-NULL MONTH IF the CURRENT MONTH has a NULL
FOR sales amount. USE RESET WHEN TO accomplish this. You will RESET WHEN
the sales amount IS NOT NULL. You can USE a cumulative SUM (ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW) FOR this example.

SELECT SalesMonth
, Sales
, SUM(Sales)
OVER
(
ORDER BY SalesMonth ASC
RESET WHEN
Sales IS NOT NULL
ROWS BETWEEN
UNBOUNDED PRECEDING
AND CURRENT ROW
) AS LastKnownSales
FROM Test.Reset_when_Monthly_Sales
;
/*
Result:
-------
SalesMonth Sales LastKnownSales
---------- ----- --------------
1 4500 4500
2 4550 4550
3 4250 4250
4 4225 4225
5 ? 4225
6 5124 5124
7 ? 5124
8 5500 5500
9 4750 4750
10 4850 4850
11 5000 5000
12 5200 5200
Completed. 12 rows processed
*/

Step 4: The reason FOR the USE OF ROWS UNBOUNDED IS because OF the possibility OF
multiple consecutive months WITH NULL Sales. IN this CASE the LAST non-NULL
Sales would be FOUND AT the LAST RESET point, which would be ANY NUMBER OF ROWS
previously. UNBOUNDED IS required because there IS NO LIMIT ON how many
consecutive NULLs might occur.

Issue the FOLLOWING STATEMENT TO SET consecutive NULLs:

UPDATE Test.Reset_when_Monthly_Sales
SET Sales = NULL
WHERE SalesMonth = 6
;
-- Completed. 1 rows processed.

THEN re-submit the QUERY FROM Step 3.

SELECT SalesMonth
, Sales
, SUM(Sales)
OVER
(
ORDER BY SalesMonth ASC
RESET WHEN
Sales IS NOT NULL
ROWS BETWEEN
UNBOUNDED PRECEDING
AND CURRENT ROW
) AS LastKnownSales
FROM Test.Reset_when_Monthly_Sales
;

/*
Result:
-------

SalesMonth Sales LastKnownSales
---------- ----- --------------
1 4500 4500
2 4550 4550
3 4250 4250
4 4225 4225
5 ? 4225
6 ? 4225
7 ? 4225
8 5500 5500
9 4750 4750
10 4850 4850
11 5000 5000
12 5200 5200

Completed. 12 rows processed
*/

Step 5: Now, REPLACE the ROWS BETWEEN UNBOUNDED PRECEDING syntax WITH
ROWS BETWEEN 1 PRECEDING AND note the changes IN the ResultSet.

SELECT SalesMonth
, Sales
, SUM(Sales)
OVER
(
ORDER BY SalesMonth ASC
RESET WHEN
Sales IS NOT NULL
ROWS BETWEEN
1 PRECEDING
AND CURRENT ROW
) AS LastKnownSales
FROM Test.Reset_when_Monthly_Sales
;
/*
Result:
-------


SalesMonth Sales LastKnownSales
---------- ----- --------------
1 4500 4500
2 4550 4550
3 4250 4250
4 4225 4225
5 ? 4225
6 ? ?
7 ? ?
8 5500 5500
9 4750 4750
10 4850 4850
11 5000 5000
12 5200 5200

Completed. 12 rows processed
*/

Step 6: The FOLLOWING QUERY will SHOW the months WHERE there are continuous sales
increases. They are identified BY ANY VALUE IN the GrowthIndex which IS NOT equal TO
1. FOR example, the sequence 1, 2, 3, 4 indicates four continuous months OF growth.
A subsequent 1 indicates a non-growth MONTH AND thus a RESET occurs.

FIRST, RESTORE the ROW that was previously changed

UPDATE Test.Reset_when_Monthly_Sales
SET Sales = 5124
WHERE SalesMonth = 6
;
-- Completed. 1 rows processed.

MODIFY your previous QUERY TO DO the FOLLOWING:
Since your windows AGGREGATE FUNCTION ONLY looks AT one PRECEDING ROW, ADD the
FOLLOWING: ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING

Your QUERY will need TO DO a RESET WHEN ANY OF the FOLLOWING are true:
? CURRENT ROW Sales amount IS NULL
? Previous ROW Sales amount IS NULL
? CURRENT ROW Sales amount IS Less Than Previous ROW Sales amount
You can USE the ROW_NUMBER FUNCTION (ordered BY SalesMonth) AS the producer
OF the GrowthIndex COLUMN.

SELECT SalesMonth
, Sales
, SUM(Sales)
OVER
(
ORDER BY SalesMonth ASC
ROWS BETWEEN
1 PRECEDING
AND 1 PRECEDING
) AS PrevSales
, ROW_NUMBER()
OVER
(
ORDER BY SalesMonth ASC
RESET WHEN
Sales IS NULL
OR PrevSales IS NULL
OR Sales <= PrevSales
) AS GrowthIndex
FROM
Test.Reset_when_Monthly_Sales
;
/*
Result:
-------

WhichMonth Sales PrevSales GrowthIndex
---------- ----- --------- -----------
1 4500 ? 1
2 4550 4500 2
3 4250 4550 1
4 4225 4250 1
5 ? 4225 1
6 5124 ? 1
7 ? 5124 1
8 5500 ? 1
9 4750 5500 1
10 4850 4750 2
11 5000 4850 3
12 5200 5000 4

Completed. 12 rows processed
*/