Sql oracle получить первую строку
Обновлено: 06.07.2024
ROWNUM - волшебный столбец базы данных Oracle, доставляющий много хлопот. Но когда вы узнаете, что это такое и как работает, ROWNUM может оказаться полезным. Я использую его для достижения двух целей:
1. Выполните обработку TOP-N. Это похоже на функцию оператора Limit в других базах данных.
После того, как я рассмотрю, как работает ROWNUM, я рассмотрю эти способы использования.
2. КАК РАБОТАЕТ ROWNUM
ROWNUM - это виртуальный столбец (не настоящий столбец), Может использоваться в запросах. ROWNUM будет преобразован в числа 1, 2, 3, 4, . N, где N - количество строк в наборе, используемом с ROWNUM. Значение ROWNUM не всегда будет присвоено строке (записи). (Это распространенное заблуждение). Строке (записи) в таблице нет соответствующего номера, вы не можете попросить взять пятую строку из таблицы - такого нет.
Также многих сбивает с толку, когда фактически присваивается значение ROWNUM.Значение ROWNUM присваивается строке после прохождения фазы предиката запроса, но до того, как запрос выполнит какую-либо сортировку или агрегирование.. Кроме того, значение ROWNUM увеличивается только после выделения, поэтому следующий запрос никогда не возвращает строки:
Поскольку первая строка в таблице не удовлетворяет требованиям ROWNUM> 1, ROWNUM не вырастет до 2. Следовательно, никогда не будет ситуации, когда ROWNUM больше 1.
Рассмотрим запрос со следующей структурой:
Вышеуказанные операторы будут выполняться в следующем порядке:
- Оператор FROM / WHERE выполняется первым
- ROWNUM выделяется и увеличивается на каждую выходную строку предложения FROM / WHERE.
- SELECT применяется
- GROUP BY применяется
- HAVING применяется
- ORDER BY применяется
Вот почему запрос следующей формы почти наверняка является ошибкой:
Вышеупомянутый запрос изначально был предназначен для поиска 5 самых высокооплачиваемых людей - запрос TOP N. Тем не менее, запрос вернет пять случайных записей (первые пять, с которыми он столкнулся),
Сортировать по зарплате. Псевдокод процесса запроса выглядит следующим образом:
Он получает первые пять записей и затем сортирует их. Запрос с WHERE ROWNUM = 5 или WHERE ROWNUM> 5 не имеет смысла. Это связано с тем, что значение ROWNUM присваивается строке во время оценки предиката и увеличивается только после того, как строка передает предложение WHERE.
Правильная версия этого запроса:
Эта версия отсортирует EMP в порядке убывания зарплаты, а затем вернет первые пять обнаруженных записей (первые пять записей). Как вы увидите в предстоящих обсуждениях Top N, Oracle Database на самом деле не сортирует весь набор результатов - он умнее этого - но концептуально именно это и произошло.
3. Обработка запросов Top-N с ROWNUM (Обработка запросов Top-N с ROWNUM)
В запросе TOP-N вы обычно хотите выполнить сложный запрос, отсортировать, а затем получить N верхних строк (верхние N строк).
ROWNUM имеет N лучших оптимизаций, которые могут облегчить этот тип запроса. Вы можете использовать ROWNUM, чтобы избежать сортировки больших коллекций. Я расскажу, как это делается концептуально, а затем рассмотрю пример.
Предположим, у вас есть запрос в такой форме:
Предположим, этот запрос возвращает большой объем данных: тысячи, сотни тысяч или более строк. Однако вас интересуют только Top-N (например, топ-10 или топ-100). Есть два способа решить эту проблему:
- Пусть клиентское приложение выполнит запрос и получит только первые N строк.
- Используйте запрос как встроенное представление и используйте ROWNUM для ограничения результатов, например SELECT * FROM（your_query_here）WHERE ROWNUM <= N.
По двум причинам второй метод намного превосходит первый. Меньшая из этих двух причин состоит в том, что клиенту требуется меньше работы, поскольку база данных отвечает за ограничение набора результатов. Более важная причина заключается в том, что специальная обработка, которую может выполнить база данных, может предоставить вам только первые N строк. Использование запросов top-N означает, что вы предоставили базе данных дополнительную информацию. Вы говорите ему: «Меня интересует только N строк; остальные я никогда не буду рассматривать». Теперь, прежде чем вы подумаете о сортировке - как работает сортировка и что нужно делать серверу, это не звучит слишком шокирующе. Давайте обсудим эти два метода с примерами запросов:
Теперь предположим, что T - это большая таблица с более чем одним миллионом записей, и каждая запись «толстая», то есть 100 или более байтов. Также предполагается, что UNINDEXED_COLUMN, как следует из названия, является неиндексированным столбцом. Предположим, вы хотите получить только первые 10 строк. База данных Oracle будет выполнять следующие операции:
- Запустить полное сканирование таблицы на T
- Сортировать по UNINDEXED_COLUMN T. Это полная сортировка.
- У вас должна закончиться память области сортировки, и вам потребуется переместить временный экстент на диск.
- Объедините временную область расширения, чтобы получить первые 10 записей во время запроса.
- Очистите (освободите) область временного расширения после завершения.
Теперь это создает много операций ввода-вывода. База данных Oracle, вероятно, скопирует всю таблицу в TEMP и запишет ее, просто чтобы получить первые 10 строк .
Затем давайте посмотрим, что может делать база данных Oracle концептуально с помощью запросов top-N:
В этом случае база данных Oracle предпримет следующие шаги:
- Выполните полное сканирование таблицы на T, как и раньше (вы не можете избежать этого шага).
- В массиве из N элементов (возможно, на этот раз в памяти) отсортируйте только: N строк.
Первые N строк заполнят этот массив строк в отсортированном порядке. При получении N + 1 строк она будет сравниваться с последней строкой в массиве. Если он входит в слот N + 1 в массиве, он будет брошен. В противном случае он будет добавлен в этот массив и отсортирован, а одна из существующих строк будет отброшена. Ваша область сортировки содержит не более N строк, поэтому вы можете отсортировать N строк вместо сортировки миллиона строк.
Кажущиеся незначительными детали использования концепции массива и сортировки только N строк могут принести огромный выигрыш в производительности и использовании ресурсов. ОЗУ, необходимое для сортировки 10 строк, меньше ОЗУ, необходимого для сортировки 1 миллиона строк (не говоря уже об использовании пространства TEMP).
Используя приведенную ниже таблицу T, вы можете увидеть, что, хотя эти два метода достигают одинаковых результатов, они используют совершенно разное количество ресурсов:
Затем используйте ROWNUM для выполнения вашего верхнего N запроса:
Наконец, запустите запрос «сделай сам», чтобы получить только первые 10 записей:
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.04 0 949 0 10
total 4 0.04 0.04 0 949 0 10
Rows Row Source Operation
10 COUNT STOPKEY (cr=949 pr=0 pw=0 time=46997 us)
10 VIEW (cr=949 pr=0 pw=0 time=46979 us)
10 SORT ORDER BY STOPKEY (cr=949 pr=0 pw=0 time=46961 us)
100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400066 us)
Запрос читает всю таблицу (потому что он должен), но с помощью шага SORT ORDER BY STOPKEY он может временно
Использование пространства ограничено 10 строками. Обратите внимание на последнюю строку операции источника строки -
Он показывает, что запрос выполнил в общей сложности 949 логических операций ввода-вывода (cr = 949),
не выполнял физическое чтение или запись (pr = 0 и pw = 0),
, и это заняло 400066 миллионных долей секунды (0,04 секунды).
Сравните его с самостоятельным методом, показанным в листинге 2.
3.2 Листинг 2: запрос "Сделай сам" без ROWNUM
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 10 0.35 0.40 155 949 6 10
total 13 0.36 0.40 155 949 6 10
Rows Row Source Operation
10 SORT ORDER BY (cr=949 pr=155 pw=891 time=401610 us)
100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400060 us)
Истекшее время включает ожидание следующих событий:
Event waited on Times
direct path write temp 33
direct path read temp 5
Как видите, этот результат сильно отличается. Стоит отметить, что затраченное время / процессорное время значительно выше, а последняя строка исходной операции строки дает представление о ее причине. Вы должны отсортировать диск, вы можете использовать pw = 891 (физическая запись) для просмотра. Ваш запрос выполнил несколько операций чтения и записи по прямому пути - 100 000 записей (а не только 10, которые нам в конечном итоге были интересны) произошли на диске, что значительно увеличило время выполнения / использование ресурсов запроса.
4. Используйте подкачку ROWNUM
Мое любимое использование ROWNUM - пейджинг. В этом случае я использую ROWNUM для получения строк от N до M. Общий вид выглядит следующим образом:
FIRST_ROWS (N) сообщает оптимизатору: «Эй, я хочу получить первую строку. Я получу N как можно быстрее».
: MAX_ROW_TO_FETCH устанавливается в последнюю строку набора результатов для получения - если вы хотите от 50 до 60 строк набора результатов, установите его на 60.
: MIN_ROW_TO_FETCH устанавливается на первую строку получаемого набора результатов, поэтому для получения строк с 50 по 60 вы можете установить его на 50.
Концепция этого сценария заключается в том, что конечный пользователь с веб-браузером завершил поиск и ожидает результатов. Первая страница результатов (и вторая страница и т. Д.) Должна быть возвращена как можно скорее. Если вы внимательно посмотрите на запрос, вы заметите, что он содержит верхние N запросов (получите первый из запроса: MAX_ROW_TO_FETCH rows), так что вы можете извлечь выгоду из оптимизации первых N запросов, которую я только что описал. Кроме того, он возвращает только определенные строки, представляющие интерес для клиента по сети, - он удаляет все ведущие строки, которые не представляют интереса, из набора результатов.
При использовании этого запроса на разбиение на страницы важно, чтобы оператор ORDER BY был отсортирован в уникальном порядке. Если сортируемые вами вещи не уникальны, вам следует добавить что-нибудь в конце ORDER BY, чтобы добиться этого. Например, если вы отсортируете 100 записей по SALARY, и все они будут иметь одинаковое значение SALARY, то указание строк с 20 по 25 фактически не имеет смысла. Чтобы проиллюстрировать этот момент, здесь мы используем небольшую таблицу с большим количеством повторяющихся значений идентификаторов:
Затем запросите строки с 148 по 150 и 151 после сортировки по столбцу идентификатора:
0 38 148
0 64 149
0 53 150
0 59 148
0 38 149
0 64 150
0 53 151
Обратите внимание, что в этом случае для одной из строк 148 результат возвращает DATA = 38, а в следующий раз результат возвращает DATA = 59. В соответствии с вашими требованиями оба запроса возвращают правильный ответ: отсортируйте данные по идентификатору, выбросите первые 147 строк, а затем верните следующие 3 или 4 строки.Оба они делают это, но поскольку идентификатор имеет много повторяющихся значений, запрос не может быть выполнен детерминированно - один и тот же порядок сортировки не может быть гарантирован от выполнения к запуску запроса. Чтобы решить эту проблему, вам нужно добавить контент, уникальный для ORDER BY. В этом случае просто используйте ROWID:
0 45 148
0 99 149
0 41 150
0 45 148
0 99 149
0 41 150
0 45 151
Теперь запрос очень детерминированный. ROWID уникален в таблице, поэтому, если вы используете ORDER BY ID
Затем используйте ORDER BY ROWID в идентификаторе, строки будут иметь четкий детерминированный порядок, и запрос будет разбит на страницы
вернет строки, как ожидалось, с уверенностью.
Пять: сводка ROWNUM
Интеллектуальная рекомендация
Michael.W Поговорите о Hyperledger Fabric. Проблема 20 - Подробная индивидуальная сортировка узла с пятью порядками с исходным кодом для чтения.
Michael.W Поговорите о Hyperledger Fabric. Проблема 20 - Подробная индивидуальная сортировка узла с пятью порядками с исходным кодом чтения Fabric Файл исходного кода одиночного режима находится в ord.
Мяу Пасс Матрица SDUT
Мяу Пасс Матрица SDUT Time Limit: 1000 ms Memory Limit: 65536 KiB Submit Statistic Problem Description Лянцзян получил матрицу, но эта матрица была особенно уродливой, и Лянцзян испытал отвращение. Чт.
Гессенская легкая двоичная структура удаленного вызова
Hessian - это легкая двоичная структура удаленного вызова, официальный адрес документа, в основном он включает протокол удаленного вызова Hessian, протокол сериализации Hessian, прокси-сервер клиента .
TCP Pasket и распаковка и Нетти Solutions
Основные введение TCP является ориентированным на соединение, обеспечивая высокую надежность услуг. На обоих концах (клиенты и терминалы сервера) должны иметь один или более гнезда, так что передающий.
В подзапросе для каждой компании данные сортируются случайным образом при использовании функции 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 не помогает, т.к. все выводимые строки оказываются уникальными.
Значит нам просто нужно явно (и правильно!) задать параметры окна, а именно, от текущей строки и неограниченно ниже, поскольку мы выбираем последнее значение:
Я хотел бы иметь только первую строку на Foo и игнорировать остальные.
как мне это сделать?
Я мог бы не согласиться с rjmunru в том, что использование ansii style joins часто может быть легче читать, чем подзапросы, но каждому своему-я просто следую тому, что говорят наши DBAs.
Если вам просто нужен первый результат запроса, вы можете использовать rownum (если вы используете oracle, другие базы данных, вероятно, имеют что-то подобное).
выбрать * из foo_t f где f.bar = 'bleh' и rownum = 1
конечно, предложение HAVING также может быть уместным, в зависимости от того, что вы пытаетесь сделать.
"HAVING используется для выполнения действия над группами, созданными GROUP BY, аналогично предложению WHERE для строк в базовой инструкции SQL. Предложение WHERE ограничивает вычисляемые строки. Предложение HAVING ограничивает возвращаемые сгруппированные строки."
просто группа игроков.Ник один, и выберите первый (мин) из описания
то есть, если вы хотите всегда первый, не зная об этом
Это старый пост, но у меня была та же проблема сегодня. Я решил его, попробовав много запросов, пока он не сработает. Я использую SQL Compact 3.5 с visual basic 2010.
этот пример для таблицы с именем "TESTMAX" со столбцами " Id "(первичный ключ)," nom "(имя) и" значение", вы можете использовать это для получения строк с максимальным" значением "для каждого" nom":
Если вы хотите удалить другие строки, вы также можете использовать:
В общем, попробуйте использовать подзапросы, а не объединение и группирование - это часто делает SQL намного проще понять.
Это возможность использовать предложение "иметь"? (Вы хотите различать агрегатную функцию - "Sum")?
(отредактировано на основе отредактированного вопроса) Затем, поскольку вы хотите фильтровать на основе значения агрегированного столбца, вам нужно предложение Having.
любопытно. Единственный способ заставить это работать - использовать временную таблицу хранения в памяти. (TSQL не синтаксис)
SQL Server 2005 вы можете использовать это:
declare @sometable table (foo int, bar int, value int )
вставить в @sometable значения (1, 5, 10) вставить в @sometable значения (1, 4, 20) вставить в @sometable значения (2, 1, 1) вставить в @sometable значения (2, 1, 10) вставить в @sometable значения (2, 1, 1) вставить в @sometable значения (2, 2, 13) вставить в @sometable значения (3, 4, 25) вставить в @sometable значения (3, 5, 1) вставить в @sometable значения (3, 1, Один) вставить в @sometable значения (3, 1, 1) вставить в @sometable значения (3, 1, 1) вставить в @sometable значения (3, 1, 1) вставить в @sometable значения (3, 1, 1)
-- temp таблица для начальной агрегации объявить таблицу @t2 (foo int, bar int, sums int) вставить в @Т2 выберите foo, bar, sum(значение) от @sometable группа по foo, bar
Требуется ограничить число возвращаемых запросом строк. Порядок не имеет значения; подойдут любые n строк.
Решение
Для ограничения числа возвращаемых строк в Oracle приходится использовать функцию ROWNUM, возвращающую порядковый номер каждой строки результирующего множества (возвращающую, начиная с 1, величину).
Рассмотрим, то происходит при использовании ROWNUM <= 5 для возвращения первых пяти строк:
- Oracle выполняет запрос.
- Oracle извлекает первую строку и называет ее строкой номер 1.
- Номер строки больше 5? Если нет, Oracle возвращает строку, потому что она отвечает критерию: ее порядковый номер меньше или равен 5. Если да, Oracle не возвращает строку.
- Oracle извлекает следующую строку и присваивает ей слудущий порядковый номер по возрастанию (2, затем 3, затем 4 и т.д.).
- Переходим к шгу 3.
Как видно из данного процесса, присвоение значений, возвращаемых функцией ROWNUM, происходит после извлечения очередной строки. Это очень важно и является ключевым моментом. Многие разработчики на Oracle пытаются реализовать извлечение только, скажем, пятой возвращенной запросом строки, задавая ROWNUM = 5. Такое использование условия равенства в сочетании с ROWNUM является неверным. При попытке возвратить пятую строку с помощью ROWNUM = 5 роисходит следующее:
- Oracle выполняет запрос.
- Oracle извлекает первую строку и называет ее строкой номер 1.
- Номер строки равен 5? Если нет, Oracle отбрасывает строку, потому что она не отвечает заданному критерию. Если да, Oracle возвращает строку. Но ответ всегда будет отрицательным!
- Oracle извлекает следующую строку и называет ее строкой номер 1, поскольку первая возвращенная запросом строка должна быть пронумерована как первая строка.
- Переходим к шагу 3.
После тщательного разбора этого процесса становится понятно, почему использование ROWNUM = 5 не обеспечивает возвращения пятой строки. Невозможно получить пятую строку, не возвратив перед этим строки с первой по четвертую!
Однако заметьте, что с помощью ROWNUM = 1 можно получить первую строку. Может показаться, что это противоречит приведенному выше объяснению. Причина, почему ROWNUM = 1 обеспечивает возвращени первой строки, в том, что Oracle для определения наличия строк в таблице приходится извлекать, по крайней мере, одну из них. Внимательно проанализируйте предыдущий процесс, подставив 1 вместо 5, и вы поймете, почему для возвращения одной строки можно в качестве условия задавать ROWNUM = 1.
Tags: Ограничение числа возвращаемых строк
PL/SQL
Читайте также: