Saya memiliki tabel dalam SQL BQ dengan kolom ID dan DateTime (TIMESTAMP). Saya ingin menghitung perbedaan stempel waktu dalam katakanlah detik antara setiap baris berturut-turut, dan membuat kolom baru dengan perbedaan waktu yang dihitung.

Meja:

ID     DateTime
a      2019-10-15 10:00:19 UTC
a      2019-10-15 10:00:29 UTC
a      2019-10-15 10:00:39 UTC
a      2019-10-15 10:00:49 UTC
a      2019-10-15 10:00:59 UTC

the desired result would look like this:

ID     DateTime                    TimeDiff
a      2019-10-15 10:00:19 UTC      null
a      2019-10-15 10:00:29 UTC       10
a      2019-10-15 10:00:39 UTC       10
a      2019-10-15 10:00:49 UTC       10
a      2019-10-15 10:00:59 UTC       10

Sejauh ini saya telah mencoba opsi ini tanpa hasil:

select ID, DateTime,
(LAG(DateTime) OVER (PARTITION BY ID ORDER BY DateTime ASC) - DateTime) AS TimeDiff
from `xxx.yyy.table` 
order by DateTime

Dan

select ID, DateTime,
timestamp_diff(lag(DateTime, 1) OVER (ORDER BY DateTime)) as TimeDiff
from `xxx.yyy.table`
order by DateTime

Dan

select ID, DateTime,
LAG(DateTime) OVER (PARTITION BY FieldID ORDER BY DateTime ASC) AS timeDiff
from `xxx.yyy.table` 
order by DateTime
0
Kuki 1 Juli 2020, 14:15

1 menjawab

Jawaban Terbaik

LAG() adalah fungsi yang tepat untuk mendapatkan nilai dari baris sebelumnya. Anda hanya perlu menggunakan TIMESTAMP_DIFF() benar:

select ID, DateTime,
       timestamp_diff(DateTime,
                      lag(DateTime, 1) OVER (ORDER BY DateTime), 
                      second
                     ) as TimeDiff
from `xxx.yyy.table`
order by DateTime;

Perhatikan bahwa sepertinya Anda menginginkan per id ini. Jika demikian, Anda juga harus PARTITION BY:

       timestamp_diff(DateTime,
                      lag(DateTime, 1) OVER (PARTITION BY id ORDER BY DateTime), 
                      second
                     ) as TimeDiff
1
Gordon Linoff 1 Juli 2020, 11:16