Oracle вернуть первое нулевое значение
Обновлено: 06.07.2024
Если вы далеки от работы с базами данных, для вас может быть открытием, что ноль – это не значение NULL, хотя, признаем, они созвучны. Кроме того, NULL не является значением пустой строки, хотя можно найти поле, содержащее данные любого типа.
NULL можно представить как значение для представления неизвестного фрагмента данных (обратите внимание: не нулевого, хотя поле при этом выглядит пустым). А еще он не равен ничему, даже другому NULL. И сегодня мы поговорим об этом загадочном (на первый взгляд) значении NULL более подробно.
Пример значения NULL
Итак, что вы должны знать о значении NULL? Давайте разбираться.
Представьте себе письменный стол, на котором лежат канцелярские принадлежности: 6 шариковых ручек и 2 простых карандаша. Также известно, что в ящике стола должны быть фломастеры. Но вот сколько их и есть ли они вообще — данных нет. Если нам нужно составить таблицу инвентаризации с вводом значения NULL, то выглядеть она будет так:
| InventoryID | Item | Количество |
| 1 | ручки | 6 |
| 2 | карандаши | 2 |
| 3 | фломастеры | NULL |
Как вы понимаете, принимать за «0» количество фломастеров в данном случае было бы неверным, так как подобная запись показывала бы, что фломастеров нет вообще. Но точные данные об их количестве отсутствуют, поэтому может оказаться, что несколько штук все же есть.
Значение NULL и НЕ NULL
IS NULL и IS NOT NULL – специально созданные операторы, которые осуществляют сравнение имеющихся NULLов. IS NULL возвращает истину, если операнда является NULLом. Соответственно, если операнд не является NULLом, то значение будет ложным.
IS NOT NULL имеет обратный принцип: значение будет истинным, если операнд не является NULLом, и ложным, если он таковым является.
Учтите, что когда речь идет об отсутствующих значениях, есть особые случаи их сравнения:
- DECODE — принимает два NULLа за равные значения;
- составные индексы — в случае, когда у двух ключей есть пустые поля, но заполненные поля при этом равны между собой, то Oracle воспримет эти ключи, как равные.
Вот так проявляет себя DECODE:
select decode( null
Значение NULL в MySQL
Результат при сравнении NULLов, в зависимости от операции SQL, часто будет иметь значение NULL. Предположим, что А НЕДЕЙСТВИТЕЛЕН:
Арифметические операторы
- A + B = NULL
- A – B = NULL
- A * B = NULL
- A/B = NULL
Ваш Путь в IT начинается здесь
Подробнее- A = B = NULL
- A! = B = NULL
- A> B = NULL
- A!
Логические операции и NULL
Для логических операторов AND и OR есть свои особенности при работе со значением NULL. Краткое руководство рассмотрим на примере.
Как правило, НЕИЗВЕСТНО обрабатывается так же, как и состояние ЛОЖЬ. Если выбрать из таблицы строки и вычисление условия X=NULL в предложении WHERE дало результат НЕИЗВЕСТНО, то ни одной строки не будет получено. Но есть и различия: выражение НЕ(ЛОЖЬ) вернет истину, а НЕ(ИЗВЕСТНО) вернет НЕИЗВЕСТНО.Чаще всего с неизвестным результатом работают как с ЛОЖЬЮ:
При отрицании неизвестности результатом будет НЕИЗВЕСТНО:
Функция NULL в MySQL
В системе MySQL есть ряд функций, позволяющих результативно работать с NULL. Это IFNULL, NULLIF и COALESCE.
- IFNULL может принять два параметра: возвращает первый аргумент, если он не является NULL, в обратном случае — возвращает второй аргумент.
- NULLIF также может принять два аргумента: если они равны, то функция возвращает NULL, в обратном случае — возвращает первый аргумент. Эта функция также будет эффективна, если в вашей таблице в столбце есть пустые строки со значением NULL.
- COALESCE может принимать список аргументов и возвращать первый аргумент не-NULL. Например, эту функцию можно применять для базы контактных данных с потенциальной возможностью в зависимости от важности информации в порядке Телефон — Электронная почта — N/A.
Мы в GeekBrains каждый день обучаем людей новым профессиям и точно знаем, с какими трудностями они сталкиваются. Вместе с экспертами по построению карьеры поможем определиться с новой профессией, узнать, с чего начать, и преодолеть страх изменений.
Карьерная мастерская это:
- Список из 30 востребованных современных профессий.
- Долгосрочный план по развитию в той профессии, которая вам подходит.
- Список каналов для поиска работы.
- 3 теста на определение своих способностей и склонностей.
- Практику в разных профессиях на реальных задачах.
Уже 50 000 человек прошли мастерскую и сделали шаг к новой профессии!
Запишитесь на бесплатный курс и станьте ближе к новой карьере:
Зарегистрироваться и получить подарки
Операторы IN и NOT IN для значения NULL
Чтобы понять взаимодействие этих операторов с NULLом, рассмотрим пример.
Создадим таблицу Т, состоящую из одного числового столбца А и строками: 1, 2, 3 и NULL.
create table t as select column_value a from table(sys.odcinumberlist(1,2,3,null));
Затем выполним трассировку запроса (учтите, что для этого нужно обладать ролью PLUSTRACE).
От трассировки в листингах оставлена часть filter, чтобы показать преобразование указанных в запросе условий.
set autotrace on
Теперь, после подготовительных действий, попробуем выбрать те записи, которые будут соответствовать набору (1, 2, NULL).
По какой-то причине строка с NULLом не выбрана. Возможно, это случилось потому, что вычисление предиката «А»=TO_NUMBER(NULL) вернуло состояние НЕИЗВЕСТНО. Попробуем явно указать условие включения NULLов в результаты запросов:
Попробуем с NOT IN:
Ни одной записи так и не появилось.
Это объясняется тем, что трехзначная логика NOT IN не взаимодействует с NULLами: при попадании NULL в условия отбора данных можно не ждать.
Значение NULL и пустая строка в СУБД
Oracle отличается от стандартов ANSI SQL в определении NULLов: он проводит знак равенства между NULL и пустой строкой. Эта особенность программы рождает много споров, хотя Oracle и заявляет, что, возможно, в будущих релизах будет изменен подход в обработке пустой строки, как NULL. Но в реальности проведение таких изменений сомнительно, так как под эту СУБД написано неимоверное количество кода.
Если попытаться найти причину, почему вообще пустую строку стали считать эквивалентной NULL, то ответ можно найти в формате хранения varchar`ов и NULLов внутри блоков данных. Табличные строки Oracle хранит в структуре, представляющей собой заголовок и следующими за ним столбцы с данными.
Каждый столбец, в свою очередь, состоит из 2-х полей: длина данных в столбце (1 или 3 байта) и сами данные. При нулевой длине varchar2 в поле с данными нечего вносить, так как оно не занимает ни байта. В поле же, где указывается длина, вносится специальное значение 0xFF, что и означает отсутствие данных.
NULL Oracle представляет аналогично, то есть отсутствует поле с данными, а в поле длины данных вносится 0xFF. Так как изначально разработчики Oracle не разделяли эти два состояния, то и сейчас принцип внесения данных не изменился.
Значение NULL
Неопределенная длина пустой строки:
Сравнение с пустой строкой невозможно:
Критика такого подхода Oracle к значениям NULL и пустой строки, основывается на том, что не всегда пустая строка может означать неизвестность. Например, когда менеджер-продавец вносит данные в карточку клиента, то в поле «Контактный номер» он может указать конкретный номер; также он может указать, что номер неизвестен (NULL); но еще он может указать, что номера как такового нет (пустая строка).
С методом хранения пустых строк, предлагаемым Oracle, последний случай будет очень затруднительно осуществить. Если смотреть на этот довод критики через призму семантики, то звучит он очень убедительно. Но с другой стороны, каким образом менеджер сможет внести в поле «Контакты» пустую строку, и как в будущем он сможет отличить ее от «номер неизвестен» (NULL)?Отличия между null и undefined
Можно сказать, что NULL – это такое значение, которое является определенным для отсутствующего объекта. UNDEFINED же означает именно неопределенность. Например:
var element;
// значение переменной element до её инициализации не определённо: undefined
// здесь при попытке получения несуществующего элемента, метод getElementById возвращает null
// переменная element теперь инициализирована значением null, её значение определено
Осуществляя проверку на NULL или UNDEFINED, нужно помнить о разнице в операторах равенства (==) и идентичности (===): с первым оператором производится преобразование типов.
typeof undefined // undefined
null === undefined // false
null == undefined // true
Это все то, что вы должны знать о значении NULL. Обрастая опытом и применяя некоторые уловки для избежания NullPointerException, вы научитесь делать безопасный код. Главным образом неразбериха возникает из-за того, что NULL может трактоваться как пустое значение или как неидентифицированное.
Поэтому важно документально фиксировать поведение метода, когда есть входящее значение NULL. Держите в памяти, что NULL – это значение по умолчанию ссылочных переменных. И вызывать методы экземпляра или получать доступ к переменным экземпляра, применяя NULL-ссылку, вы не можете.
Специальное значение NULL означает отсутствие данных, констатацию того факта, что значение неизвестно. По умолчанию это значение могут принимать столбцы и переменные любых типов, если только на них не наложено ограничение NOT NULL . Также, СУБД автоматически добавляет ограничение NOT NULL к столбцам, включенным в первичный ключ таблицы.
Основная особенность NULLа заключается в том, что он не равен ничему, даже другому NULLу. С ним нельзя сравнить какое-либо значение с помощью любых операторов: = , < , > , like … Даже выражение NULL != NULL не будет истинным, ведь нельзя однозначно сравнить одну неизвестность с другой. Кстати, ложным это выражение тоже не будет, потому что при вычислении условий Oracle не ограничивается состояниями ИСТИНА и ЛОЖЬ . Из-за наличия элемента неопределённости в виде NULLа существует ещё одно состояние — НЕИЗВЕСТНО .
Таким образом, Oracle оперирует не двухзначной, а трёхзначной логикой. Эту особенность заложил в свою реляционную теорию дедушка Кодд, а Oracle, являясь реляционной СУБД, полностью следует его заветам. Чтобы не медитировать над “странными” результатами запросов, разработчику необходимо знать таблицу истинности трёхзначной логики. Ознакомиться с ней можно, например, на английской википедии: Three-valued_logic.
Для удобства сделаем процедуру, печатающую состояние булевого параметра:
Привычные операторы сравнения пасуют перед NULLом:
Существуют специальные операторы IS NULL и IS NOT NULL , которые позволяют производить сравнения с NULLами. IS NULL вернёт истину, если операнд имеет значение NULL и ложь, если он им не является.
Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:
Кроме того, есть пара исключений из правил, касающихся сравнений с отсутствующими значениями. Во-первых, — это функция DECODE , которая считает два NULLа эквивалентными друг другу. Во-вторых, — это составные индексы: если два ключа содержат пустые поля, но все их непустые поля равны, то Oracle считает эти два ключа эквивалентными.
DECODE идёт против системы:
Пример с составными индексами находится в параграфе про индексы.
Обычно, состояние НЕИЗВЕСТНО обрабатывается так же, как ЛОЖЬ . Например, если вы выбираете строки из таблицы и вычисление условия x = NULL в предложении WHERE дало результат НЕИЗВЕСТНО , то вы не получите ни одной строки. Однако, есть и отличие: если выражение НЕ(ЛОЖЬ) вернёт истину, то НЕ(НЕИЗВЕСТНО) вернёт НЕИЗВЕСТНО . Логические операторы AND и OR также имеют свои особенности при обработке неизвестного состояния. Конкретика в примере ниже.
В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ :
Отрицание неизвестности даёт неизвестность:
Оператор OR :
Оператор AND :
Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу T с одним числовым столбцом A и четырьмя строками: 1, 2, 3 и NULL
Включим трассировку запроса (для этого надо обладать ролью PLUSTRACE ).
В листингах от трассировки оставлена только часть filter, чтобы показать, во что разворачиваются указанные в запросе условия.
Предварительные действия закончены, давайте теперь поработаем с операторами. Попробуем выбрать все записи, которые входят в набор (1, 2, NULL) :
Как видим, строка с NULLом не выбралась. Произошло это из-за того, что вычисление предиката "A"=TO_NUMBER(NULL) вернуло состояние НЕИЗВЕСТНО . Для того, чтобы включить NULLы в результат запроса, придётся указать это явно:
Попробуем теперь с NOT IN :
Вообще ни одной записи! Давайте разберёмся, почему тройка не попала в результаты запроса. Посчитаем вручную фильтр, который применила СУБД, для случая A=3 :
Из-за особенностей трёхзначной логики NOT IN вообще не дружит с NULLами: как только NULL попал в условия отбора, данных не ждите.
Здесь Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. Тем более, говорить об этом они начали как минимум с седьмой версии СУБД (1992-1996 годы), а сейчас уже двенадцатая на подходе.
NULL и пустая строка эквивалентны:
непременный атрибут жёсткого спора:
Если последовать завету классика и посмотреть в корень, то причину эквивалентности пуcтой строки и NULLа можно найти в формате хранения varchar`ов и NULLов внутри блоков данных. Oracle хранит строки таблицы в структуре, состоящей из заголовка, за которым следуют столбцы данных. Каждый столбец представлен двумя полями: длина данных в столбце (1 или 3 байта) и, собственно, сами данные. Если varchar2 имеет нулевую длину, то в поле с данными писать нечего, оно не занимает ни байта, а в поле с длиной записывается специальное значение 0xFF , обозначающее отсутствие данных. NULL представлен точно так же: поле с данными отсутствует, а в поле с длиной записывается 0xFF . Разработчики Оракла могли бы, конечно, разделить эти два состояния, но так уж издревле у них повелось.
Лично мне эквивалентность пустой строки и NULLа кажется вполне естественной и логичной. Само название «пустая строка» подразумавает отсутствие значения, пустоту, дырку от бублика. NULL, в общем-то, обозначает то же самое. Но здесь есть неприятное следствие: если про пустую строку можно с уверенностью сказать, что её длина равна нулю, то длина NULLа никак не определена. Поэтому, выражение length('') вернёт вам NULL, а не ноль, как вы, очевидно, ожидали. Ещё одна проблема: нельзя сравнивать с пустой строкой. Выражение val = '' вернёт состояние НЕИЗВЕСТНО , так как, по сути, эквивалентно val = NULL .
Длина пустой строки не определена:
Сравнение с пустой строкой невозможно:
Критики подхода, предлагаемого Ораклом, говорят о том, что пустая строка не обязательно обозначает неизвестность. Например, менеджер по продажам заполняет карточку клиента. Он может указать его контактный телефон (555-123456), может указать, что он неизвестен (NULL), а может и указать, что контактный телефон отсутствует (пустая строка). С оракловым способом хранения пустых строк реализовать последний вариант будет проблемно. С точки зрения семантики довод правильный, но у меня на него всегда возникает вопрос, полного ответа на который я так и не получил: как менеджер введёт в поле «телефон» пустую строку и как он в дальнейшем отличит его от NULLа? Варианты, конечно, есть, но всё-таки…
Вообще-то, если говорить про PL/SQL, то где-то глубоко внутри его движка пустая строка и NULL различаются. Один из способов увидеть это связан с тем, что ассоциативные коллекции позволяют сохранить элемент с индексом '' (пустая строка), но не позволяют сохранить элемент с индексом NULL:
Использовать такие финты ушами на практике не стоит. Во избежание проблем лучше усвоить правило из доки: пустая строка и NULL в оракле неразличимы.
Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.
Задача. У Маши до замужества с Колей было неизвестное количество любовников. Коля знает, что после замужества у Маши был секс с ним, Сашей и Витей. Помогите найти Коле точное количество любовников Маши.
Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:
С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.
Почти все агрегатные функции, за исключением COUNT (и то не всегда), игнорируют пустые значения при расчётах. Если бы они этого не делали, то первый же залетевший NULL привёл бы результат функции к неизвестному значению. Возьмём для примера функцию SUM , которой необходимо просуммировать ряд (1, 3, null, 2) . Если бы она учитывала пустые значения, то мы бы получили такую последовательность действий:
1 + 3 = 4; 4 + null = null; null + 2 = null .
Вряд ли вас устроит такой расчёт при вычислении агрегатов, ведь вы наверняка не это хотели получить. А какой бы был геморрой с построением хранилищ данных… Бррррр…
Таблица с данными. Используется ниже много раз:
Пустые значения игнорируются агрегатами:
Функция подсчёта количества строк COUNT , если используется в виде COUNT(*) или COUNT(константа) , будет учитывать пустые значения. Однако, если она используется в виде COUNT(выражение) , то пустые значения будут игнорироваться.
с константой:
С выражением:
Также, следует быть осторожным с функциями вроде AVG . Поскольку она проигнорирует пустые значения, результат по полю N будет равен (1+3+2)/3 , а не (1+3+2)/4 . Возможно, такой расчёт среднего вам не нужен. Для решения подобных проблем есть стандартное решение — воспользоваться функцией NVL :
Агрегатные функции возвращают состояние НЕИЗВЕСТНО , если они применяются к пустому набору данных, либо если он состоит только из NULLов. Исключение составляют предназначенные для подсчёта количества строк функции REGR_COUNT и COUNT(выражение) . Они в перечисленных выше случаях вернут ноль.
Набор данных только из NULLов:
Пустой набор данных:
NULL в OLAP
Очень коротко ещё об одной особенности, связанной с агрегатами. В многомерных кубах NULL в результах запроса может означать как отсутствие данных, так и признак группировки по измерению. Самое противное, что на глаз эти две его ипостаси никак не различишь. К счастью, есть специальные функции GROUPING и GROUPING_ID , у которых глаз острее. GROUPING(столбец) вернёт единицу, если NULL в столбце измерения означает признак группировки по этому столбцу и ноль, если там содержится конкретное значение (в частности, NULL). Функция GROUPING_ID — это битовый вектор из GROUPING ов, в этой заметке она точно лишняя.
В общем, такая вот краткая и сумбурная информация про дуализм NULLа в многомерном анализе. Ниже пример использования GROUPING , а за подробностями велкам ту Data Warehousing Guide, глава 21.
Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:
Несколько общих функций, связанных со значениями NULL:
1. LNNVL (состояние) (отрицательное)
Функция: Используется в условии в предложении where инструкции, если условие имеет значение True, оно возвращает False, если условие Unknown или False, оно возвращает True. Эта функция не может использоваться в сложных условиях, таких как AND, OR, BETWEEN.
Пример: select * from A, где innvl (год не равен нулю); // Извлечь запись, где год пуст в таблице A
select * from A, где innvl (year! = 12345); // Год в таблице A равен 12345 или равен нулю
Применение: Условия, когда xnxq или kkxq таблиц pyfa_kcxx и kkjh_kcxx различны (не равны и не пусты):
lnnvl(pyfa_kcxx.xnxq = kkjh_kcxx.xnxq) and (pyfa_kcxx.xnxq is not null or kkjh_kcxx.xnxq is not null))
or (lnnvl(pyfa_kcxx.kkxq = kkjh_kcxx.kkxq) and (pyfa_kcxx.kkxq is not null or kkjh_kcxx.kkxq is not null))
Роль: типы данных двух параметров должны быть совместимы, чтобы определить, является ли оригинал пустым, если он пуст, затем вернуть значение ifnull, в противном случае вернуть оригинал.
1. Если параметр 1 является символьным типом, тогда преобразуйте параметр 2 в тип параметра 1, возвращаемое значение - VARCHAR2.
2. Если параметр 1 является числовым, то определите наивысший числовой приоритет двух параметров (например, действительные числа двойной точности имеют более высокий приоритет, чем действительные числа одинарной точности), а затем преобразуйте их в значения с высоким приоритетом и верните значение типа
Функция: определить, является ли оригинал пустым, если он пуст, он возвращает значение ifnull, в противном случае он возвращает значение ifnotnull.
Примечания: Если типы ifnotnull и ifnull различаются, ifnull будет преобразован в тип ifnotnull, и если его невозможно преобразовать, будет сообщено об ошибке.
Роль: если expr1 и expr2 равны, вернуть ноль, иначе вернуть expr1
5、COALESCE (expr1,expr2,expr3. exprm)
Эффект: возвращает первое непустое выражение в выражении или ноль, если все они пусты.
Примечание. Все выражения должны быть одного типа или могут быть неявно преобразованы в один и тот же тип, в противном случае будет сообщено об ошибке.
Проблемы, вызванные неявным преобразованием Oracle:
Неявное преобразование может быть выполнено между NAME и VARCHAR2, где VARCHAR2-> NUMBER не приведет к сбою индекса, а NUMBER-> VARCHAR2 сделает индекс недействительным;
Неявное преобразование типов данных, вызванное несогласованностью типов данных VARCHAR2 и NVARCHAR2, приведет к сбою индекса.
Вопрос: Как вернуть значение столбца: первое, второе, третье и.т.д перед максимальным значением?
Допустим есть таблица test_for_find со следующими значениями столбца:
Необходимо написать запрос, который должен возвратить число 6,
как первое перед максимальным (число 7), число 5 как второе перед максимальным и.т.д
Условие: без использования PL/SQL и подзапросов.
--------------------------------------------------------------------------------------
Для наилучшего понимания создадим таблицу, заполним данными и создадим индекс:
Как известно для определения максимального значения столбца, используется функция max().Чтобы определить максимальное значение необходим следующий запрос:
Но нам необходимо не максимальное, а значение перед максимальным:
Однако непонятно, как таким запросом определить второе перед максимальным, тем более что уже было нарушено условие - "без подзапросов"? Очевидно что в условии запроса, должно быть дополнительное условие, которое бы указывало на порядковый номер этого значения в упорядоченном списке значений. Как известно, для упорядочивания используется фраза "order by". С её помошью можно упорядочить данные по убыванию, а используя псевдостолбец ROWNUM - можно ограничить количество строк. Если порядочить данные по убыванию и указать ROWNUM<4, то запрос должен вернуть три последних значения:7,6,5. Применив же функцию min() - мы определим, что второе перед максимальным - это 5.
Запрос же такой:
Однако, на практике убеждаемся что, это не так:
Почему же не сработала логика наших рассуждений? Всё дело в том, что ROWNUM срабатывает раньше, чем упорядочивание(т.е действие фразы order by). Значение ROWNUM присваивается строке до выполнения
сортировки строк. Поэтому определение минимального значения происходит из случайного набор данных:
Отсюда вывод: что-бы использовать ROWNUM, нам необходимо иметь заранее упорядоченный список значений. Для это изменим запрос так:
Теперь запрос работает правильно. Однако, опять же нарушается правило "без подзапроса". Что же делать, где еще можно взять такой список? - конечно из индекса. Для того что-бы правильно решить задачу необходимо использовать хинты. А точнее хинт, который позволяет конкретно указать на использование индекса, в котором данные уже упорядочены по убыванию. Для того что-бы задействовать индекс, столбец serial_key обязательно должен присутствовать во фразе "WHERE":
Третий перед максимальным:
и.т.д. Теперь запрос удовлетворяет поставленным условиям.
1.ошибка номер раз: если какое либо значение повторяется в таблице больше одного раза - то возможен случай когда предшествующее максимальному будет такое же как и max.
поэтому
[code]
declare @_TEMP table (NUM int)
insert @_TEMP(NUM)
select 1
union all
select 5
union all
select 2
union all
select 7
union all
select 7
union all
select 9
union all
select 2
union all
select 6
select t2.NUM
from (
select t.NUM, row_number() over (order by t.NUM desc) NN
from (
select distinct NUM
from @_TEMP
) t
)t2
where
t2.NN = 3[/code]
Имхо, решение с индексом и хинтом абсолютно ненадежное и не дай бог применять подобную логику в продакшене.
Не врублюсь, а разве нижеприведенный запрос не решает задачу.
SELECT MAX(serial_key) FROM test_for_find t WHERE serial_key < 7
Я бы не стал полагаться на индексы, а в данном случае на каждую таблицу должен быть индекс по этому полю! А если такого нет, или его удалили!?
Да и rownum не желательно использовать.
Тем более мы не знаем, какое из значений максимальное! Следовательно, прибивать гвоздями (and rownum < 5) - глупо!
С помощью ранжирующей функции решение вопроса становиться проще:
SELECT serial_key - 1 serial_key,
row_number () OVER (ORDER BY (serial_key) DESC) dr
FROM test_for_find
WHERE serial_key - 1 <> 0;
select * FROM test_for_find
А из результата ориентируясь по полю dr, можно выбирать какое угодно значение перед максимальным.
В подзапросе для каждой компании данные сортируются случайным образом при использовании функции newid(), после чего выбирается одна (первая строка) этого отсортированного набора:
Разумеется, вы скорее всего получите другой результат, но, поскольку данных в таблице немного, рано или поздно вы сможете получить и такой. :-)
Использование функции FIRST_VALUE
Эта оконная функция возвращает первое из упорядоченного набора значений. Теперь мы можем сделать все без подзапросов, выделив в окне набор рейсов для компании из текущей строки запроса с помощью предложения PARTITION BY и упорядочив его, как и в предыдущем примере, случайным образом в предложении ORDER BY:
Ключевое слово DISTINCT нужно здесь для того, чтобы не повторять одну и ту же компанию для каждого выполняемого ею рейса.
Засада с LAST_VALUE
Казалось бы, какая разница брать первое или последнее значение из случайным образом упорядоченного набора? Но давайте посмотрим, что мы получим, если в предыдущем запросе заменить FIRST_VALUE на LAST_VALUE:
Я приведу результаты только для id_comp = 1. Вы можете сами выполнить запрос, чтобы убедиться, что будут выводиться абсолютно все рейсы из таблицы Trip.
Что мы делаем в подобных случаях? Конечно, обращаемся к документации, а там мы читаем. Нет, постойте, сначала полный синтаксис:
Здесь
IGNORE NULLS или RESPECT NULLS определяют, будут ли учитываться NULL-значения;
предложение_rows_range задает параметры окна.
А теперь читаем:
Диапазоном по умолчанию является RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Т.е. окном является диапазон от текущей строки и неограниченно выше. Поскольку мы выбираем последнюю строку диапазона, то всегда будет выводиться текущая строка, как бы не сортировались строки. Т.е. сколько бы строк выше не оказалось при случайной сортировке.
Потому и DISTINCT не помогает, т.к. все выводимые строки оказываются уникальными.
Значит нам просто нужно явно (и правильно!) задать параметры окна, а именно, от текущей строки и неограниченно ниже, поскольку мы выбираем последнее значение:
Читайте также: