Hi!
In the following query I ended up using unnest(array(subquery)) in order to pair up the rows with starting indices and the rows with ending indices. This is exactly what "FROM ROWS FROM (function)" would do but alas I have a subquery. In the target list I have to use scalar subqueries so doing this directly there is a no-go. Hence the array intermediary. Am I missing/forgetting some feature that can do this without the intermediate array?
Thanks!
David J.
WITH vals (i,v) AS (VALUES (0,1),(1,0),(2,0),(3,1),(4,0),(5,0),(6,1),(7,1),(8,0),(9,1)),
boundaries AS (SELECT *,
CASE WHEN COALESCE(LAG(v) OVER (ORDER BY i), -1) <> 0 AND v=0 THEN 'Start' ELSE NULL END AS start_tag,
CASE WHEN COALESCE(LEAD(v) OVER (ORDER BY i), -1) <> 0 AND v=0 THEN 'End' ELSE NULL END AS end_tag
FROM vals
),
frames AS (SELECT
UNNEST(ARRAY((SELECT i FROM boundaries WHERE start_tag = 'Start' ORDER BY i))) AS start_at,
UNNEST(ARRAY((SELECT i FROM boundaries WHERE end_tag = 'End' ORDER BY i))) AS end_at
)
SELECT vals.i, vals.v,
frames.start_at AS group_index,
row_number() OVER (PARTITION BY frames.start_at ORDER BY vals.i)
FROM vals
JOIN frames ON vals.i BETWEEN frames.start_at AND frames.end_at
boundaries AS (SELECT *,
CASE WHEN COALESCE(LAG(v) OVER (ORDER BY i), -1) <> 0 AND v=0 THEN 'Start' ELSE NULL END AS start_tag,
CASE WHEN COALESCE(LEAD(v) OVER (ORDER BY i), -1) <> 0 AND v=0 THEN 'End' ELSE NULL END AS end_tag
FROM vals
),
frames AS (SELECT
UNNEST(ARRAY((SELECT i FROM boundaries WHERE start_tag = 'Start' ORDER BY i))) AS start_at,
UNNEST(ARRAY((SELECT i FROM boundaries WHERE end_tag = 'End' ORDER BY i))) AS end_at
)
SELECT vals.i, vals.v,
frames.start_at AS group_index,
row_number() OVER (PARTITION BY frames.start_at ORDER BY vals.i)
FROM vals
JOIN frames ON vals.i BETWEEN frames.start_at AND frames.end_at
