Как в sql ограничить количество выводимых записей
Перейти к содержимому

Как в sql ограничить количество выводимых записей

  • автор:

Ограничение результатов поиска с помощью RANK

Функции CONTAINSTABLE и FREETEXTTABLE возвращают столбец с именем, RANK содержащий порядковые значения от 0 до 1000 (значения ранжирования). Эти значения используются для ранжирования возвращенных строк согласно их соответствию критерию выбора. Ранжирующие значения указывают только относительный порядок релевантности строк в результирующем наборе, при этом чем меньше значение, тем меньше релевантность. Фактические значения несущественны и, как правило, различны для каждого выполнения запроса.

FREETEXT И CONTAINS предикаты не возвращают значения ранжирования.

Количество элементов, соответствующих условию поиска, часто большое. Чтобы запретить CONTAINSTABLE или FREETEXTTABLE запрашивать слишком много совпадений, используйте необязательный параметр top_n_by_rank , который возвращает только подмножество строк. top_n_by_rank представляет собой целочисленное значение, n, указывающее, что возвращаются только совпадения с наибольшим рейтингом n в порядке убывания. Если параметр top_n_by_rank скомбинирован с другими параметрами, то запрос может вернуть меньше строк, чем фактически соответствует всем предикатам.

SQL Server упорядочивает совпадения по рангу и возвращает только указанное количество строк. Это может привести к значительному повышению производительности. Например, запрос, который обычно возвращает 100 000 строк из таблицы 1000 000 строк, обрабатывается быстрее, если запрашиваются только первые 100 строк.

Примеры использования RANK для ограничения результатов поиска

Пример А. Поиск только трех соответствий с максимальным рангом

В следующем примере используется CONTAINSTABLE для возврата только трех первых совпадений.

USE AdventureWorks2022; GO SELECT K.RANK, AddressLine1, City FROM Person.Address AS A INNER JOIN CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("des*", Rue WEIGHT(0.5), Bouchers WEIGHT(0.9))', 3) AS K ON A.AddressID = K.[KEY]; GO 
RANK Address City ----------- -------------------------------- ------------------------------ 172 9005, rue des Bouchers Paris 172 5, rue des Bouchers Orleans 172 5, rue des Bouchers Metz 

Пример B. Поиск первых пяти совпадений

В следующем примере используется CONTAINSTABLE для возврата описания первых пяти продуктов, где Description столбец содержит слово «алюминиевая» рядом с словом или словом light lightweight .

USE AdventureWorks2022; GO SELECT FT_TBL.ProductDescriptionID, FT_TBL.Description, KEY_TBL.RANK FROM Production.ProductDescription AS FT_TBL INNER JOIN CONTAINSTABLE(Production.ProductDescription, Description, '(light NEAR aluminum) OR (lightweight NEAR aluminum)', 5) AS KEY_TBL ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]; GO 

Как ранжируются результаты поиска

Полнотекстовый поиск в SQL Server может создать необязательную оценку (или значение ранжирования), указывающую релевантность данных, возвращаемых полнотекстовый запросом. Это ранжирующее значение вычисляется для каждой строки и может использоваться как критерий упорядочения для сортировки результирующего набора данного запроса по релевантности. Ранжирующие значения показывают только относительный порядок релевантности строк в результирующем наборе. Фактические значения несущественны и, как правило, различны для каждого выполнения запроса. Значение ранжирования не содержит никакой важности в запросах.

Статистика ранжирования

При построении индекса собираются статистические данные, которые будут использоваться в ранжировании. Процесс создания полнотекстового каталога не приводит непосредственно к одной структуре индекса. Вместо этого подсистема полнотекстового текста для SQL Server создает промежуточные индексы при индексировании данных. Затем средство полнотекстового поиска выполняет слияние этих индексов в больший индекс по мере необходимости. Этот процесс может повторяться много раз. После этого средство полнотекстового поиска выполняет слияние в единый файл, объединяющее все промежуточные индексы в один большой главный индекс.

Статистические данные собираются на каждом уровне промежуточных индексов. Слияние этих данных производится при слиянии индексов. Некоторые статистические значения могут формироваться только в процессе создания главного индекса.

