Last updated
Last updated
Reset When determines the partition on which an SQL window function operates based on some specific condition. If the condition evaluates to True, a new dynamic sub partition is created within the existing window partition. For more information about Reset When, see the .
For each account, suppose you want to analyze the sequence of consecutive monthly balance increases. When the balance of one month is less than or equal to the balance of the previous month, the requirement is to reset the counter to zero and restart.
To analyze this data, Teradata SQL uses a window function with a nested aggregate and a Reset When statement, as follows:
Snowflake does not support the Reset When clause in window functions. To reproduce the same result, the Teradata SQL code must be translated using native SQL syntax and nested subqueries, as follows:
Two nested sub-queries are needed to support the Reset When functionality in Snowflake.
In the inner sub-query, a dynamic partition indicator (dynamic_part) is created and populated. dynamic_part is set to 1 if one month's balance is less than or equal to the preceding month's balance; otherwise, it's set to 0.
In the next layer, a new_dynamic_part attribute is generated as the result of a SUM window function.
Finally, a new_dynamic_part is added as a new partition attribute (dynamic partition) to the existing partition attribute (account_id) and applies the same ROW_NUMBER() window function as in Teradata.
After these changes, Snowflake generates the same output as Teradata.
Same example as above, except that now the window function used in the RESET WHEN condition is defined as a column called previous. This variation changes the transformation slightly since it is no longer necessary to define the previous_value as in the previous example. It is the same workaround.
The RESET WHEN clause could have some variations such as its condition. Currently, SnowConvert only supports binary conditions (<=, >=, <> or =), in any other type, as IS NOT NULL, SnowConvert will remove the RESET WHEN clause and add an error message since it is not supported in Snowflake, as shown in the following example.
: RESET WHEN clause is not supported in this scenario due to its condition.
CREATE OR REPLACE TABLE account_balance (
account_id INTEGER NOT NULL,
month_id INTEGER,
balance INTEGER,
UNIQUE (account_id, month_id)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;
INSERT INTO account_balance
VALUES (1, 1, 60);
INSERT INTO account_balance
VALUES (1, 2, 99);
INSERT INTO account_balance
VALUES (1, 3, 94);
INSERT INTO account_balance
VALUES (1, 4, 90);
INSERT INTO account_balance
VALUES (1, 5, 80);
INSERT INTO account_balance
VALUES (1, 6, 88);
INSERT INTO account_balance
VALUES (1, 7, 90);
INSERT INTO account_balance
VALUES (1, 8, 92);
INSERT INTO account_balance
VALUES (1, 9, 10);
INSERT INTO account_balance
VALUES (1, 10, 60);
INSERT INTO account_balance
VALUES (1, 11, 80);
INSERT INTO account_balance
VALUES (1, 12, 10);1
1
60
1
2
99
1
3
94
1
4
90
1
5
80
1
6
88
1
7
90
1
8
92
1
9
10
1
10
60
1
11
80
1
12
10
SELECT
account_id,
month_id,
balance,
(
ROW_NUMBER() OVER (
PARTITION BY account_id
ORDER BY
month_id RESET WHEN balance <= SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
)
) -1
) AS balance_increase
FROM account_balance
ORDER BY 1, 2;1
1
60
0
1
2
99
1
1
3
94
0
1
4
90
0
1
5
80
0
1
6
88
1
1
7
90
2
1
8
92
3
1
9
10
0
1
10
60
1
1
11
80
2
1
12
10
0
SELECT
account_id,
month_id,
balance,
(
ROW_NUMBER() OVER (
PARTITION BY
account_id, new_dynamic_part
ORDER BY
month_id
) -1
) AS balance_increase
FROM
(
SELECT
account_id,
month_id,
balance,
previous_value,
SUM(dynamic_part) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS new_dynamic_part
FROM
(
SELECT
account_id,
month_id,
balance,
SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS previous_value,
(CASE
WHEN balance <= previous_value
THEN 1
ELSE 0
END) AS dynamic_part
FROM
account_balance
)
)
ORDER BY 1, 2;1
1
60
0
1
2
99
1
1
3
94
0
1
4
90
0
1
5
80
0
1
6
88
1
1
7
90
2
1
8
92
3
1
9
10
0
1
10
60
1
1
11
80
2
1
12
10
0
SELECT
account_id,
month_id,
balance,
SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS previous,
(
ROW_NUMBER() OVER (
PARTITION BY account_id
ORDER BY
month_id RESET WHEN balance <= previous
)
) AS balance_increase
FROM account_balance
ORDER BY 1, 2;1
1
60
0
1
2
99
60
1
1
3
94
99
0
1
4
90
94
0
1
5
80
90
0
1
6
88
80
1
1
7
90
88
2
1
8
92
90
3
1
9
10
92
0
1
10
60
10
1
1
11
80
60
2
1
12
10
80
0
SELECT
account_id,
month_id,
balance,
SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS previous,
(
ROW_NUMBER() OVER (
PARTITION BY
account_id, new_dynamic_part
ORDER BY
month_id
)
) AS balance_increase
FROM
(
SELECT
account_id,
month_id,
balance,
SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS previous,
SUM(dynamic_part) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS new_dynamic_part
FROM
(
SELECT
account_id,
month_id,
balance,
SUM(balance) OVER (
PARTITION BY account_id
ORDER BY month_id
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS previous,
(CASE
WHEN balance <= previous
THEN 1
ELSE 0
END) AS dynamic_part
FROM
account_balance
)
)
ORDER BY 1, 2;1
1
60
0
1
2
99
60
1
1
3
94
99
0
1
4
90
94
0
1
5
80
90
0
1
6
88
80
1
1
7
90
88
2
1
8
92
90
3
1
9
10
92
0
1
10
60
10
1
1
11
80
60
2
1
12
10
80
0
SELECT
account_id,
month_id,
balance,
ROW_NUMBER() OVER (
PARTITION BY account_id
ORDER BY month_id
RESET WHEN balance IS NOT NULL
ROWS UNBOUNDED PRECEDING
) as balance_increase
FROM account_balance
ORDER BY 1,2;SELECT
account_id,
month_id,
balance,
ROW_NUMBER() OVER (
PARTITION BY account_id
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0077 - RESET WHEN CLAUSE IS NOT SUPPORTED IN THIS SCENARIO DUE TO ITS CONDITION ***/!!!
ORDER BY month_id
ROWS UNBOUNDED PRECEDING
) as balance_increase
FROM
account_balance
ORDER BY 1,2;Translation reference to convert Teradata Reset When functionality to Snowflake
CREATE TABLE account_balance
(
account_id INTEGER NOT NULL,
month_id INTEGER,
balance INTEGER
)
UNIQUE PRIMARY INDEX (account_id, month_id);
INSERT INTO account_balance VALUES (1, 1, 60);
INSERT INTO account_balance VALUES (1, 2, 99);
INSERT INTO account_balance VALUES (1, 3, 94);
INSERT INTO account_balance VALUES (1, 4, 90);
INSERT INTO account_balance VALUES (1, 5, 80);
INSERT INTO account_balance VALUES (1, 6, 88);
INSERT INTO account_balance VALUES (1, 7, 90);
INSERT INTO account_balance VALUES (1, 8, 92);
INSERT INTO account_balance VALUES (1, 9, 10);
INSERT INTO account_balance VALUES (1, 10, 60);
INSERT INTO account_balance VALUES (1, 11, 80);
INSERT INTO account_balance VALUES (1, 12, 10);1
1
60
1
2
99
1
3
94
1
4
90
1
5
80
1
6
88
1
7
90
1
8
92
1
9
10
1
10
60
1
11
80
1
12
10