Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL alternative for Set::IntSpan::Partition
WITH
args AS (
SELECT
'
[
[[1,3]],
[[3,5],[7,8]],
[[8,8]],
[[0,100]]
]
' AS data
),
data AS (
SELECT
sets.key AS key,
json_extract(spans.value, '$[0]') AS lower,
json_extract(spans.value, '$[1]') AS upper
FROM
args
INNER JOIN json_each(args.data) sets
INNER JOIN json_each(sets.value) spans
ORDER BY
lower,
upper
),
cuts AS (
SELECT 'lower' AS type, lower AS value FROM data
UNION
SELECT 'upper' AS type, lower-1 AS value FROM data
UNION
SELECT 'upper' AS type, upper AS value FROM data
UNION
SELECT 'lower' AS type, upper+1 AS value FROM data
),
bounds AS (
SELECT
CASE
WHEN type = 'lower' THEN value
END AS lower,
lead(value) OVER (ORDER BY value) AS upper
FROM
cuts
),
mapped AS (
SELECT DISTINCT
data.key AS key,
bounds.lower AS lower,
bounds.upper AS upper
FROM
data INNER JOIN bounds ON (
bounds.lower BETWEEN data.lower AND data.upper
AND
bounds.upper BETWEEN data.lower AND data.upper
)
ORDER BY
1, 2, 3
),
final AS (
SELECT
lower,
upper,
-- FIXME: SQLite does not guarantee ordering here
JSON_GROUP_ARRAY(key)
FROM
mapped
GROUP BY
lower,
upper
)
SELECT
*
FROM
final
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.