Функции ранжирования T-SQL

В этой статье я расскажу вам, как пронумеровать строки результата запроса, возвращаемого MySQL .

Функция row_number() – это функция ранжирования, возвращающая порядковый номер строки, начиная с 1 для первой строки. Номер строки часто бывает нужен при генерации отчётов. Эта функция реализована в MS SQL и в Oracle . В MySQL подобная функция отсутствует, но её несложно реализовать за счёт глобальных переменных.

Нумерация строк

Чтобы пронумеровать строки, мы должны объявить переменную запроса. Продемонстрируем этот подход на примере простой таблицы, содержащей список работников предприятия (employees ). Следующий запрос выбирает 5 работников из таблицы, присваивая им номера по порядку, начиная с 1:

SET @row_number = 0; SELECT (@row_number:=@row_number + 1) AS num, firstName, lastName FROM employees LIMIT 5;

В выше приведённом запросе мы:

• Определили переменную row_number и инициализировали её нулевым значением;
• Увеличивали её значение на 1 при каждой итерации запроса.

Другая техника, позволяющая достичь того же результата, заключается в создании вместо глобальной переменной производной таблицы и перекрёстном объединении этих двух таблиц. Пример такого запроса:

SELECT (@row_number:=@row_number + 1) AS num, firstName, lastName FROM employees,(SELECT @row_number:=0) AS t LIMIT 5;

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

Возобновление нумерации в группах

Как нам задать отдельную нумерацию для каждой группы строк, объединённых выражением ORDER BY или GROUP BY ? Например, как имитировать следующий запрос:

SELECT customerNumber, paymentDate, amount FROM payments ORDER BY customerNumber;

Нам нужно сформировать список платежей, в котором каждому платежу будет соответствовать определённый порядковый номер. Для того чтобы получить требуемый результат, нам понадобятся две переменные: одна – с порядковым номером строки, другая – для хранения идентификатора клиента из предыдущей строки, чтобы сравнить его с текущим. Наш запрос будет выглядеть так:

SELECT @row_number:=CASE WHEN @customer_no = customerNumber THEN @row_number + 1 ELSE 1 END AS num, @customer_no:=customerNumber as CustomerNumber, paymentDate, amount FROM payments ORDER BY customerNumber;

Мы использовали оператор CASE для вычисления условия: если номер клиента остаётся прежним, мы увеличиваем номер строки на 1, в противном случае мы устанавливаем номер строки равным 1. Результат будет тем же, что и на выше приведённом скриншоте.

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

SELECT @row_number:=CASE WHEN @customer_no = customerNumber THEN @row_number + 1 ELSE 1 END AS num, @customer_no:=customerNumber as CustomerNumber, paymentDate, amount FROM payments,(SELECT @customer_no:=0,@row_number:=0) as t ORDER BY customerNumber;

Итак, мы научились эмулировать нумерацию строк запроса в MySQL .

Перевод статьи «MySQL row_number Emulation » был подготовлен дружной командой проекта .

Хорошо Плохо

Синтаксис:

ROW_NUMBER () OVER ([ ] )

Делит результирующий набор, полученный по предложению FROM .

определяет порядок, в котором значение функции ROW_NUMBER назначается строкам в секции. Целое число не может представлять столбец, если аргумент используется в ранжирующей функции.

Предложение ORDER BY определяет последовательность, в которой строкам назначаются уникальные номера с помощью функции ROW_NUMBER в пределах указанной секции.

Пример 23.8 . Использование функции ROW_NUMBER ()

Пусть нам нужно провести группировку проданных товаров по объему продаж, используя схему на рис. 23.5

Результат выполнения запроса приведен ниже.

Вывод 8 .

Свитерам, джинсам и ремням (с s_amount = 75) назначаются различные номера строк (5, 6, 7).

Подобно функции NTILE() , функция ROW_NUMBER() является недетерминистической функцией, так что "свитеры" мог бы получить номер строки 7 (вместо 5), а "ремни" - 5 (вместо 7). Чтобы избежать подобных ситуаций, необходимо сортировать результирующее множество по уникальному ключу.

Функции, генерирующие отчеты

После того как запрос выполнен, значения агрегатов (типа количество строк в результирующем множестве или среднее значение в колонке) могут быть вычислены для секции и быть доступными для других отчетов. Агрегатные функции генерирования отчетов (Reporting aggregate functions ) возвращают значения агрегатов для каждой строки в секции . К агрегатным функциям генерирования отчетов относятся функции SUM() , AVG() , MAX() , MIN() , COUNT() , использующее предложение OVER . Их поведение относительно NULL -значений такое же, как и в агрегатных функциях SQL .

В предыдущих разделах настоящей лекции мы уже обсуждали такое применение агрегатных функций. Поэтому в настоящем разделе приведем только несколько примеров.

Функции генерирования отчетов допустимы только для предложений SELECT . Основное их назначение состоит в способности выполнять многократный разбор блока данных результирующего множества запроса. Запросы типа "Подсчитать число продавцов, у которых уровень продаж больше на 10% от числа продаж по городу" не требуют соединений между отдельными блоками запроса.

Пример 23.9 . Использование агрегатных функций для генерирования отчетов

Пусть нам нужно для каждого товара найти регион, в котором наблюдается максимальный уровень продаж каждого товара, используя схему на рис. 23.5 . Это можно сделать с помощью следующего запроса:

