Завдання знаходження прогалин і діапазонів в SQL дуже часто доводиться вирішувати в реальних життєвих ситуаціях. Основний принцип полягає в тому, що у вас є певна послідовність чисел або значень дат і часу, між якими повинен дотримуватися фіксований інтервал, але деякі елементи відсутні. Рішення завдання пошуку прогалин передбачає перебування елементів, яких не вистачає в послідовності, а пошук діапазонів - знаходження безперервних діапазонів існуючих значень. Для демонстрації методики пошуку прогалин і діапазонів я скористаюся таблицею по імені T1 з чисельної послідовністю в стовпці col1 з цілим інтервалом, рівним одиниці, і таблицю T2 з послідовністю методу дати і часу в стовпці col1 з інтервалом в один день. Ось код створення T1 і T2 і наповнення їх тестовими даними:
Як говорилося раніше, завдання пошуку прогалин передбачає знаходження діапазонів відсутніх значень в послідовності. Для наших тестових даних необхідний результат для чисельної послідовності в T1 такий:
А ось потрібний результат для послідовності міток дат і часу в T2:
Завдання знаходження діапазонів має на увазі виявлення діапазонів існуючих значень. Ось очікуваний результат для числової послідовності:
А ось необхідний результат для тимчасової послідовності дат:
Одне з найбільш ефективних рішень задачі пошуку діапазонів передбачає використання ранжирування. Використовується функція DENSE_RANK для створення послідовності цілих чисел в упорядкуванні по col1 і обчислюється різниця між col1 і «щільним рангом» (drnk), приблизно так;
Зауважте, що в межах діапазону різниця однакова, причому вона унікальна для кожного діапазону. Це відбувається тому, що col1 і drnk збільшуються з одним інтервалом. При переході на наступний діапазон col1 збільшується більш, ніж на один інтервал, a drnk завжди збільшується на один інтервал. Тому різниця в кожному наступному інтервалі більше, ніж в попередньому. Завдяки тому, що ця різниця однакова і унікальна в межах кожного діапазону, можна використовувати її в якості ідентифікатора групи. Так що залишається тільки згрупувати рядки з цієї різниці і повернути максимальне і мінімальне значення col1 в кожній групі:
План цього рішення показаний на малюнку:
План дуже ефективний, тому що при обчисленні щільного рангу використовується упорядкування індексу на основі col1.