背景
最小と最大のペアの集合があるとする。
例えば、[最小, 最大]とすると、
[1, 2], [3, 4], [4, 5], [6, 7], [6, 8], [9, 10]
というような集合。
[3, 4], [4, 5]
は、まとめて、
[3, 5]
にしたい。
他にも、
[6, 7], [6, 8]
も、まとめて、
[6, 8]
にしたい。
最終的に、
[1, 2], [3, 5], [6, 8], [9, 10]
と出力したい。
つまり、ある要素の最大が、別の要素の最小値と同じかそれより大きい場合は、
その２つの要素を一つの要素にまとめたいということがある。
そのクエリ
WITH sample_data as (
SELECT
s.a AS a,
s.z as z
FROM (
SELECT
[
STRUCT(1 as a, 2 AS z),
STRUCT(3 as a, 4 AS z),
STRUCT(4 as a, 5 AS z),
STRUCT(6 as a, 7 AS z),
STRUCT(6 as a, 8 AS z),
STRUCT(9 as a, 10 AS z)
] _a)
JOIN
UNNEST(_a) s),
w as (
SELECT
a,
z,
LAG(z) OVER (ORDER BY a, z) AS pre_z,
LAG(a) OVER (ORDER BY z desc, a desc) AS post_a
FROM sample_data
ORDER BY a, z),
head AS (
SELECT DISTINCT
a
FROM
w
WHERE
pre_z IS NULL OR
a > pre_z),
tail AS (
SELECT DISTINCT
z
FROM
w
WHERE
post_a IS NULL OR
post_a > z),
head_tail AS (
SELECT
a,
MIN(z) AS z
FROM
head
CROSS JOIN
tail
WHERE
a <= z
GROUP BY a
)
SELECT
*
FROM head_tail
解説
ウィンドウ関数を使って、並べた一つ上（もしくはひとつ下の）最大値（最小値）がその列の最小値より小さい（最大値より大きい）場合、まとめた要素の最小値（最大値）となる。