SELECT s_productkey, s_regionkey, sum_s_amount FROM (SELECT p_productkey, r_regionkey, SUM(s_amount) AS "sum_s_amount", MAX(SUM(s_amount)) OVER (PARTITION BY p_productkey) AS "max_sum_s_amount" FROM sales GROUP BY p_productkey, r_regionkey) WHERE sum_s_amount = max_sum_s_amount;

Данные внутреннего запроса к таблице фактов "Продажи" (sales), сгруппированные по колонкам p_productkey и p_regionkey , агрегируются для первых трех колонок, и функция MAX(SUM(s_amount)) возвращает результат.

Вывод 9 .

Результат выполнения внешнего запроса приведен ниже.

Определение проблемы:

Введите любое число в ячейке A1 . Теперь попробуйте следующие формулы в любой точке первой строки.

SUM(INDIRECT("A"&ROW())) =SUMPRODUCT(INDIRECT("A"&ROW()))

Первая формула оценивается, вторая – ошибка #VALUE. Это вызвано тем, что функция ROW() ведет себя по-разному в SUMPRODUCT() .

В первой формуле ROW() возвращает 1 . Во второй формуле строка возвращает {1} (массив одной длины), хотя формула не была введена как формула CSE.

Почему это происходит?

Задний план

Мне нужно оценить формулу типа

SUMPRODUCT(INDIRECT(*range formed by concatenation and using ROW()*)>1)

Это приводит к ошибке. В качестве обходного пути к этой проблеме я теперь вычисляю ROW() в другой ячейке (в той же строке, очевидно) и объединяю ее внутри моего INDIRECT() . Кроме того, я также попытался инкапсулировать его внутри функции sum, например SUM(ROW()) , и это тоже работает.

Я был бы признателен, если бы кто-нибудь мог объяснить (или указать мне ресурс, который может объяснить), почему ROW() возвращает массив внутри SUMPRODUCT() без ввода CSE.

Solutions Collecting From Web of "Функция ROW () ведет себя по-разному внутри SUM () и SUMPRODUCT ()"

Интересный вопрос. Здесь есть тонкие проблемы, которые я не видел документально.

Кажется, INDIRECT("A"&ROW()) возвращает массив, состоящий из одного элемента, который является ссылкой на ячейку, а не значения в этой ячейке. Многие функции не могут правильно разрешить этот тип данных, но некоторые функции, такие как N и T, могут «разыгрывать» массив и возвращать базовое значение.

Возьмите этот случай, когда в массиве есть два элемента:

SUM(N(INDIRECT("A"&ROW(1:2))))

Это возвращает A1 + A2, когда массив введен, но он возвращает A1 только при вводе. Однако изменение ROW (1: 2) до {1; 2} в этой формуле возвращает правильный результат при вводе в обычном порядке. Эквивалентная формула SUMPRODUCT возвращает A1 + A2 независимо от того, введен ли массив или нет.

Это может быть связано с тем, как аргументы регистрируются в функции. Согласно http://msdn.microsoft.com/en-us/library/bb687900.aspx существуют два метода регистрации аргументов функции для обработки типов данных Excel:

Тип P / Q: «Excel преобразует одноклеточные ссылки на простые значения и многоклеточные ссылки на массивы при подготовке этих аргументов».

Аргументы SUM, похоже, соответствуют типу R / U, в то время как аргументы SUMPRODUCT ведут себя как тип P / Q. Массив, вводящий формулу SUM выше, заставляет опорный аргумент диапазона в ROW оцениваться как массив, тогда как это происходит автоматически с SUMPRODUCT.

Обновить

После небольшого исследования, вот еще одно доказательство, которое могло бы поддержать эту теорию. На основании ссылки в комментарии формула = SUM ((A1, A2)) дает те же значения, что и:

Executeexcel4macro("CALL(""Xlcall32"",""Excel4"",""2JRJR"",4,1,(!R1C1,!R2C1))")

Регистрация последнего аргумента как типа P путем изменения 2JRJR на 2JRJP дает ошибку в этом случае, но позволяет использовать диапазоны для одной области, такие как!R1C1:!R2C1 . С другой стороны, изменение 4 (xlfsum) до 228 (xlfsumproduct) допускает только ссылки на одну область так, как это называется так же, как SUMPRODUCT.

Когда ROW() возвращает массив, используйте INDEX для получения 1-го элемента.

Затем вы пример: =SUMPRODUCT(INDIRECT("A"&INDEX(ROW(),1)))

Я не думаю, что ROW () ведет себя по-другому здесь, он возвращает массив в обоих случаях. Я предполагаю, что SUM и SUMPRODUCT рассматривают этот массив по-разному – не знаю, почему.

Многие функции или их комбинации возвращают массивы – вам не нужно CTRL + SHIFT + ENTER, чтобы это произошло, вам нужно только CSE во многих случаях обрабатывать созданные массивы.

Я бы просто использовал INDEX вместо INDIRECT (что также приносит вам пользу, избегая изменчивой функции), т.е.

SUMPRODUCT(INDEX(A:A,ROW()))

…. расширяя это до вашего диапазона, эта формула будет подсчитывать количество значений> 1 в диапазоне в столбце A, где x определяет начальную строку, а y – конечную строку

COUNTIF(INDEX(A:A,x):INDEX(A:A,y),">1")

x и y можно вычислить по формулам

вы можете использовать SUMPRODUCT или COUNTIFS аналогичным образом, если есть дополнительные условия для добавления