Aggregate according to Time Interval — From SQL to SPL #27

Problem description & analysis:

A certain database table stores time-series data, with intervals of several seconds between each record.

Task: Now we need to do a group and aggregation every minute, summarizing data for 5 minutes each time. For example, generate three records in minutes 1, 2, and 3, and summarize the data for minutes 1–5, 2–6, and 3–7 respectively.

Code comparisons:

SQL：

SELECT
    [From], DATEADD(MINUTE, 1, [To]) [To], payload
FROM (
    SELECT
        dt, MIN(dt) OVER(ORDER BY dt ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) [From],
        dt [To], SUM(payload) OVER(ORDER BY dt ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) payload
    FROM (
        SELECT
            DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dt), 0) dt, 
SUM(payload) payload
        FROM #tmstmp
        GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dt), 0)
    ) q
) q
WHERE DATEDIFF(MINUTE, [From], [To]) > 3

SQL needs to implement it using nested subqueries and multiple window functions, which makes the code cumbersome.

SPL: SPL provides the syntax for directly accessing positions.

👉🏻 Try.DEMO

A1： Load data.

A2： Group and aggregate by minute.

A3： Generate a new two-dimensional table, where To is taken from the current record, From is taken from 5 minutes after the current record, and the payload summarizes the interval from the current record to the 4th record.

A4： Take from the first item backward to the fifth item.

---

Free to Try, Powerful to Use — esProc SPL FREE Download.