Хотя SQL Server ранжирует результирующий набор запросов, он использует статистику из крупнейшего промежуточного индекса. Это зависит от того, объединяются ли промежуточные индексы. В результате статистика ранжирования может отличаться в точности, если промежуточные индексы не объединены. По этой причине один и тот же запрос в разное время может возвращать различные результаты ранжирования по мере добавления, изменения и удаления данных, а также по мере слияния небольших индексов.

Чтобы свести к минимуму размер индекса и сложность вычислений, статистические данные часто округляются.

В следующем списке содержатся некоторые часто используемые термины и статистические значения, важные для вычисления ранга.

Термин / значение Description
Свойство Полнотекстовый индексированный столбец строки.
Документ Сущность, возвращаемая в запросах. В SQL Server это соответствует строке. Документ может иметь несколько свойств точно так же, как и строка может иметь несколько полнотекстовых индексированных столбцов.
Указатель Один инвертированный индекс одного или нескольких документов. Это может быть полностью в памяти или на диске. Многие статистические данные относятся к конкретному индексу, в котором было обнаружено соответствие.
Полнотекстовый каталог Коллекция промежуточных индексов, которые для запросов считаются одной сущностью. Каталоги — это подразделение организации, видимое администратору SQL Server.
Word, токен или элемент Единица проверки соответствия в средстве полнотекстового поиска. Потоки текста из документов формируются в слова или в токены согласно правилам конкретного языка.
Наличие Смещение слова в свойстве документа, определенное средством разбиения по словам. Первое слово имеет вхождение в позиции 1, следующее — в позиции 2 и т.д. Чтобы избежать ложных положительных результатов в запросах по сходству и запросах фраз, в конце предложения и абзаца используется увеличенный промежуток между вхождениями.
TermFrequency Количество вхождений значения ключа в строку.
IndexedRowCount Общее число индексированных строк. Вычисляется на основе счетчиков в промежуточных индексах. Точность этого числа может быть различной.
KeyRowCount Общее число строк в полнотекстовом каталоге, содержащем конкретный ключ.
MaxOccurrence Наибольшее число вхождений для конкретного свойства в строке, хранящееся в полнотекстовом каталоге.
MaxQueryRank Максимальный ранг (1000), возвращенный средством полнотекстового поиска.

Проблемы с вычислением ранга

Процесс вычисления ранга зависит от многих факторов. Средства разбиения по словам в различных языках по-разному разбивают текст на лексемы. Например, строку «dog-house» одно средство разбиения по словам может разбить на «dog» и «house», а другое — на «dog-house». Это означает, что сопоставление и ранжирование зависят от указанного языка, так как не только слова отличаются, но и длина документа. Разница в длине документа может повлиять на ранжирование во всех запросах.

Такие статистические данные, как IndexRowCount , могут различаться в широких пределах. Например, если каталог имеет 2 миллиарда строк в главном индексе, то новый документ индексируется хранящимся в памяти индексом. Поэтому ранги для этого документа, вычисленные на основе количества документов в индексе, хранящемся в памяти, могут отличаться от рангов для документов из главного индекса. По этой причине рекомендуется после того, как любое население приводит к индексации или повторному индексации большого количества строк, вы объединяете индексы в главный индекс с помощью ALTER FULLTEXT CATALOG . REORGANIZE инструкции Transact-SQL. Подсистема полнотекстового текста также автоматически объединяет индексы на основе параметров, таких как число и размер промежуточных индексов.

ЗначенияMaxOccurrence нормализуются в один из 32 диапазонов. Это означает, например, что документ длиной 50 слов обрабатывается так же, как и документ 100 слов. Ниже приведена таблица, используемая для нормализации. Поскольку длина документа находится в диапазоне между смежными значениями таблицы 32 и 128, они эффективно обрабатываются как имеющие ту же длину, 128 (32 = 128 < docLength <).

Ранг CONTAINSTABLE

РанжированиеCONTAINSTABLE использует следующий алгоритм:

StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount ) Rank = min( MaxQueryRank, HitCount * 16 * StatisticalWeight / MaxOccurrence ) 

Фразовые соответствия ранжируются как отдельные ключи, за исключением того, что значение KeyRowCount (количество строк, содержащих эту фразу) является приближением — оно может быть неточным и выше фактического значения.

Ранг NEAR

