Teradata、重置时间、分区依据、排序依据 [英] teradata, reset when, partition by, order by
问题描述
我需要帮助理解以下代码.在 Teradata 中使用时,我从未见过重置.在 Teradata 中 RESET WHEN 有什么作用?我了解分区和按部分排序.我也不确定为什么这不是由 PARTITION BY A.ACCT_DIM_NB, A.DAY_TIME_DIM_NB ORDER BY A.TXN_POSTING_SEQ 分区.另外,ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 是否只使用整个分区窗口?
I need help understanding the below code. I have never seen reset when used in Teradata. What does RESET WHEN do in Teradata? I understand the partition and order by part. I was also unsure why this wasn't partitioned by PARTITION BY A.ACCT_DIM_NB, A.DAY_TIME_DIM_NB ORDER BY A.TXN_POSTING_SEQ . Also, is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW just using the whole partitioned window?
Removed
推荐答案
我也不确定为什么这不是由 PARTITION BY Y.ACCT_DIM_NB、Y.DAY_TIME_DIM_NB ORDER BY Y.DAY_TIME_DIM_NB、Y.TXN_POSTING_SEQ 划分的
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
不知道,但这会返回不同的结果(并且 Y.DAY_TIME_DIM_NB
在 ORDER BY
中不需要,因为它已经被它分区了)
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)
另外,ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 是否只使用整个分区窗口?
Also, is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW just using the whole partitioned window?
它与 ROWS UNBOUNDED PRECEDING
完全相同,即累积最大值的语法变体.lpartition是ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
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
RESET WHEN 在 Teradata 中有什么作用?
What does RESET WHEN do in Teradata?
RESET WHEN
是用于动态添加分区的 Teradata 扩展,它是两个(在您的情况下)或三个嵌套 OLAP 函数的较短语法:
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
这篇关于Teradata、重置时间、分区依据、排序依据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!