Teradata 中分区窗口的时差(以小时和秒为单位)(会话记录) [英] Time difference in hours and seconds over a partition window in Teradata (Sessionizing Records)

问题描述

给定一个这样的表格:

cust_id                time
    123 2015-01-01 12:15:05
    123 2015-01-01 12:17:06
    123 2015-01-02 13:15:08
    123 2015-01-02 15:15:10
    456 2015-01-01 10:15:05
    456 2015-01-01 12:15:07
    456 2015-01-01 14:11:10

我想通过 cust_id 计算每个先前记录之间的时间差(想想 lag 函数).我想要的输出:

I would like to calculate the time difference between each preceding record (think lag function) by cust_id. My desired output:

cust_id                time diff_hours  diff_seconds
    123 2015-01-01 12:15:05       NULL          NULL
    123 2015-01-01 12:17:06       0.00           121
    123 2015-01-02 13:15:08       1.04         89882
    123 2015-01-02 15:15:10       0.08          7202
    456 2015-01-01 10:15:05       NULL          NULL
    456 2015-01-01 12:15:07       0.08          7202 
    456 2015-01-01 14:11:10       0.08          6963

如何在 Teradata 中完成此操作?

How do I accomplish this in Teradata?

我尝试过以下内容:

SELECT
  *
  , (time - time) OVER (PARTITION BY cust_id ORDER BY time ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM
  table_01

然而，虽然 NULL 出现在预期的位置，但我一直收到 0.0 以获取所有其他结果.我也尝试使用 (time - time) 和 SUM 包装，我尝试使用 EXTRACT(SECOND FROM TIME) 和其他一些变体-- 例如，尝试将 DAY(4) 置于 SECOND，但我似乎无法完全正确地获得语法/排序/转换，尤其是在将窗口函数投入混合时.>

However, while the NULLs show up where expected, I keep receive 0.0 for all other results. I have also tried using wrapping (time - time) with SUM and I have tried using EXTRACT(SECOND FROM TIME) and a few other variants -- e.g., trying to place DAY(4) to SECOND, but I can't seem to get the syntax/ordering/conversion quite right, especially when tossing a window function into the mix.

推荐答案

Teradata 中没有 LAG，但您可以重写它:

There's no LAG in Teradata, but you can rewrite it:

SELECT
  t.*
  , (time)
    - min(time) 
      OVER (PARTITION BY cust_id 
            ORDER BY time
            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) SECOND(4)
FROM
  table_01 as t

当您尝试获取秒数时，您会遇到间隔溢出"错误，即超过 9999 秒.要么更改为 DAY(4) TO SECOND，要么使用我几年前写的这个 SQL UDF 来计算两个时间戳的秒差:

When you try to get seconds you will encouter "Interval Overflow" errors, i.e. more than 9999 seconds. Either change to DAY(4) TO SECOND or use this SQL UDF I wrote a few years ago for calculating the difference of two timestamps in seconds:

REPLACE FUNCTION TimeStamp_Diff_Seconds
(
   ts1 TIMESTAMP(6)
  ,ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CAST((CAST(ts2 AS DATE)- CAST(ts1 AS DATE)) AS DECIMAL(18,6)) * 60*60*24)
      + ((EXTRACT(  HOUR FROM ts2) - EXTRACT(  HOUR FROM ts1)) * 60*60)
      + ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) * 60)
      +  (EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1))
;

这篇关于Teradata 中分区窗口的时差(以小时和秒为单位)(会话记录)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！