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. [Source Table] 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.

May 12, 2025 - 04:27

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.
[Source Table]

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.