Как сделать нумерацию строк в sql
Добавил пользователь Евгений Кузнецов Обновлено: 04.10.2024
Гость
В приведенном примере проще добавить в таблицу Product номер записи (с авто-инкрементом, если нужно).
Можно сгенерировать порядковый номер на стандартном SQL с помощью подзапросов или представлений, если в таблице присутствует уникальное поле, по которому можно сортировать записи. Но для больших выборок это может оказаться неэффективным - O(n^2).
В большинстве практических случаев самый эффективный способ - добавить номер на стадии обработки результата (на PHP или C или на чем там программа написана).
Гость
Легко делается при помощи переменных, вариант для MySQL выглядит примерно так:
set @cnt=0;
SELECT @cnt:=@cnt+1 as cnt, Name, Price FROM Product
ValW
лучший ответ Если СУБД MSSQL, то там вообще нет такого понятия, как номер строки, но суррогатный номер все-таки можно ввести, используя конструкцию "ROW_NUMBER() OVER(ORDER BY":
select ROW_NUMBER() OVER(ORDER BY Name), Name, Price, FROM Product
только использовать его нужно осторожно, так как Этот номер не будет точно идентифицировать запись, а будет только указывать номер записи в КАЖДЫЙ МОМЕНТ выполнения запроса.
то есть в случае, если между моментами выполнения двух запросов в таблице появится запись, которая "сдвинет" весь набор по установленной сортировке (в нашем случае сортировка по полю Name), то записи как бы "перенумеруются":-)
Изучение задач, связанных с программированием баз данных на платформе MS SQL Server.
воскресенье, 2 декабря 2012 г.
Нумерация строк таблицы
Думаю всем программистам баз данных известна группа задач, в которых при определенных условиях требуется запросом получить набор всех записей некоторой таблицы, вместе со столбом, содержащим номер строки. То есть, чтобы в выходном наборе строки были пронумерованы последовательными числами, начиная с 1.
Итак, что же известно и методах решения таких задач. Начиная с MS SQL Server 2005, довольно легко, используя функцию ранжирования row_number , решить задачу о нумерации всех строк любой таблицы. Благодаря этой новинке от Microsoft, можно не создавать для нумерации временные таблицы с identity-полями, не мучиться с курсорами, в общем, в реальном программировании задача стала простой. Но что если программиста попросили решить задачу с помощью голого select-а (то есть без всяких вспомогательных объектов, вроде временных таблиц или циклов), да еще и при помощи средств MS SQL Server 2000.
Не стоит теряться. Тут все зависит от того, есть ли у таблицы один или несколько столбцов, которые дают уникальность строк этой таблицы. Предположим, что такая уникальность есть. Я бы не хотел рассматривать случай, когда в таблице есть одно поле дающее уникальность, поскольку в такой постановке описание решения уже дано на многих уважаемых и известных форумах. Поэтому давайте сперва рассмотрим случай, когда таблица имеет два поля, по которым имеется уникальность строк, а затем посмотрим, что можно сделать, если уникальность по строкам отсутствует.
Итак, сформулируем точное условие задачи.
Дана таблица dbo.Customers:
Заметим, что в таблице нет уникальности ни по имени, ни по фамилии. Есть уникальность по двум этим столбцам.
Давайте немного обсудим полученное решение. Здесь происходит левое соединение двух экземпляров таблицы. Но при этом в условии соединения используется не предикат равенства, а условие на то, что кортеж из двух элементов (имя и фамилия) левой таблицы больше по лексикографическому порядку кортежа таблицы справа. С помощью оператора group by и функции count происходит подсчет для каждой пары таблицы слева числа пар таблицы справа, меньших чем пара левой таблицы. Если пара левой таблицы является минимальной, то для нее есть только одна пустая запись от таблицы справа, что проверяется выражением case when.
А что если в таблице есть идентичные строки, то есть нет уникальности даже по всем наборам столбцам. У меня сложилось впечатление, что в этом случае, решения одним запросом средствами MS SQL Server 2000 нет. Сделаем тогда одно естественное допущение: предположим, что число дублей ограничено некоторым известным числом. То есть, например, если в описанной выше таблице есть строка ( 'Андрей', 'Петров' ), то существует еще не более чем, скажем 2 точно таких же строки. В этом случае задачу решить можно. Итак, будем предполагать, что общее число одинаковых строк не больше чем 3 для любой строки. Наполним заново таблицу dbo.Customers:
Перед началом решения немного обсудим общую идею. У нас нет столбцов, по которым была бы уникальность. Так давайте создадим их. В этом поможет исходное допущение и оператор cross join:
Стандарт SQL поддерживает четыре оконные функции, которые служат для ранжирования. Это ROW_NUMBER, NTILE, RANK и DENSE_RANK. В стандарте первые две считаются относящимися к одной категории, а вторые две — ко второй. Это связано с различиями в отношении детерминизма. Подробнее я расскажу в процессе рассказа об отдельных функциях.
Функции ранжирования появились еще в SQL Server 2005. Тем не менее я покажу альтернативные, основанные на наборах методы для получения того же результата. Я сделаю это по двум причинам: во-первых, это может быть интересным, а во-вторых, я верю, что это помогает лучше понять нюансы работы функций. Тем не менее имейте в виду, что на практике я настоятельно рекомендую придерживаться оконных функций, потому что они и проще, и намного эффективнее, чем альтернативные решения. Подробнее об оптимизации мы поговорим позже.
Все четыре функции ранжирования поддерживают необязательное предложение секционирования и обязательное предложение упорядочения окна. Если предложение секционирования окна отсутствует, весь результирующий набор базового запроса (вспомните о входных данных этапа SELECT) считается одной секцией. Что касается предложения упорядочения окна, то оно обеспечивает упорядочение при вычислениях. Понятно, что ранжирование строк без определения упорядочения вряд ли имеет смысл. В ранжирующих оконных функциях упорядочение служит другой цели по сравнению с функциями, поддерживающими кадрирование, такими как агрегирующие оконные функции. В первом случае упорядочение имеет логический смысл для самих вычислений, а во втором — упорядочение связано с кадрированием, то есть служит целям фильтрации.
Функция ROW_NUMBER
Эта функция вычисляет последовательные номера строк, начиная с 1, в соответствующей секции окна и в соответствии с заданным упорядочением окна. Посмотрите на пример запроса:
Вот сокращенный результат выполнения этого запроса:
Этот запрос кажется тривиальным, но здесь есть несколько моментов, о которых стоит сказать.
Так как в запросе нет предложения представления ORDER BY, упорядочение представления не гарантируется. Поэтому упорядочение представления здесь нужно считать произвольным. На практике SQL Server оптимизирует запрос с учетом отсутствия предложения ORDER BY, поэтому строки могут возвращаться в любом порядке. Если нужно гарантировать упорядочение представления, нужно не забыть добавить предложение представления ORDER BY. Если нужно, чтобы упорядочение представления выполнялось на основе номера строки, можно использовать псевдоним, назначенный в процессе вычисления предложения представления ORDER BY примерно так:
Считайте вычисление номеров строк генерацией еще одного атрибута в результирующем наборе запроса. Естественно, если хочется, можно получить упорядочение представления, которое отличается от упорядочения окна, как в следующем запросе:
Можно использовать оконный агрегат COUNT для создания операции которая логически эквивалентна функции ROW_NUMBER. Допустим, WPO - определение секционирования и упорядочения окна, примененное в функции ROW_NUMBER. Тогда ROW_NUMBER OVER WPO эквивалентно COUNT(*) OVER(WPO ROWS UNBOUNDED PRECEDING). Например, следующее эквивалентно запросу из листинга предыдущего примера:
Как я говорил, это хорошая тренировка — попытаться и создать альтернативные варианты вместо использования оконных функций, и не важно, что эти варианты сложнее и менее эффективные. Если уж мы говорим о функции ROW_NUMBER, то вот основанная на наборах стандартная альтернатива запросу, в которой оконные функции не используются:
В этом решении используется агрегат COUNT во вложенном запросе для определения, у скольких строк значение упорядочения (в нашем случае orderid) меньше или равно текущему. Это просто, если у вас уникальное упорядочение, основанное на одном атрибуте. Но все сильно усложняется, если упорядочение неуникально, что я и продемонстрирую при обсуждении детерминизма.
Детерминизм
Если упорядочение окна уникально, вычисление ROW_NUMBER является детерминистическим. Это означает, что у запроса есть только одни правильный результат, то есть если не менять входные данные, вы гарантировано будете получать повторяющиеся результаты. Но если упорядочение окна не уникально, вычисление становится недетерминистическим. Функция ROW_NUMBER генерирует уникальные номера строк в рамках секции, даже для строк с одинаковыми значениями в атрибутах упорядочения окна. В качестве примера посмотрите на следующий запрос:
Если нужно гарантировать повторяемость результатов, нужно сделать запрос детерминистическим. Это можно сделать, добавив дополнительный параметр в определение упорядочения окна, чтобы обеспечить уникальность в рамках секции. К примеру в следующем запросе уникальность упорядочения в окне достигается за счет добавления в список orderid DESC:
При использовании оконных функций детерминизм вычисления номеров страниц реализуется просто. Сделать то же, не прибегая к оконным функция сложнее, но вполне реально:
Но вернемся к функции ROW_NUMBER: как мы выдели, ее можно использовать для создания недетерминистических вычислений при использовании неуникального упорядочения. Таким образом, недетерминизм разрешен, но странно то, что он не разрешен полностью. Я имею в виду то, что предложение ORDER BY обязательно. Но что, если вы хотите просто получить в секции уникальные номера строк не обязательно в каком-то определенном порядке? Можно создать такой запрос:
Но, как уже говорилось, предложение ORDER BY в функциях ранжирования является обязательным:
Вы можете поумничать и определить константу в списке ORDER BY:
Ho SQL Server вернет такую ошибку:
Однако решение есть, и я вскоре его приведу.
Предложение OVER и последовательности
Для получения очередного значения последовательности применяется функция NEXT VALUE FOR. Вот пример:
Эту функцию можно вызывать в запросе, возвращающем несколько строк:
Это стандартный код. В SQL Server 2012 возможности функции NEXT VALUE FOR расширены и позволяют определять упорядочение в предложении OVER подобно тому, как это делается в оконных функциях. Таким образом можно гарантировать, что значения последовательности отражают желаемое упорядочение. Вот пример использования расширенной функции NEXT VALUE FOR:
Аналогичный принцип детерминизма применим к предложению OVER в функции NEXT VALUE FOR, как это происходит в оконных функциях.
Итак, нет прямого способа назначить номера строкам без упорядочения, но, по-видимому, SQL Server не смущает вложенный запрос, возвращающий константу в качестве элемента упорядочения окна. Вот пример:
Функция NTILE
Эта функция позволяет разбивать строки в секции окна на примерно равные по размеру подгруппы (tiles) в соответствии с заданным числом подгрупп и упорядочением окна. Допустим, что нужно разбить строки представления OrderValues на 10 подгрупп одинакового размера на основе упорядочения по val. В представлении 830 строк, поэтому требуется 10 подгрупп, размер каждой будет составлять 83 (830 деленное на 10). Поэтому первым 83 строкам (одной десятой части), упорядоченным по val, будет назначен номер группы 1, следующим 83 строкам — номер подгруппы 2 и т. д. Вот запрос, вычисляющий номера как строк, так и подгрупп:
Если вы думаете, что разбиение на подгруппы похоже на разбиение на страницы, хочу вас предупредить, что не стоит их путать. При разбиении на страницы, размер страницы является константой, а число страниц меняется динамически — оно определяется делением числа строк в результате запроса на размер страницы. При разбиении на подгруппы число подгрупп является константой, а размер подгруппы меняется и определяется как число строк деленное на заданное число подгрупп. Ясно, для чего нужно разбиение на страницы, а разбиение на подгруппы обычно используется для аналитических задач — когда нужно распределить данные среди заданного числа равных по размеру сегментов с использованием упорядочения по определенному измерению.
Но вернемся к результату запроса, вычисляющего номера как строк, так и подгрупп: как видите они тесно связаны друг с другом. По сути, можно считать, что номер подгруппы вычисляется на основе номера строки. В предыдущем разделе мы говорили, что если упорядочение окна не является уникальным, функция ROW_NUMBER является недетерминистической. Если разбиение на подгруппы принципиально основано на номерах строк, то это означает, что вычисление NTILE также недетерминистично, если упорядочение окна не уникально. Это означает, что у данного запроса может быть несколько правильных результатов. Можно посмотреть на это с другой стороны: двум строкам с одним значением упорядочения могут быть назначены разные номера подгрупп. Если нужен гарантированный детерминизм, можно следовать моим рекомендациям по получению детерминистических номеров строк, а именно добавить в упорядочение окна дополнительный параметр:
Следуя привычному методу, попытаемся создать альтернативные решения, заменяющие функцию NTILE и не содержащие оконных функций.
Я покажу один способ решения задачи. Для начала, вот код, который вычисляет число подгрупп по заданным размерности, числу подгрупп и числу строк:
Вычисление вполне очевидно. Для входных данных код возвращает 5 в качестве числа подгрупп.
Затем применим эту процедуру к строкам представления OrderValues. Используйте агрегат COUNT, чтобы получить размерность результирующего набора, а не входные данные @cnt, а также примените описанную ранее логику для вычисления номеров строк без использования оконных функций вместо входных данных @rownum:
Как обычно, не пытайтесь повторить это в производственной среде! Это пример предназначен для обучения, а его производительность в SQL Server ужасна по сравнению с функцией NTILE.
Функции RANK и DENSE_RANK
Альтернативное решение, заменяющее RANK и DENSE_RANK и не использующие оконные функции, создается просто:
Для вычисления ранга определяется число строк с большим значением, по которому ведется упорядочение, (как вы помните, упорядочение ведется по убыванию) и к нему добавляется единица. Для вычисления плотного ранга нужно пересчитать уникальные большие значения, по которым ведется упорядочение, и добавить к полученному числу единицу.
Детерминизм
Как вы уже сами, наверное, поняли, что как RANK, так и DENSE_RANK детерминистичны по определению. При одном значении упорядочения — независимо от его уникальности — возвращается одно и то же значение ранга. Вообще говоря, эти две функции обычно интересны, если упорядочение неуникально. Если упорядочение уникально, они дают те же результаты, что и Функция ROW_NUMBER.
Я хотел бы создать номер строки для каждой строки в результатах sql-запроса. Как это возможно?
пример: в запросе
Я хотел бы добавить столбец показывает номер строки
Я работаю над sql server 2005.
это зависит от используемой базы данных. Один из вариантов, который работает для SQL Server, Oracle и MySQL:
измените SomeField и SomeTable согласно вашей специфической таблице и уместному полю для того чтобы приказать мимо. Предпочтительно, чтобы SomeField был уникальным в контексте запроса, естественно.
в вашем случае запрос будет следующим (Faiz создал такой запрос первым):
Читайте также: