SQL group data on intervals of time

มีงานต้องดึงค่าเฉลี่ยของค่าต่างๆตามช่วงเวลา เช่นค่าเฉลี่ยของค่า “AA” ทุก 1 ชัวโมง หรือผลรวมของ “AA” ต่อ 1 ชั่วโมง โดยทำบน MSSQL

ลองหาข้อมูลผมมีทีการทำหลายแบบ เจอแบบนี้น่าจะตรงกับความต้องการ

SELECT DATEADD(HOUR, 1 + ((DATEDIFF(HOUR, 0, LOG_DATETIME) / 1) * 1)-1, 0) AS LOG_DATETIME
   ,AVG(WQ_PH) AS WQ_PH
   ,AVG(WQ_CD) AS WQ_CD
FROM VKATHU_LOG 
GROUP BY DATEADD(HOUR, 1 + ((DATEDIFF(HOUR, 0, LOG_DATETIME) / 1) * 1)-1, 0)
ORDER BY LOG_DATETIME DESC

หลักการคือการสร้าง Columns เวลาขึ้นมาใหม่ ค่ามีที่เป็นเวลาช่วงเดียวกัน เพื่อสะดวกต่อการใช้ “GROUP BY” หน้าตาจะออกมาแบบนี้

SELECT ID,LOG_DATETIME,dateadd(minute, 5 + ((datediff(minute, 0, LOG_DATETIME) / 5) * 5)-5, 0) AS LOG_DATETIME
   ,WQ_PH
   ,WQ_CD
FROM VKATHU_LOG ORDER BY ID DESC

การสร้าง Columns เวลาขึ้นมาใหม่

เมื่อได้ Columns เวลาใหม่มาแล้วก็จะสามารถ “GROUP BY” ค่าต่างๆตามเวลาใหม่ได้ดังคำสั่งด้านบน

dateadd, datediff ดูการใช้งานได้จาก https://www.w3schools.com/sql/func_sqlserver_dateadd.asp

Ref. https://dba.stackexchange.com/questions/17669/grouping-records-based-on-intervals-of-time