前1个与前1个之间的行 [英] rows between 1 preceding and preceding 1

问题描述

我是SQL的新手，我想知道该功能(在1个前置和1个前置之间的行)在teradata中的作用是什么，我想要一个简单的说明，我正在尝试将此功能用作测试用例以获取历史记录表中开始日期和结束日期之间的时间间隔，任何人都可以帮忙或提供任何有用的链接.

I am new to SQL and I want to know what exactly the function ( rows between 1 preceding and 1 preceding )do in teradata ,I want a simple clarification please ,I am trying to use this function as a testcase to get the time gaps in history table between start and end date,can anyone help please or provide any useful links.

SELECT DISTINCT CUST_ID
FROM
(
SELECT 
CUST_ID,
STRT_dt - 
MIN(END_dt) OVER (PARTITION BY CUST_ID ORDER BY END_dt
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS diff
FROM table
QUALIFY diff > 1
) dt

推荐答案

这将返回与标准SQL的LAG(END_dt) OVER (PARTITION BY CUST_ID ORDER BY END_dt相同的结果，即前一行的END_dt(或每个CUST_ID的第一行为NULL).

This returns the same result as Standard SQL's LAG(END_dt) OVER (PARTITION BY CUST_ID ORDER BY END_dt, i.e. the previous row's END_dt (or NULL for the 1st row per CUST_ID).

当您切换到FOLLOWING而不是PRECEDING时，它是下一行，即标准SQL中的LEAD.

When you switch to FOLLOWING instead of PRECEDING it's the next row, LEAD in Standard SQL.

LAG和LEAD都最终在TD16.10中实现.

Both LAG and LEAD are finally implemented in TD16.10.

由于您只是想找到差距，而又不了解实际的差异，因此您也可以将其简化为:

As you simply want to find gaps and you don't access the actual difference you can also simplify it to:

SELECT DISTINCT CUST_ID
FROM table
QUALIFY
   STRT_dt - 
   MIN(END_dt)
   OVER (PARTITION BY CUST_ID
         ORDER BY END_dt
         ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) > 1

这篇关于前1个与前1个之间的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！