时间戳

首页 > 解决方案 > Teradata SQL - 来自行的最小最大事务日期

sql - Teradata SQL - 来自行的最小最大事务日期

问题描述

尝试了 Qualify row_Number () 和 Qualify Min & Max 函数，但仍然无法获取交易日期范围。请参阅下面的数据结构

在此处输入图像描述

需要以下输出的帮助

在此处输入图像描述

先感谢您

标签： sqlteradatawindow-functionsgaps-and-islands

解决方案

您需要先找到连续日期的组。有几种方法可以做到这一点，在你的情况下，最好的方法是基于将一个序列与另一个序列进行比较，其中有间隙：

with cte as
 (
   select t.*
      -- consecutive numbers = sequence without gaps
     ,row_number()
      over (partition by location, cust#, cust_type -- ??
            order by transaction_date) as rn
      -- consecutive numbers as long as there's no missing date = sequence with gaps
     ,(transaction_date - date '0001-01-01') as rn2

      -- assign a common (but meaningless) value to consecutive dates,
      -- value changes when there's a gap
     ,rn2 - rn as grp
   from tab as t
 )
select location, cust#, cust_type -- ??
  ,min(transaction_date), max(transaction_date)
  ,min(amount), max(amount)
from cte
 -- add the calculated "grp" to GROUP BY 
group by location, cust#, cust_type, grp

用于 PARTITION BY/GROUP BY 的列取决于您的规则。

推荐阅读