teradata, reset when, partition by, order by
I was also unsure why this wasn't partitioned by PARTITION BY Y.ACCT_DIM_NB, Y.DAY_TIME_DIM_NB ORDER BY Y.DAY_TIME_DIM_NB, Y.TXN_POSTING_SEQ
Don't know, but this would return a different result (and Y.DAY_TIME_DIM_NB
is not needed in ORDER BY
because it's already partitioned by it)
Also, is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW just using the whole partitioned window?
It's exactly the same as ROWS UNBOUNDED PRECEDING
, i.e. a syntax variation for a Cumulative Max. The lpartition is ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
What does RESET WHEN do in Teradata?
The RESET WHEN
is a Teradata extension for dynamically adding partitions, it's a shorter syntax for two (in your case) or three nested OLAP functions:
-- using RESET WHEN
MAX(A.RUN_BAL_AM)
OVER (PARTITION BY A.ACCT_DIM_NB
ORDER BY A.DAY_TIME_DIM_NB, A.TXN_POSTING_SEQ
RESET WHEN A.CS_TXN_CD NOT IN ('072','075','079','107','111','112','139','181','318')
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS EOD_BAL_AM
-- Same result using Standard SQL
SELECT
Max(A.RUN_BAL_AM)
Over (PARTITION BY A.ACCT_DIM_NB, dynamic_partition
ORDER BY A.DAY_TIME_DIM_NB, A.TXN_POSTING_SEQ
ROWS BETWEEN Unbounded Preceding AND CURRENT ROW) AS EOD_BAL_AM
FROM
(
SELECT
-- this cumulative sum over 0/1 assigns a new value for each series of rows based on the CASE
Sum(CASE WHEN A.CS_TXN_CD NOT IN ('072','075','079','107','111','112','139','181','318') THEN 1 ELSE 0 end)
Over (PARTITION BY A.ACCT_DIM_NB, dynamic_partition
ORDER BY A.DAY_TIME_DIM_NB, A.TXN_POSTING_SEQ
ROWS Unbounded Preceding) AS dynamic_partition
FROM ...
) AS dt
SQL Count reset when binary column equals 0
Assign a grouping
to each row. Then use row_number()
:
select t.*,
(case when flag = 0 then 0
else row_number() over (partition by animal, grouping order by order) - 1
end) as count
from (select t.*,
sum(case when flag = 0 then 1 else 0 end) over
(partition by animal
order by order
rows between unbounded preceding and current row
) as grouping
from t
) t
The - 1
is because the each sequence of 1
also includes the previous 0
. You can also express this as:
else row_number() over (partition by animal, grouping, flag order by order)
so only the 1
s are in the group.
Resetting ROW_NUMBER count based on 3 month gap in date
Teradata supports a proprietary extension to Windowed Aggregates, RESET WHEN, which adds a kind of dynamic partition:
Row_Number()
Over(PARTITION BY c.src_sbscrbr_id
, c.src_mbr_sqnc_nbr
, cl.hlth_srvc_cd
, df.serv_prov_id
ORDER BY cl.clm_line_srvc_strt_dt
-- restart the row number when the previous date is more than 3 months ago
RESET WHEN Min(clm_line_srvc_strt_dt)
Over (PARTITION BY c.src_sbscrbr_id
, c.src_mbr_sqnc_nbr
, cl.hlth_srvc_cd
, df.serv_prov_id
ORDER BY cl.clm_line_srvc_strt_dt
ROWS BETWEEN 1 Preceding AND 1 Preceding)
< Add_Months(cl.clm_line_srvc_strt_dt, -3)) AS rncnt
Based on the error message in Gordon's answer your Teradata version doesn't support LAG, yet (must be 16.10+). The MIN is the same as:
LAG(clm_line_srvc_strt_dt)
Over (PARTITION BY c.src_sbscrbr_id
, c.src_mbr_sqnc_nbr
, cl.hlth_srvc_cd
, df.serv_prov_id
ORDER BY cl.clm_line_srvc_strt_dt)
Btw, there's no need to ORDER by the same columns you already use in PARTITION (within a partition it's the same value anyway)
Issue with Ranking / Row Number / Ordering
It looks like this can be solved using a RESET WHEN
in your window function:
MAX() OVER(
PARTITION BY <...>
ORDER BY Order_Flow
RESET WHEN Storage = 20
)
I believe you can leave out the PARTITION BY
if you just want to control the ordering and don't need to do any partitioning. Or just use a constant value, like PARTITION BY 1
or something to that effect.
Documentation:
https://docs.teradata.com/reader/756LNiPSFdY~4JcCCcR5Cw/8uRgqNTevlcmjBfsU3WQsw
Stackoverflow:
teradata, reset when, partition by, order by
Related Topics
SQL Explain Plan: What Is Materialize
Percentage from Total Sum After Group by SQL Server
How to Remove Repeated Column Values from Report
Sql-Only Find Time and Not Date in Access Date/Time Field
Why Would Year Fail with a Conversion Error from a Date
Splitting a Comma-Separated Field in Postgresql and Doing a Union All on All the Resulting Tables
Comma-Separated Value Insertion in SQL Server 2005
Temporary Table in SQL Server Causing ' There Is Already an Object Named' Error
Select 2 Columns in One and Combine Them
Getting List of Table Comments in Postgresql
What's the Best Way to Store Sort Order in SQL
Oracle SQL:Timestamps in Where Clause
Differencebetween Postgres Distinct VS Distinct On
How to Get the Min() of Two Fields in Postgres