Обсуждение: Rows From but with Subqueries (or a cleaner non-array-using alternative)?
Rows From but with Subqueries (or a cleaner non-array-using alternative)?
От
"David G. Johnston"
Дата:
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
Re: Rows From but with Subqueries (or a cleaner non-array-using alternative)?
От
Michael Lewis
Дата:
In pseudo code, group_index is defined as:
case when LAG(v) OVER (ORDER BY i) = v then lag(i) ELSE i END, right?
If you have that in the first cte instead of the start/end business, then you can just select vals, group number, and row_num over that new grouping, right?
case when LAG(v) OVER (ORDER BY i) = v then lag(i) ELSE i END, right?
If you have that in the first cte instead of the start/end business, then you can just select vals, group number, and row_num over that new grouping, right?
Something like this?
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)),
grouped_vals AS (SELECT *,
case when LAG(v) OVER (ORDER BY i) = v then lag(i) OVER (ORDER BY i) ELSE i END AS group_index
FROM vals
)
select *, row_number() OVER (PARTITION BY group_index ORDER BY i)
from grouped_vals
where v = 0;
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)),
grouped_vals AS (SELECT *,
case when LAG(v) OVER (ORDER BY i) = v then lag(i) OVER (ORDER BY i) ELSE i END AS group_index
FROM vals
)
select *, row_number() OVER (PARTITION BY group_index ORDER BY i)
from grouped_vals
where v = 0;