лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
PARTITION BY в запросе 1. SELECT row_number () over ( PARTITION BY id_comp ORDER BY id_comp,trip_no ) num, 2. trip_no, id_comp 3. FROM Trip 4. WHERE ID_comp < 3 5. ORDER BY ID_comp, trip_no; Идея решения состоит в следующем. Введем еще одну переменную для хранения номера компании. При инициализации присвоим ей несуществующий номер (например, 0). Затем для каждой строки будем проверять, совпадает ли номер с номером компании текущей строки. Если значения совпадают, будем инкрементировать, если нет, сбрасывать в 1. Наконец, будем присваивать переменной номер компании из текущей строки. Дело в том, что проверка выполняется до присвоения, тем самым мы сравниваем текущее значение номера компании с номером компании из предыдущей строки (в заданном порядке сортировки). Теперь сам запрос. 1. SELECT 2. CASE WHEN @comp=id_comp THEN @i:=@i +1 ELSE @i:= 1 END num, 3. @comp:=id_comp id_comp, trip_no 4. FROM Trip, ( SELECT @i:= 0 , @comp:= 0 ) X 5. WHERE ID_comp < 3 6. ORDER BY ID_comp, trip_no; num id_comp trip_no 1 1 1181 2 1 1182 3 1 1187 4 1 1188 5 1 1195 6 1 1196 1 2 1145 2 2 1146 Или, коль скоро вы отошли от стандарта, можно использовать функцию IF, чтобы сократить запись: 1. SELECT 2. IF ( @comp=id_comp, @i:=@i +1 , @i:= 1 ) num, 3. @comp:=id_comp id_comp, trip_no 4. FROM Trip, ( SELECT @i:= 0 , @comp:= 0 ) X 5. WHERE ID_comp < 3 6. ORDER BY id_comp, trip_no; Проверить эти запросы вы можете из консоли , выбрав из списка MySQL. Функции RANK() и DENSE_RANK() Эти функции, как и функция ROW_NUMBER() , тоже нумеруют строки, но делают это несколько отличным способом. Это отличие проявляется в том, что строки, которые имеют одинаковые значения в столбцах, по которым выполняется упорядочивание, получают одинаковые номера (ранги). Например, значения (отсортированные по возрастанию) 1 5 6 6 6 получат такие номера: 1 1 5 2 6 3 6 3 6 3 Возникает вопрос, с какого номера продолжится нумерация, если, скажем, в последовательности чисел появится 7 и т.д.? Здесь есть два варианта: 1) с номера 4, т.к. это следующий номер по порядку; 2) с номера 6, т.к. следующая строка будет шестая по счету. Такая "неоднозначность" и привела к появлению двух функций вместо одной - RANK и DENSE_RANK, первая из которых продолжит нумерацию с 6, а вторая (плотная) - с 4. Рассмотрим несколько примеров. Начнем с демонстрации отличия в поведении функций RANK и ROW_NUMBER: 1. SELECT *, ROW_NUMBER () OVER ( ORDER BY type ) num, 2. RANK () OVER ( ORDER BY type ) rnk 3. FROM Printer; Здесь в двух последних столбцах выводятся значения сравниваемых функций при упорядочивании строк по столбцу type: code model color type price num rnk 2 1433 y Jet 270,00 1 1 3 1434 y Jet 290,00 2 1 1 1276 n Laser 400,00 3 3 6 1288 n Laser 400,00 4 3 4 1401 n Matrix 150,00 5 5 5 1408 n Matrix 270,00 6 5 Как и следовало ожидать, ROW_NUMBER пронумеровывает весь набор строк, возвращаемых запросом. Функция RANK, как оказалось, работает по второму из рассмотренных выше варианту, т.е. следующим номером после строк с одинаковым рангом будет номер строки. А теперь сравним "плотный" и "неплотный" ранги: 1. SELECT *, RANK () OVER ( ORDER BY type ) rnk, 2. DENSE_RANK () OVER ( ORDER BY type ) rnk_dense 3. FROM Printer; code model color type price rnk rnk_dense 2 1433 y Jet 270,00 1 1 3 1434 y Jet 290,00 1 1 1 1276 n Laser 400,00 3 2 6 1288 n Laser 400,00 3 2 4 1401 n Matrix 150,00 5 3 5 1408 n Matrix 270,00 5 3 Следует также обратить вниманию на порядок, в котором выводятся строки результирующего набора. Поскольку оператор SELECT в нашем примере не имеет предложения ORDER BY, а для вычисления рангов используется одинаковое упорядочивание по столбцу type, то и результат выводится в том же порядке. В целях оптимизации, если вам не нужно какое-либо другое упорядочение результирующего набора, используйте этот факт, чтобы не выполнять лишние сортировки, которые ухудшают производительность запроса. Как и для функции ROW_NUMBER, в предложении OVER может использоваться конструкция PARTITION BY, разбивающая весь набор строк, возвращаемых запросом, на группы, к которым затем применяется соответствующая функция. Запрос 1. SELECT *, RANK () OVER ( PARTITION BY type ORDER BY price ) rnk FROM Printer; позволяет в каждой группе, определяемой типом принтера, ранжировать модели по цене в порядке ее возрастания: code model color type price rnk 2 1433 y Jet 270,00 1 3 1434 y Jet 290,00 2 1 1276 n Laser 400,00 1 6 1288 n Laser 400,00 1 4 1401 n Matrix 150,00 1 5 1408 n Matrix 270,00 2 А вот как можно выбрать самые дешевые модели в каждой категории: 1. SELECT model, color, type, price 2. FROM ( 3. SELECT *, RANK () OVER ( PARTITION BY type ORDER BY price ) rnk 4. FROM Printer 5. ) Ranked_models 6. WHERE rnk = 1 ; model color type price 1433 y Jet 270,00 1276 n Laser 400,00 1288 n Laser 400,00 1401 n Matrix 150,00 Запрос можно было бы написать короче, если бы функцию RANK можно было бы применять в предложении WHERE, т.к. само значение ранга нам не требуется. Однако это запрещено (как и для других ранжирующих функций), по крайней мере, в SQL Server. Наконец, рассмотрим еще один пример. Пример. Найти производителей, которые производят более 2-х моделей PC. Эта задача имеет традиционное решение через агрегатные функции : 1. SELECT maker FROM Product 2. WHERE type = 'PC' 3. GROUP BY maker 4. HAVING COUNT ( * ) > 2 ; Однако эту задачу можно решить и с помощью функции RANK. Идея состоит в следующем: ранжировать модели каждого производителя по уникальному ключу и выбрать только тех производителей, модели которых достигают ранга 3: 1. SELECT maker 2. FROM ( 3. SELECT maker, RANK () OVER ( PARTITION BY maker ORDER BY model ) rnk 4. FROM Product 5. WHERE type = 'PC' 6. ) Ranked_makers 7. WHERE rnk = 3 ; И в одном, и в другом случае, естественно, мы получим один и тот же результат: maker E Еще раз повторю: упорядочивание в последнем случае должно быть выполнено по уникальной комбинации столбцов, т.к., в противном случае, моделей может быть больше трех, а ранг меньше (например, 1, 2, 2,...). В нашем случае данное условие выполнено, т.к. упорядочивание выполняется по столбцу model, который является первичным ключом в таблице Product. Кстати, планы выполнения этих запросов демонстрируют одинаковые стоимости наиболее расходных операций – сканирования таблицы и сортировку (которая я первом случае присутствует неявно и вызвана операцией группировки). Пример использования DENSE_RANK Часто встречается задача нахождения N-го по величине значения из набора значений некоторого столбца таблицы, например: Найти второе по величине значение цены в таблице PC. Давайте выведем отсортированный список значений цены из таблицы PC для контроля, добавив столбцы со значениями ранжирующих функций: 1. SELECT price, DENSE_RANK () OVER ( ORDER BY price DESC ) dense_rnk, 2. RANK () OVER ( ORDER BY price DESC ) rnk, 3. ROW_NUMBER () OVER ( ORDER BY price DESC ) rn 4. FROM PC ORDER BY price DESC ; price dense_rnk rnk rn 980,00 1 1 1 970,00 2 2 2 950,00 3 3 3 850,00 4 4 4 850,00 4 4 5 850,00 4 4 6 600,00 5 7 7 600,00 5 7 8 400,00 6 9 9 350,00 7 10 10 350,00 7 10 11 350,00 7 10 12 В рамках стандарта SQL-92 эту задачу можно решить следующим образом: 1. SELECT MAX ( price ) "2nd_price" FROM PC 2. WHERE price < ( SELECT MAX ( price ) FROM PC ) ; 2nd_price 970,00 Т.е. мы находим значение максимума среди всех значений, меньших максимального. А если нам потребуется найти значение третьей по величине цены? Можно поступить аналогично: 1. SELECT MAX ( price ) "3rd_price" FROM PC WHERE price < 2. ( 3. SELECT MAX ( price ) FROM PC 4. WHERE price < ( SELECT MAX ( price ) FROM PC ) 5. ) ; 3rd_price 950,00 А как найти N-е значение цены? Следуя используемой логике, мы можем добавлять новые "матрешки" к уже существующим вплоть до N-ой. Это решение никак не назовешь универсальным. Для решения подобных задач хорошо подходит функция DENSE_RANK. Например, исходную задачу с помощью этой функции можно решить так: 1. SELECT DISTINCT price FROM ( 2. SELECT DENSE_RANK () OVER ( ORDER BY price DESC ) rnk, price FROM PC 3. ) X WHERE rnk= 2 ; А чтобы найти любую другую порядковую цену (например, третью), достаточно поменять константу в условиях отбора: 1. SELECT DISTINCT price FROM ( 2. SELECT DENSE_RANK () OVER ( ORDER BY price DESC ) rnk, price FROM PC 3. ) X WHERE rnk= 3 ; Следует отметить, что использование DENSE_RANK, а не RANK, обусловлено тем, что в случае наличия одинаковых цен, значения, возвращаемые функцией RANK, будут иметь пропуски (рассмотрите задачу нахождения пятой по величине цены). Если же ставить задачу нахождения неуникального N-го значения, то можно использовать функцию ROW_NUMBER (например, третий человек в шеренге по росту). А если значения в таблице уникальны, то решение с помощью любой из этих функций даст один и тот же результат. Функция NTILE Задача. Распределить баллончики по 3-м группам поровну. Группы заполняются в порядке возрастания v_id. Эту задачу решает функция ранжирования NTILE, которая появилась в SQL Server 2008. Эта функция возвращает номер группы, в которую попадает соответствующая строка результирующего набора. 1. SELECT *, NTILE ( 3 ) OVER ( ORDER BY v_id ) gr FROM utv ORDER BY v_id; Параметром функции NTILE является число групп. Остальное вам уже известно . :-) Если мы захотим распределить порознь баллончики каждого цвета, то, как и для других функций ранжирования, можно добавить конструкцию PARTITION BY в предложение OVER: 1. SELECT *, NTILE ( 3 ) OVER ( PARTITION BY v_color ORDER BY v_id ) gr 2. FROM utv ORDER BY v_color, v_id; Обратите внимание на группы синего цвета (B). В двух первых группах оказалось по 6 баллончиков, а в третьей только 5. В случае, когда число строк не делится нацело на число групп, функция NTILE помещает в последние группы на одну строку меньше, чем в первые. Наконец, если аргумент функции NTILE окажется больше числа строк, то будет сформировано количество групп, равное числу строк, и в каждой группе окажется по одной строке. Оконные функции Фактически мы познакомились с этими функциями, когда рассматривали функции ранжирования . Только сейчас мы будем использовать агрегатные функции вместо функций, которые задают номер/ранг строки. Есть еще одно отличие (в реализации Майкрософт SQL Server 2005/2008) – предложение OVER() не содержит дополнительного предложения ORDER BY, поскольку значение агрегата не зависит от сортировки строк в «окне». Как и ранее , предложение PARTITION BY определяет «окно», т.е. набор строк, характеризуемых равенством значений списка выражений, указанного в этом предложении. Если предложение PARTITION BY отсутствует, то агрегатные функции применяются ко всему результирующему набору строк запроса. В отличие от классической группировки , где мы получаем на каждую группу одну строку, которая может содержать агрегатные значения, подсчитанные для каждой такой группы, здесь мы можем добавить агрегат к детализированным (несгруппированным) строкам. Рассмотрим несколько примеров использования оконных функций. Постраничная разбивка записей (пейджинг) Такая задача часто возникает в тех случаях, когда количество строк, возвращаемых запросом, превышает разумный размер страницы. Примером может служить представление результатов поисковой выдачи или сообщений на форумах сайтов. Результаты сортируются по некоторым критериям (например, по релевантности или по дате сообщения), а затем разбиваются по N строк на страницу. Главная проблема здесь состоит в том, чтобы не загружать на клиента весь набор строк, а выводить только запрашиваемую пользователем страницу (мало кто просматривает все страницы подряд). При отсутствии такой возможности пришлось бы выполнять разбивку по страницам программными средствами клиента, что негативно сказывается на трафике и времени загрузки страницы. Итак, нам нужно вывести, наряду с детализированными данными, общее число строк (или число страниц) и номер страницы для каждой записи, возвращаемой запросом. Если нам это удастся сделать, то чтобы не возвращать весь результирующий набор на клиента, мы можем на базе этого запроса создать хранимую процедуру, в которую в качестве входных параметров будет передаваться требуемое число записей на странице и номер страницы, а возвращаться набор записей с затребованной страницы. Такой подход будет экономно расходовать трафик, а навигация по страницам будет использовать кэшированный план исполнения хранимой процедуры. Для примера рассмотрим разбивку по 2 записи на страницу строк из таблицы Laptop, упорядоченных по убыванию цены. Вот таким образом можно добавить столбец, содержащий общее число строк в таблице: 1. SELECT *, COUNT ( * ) OVER () AS total 2. FROM Laptop; Заметим, что подобное можно было сделать в рамках стандарта SQL-92 с помощью подзапроса: 1. SELECT *, ( SELECT COUNT ( * ) FROM Laptop ) AS total 2. FROM Laptop; Однако представьте себе, что мы используем не простую таблицу (Laptop), а громоздкий запрос, который может содержать десятки и сотни строк. При этом «оконный» вариант не претерпел бы изменений, а в «классическом» случае пришлось бы полностью дублировать код запроса в подзапросе для вычисления числа строк. Чтобы посчитать число страниц, воспользуемся следующим простым алгоритмом: если число строк запроса нацело делится на число записей на странице, то результат целочисленного деления одного на другое дает число страниц; если существует ненулевой остаток целочисленного деления числа строк запроса на число записей на странице, то к результату целочисленного деления добавляем единицу (последняя страница неполная). Это алгоритм реализуется стандартными средствами с помощью оператора CASE : 1. SELECT *, 2. CASE WHEN total % 2 = 0 THEN total/ 2 ELSE total/ 2 + 1 END AS num_of_pages 3. FROM ( 4. SELECT *, COUNT ( * ) OVER () AS total 5. FROM Laptop 6. ) X; Чтобы получить для каждой строки запроса номер страницы, на которую она должна попасть, мы можем применить аналогичный алгоритм, только применить его нужно не к общему числу строк (total), а к номеру строки. Этот номер строки мы сможем получить с помощью ранжирующей функции ROW_NUMBER , выполнив требуемую по условию сортировку по цене: 1. SELECT *, 2. CASE WHEN num % 2 = 0 THEN num/ 2 ELSE num/ 2 + 1 END AS page_num, 3. CASE WHEN total % 2 = 0 THEN total/ 2 ELSE total/ 2 + 1 END AS num_of_pages 4. FROM ( 5. SELECT *, ROW_NUMBER () OVER ( ORDER BY price DESC ) AS num, 6. COUNT ( * ) OVER () AS total 7. FROM Laptop 8. ) X; co de mod el spe ed ra m h d price scre en nu m tot al page_ num num_of_ pages 3 1750 754 128 12 1200,0 0 14 1 6 1 3 5 1752 750 128 10 1150,0 0 14 2 6 1 3 4 1298 600 64 10 1050,0 0 15 3 6 2 3 2 1321 500 64 8 970,00 12 4 6 2 3 6 1298 450 64 10 950,00 12 5 6 3 3 1 1298 350 32 4 700,00 11 6 6 3 3 Хранимая процедура, о которой говорилось выше, может выглядеть так: 1. CREATE PROCEDURE paging 2. @n int -- число записей на страницу 3. , @p int = 1 -- номер страницы, по умолчанию - первая 4. AS 5. SELECT * FROM 6. ( SELECT *, 7. CASE WHEN num % @n = 0 THEN num/@n ELSE num/@n + 1 END AS page_num, 8. CASE WHEN total % @n = 0 THEN total/@n ELSE total/@n + 1 END AS num_of_pages 9. FROM 10. ( SELECT *, 11. ROW_NUMBER () OVER ( ORDER BY price DESC ) AS num, 12. COUNT ( * ) OVER () AS total FROM Laptop 13. ) X 14. ) Y 15. WHERE page_num = @p; 16. GO Таким образом, если нам нужно получить вторую страницу при условии размещения 2-х записей на странице, достаточно написать 1. exec paging @n= 2 , @p= 2 В результате получим: co de mo del spe ed ra m h d pri ce scr een nu m to tal page_ num num_of _pages 4 1298 600 64 10 1050 ,00 15 3 6 2 3 2 1321 500 64 8 970, 00 12 4 6 2 3 А вот так будет выглядеть неполная вторая страница, если число записей на странице будет равно четырем: 1. exec paging @n= 4 , @p= 2 co de mo del spe ed ra m h d pri ce scr een nu m to tal page_ num num_of _pages 6 1298 450 64 10 950, 00 12 5 6 2 2 1 1298 350 32 4 700, 00 11 6 6 2 2 Новые возможности стандарта, которые были реализованы в SQL Server 2012, делают разбивку на страницы очень простой операцией. Речь идет о новых необязательных конструкциях в предложении ORDER BY, а именно, OFFSET и FETCH. C их помощью можно указать сколько строк из результата запроса возвращать (FETCH) клиенту и начиная с какой строки (OFFSET) это делать. Теперь расширенный синтаксис предложения ORDER BY имеет вид: 1. ORDER BY <выражение> 2. [ ASC | DESC ] 3. [ ,...n ] 4. [ 5. OFFSET <целочисленное_выражение_1> { ROW | ROWS } 6. [ FETCH { FIRST | NEXT } <целочисленное_выражение_2> { ROW | ROWS } ONLY ] 7. ] FIRST и NEXT являются синонимами, как и ROW с ROWS, т.е. можно использовать любой из двух вариантов. целочисленное_выражение_2 определяет число возвращаемых строк, а целочисленное_выражение_1 - количество строк от начала отсортированного набора, которое следует пропустить перед выводом. Если предложение FETCH отсутствует, то выводиться будут все строки, начиная с целочисленное_выражение_1 + 1. С учетом новых возможностей процедуру постраничного вывода строк, которая была рассмотрена выше, можно реализовать совсем просто: 1. CREATE PROC paging 2. @n int = 2 -- число записей на страницу, по умолчанию 2 3. , @p int = 1 -- номер страницы, по умолчанию - первая 4. AS 5. SELECT * FROM Laptop 6. ORDER BY price DESC OFFSET @n* ( @p -1 ) ROWS FETCH NEXT @n ROWS ONLY ; Заметим, что стандартный синтаксис предложения ORDER BY поддерживает также PostgreSQL. Другие примеры использования оконных функций Использование оконных функций, как и CTE , может сократить объем кода. Вернемся к задаче , на которой мы демонстрировали преимущества использования CTE: Найти максимальную сумму прихода/расхода среди всех 4-х таблиц базы данных "Вторсырье", а также тип операции, дату и пункт приема, когда и где она была зафиксирована. Используем следующий алгоритм. К результатам запроса, объединяющему все операции из 4 таблиц базы «Вторсырье», добавим столбец, который с помощью оконной функции MAX определит максимальную сумму. Затем мы отберем те строки, у которых сумма операции совпадает с этим максимальным значением: 1. SELECT max_sum, type, date, point 2. FROM ( 3. SELECT MAX ( inc ) over () AS max_sum, * 4. FROM ( 5. SELECT inc, 'inc' type, date, point FROM Income 6. UNION ALL 7. SELECT inc, 'inc' type, date, point FROM Income_o 8. UNION ALL 9. SELECT out, 'out' type, date, point FROM Outcome_o 10. UNION ALL 11. SELECT out, 'out' type, date, point FROM Outcome 12. ) X 13. ) Y 14. WHERE inc = max_sum; max_sum type date point 18000,00 inc 2001-10-02 00:00:00.000 3 Рассмотрим еще один пример. Для каждого ПК из таблицы PC найти разность между его ценой и средней ценой на модели с таким же значением скорости ЦП. Здесь, в отличие от предыдущих задач, требуется выполнить разбиение компьютеров на группы с одинаковым значением speed, которое мы реализуем с помощью предложения |