Як sql-запитом витягти з бази даних інформацію, якої там немає +11
- 15.06.16 1:25 •
- avshukan •
- # 303364 •
- Хабрахабр •
- 34 •
- 9300
- такий же як Forbes, тільки краще.
Під таким хитрим заголовком ховається досить нескладне завдання, але спочатку невеликий вступ:
Приходять користувачі і просять: «Ось ми внесли дані в базу, а скажіть нам, чого не вистачає? Які дані ми ще не внесли в базу і їх не вистачає для повного щастя? »
Перша (і скажемо чесно, дуже дурна) реакція: «Як же я вам знайду те, чого немає в базі даних?».
Але відкинемо емоції і застосуємо логіку. Адже, як правило, потрібні дані, формування яких підпорядковується якомусь правилу - номери квитанцій, довідок і так далі ... І я виходжу з того, що всі ці номери і ідентифікатори можуть бути перетворені в натуральну послідовність.
Тобто завдання буде сформульована таким чином: в базі даних зберігається послідовність натуральних чисел, в якій є пропуски, і необхідно вивести пропущені числа для користувача.
У такому формулюванні завдання вже виглядає досить простий. Більш того - виникає бажання реалізувати це завдання одним єдиним sql-запитом.
Давайте створимо таблицю і заповнимо якимись даними.
Основна ідея така: порівняти таблицю з самою собою ж і для кожного значення ІКС знайти мінімальне ІГРЕК (яке все ж більше ікси), де (ІКС + 1) і (ІГРЕК - 1) будуть нашими кордонами пропущених діапазонів чисел. Додавши логічне умова, що, (ІКС + 1) повинен бути не менше (ІГРЕК - 1) отримаємо наступні діапазони: від 4 до 4, від 6 до 6, від 10 до 10 і від 13 до 15.
Які є нюанси:
1) Чи може бути пропущений перший елемент послідовності (в нашому випадку це 1)
2) Невідомий останній елемент послідовності (а раптом це 22). Можна, звичайно, запрошувати цю інформацію у користувача, але досвід підказує, що краще цього уникати.
3) Діапазон «від 4 до 4» виглядає глючно, треба замінити просто на одне число
4) Результат все-таки бажано отримати значенням одного рядка, а не набором рядків
Враховуємо зауваження і отримуємо варіант скрипта під MySQL:
і варіант під Oracle:
Результатом їх виконання є рядок '1-2, 4, 6, 10, 13-15, 18.'
По-перше, цей рядок містить те, що хотіли користувачі.
По-друге, результат виглядає зрозуміло для будь-якого користувача.
І по-головних, запит виводить дані, які дійсно в базі даних не зберігаються!
Варіант для MySQL від asmm
Варіант для Oracle від xtender
Варіант для MSSQL від yizraor
Ну це загальний кейс, а не sql ... Найпростіше - знайти квиток (на поїзд), якщо квитків немає. Особливість РЖД і подібних систем - довго тримають бронь з першого до великого міста полустанку, напр для Києва це може бути Дарниця, Бровари, Бахмач, Конотоп - і якщо з самого Києва в Москву квитків наглухо немає - то з зазначених пунктів вони цілком можуть бути, і, що найдивніше, будуть в системі і квитки дО них - і цілком можна в тій же касі купити на один і той же поїзд-вагон-місце - ланцюжок Київ-Бахмач + Бахмач-Москва. (А якщо розбивку зробити ще раз в Бєлгороді - то вийде ще й дешевше).
Тут головне в голові мати зарубку, що «якщо в (будь-якої) системі чогось немає - це не означає, що цього немає взагалі», типу «в кожній пустнине є оазис ... Але не кожен верблюд може його знайти»
А вже як в одній нефтелавке кілька свердловин в системі втратили ... (про що дуже довго і не здогадувалися) ...
Ще народ регулярно камери спостереження втрачає - і теж з обох кінців пошуки кумедні - що ті, у кого камер живих менше. ніж на кшталт інстальованих не відразу можуть схаменутися, що навпаки, на місцевості - висить на верхотурі камера, харчується мало не від освітлення, потік жене по вай-фаю, чия, коли, ким поставлена - невідомо, орендарі відхрещуються - не наша мовляв ...
На попередньому місці роботи (музей) часто виникала необхідність дізнатися, які інвентарні номери (на яких будувалася логіка всієї роботи) пропущено. Рішення було практично таким же, як ви його представили (єдина відмінність в тому, що номери могли мати вигляд [some_letters] 12345 [[start_number] [letter]] [-] [[end_number] [letter]], в квадратних дужках - опціональні блоки).
На щастя, існувала база, в якій були перераховані проміжки цих номерів (у вигляді [some_letters] 12345 [start-end]).
Запит, правда, в результаті був складніше, тому що з'явилося ще безліч кейсів для перевірки (користувачі вводили вкрай різняться дані, та й, врешті-решт, з'ясувалося, що релевантні шукати не пропущені номери, а номери, для яких зробили відразу кілька записів) .
Так, я розглядав найпростіший випадок (-:
З використанням змінних можна простіше зробити. В принципі, можна і без 3-го рівня вкладеності обійтися, але так зрозуміліше виглядає.