Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 235
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)"Выпуск 235 от 28 марта 2009 г.Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 50-й задачи первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ По многочисленным просьбам фактическое время выполнения авторского и тестового решения выводится также и при правильном решении задачи (второй/третий рейтинговые этапы задач на SELECT). § Принял английский вариант формулировки рейтинговой задачи 83, который любезно предложил BW. § Оказалось, что оптимизатор для некоторых запросов показывает такую стоимость, для которой не хватало 12 знаков для отображения на странице рейтинга по стоимости. На этот казус обратил внимание _Bkmz_. Увеличил до 15 знаков. Если оценка стоимости превысит и этот размер, то на странице будут выводиться звездочки ("*"). § Компенсировал время простоя с 26-03-2009 по 27-03-2009. § Изменения среди лидеров (решенные за неделю задачи третьего этапа): § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3835 Число участников рейтинга - 19772 Число участников второго этапа - 1755 Число участников третьего этапа - 47 Сертифицировано на сайте - 363 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLМассивы и списки в SQL Server 2005 (начало в вып.217-229, 231-234)Erland Sommarskog (оригинал: Arrays and Lists in SQL Server 2005 )Перевод: Моисеенко С.И. Использование рекурсивных CTEЭто метод, который является совершенно новым для SQL 2005. Этот метод первоначально предложил мне SQL Server MVP Найджел Риветт (Nigel Rivett). Функция, основанная на этой идее, также опубликована в книге SQL Server MVP Ицика Бен-Гана (Itzik Ben-Gan) Inside SQL Server 2005: T-SQL Querying (SQL Server 2005 изнутри: Написание запросов в T-SQL). Этот метод не входит в число самых быстрых; он превосходит итерационный метод всего лишь на 15 %. Однако, Найджел Риветт предпочитает этот метод, потому что, говорит он, работая в качестве консультанта, пытается оставить по возможности небольшой след, предпочтительно, даже не функцию. И действительно, метод CTE довольно удобно использовать непосредственно в хранимой процедуре. Тем не менее, я покажу метод, упакованный во встраиваемую функцию: CREATE FUNCTION cte_split_inline (@list nvarchar(MAX),
@delim nchar(1) = ',') RETURNS TABLE AS
RETURN
WITH csvtbl(start, stop) AS (
SELECT start = convert(bigint, 1),
stop = charindex(@delim COLLATE Slovenian_BIN2, @list + @delim)
UNION ALL
SELECT start = stop + 1,
stop = charindex(@delim COLLATE Slovenian_BIN2,
@list + @delim, stop + 1)
FROM csvtbl
WHERE stop > 0
)
SELECT ltrim(rtrim(substring(@list, start,
CASE WHEN stop > 0 THEN stop - start ELSE 0 END)))
AS Value
FROM csvtbl
WHERE stop > 0
go
То, что начинается с WITH, и есть Общее Табличное Выражение (Common Table Expression - CTE). Простой CTE подобен макросу, который Вы определяете перед запросом, и который затем Вы можете использовать в запросе так, как будто это таблица. Немного более причудливая производная таблица, если угодно. Но CTE приведенный выше - есть специальная форма CTE, а именно, рекурсивный CTE. Рекурсивный CTE, состоит из двух операторов SELECT, объединяемых при помощи оператора UNION ALL. Первый оператор SELECT - это начальная точка. Второй оператор SELECT делает ссылку на сам CTE. Структуру можно представить в виде длинного списка: SELECT ... UNION ALL SELECT ... FROM CTE UNION ALL SELECT ... FROM CTE ... где каждый оператор SELECT имеет на входе CTE из оператора SELECT, расположенного непосредственно выше. Рекурсия продолжается, пока операторы SELECT продолжают производить новые строки. Окончательный результат UNION ALL всех операторов SELECT. (Этот рекурсивный запрос является несколько необычным, так как он не ссылается ни на какие таблицы. Обычно Вы используете рекурсивный CTE, чтобы раскрутить инвентарные списки и другие иерархические структуры). Итак, в этой функции, первый SELECT возвращает 1, c которой начинается список, и положение первого разделителя. Второй SELECT устанавливает start в положение после первого разделителя и stop - в положение второго разделителя. Третий SELECT, что подразумевается рекурсией, возвращает положение после второго разделителя как начальное положение и положение для третьего разделителя. Это продолжается до тех пор, пока не закончатся разделители, и значение stop не станет равным 0; при этом последний оператор SELECT не возвращает вообще ничего. Все это создает виртуальную таблицу, содержащую начальную и конечную позиции для всех элементов списка, и мы можем использовать строки в этой таблице в качестве входных значений для substring в сочетании со специальной обработкой последнего элемента списка. Ниже приведен пример использования этой функции: CREATE PROCEDURE get_company_names_cte @customers nvarchar(2000) AS SELECT C.CustomerID, C.CompanyName FROM Northwind..Customers C JOIN cte_split_inline(@customers, ',') s ON C.CustomerID = s.Value OPTION (MAXRECURSION 0) go EXEC get_company_names_cte 'ALFKI, BONAP, CACTU, FRANK' Обратите внимание на предложение OPTION. Без этого предложения SQL Server прервал бы выполнение функции преждевременно, если в списке содержится более 100 элементов, т.к. 100 - это значение по умолчанию для MAXRECURSION (максимальное число итераций). MAXRECURSION служит защитой от бесконечно выполняющихся рекурсий. Для "более нормального" использования CTE, например, для отношений предприниматель-менеджер, 100 - это много, но в нашем случае 100 - слишком маленькое число. Поэтому мы устанавливаем MAXRECURSION в значение 0, которое полностью отключает проверку числа итераций. Но почему предложение OPTION не поместить внутрь функции? Только по той причине, что использование предложений OPTION запрещено во встраиваемых функциях. Вспомним, что встраиваемые функции фактически функциями не являются, это лишь макроопределения, которые вставляются в вызывающий их запрос. Функция не возвращает позицию в списке, но можно легко это сделать, добавив столбец счетчика к CTE: SELECT ..., listpos = 1 ... UNION ALL SELECT ..., listpos = listpos + 1 Как Вы можете догадаться, эта функция теряет в производительности из-за комбинации nvarchar (MAX) и charindex. К ней можно обратиться, написав многооператорную функцию, которая разбивает входной список на куски в манере, подобной duo_chunk_split_me. Однако мои тесты показали, что избыточные затраты на распределение данных по возвращаемой таблице в многооператорной функции несколько превосходят стоимость для charindex на nvarchar(MAX). Непритязательность метода CTE делает функцию разбиения на куски менее привлекательной, и я оставляю в качестве упражнения читателю ее реализацию. Или посмотрите ссылку. Процедуры, которые используют cte_split_inline, подвержены проблеме кэширования, обсуждаемой мной ранее, поэтому предпочтительней возможно будет скопировать входной параметр в локальную переменную. (Продолжение следует...) Полезная информация§ Приглашаем вас посетить новый проект - Интерактивный учебник по SQL. § Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Хотите поддержать проект? Вот инструкция по применению. :-) КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
