问题描述
限时送ChatGPT账号..给定一个这样的表格:
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 NULL
s 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屋!
更多推荐
[db:关键词]
发布评论