Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 232
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)"Выпуск 232 от 7 марта 2009 г.Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 50-й задачи первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. С наступающим праздником, дорогие женщины!
|
| No | Person | Number of Sel_ex |
Last_Sel | Number of DML_ex |
Scores | Days | Days_2 | Days_3 | S_3 | LastSolved | LastVisit |
| 1 | Креславский О.М. (Arcan) | 153 | 153 | 21 | 372 | 763 | 62.969 | 44.093 | 49 | 03 Mar 2009 | 06 Mar 2009 |
| 2 | Карасёва Н.В. (vlksm) | 153 | 153 | 21 | 372 | 1032 | 88.313 | 59.250 | 49 | 03 Mar 2009 | 06 Mar 2009 |
| 3 | Сальников С.А. ($erges) | 152 | 152 | 21 | 369 | 356 | 3.781 | 4.118 | 46 | 16 Feb 2009 | 06 Mar 2009 |
| 4 | Сенкевич С.В. (GreyC) | 152 | 152 | 21 | 369 | 358 | 57.860 | 27.102 | 46 | 26 Jan 2009 | 06 Mar 2009 |
| 5 | Селезнёв А.С. (Артём С.) | 150 | 152 | 21 | 363 | 444 | 47.248 | 37.524 | 46 | 25 Jan 2009 | 03 Mar 2009 |
| 6 | Никотин В.М. (@Nikotin) | 150 | 150 | 21 | 363 | 108 | 8.371 | 3.751 | 40 | 13 Dec 2008 | 06 Mar 2009 |
| 7 | Печатнов В.В. (pvv) | 146 | 149 | 21 | 350 | 357 | 30.849 | 17.490 | 36 | 10 Oct 2008 | 06 Mar 2009 |
| 8 | Зотов П.Г. (Ozzy) | 144 | 153 | 21 | 346 | 341 | 93.533 | 111.247 | 36 | 14 Feb 2009 | 06 Mar 2009 |
| 9 | Муллаханов Р.Х. (rem) | 149 | 152 | 21 | 358 | 483 | 14.427 | 20.056 | 35 | 05 Jan 2009 | 05 Mar 2009 |
| 10 | >Умрихина Е.В. (Umrikhina) | 148 | 148 | 21 | 356 | 316 | 18.299 | 20.658 | 33 | 06 Mar 2009 | 06 Mar 2009 |
| 11 | Мурашкин И.В. (lepton) | 143 | 152 | 21 | 343 | 1059 | 68.477 | 57.992 | 33 | 14 Feb 2009 | 15 Feb 2009 |
| 12 | Дроздков А.Н. (anddros) | 147 | 151 | 21 | 354 | 260 | 6.074 | 6.723 | 31 | 11 Feb 2009 | 06 Mar 2009 |
| 13 | Шиндин А.В. (AlShin) | 147 | 150 | 21 | 353 | 79 | 20.458 | 7.203 | 30 | 05 Jan 2009 | 28 Jan 2009 |
| 14 | Держальцев В.А. (MadVet) | 137 | 146 | 21 | 331 | 1257 | 60.783 | 28.482 | 28 | 24 Sep 2008 | 30 Dec 2008 |
| 15 | Любченко В.А. (IAS56) | 136 | 146 | 21 | 330 | 615 | 403.343 | 373.617 | 28 | 11 May 2008 | 01 Dec 2008 |
| 16 | Голубин Р.С. (Roman S. Golubin) | 141 | 145 | 21 | 336 | 1260 | 93.162 | 58.822 | 25 | 29 Jan 2009 | 08 Feb 2009 |
| 17 | Nikolaenko A.V. (Shadow77) | 142 | 147 | 21 | 337 | 436 | 77.451 | 14.010 | 23 | 22 Oct 2008 | 11 Dec 2008 |
| 18 | Солдатенков Ю.С. (SolYUtor) | 138 | 146 | 21 | 329 | 819 | 22.615 | 6.102 | 20 | 14 Aug 2008 | 23 Oct 2008 |
| 19 | Белогурова К. (Katy_Ekb) | 133 | 143 | 21 | 319 | 552 | 10.666 | 4.673 | 18 | 27 Nov 2008 | 18 Jan 2009 |
| 20 | Егоров А.Б. (ABEgorov) | 137 | 144 | 21 | 327 | 180 | 12.897 | 8.815 | 18 | 03 Aug 2008 | 12 Aug 2008 |
Лучшие результаты за неделю
| No | surname | n_sel | sel_all | sel_scores | dml_scores | scores | rating | last_visit |
| 1 | >Филипцов А.В. (AlexPhil) | 68 | 68 | 124 | 34 | 158 | 583 | 06 Mar 2009 |
| 2 | Щербонос А.Н. (AlexNous) | 50 | 50 | 94 | 23 | 117 | 1224 | 06 Mar 2009 |
| 3 | yui Y.Y. (yui) | 36 | 36 | 68 | 26 | 94 | 1832 | 05 Mar 2009 |
| 4 | Тотьмянин С.А. (SCAT) | 31 | 137 | 76 | 0 | 76 | 49 | 06 Mar 2009 |
| 5 | >Титкин А.А. (Creol) | 39 | 39 | 75 | 0 | 75 | 2659 | 06 Mar 2009 |
| 6 | Павленко Д.К. (Fantast) | 30 | 88 | 63 | 0 | 63 | 301 | 05 Mar 2009 |
| 7 | >Захарова (Klaris) | 28 | 28 | 49 | 9 | 58 | 3997 | 06 Mar 2009 |
| 8 | Волков (LehVolk) | 30 | 30 | 53 | 4 | 57 | 4103 | 05 Mar 2009 |
| 9 | >Будыльский А.В. (BAV88) | 31 | 31 | 56 | 0 | 56 | 4183 | 06 Mar 2009 |
| 10 | Жуков Р.В. (ZhoR) | 18 | 29 | 37 | 17 | 54 | 3343 | 06 Mar 2009 |
| 11 | Шапкин Р.Ю. (shar.nir) | 29 | 29 | 53 | 0 | 53 | 4467 | |
| 12 | Felonyuk I. (inna1) | 26 | 26 | 49 | 0 | 49 | 4917 | 04 Mar 2009 |
| 13 | >Поляков А. (@|_EX) | 25 | 27 | 46 | 0 | 46 | 5108 | 06 Mar 2009 |
| 14 | >Пустохайлов А.А. (Pustohaylov) | 26 | 26 | 46 | 0 | 46 | 5287 | 06 Mar 2009 |
| 15 | Cool А. (Зигмунд) | 24 | 24 | 41 | 3 | 44 | 5525 | 05 Mar 2009 |
| 16 | Шапкин Р.Ю. (shar.nir_rep) | 18 | 31 | 43 | 0 | 43 | 4041 | 03 Mar 2009 |
| 17 | >Z E. (Elz) | 16 | 119 | 42 | 0 | 42 | 137 | 06 Mar 2009 |
| 18 | Косяков А.Г. (AlexeyKo) | 24 | 24 | 39 | 1 | 40 | 6045 | 05 Mar 2009 |
| 19 | >Баткаев Р. (yorcky) | 24 | 24 | 40 | 0 | 40 | 6064 | 06 Mar 2009 |
| 20 | >Иванов М.В. (Se7ven) | 18 | 26 | 38 | 0 | 38 | 5351 | 06 Mar 2009 |
| 21 | >Rice (Banshee) | 15 | 27 | 37 | 0 | 37 | 4767 | 06 Mar 2009 |
| 22 | Филимонов А.В. (AFilAFil) | 14 | 32 | 36 | 0 | 36 | 3341 | 05 Mar 2009 |
| 23 | >winay W. (winner) | 17 | 17 | 28 | 7 | 35 | 6585 | 06 Mar 2009 |
| 24 | Филипцов А.В. (AlexPhil2008) | 0 | 31 | 0 | 34 | 34 | 1977 | 03 Mar 2009 |
| 25 | Жовтоног Е.А. (JA_JAMES) | 13 | 13 | 17 | 17 | 34 | 6758 | 06 Mar 2009 |
| 26 | Зиновьев Д.В. (DimkaZ) | 17 | 17 | 23 | 7 | 30 | 7512 | 06 Mar 2009 |
Изучаем SQL
Массивы и списки в SQL Server 2005 (начало в вып.217-229, 231)
Erland Sommarskog (оригинал: Arrays and Lists in SQL Server 2005 )Перевод: Моисеенко С.И.
Заключительные замечания
Являясь более быстрым методом, чем итерационная функция, он более труден для понимания и, вероятно, его трудней распространить на более сложные форматы.
Эти функции не имеют столбца, подобного listpos. С этой целью Вы можете использовать функцию row_number:
listpos = row_number() OVER (ORDER BY s.sliceno, N.Number)
Где sliceno - дополнительный столбец счетчика, который следовало бы добавить к таблице @slices. Возможно, что это скажется на производительности, но я не исследовал этот момент.
По сравнению с другими методами, производительность существенно зависит от полной длины строки, так как мы сравниваем каждый символ во входной строке с символом-разделителем. Таким образом, будут иметь значение и дополнительные пробелы, и длина элементов.
Я не включал никакой функции, которая возвращает список чисел. Вы могли бы написать новую функцию, которая использует convert в соответствующих местах, но Вы можете также поступить следующим образом:
CREATE PROCEDURE get_product_names_tblnum @ids varchar(50) AS
SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
JOIN duo_chunk_split_me(@ids, DEFAULT) i ON
P.ProductID = convert(int, i.str)
go
EXEC get_product_names_tblnum '9, 12, 27, 37'
Несмотря на дробление на куски, все же есть некоторые проблемы надежности при работе с таблицей чисел. Пользователи, например, могут удалить числа в середине или добавить числа, которые не должны там быть. Если Вы параноик, то можете ввести ограничение check, для проверки минимального значения, и написать триггер, который будет грязно ругаться, если кто-нибудь залезет в таблицу. С другой стороны, ограничения и триггеры можно отключить, поэтому истинный параноик вероятно предпочтет другой метод... Кстати, таблица чисел может пригодиться и для решения других задач SQL, а не только для распаковки разделяемого запятыми списка. Таким образом, весьма полезно было бы иметь подобную таблицу в вашей базе данных.
Массив элементов фиксированной длины
Это метод, который был предложен Стивом Кассом (Steve Kass), SQL Server MVP, вдохновленного идеей, изложенной в книге Кена Хендерсона (Ken Henderson) "The Guru's Guide to Transact-SQL".
Точно так же как XML, этот метод требует специального формата входной строки. Вместо использования разделителей, как в других методах, элементы списка имеют фиксированную длину. Это дает два преимущества: 1) Вы никогда не столкнетесь с проблемой, когда разделитель присутствует в данных. 2) производительность. За исключением очень длинных входных строк, этот метод является самым быстрым среди всех, рассмотренных в этой статье.
Основная идея
Вот простой пример, который непосредственно использует этот метод, без функции:
CREATE PROCEDURE get_product_names_fix @ids varchar(8000),
@itemlen tinyint AS
SELECT P.ProductID, P.ProductName
FROM Northwind..Products P
JOIN Numbers n ON P.ProductID = convert(int,
substring(@ids, @itemlen * (n.Number - 1) + 1, @itemlen))
AND n.Number <= len(@ids) / @itemlen
go
EXEC get_product_names_fix ' 9 12 27 37', 4
Каждый элемент в "массиве" имеет одну и ту же длину, что определено параметром @itemlen. Мы используем функцию substring для извлечения каждого отдельного элемента. Таблица Numbers, которая здесь встречается, представляет собой ту самую таблицу, которую мы создали в начале раздела "Использование таблицы чисел".
А вот функция, которая содержит этот метод:
CREATE FUNCTION fixstring_single(@str nvarchar(MAX), @itemlen tinyint)
RETURNS TABLE AS
RETURN(SELECT listpos = n.Number,
str = substring(@str, @itemlen * (n.Number - 1) + 1, @itemlen)
FROM Numbers n
WHERE n.Number <= len(@str) / @itemlen +
CASE len(@str) % @itemlen WHEN 0 THEN 0 ELSE 1 END)
Цель выражения в последней строке состоит в том, чтобы учесть вариант, когда последний элемент в массиве оказывается короче других в результате потери концевых пробелов. Вы можете увидеть, что эта функция возвращает положение элемента в списке, которое оказывается просто числом из таблицы. Вот пример, использующий fixstring_single со списком строк:
CREATE PROCEDURE get_company_names_fix @customers nvarchar(2000) AS SELECT C.CustomerID, C.CompanyName FROM Northwind..Customers C JOIN fixstring_single(@customers, 6) s ON C.CustomerID = s.str go EXEC get_company_names_fix 'ALFKI BONAP CACTU FRANK'
(Продолжение следует...)
Полезная информация
§ Приглашаем вас посетить новый проект - Интерактивный учебник по SQL.
Ресурс позиционируется как "справочное обеспечение" для сайта SQL-EX.RU, но может использоваться и независимо от него.
§ Онлайновый выпуск рассылки можно почитать на сайте.
§ Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL.
§ Хотите поддержать проект? Вот инструкция по применению. :-)
Контакты
По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.
| В избранное | ||
