SQL หาระยะเวลาที่แตกต่างกันระหว่าง 2 records ใน BigQuery

ใน BigQuery เราสามารถใช้ฟังก์ชั่น LAG ได้ เพื่อที่จะดูข้อมูลของ record ก่อนหน้า ซึ่งทำให้เราสามารถคำนวณความแตกต่างระหว่าง record ปัจจุบัน กับ record ก่อนหน้าได้ เช่น ระยะเวลาระหว่าง 2 records เป็นตัว

ตัวอย่าการใช้การก็ถ้าเรามีตาราง Activity Log อยู่ (มี column ชื่อ activity_id กับ customer_id) แล้วเราอยากเทียบดูว่าระหว่าง 2 activities ของ customer สักคนหนึ่งเนี่ย มีระยะเวลาห่างกันเป็นกี่วินาที เราจะเขียน SQL ได้ตามนี้

SELECT
  activity_id,
  customer_id,
  TIMESTAMP_DIFF(
    TIMESTAMP(create_datetime),
    LAG(TIMESTAMP(create_datetime)) OVER (PARTITION BY customer_id ORDER BY create_datetime ASC),
    SECOND
  ) AS time_diff_in_sec
FROM
  `main.activity_log`
ORDER BY
  activity_id

ตรงฟังก์ชั่น LAG จะต้องมีการทำ PARTITION BY customer_id ด้วย เพราะเราต้องการที่จะดู activity เป็นราย customer เนอะ เสร็จแล้วเราก็ต้องใส่ ORDER BY create_datetime ด้วย เพราะว่าตอนที่เราใช้ฟังก์ชั่น TIMESTAMP_DIFF เราจะได้เอา 2 records มาลบกันได้ถูก :sunglasses:

ถ้าเป็น Database หรือ Data Warehouse อื่นๆ ฟังก์ชั่นคิดว่าน่าจะแตกต่างกันไม่มากไปจากนี้~

1 Like