CONTAINSTABLE поддерживает запросы на два или более терминов поиска в близости друг от друга с помощью NEAR параметра. Ранжирующее значение для каждой из возвращаемых строк зависит от нескольких параметров. Одним из основных факторов ранжирования является общее количество совпадений (или попаданий) по отношению к длине документа. Таким образом, например, если у документа из 100 слов и документа из 900 слов будет идентичный набор совпадений, то документ из 100 слов получит более высокий ранг.

Общая длина каждого попадания в строку также способствует ранжированию этой строки на основе расстояния между первым и последним условиями поиска этого хита. Чем меньше расстояние, тем больше попадание увеличивает ранжирующее значение строки. Если полнотекстовый запрос не указывает целое число в качестве максимального расстояния, документ, содержащий только попадания, расстояние которых превышает 100 логических терминов, имеет рейтинг 0.

Ранг ISABOUT

CONTAINSTABLE поддерживает запросы к взвешанным терминам с помощью ISABOUT параметра. ISABOUT — это запрос векторного пространства в традиционной терминологии получения информации. В качестве алгоритма ранжирования по умолчанию используется широко известная формула Жаккарда. Ранжирование вычисляется для каждого термина в запросе, а затем объединяется, как описано в следующем алгоритме.

ContainsRank = same formula used for CONTAINSTABLE ranking of a single term (above). Weight = the weight specified in the query for each term. Default weight is 1. WeightedSum = Σ[key=1 to n] ContainsRankKey * WeightKey Rank = ( MaxQueryRank * WeightedSum ) / ( ( Σ[key=1 to n] ContainsRankKey^2 ) + ( Σ[key=1 to n] WeightKey^2 ) - ( WeightedSum ) ) 

Ранг FREETEXTTABLE

РанжированиеFREETEXTTABLE основано на формуле ранжирования OKAPI BM25. FREETEXTTABLE запросы добавляют слова в запрос с помощью инлекционного создания (инфлектированные формы исходных слов запроса); эти слова рассматриваются как отдельные слова, без особых отношений с словами, из которых они были созданы. Синонимы, сформированные с помощью тезауруса, обрабатываются как отдельные, независимые и взвешенные выражения. Каждое слово в запросе вносит свой вклад в ранжирование.

