SQLにおけるギャップとアイランドの問題の解決方法

ROW_NUMBERを使用したギャップとアイランドの特定:

WITH CTE AS (
SELECT column_name, ROW_NUMBER() OVER (ORDER BY column_name) AS rn
FROM table_name
)
SELECT column_name, rn - ROW_NUMBER() OVER (ORDER BY column_name) AS gap
FROM CTE

LEADとLAG関数を使用したギャップとアイランドの特定:

SELECT column_name, 
LEAD(column_name) OVER (ORDER BY column_name) - column_name AS gap,
LAG(column_name) OVER (ORDER BY column_name) - column_name AS island
FROM table_name

EXISTS句を使用したギャップとアイランドの特定:

SELECT t1.column_name
FROM table_name t1
WHERE NOT EXISTS (
SELECT 1
FROM table_name t2
WHERE t2.column_name = t1.column_name + 1
)