sql-запитом витягти з бази даних інформацію, якої там немає

Під таким хитрим заголовком ховається досить нескладне завдання, але спочатку невеликий вступ:
Приходять користувачі і просять: «Ось ми внесли дані в базу, а скажіть нам, чого не вистачає? Які дані ми ще не внесли в базу і їх не вистачає для повного щастя?»
Перша (і скажемо чесно, дуже дурна) реакція: «Як же я вам знайду те, чого немає в базі даних?».
Але відкинемо емоції і застосуємо логіку. Адже, як правило, потрібні дані, формування яких підпорядковується якомусь правилу — номери квитанцій, довідок і так далі… І я виходжу з того, що всі ці номери і ідентифікатори можуть бути перетворені в натуральну послідовність.
Тобто завдання буде сформульована наступним чином: у базі даних зберігається послідовність натуральних чисел, в якій є пропуски, і необхідно вивести пропущені числа для користувача.
У такому формулюванні завдання вже виглядає досить простий. Більше того, виникає бажання реалізувати це завдання одним єдиним sql-запитом.
Давайте створимо таблицю і заповнимо якими-небудь даними.
CREATE TABLE IF NOT EXISTS `Test` (`id` int(6) NOT NULL);
INSERT INTO `Test` (`id`) VALUES (3), (5), (7), (8) , (9) , (11) , (12), (16) , (17) ;

Основна ідея наступна: порівняти таблицю з самою собою ж і для кожного значення ІКС знайти мінімальне ІГРЕК (який все ж більше Ікси), де (Х + 1) і (ІГРЕК — 1) будуть нашими кордонами пропущених діапазонів чисел. Додавши логічне умова, що, (Х + 1) повинен бути не менше (ІГРЕК — 1) отримаємо наступні діапазони: від 4 до 4, від 6 до 6, від 10 до 10 та від 13 до 15.
Які є нюанси:
1) Може бути пропущений перший елемент послідовності (в нашому випадку це 1)
2) Невідомий останній елемент послідовності (а раптом це 22). Можна, звичайно, запрошувати цю інформацію у користувача, але досвід підказує, що краще цього уникати.
3) Діапазон «від 4 до 4» виглядає глючно, треба замінити просто на одне число
4) Результат все-таки бажано отримати значенням одного рядка, а не набором рядків
Враховуємо зауваження і отримуємо варіант скрипта під MySQL:
SELECT GROUP_CONCAT( ranges )
FROM (
SELECT
CASE
WHEN id2 IS NULL
THEN CONCAT( id1, '...' )
WHEN id1 = id2
THEN id1
ELSE CONCAT( id1, '-', id2 )
END ranges
FROM (
SELECT id +1 id1, (
SELECT MIN( id ) -1
FROM `Test` t2
WHERE t2.id > t1.id
)id2
FROM `Test` t1
UNION
SELECT 1 , MIN( id ) -1
FROM `Test` t3
)t
WHERE id1 <= id2
OR id2 IS NULL
ORDER BY id1
)tt

і варіант під Oracle:
SELECT LISTAGG (ranges, ', ')
FROM (
SELECT CASE
WHEN id2 IS NULL THEN TO_CHAR (id1) || '...'
WHEN id1 = id2 THEN TO_CHAR (id1)
ELSE TO_CHAR (id1) || '-' || TO_CHAR (id2)
END ranges
FROM (
SELECT id + 1 id1,
(SELECT MIN (id) - 1
FROM TEST t2
WHERE t2.id < t1.id) id2
FROM TEST t1
UNION
SELECT 1, MIN (id) - 1
FROM TEST t3) t
WHERE id1 <= id2 OR id2 IS NULL
ORDER BY id1
) tt

Результатом їх виконання є рядок '1-2, 4, 6, 10, 13-15, 18...'
По-перше, цей рядок містить те, що хотіли користувачі.
По-друге, результат виглядає зрозуміло для будь-якого користувача.
І по-головних, запит виводить дані, які дійсно в базі даних не зберігаються!
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.