ts_slide.sqlx

config { hasOutput: true }
/*
 * Copyright 2019 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

-- ts_slide:
-- Input:
-- ts: timestamp to be evaluated into sliding or [hopping]() windows
-- period: the interval size to start a new window
-- duration: the size of the sliding windows
-- Output: array of windows the timestamp belongs to. Inclusive of the window_start and exclusive of the window_end 
CREATE OR REPLACE FUNCTION ${self()}(ts TIMESTAMP, period INT64, duration INT64)
RETURNS ARRAY<STRUCT<window_start TIMESTAMP, window_end TIMESTAMP>>
AS ((
  SELECT 
    ARRAY_AGG(
      STRUCT(
        window_start, 
        TIMESTAMP_ADD(window_start, INTERVAL duration SECOND) AS window_end
      )
    )
  FROM UNNEST(
    GENERATE_TIMESTAMP_ARRAY(
      ${ref("ts_tumble")}(
        TIMESTAMP_SUB(ts, INTERVAL duration - period SECOND),
        period
      ),
      ts,
      INTERVAL period SECOND
    )
  ) AS window_start
));