Rank = Σ[Terms in Query] w ( ( ( k1 + 1 ) tf ) / ( K + tf ) ) * ( ( k3 + 1 ) qtf / ( k3 + qtf ) ) ) Where: w is the Robertson-Sparck Jones weight. In simplified form, w is defined as: w = log10 ( ( ( r + 0.5 ) * ( N - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) * ( n - r + 0.5 ) ) N is the number of indexed rows for the property being queried. n is the number of rows containing the word. K is ( k1 * ( ( 1 - b ) + ( b * dl / avdl ) ) ). dl is the property length, in word occurrences. avdl is the average length of the property being queried, in word occurrences. k1, b, and k3 are the constants 1.2, 0.75, and 8.0, respectively. tf is the frequency of the word in the queried property in a specific row. qtf is the frequency of the term in the query. 

Связанный контент

SQL оператор SELECT LIMIT

В этом учебном материале вы узнаете, как использовать оператор SELECT LIMIT в SQL, с синтаксисом и примерами.

Описание

SQL оператор SELECT LIMIT используется для извлечения записей из одной или нескольких таблиц в базе данных и ограничения количества возвращаемых записей на основании предельного значения.

Подсказка: SELECT LIMIT поддерживается не во всех базах данных SQL.
Для баз данных, таких как SQL Server или MSAccess, используйте оператор SELECT TOP, чтобы ограничить свои результаты. Оператор SELECT TOP является патентованным эквивалентом оператора SELECT LIMIT.

Синтаксис

Синтаксис для оператора SELECT LIMIT в SQL.

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT number_rows [ OFFSET offset_value ];

Параметры или аргумент

expressions Столбцы или расчеты, которые вы хотите получить tables Таблицы, из которых вы хотите получить записи. В предложении FROM должна быть указана хотя бы одна таблица WHERE conditions Необязательный. Условия, которые должны быть выполнены для записей, которые будут выбраны ORDER BY expression Необязательный. Он используется в операторе SELECT LIMIT, чтобы вы могли упорядочить результаты и выбрать те записи, которые вы хотите вернуть. ASC — возрастающий порядок, а DESC — убывающий LIMIT number_rows Он указывает ограниченное количество строк в наборе результатов, которые должны быть возвращены на основе number_rows . Например, LIMIT 10 вернет первые 10 строк, соответствующих критериям SELECT. Именно здесь важен порядок сортировки, поэтому обязательно используйте предложение ORDER BY соответствующим образом OFFSET offset_value Необязательный. Первая строка, возвращаемая LIMIT, будет определена offset_value

Пример — использование ключевого слова LIMIT

Давайте посмотрим, как использовать оператор SELECT с опцией LIMIT в SQL.
Например.

Ограничение числа возвращаемых строк

Требуется ограничить число возвращаемых запросом строк. Порядок не имеет значения; подойдут любые n строк.

Решение

В Oracle ограничение на количество возвращаемых строк накладывается с помощью функции ROWNUM в предикате WHERE:

select e.* from emp e where rownum  
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- ---------- --- --------- --- ---- ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 

Обсуждение

Для ограничения числа возвращаемых строк в Oracle приходится использовать функцию ROWNUM, возвращающую порядковый номер каждой строки результирующего множества (возвращающую, начиная с 1, величину).

Рассмотрим, то происходит при использовании ROWNUM

  1. Oracle выполняет запрос.
  2. Oracle извлекает первую строку и называет ее строкой номер 1.
  3. Номер строки больше 5? Если нет, Oracle возвращает строку, потому что она отвечает критерию: ее порядковый номер меньше или равен 5. Если да, Oracle не возвращает строку.
  4. Oracle извлекает следующую строку и присваивает ей слудущий порядковый номер по возрастанию (2, затем 3, затем 4 и т.д.).
  5. Переходим к шгу 3.

Как видно из данного процесса, присвоение значений, возвращаемых функцией ROWNUM, происходит после извлечения очередной строки. Это очень важно и является ключевым моментом. Многие разработчики на Oracle пытаются реализовать извлечение только, скажем, пятой возвращенной запросом строки, задавая ROWNUM = 5. Такое использование условия равенства в сочетании с ROWNUM является неверным. При попытке возвратить пятую строку с помощью ROWNUM = 5 роисходит следующее:

  1. Oracle выполняет запрос.
  2. Oracle извлекает первую строку и называет ее строкой номер 1.
  3. Номер строки равен 5? Если нет, Oracle отбрасывает строку, потому что она не отвечает заданному критерию. Если да, Oracle возвращает строку. Но ответ всегда будет отрицательным!
  4. Oracle извлекает следующую строку и называет ее строкой номер 1, поскольку первая возвращенная запросом строка должна быть пронумерована как первая строка.
  5. Переходим к шагу 3.

После тщательного разбора этого процесса становится понятно, почему использование ROWNUM = 5 не обеспечивает возвращения пятой строки. Невозможно получить пятую строку, не возвратив перед этим строки с первой по четвертую!

Однако заметьте, что с помощью ROWNUM = 1 можно получить первую строку. Может показаться, что это противоречит приведенному выше объяснению. Причина, почему ROWNUM = 1 обеспечивает возвращени первой строки, в том, что Oracle для определения наличия строк в таблице приходится извлекать, по крайней мере, одну из них. Внимательно проанализируйте предыдущий процесс, подставив 1 вместо 5, и вы поймете, почему для возвращения одной строки можно в качестве условия задавать ROWNUM = 1.

Tags: Ограничение числа возвращаемых строк

PL/SQL

tags: Администрирование Oracle DataBase || SQL & PL/SQL

Исходные коды проекта хранятся на github. Можете заводить Issue и Discussions, при необходимости.
Чтобы задать вопрос, добавить свои знания, исправить ошибки и неточности, пишите в телеграм чате.

Ограничение количество выводимых записей MySQL

В этом уроке вы узнаете, как получить ограниченное количество записей из таблицы базы данных MySQL с помощью условия LIMIT.

Условие PHP MySQL LIMIT

Условие LIMIT используется для ограничения количества строк, возвращаемых оператором SELECT . Эта функция очень полезна для оптимизации времени загрузки страницы, а также для повышения читабельности веб-сайта. Например, вы можете разделить большое количество записей на несколько страниц, используя разбиение на страницы, где ограниченное количество записей будет загружено на каждую страницу из базы данных, когда пользователь запросит эту страницу, щелкнув ссылку разбиения на страницы.

Базовый синтаксис условия LIMIT может быть задан следующим образом:

SELECT column_name(s) FROM table_name LIMIT row_offset, row_count;

Условие LIMIT принимает один или два параметра, которые должны быть неотрицательным целым числом:

  • Если указаны два параметра, первый параметр указывает смещение первой возвращаемой строки, то есть начальную точку, тогда как второй параметр указывает количество возвращаемых строк. Смещение первой строки равно 0 (а не 1).
  • В то время, когда указан только один параметр, он определяет максимальное количество строк, возвращаемых с начала набора результатов.

Например, чтобы получить первые 4 строки, вы можете использовать следующий запрос:

SELECT * FROM persons LIMIT 4;

Чтобы получить строки от 16 до 25 (включительно), вы можете использовать оператор OFFSET :

В приведенном ниже SQL-запросе говорится: вернуть только 10 записей, начать с записи 16 (OFFSET 15):

SELECT * FROM persons LIMIT 10 OFFSET 15;

Вы также можете использовать более короткий синтаксис для достижения того же результата:

SELECT * FROM persons LIMIT 15, 10;
Обратите внимание, что при использовании запятой числа меняются местами.

Примеры ограничения результатов выбора

Давайте сделаем SQL-запрос, используя условие LIMIT в операторе SELECT , после чего мы выполним этот запрос, передав его функции PHP mysqli_query() , чтобы получить ограниченное количество записей. Рассмотрим следующую таблицу persons в БД demo:

Выбор данных из таблиц базы данных MySQL

Код PHP в следующем примере будет отображаться только две строки из таблицы persons:

Пример

Процедурный Объектно-ориентированный PDO

 // Попытка выполнения запроса select $sql = "SELECT * FROM persons LIMIT 2"; if($result = mysqli_query($conn, $sql)) < if(mysqli_num_rows($result) >0)< echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; while($row = mysqli_fetch_array($result))< echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; > echo "
idfirst_namelast_nameemail
" . $row['id'] . "" . $row['first_name'] . "" . $row['last_name'] . "" . $row['email'] . "
"; // Закрыть набор результатов mysqli_free_result($result); > else < echo "Записей, соответствующих вашему запросу, не найдено."; >> else < echo "ОШИБКА: не удалось выполнить $sql. " . mysqli_error($conn); >// Закрыть соединение mysqli_close($conn); ?>
 // Попытка выполнения запроса select $sql = "SELECT * FROM persons LIMIT 2"; if($result = $mysqli->query($sql))< if($result->num_rows > 0)< echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; while($row = $result->fetch_array())< echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; > echo "
idfirst_namelast_nameemail
" . $row['id'] . "" . $row['first_name'] . "" . $row['last_name'] . "" . $row['email'] . "
"; // Free result set $result->free(); > else < echo "Записей, соответствующих вашему запросу, не найдено."; >> else< echo "ОШИБКА: не удалось выполнить $sql. " . $mysqli->error; > // Закрыть соединение $mysqli->close(); ?>
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ОШИБКА: не удалось подключиться. " . $e->getMessage()); > // Попытка выполнения запроса select try< $sql = "SELECT * FROM persons LIMIT 2"; $result = $pdo->query($sql); if($result->rowCount() > 0)< echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; while($row = $result->fetch())< echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; > echo "
idfirst_namelast_nameemail
" . $row['id'] . "" . $row['first_name'] . "" . $row['last_name'] . "" . $row['email'] . "
"; // Доступный набор результатов unset($result); > else < echo "Записей, соответствующих вашему запросу, не найдено."; >> catch(PDOException $e)< die("ОШИБКА: не удалось выполнить $sql. " . $e->getMessage()); > // Закрыть соединение unset($pdo); ?>

После выполнения сценария результат вывода будет выглядеть так:

+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 1 | Peter | Parker | peterparker@mail.com | | 2 | John | Rambo | johnrambo@mail.com | +----+------------+-----------+----------------------+

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *