| ← Июль 2008 → | ||||||
|
1
|
2
|
3
|
4
|
6
|
||
|---|---|---|---|---|---|---|
|
7
|
8
|
9
|
10
|
11
|
13
|
|
|
14
|
15
|
16
|
17
|
18
|
20
|
|
|
21
|
22
|
23
|
24
|
25
|
27
|
|
|
28
|
29
|
30
|
31
|
|||
За последние 60 дней ни разу не выходила
Сайт рассылки:
http://www.sql-ex.ru
Открыта:
15-09-2004
Статистика
0 за неделю
Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 198
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 198 (12 июля 2008 г.)Новым посетителям сайта
Сайт посвящен изучению языка, с помощью которого осуществляется
взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в
выполнении заданий на написание запросов к учебным базам данных; при этом
система контролирует правильность выполнения заданий. В настоящее время
реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя
оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE
и UPDATE. Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 54-х задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта
§
Хоть и незначительные расхождения в схемах и данных баз, используемых для решения задач на SELECT и DML, все же имеют место.
Это вызывало справедливые замечания.
§
Исправил баг с удвоением кавычек, который имел место при редактировании сообщений на форуме.
§
Добавил на страницу результатов голосования по задачам всплывающие подсказки с краткими формулировками задач. § Переработал описания учебных баз данных с учетом накопивших замечаний.
§
Очередное добавление данных от _Bkmz_ усилило проверку задачи 89.
§
С результатом (задач 138, время 23.307) подошел к третьему этапу Shadow77.
§
Изменения среди лидеров (решенные за неделю задачи третьего этапа):
§
Новые лица в ТОР 100 и вернувшиеся туда:
§
Продвинулись в рейтинге:
§
Продвижение ближайших претендентов на попадание в ТОР 100:
§
На этой неделе сертифицированы: § Число подписчиков - 3788 Число участников рейтинга - 17914 Число участников второго этапа - 1429 Число участников третьего этапа - 33 Сертифицировано на сайте - 292 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLТребования к SQL Server по усилению поддержки: предложение OVER() и упорядоченные вычисления (продолжение, начало в вып.195)Itzik Ben-Gan и Sujata Mehta (оригинал: SQL Server Feature Enhancement Request - OVER Clause and Ordered Calculations )Перевод Карасева Н.В. ---------------------------------------------------------- -- Агрегация с накоплением -- Решение 3 : Использование курсоров ---------------------------------------------------------- USE tempdb; GO -- Cursor DECLARE @aggtable TABLE(empid INT, Ordermonth DATETIME, qty INT, cumulativeqty INT, avgqty DECIMAL(12,2)); DECLARE @empid INT, @prevempid INT, @ordermonth DATETIME, @qty INT, @cntqty INT, @cumulativeqty INT, @avgqty DECIMAL(12,2); DECLARE aggcursor CURSOR FAST_FORWARD FOR SELECT empid, ordermonth, qty FROM dbo.EmpOrders ORDER BY empid, ordermonth, qty; OPEN aggcursor; FETCH NEXT FROM aggcursor INTO @empid, @ordermonth, @qty; SELECT @prevempid = @empid, @cntqty = 0, @cumulativeqty = 0, @avgqty = 0.0; WHILE @@fetch_status = 0 BEGIN IF @empid <> @prevempid SELECT @prevempid = @empid, @cntqty = 0, @cumulativeqty = 0, @avgqty = 0.0; SET @cntqty = @cntqty + 1; SET @cumulativeqty = @cumulativeqty + @qty; SET @avgqty = 1.0 * @cumulativeqty/@cntqty; INSERT INTO @aggtable (empid, Ordermonth, qty, cumulativeqty, avgqty) VALUES (@empid, @ordermonth, @qty, @cumulativeqty, @avgqty); FETCH NEXT FROM aggcursor INTO @empid, @ordermonth, @qty; END CLOSE aggcursor; DEALLOCATE aggcursor; SELECT empid, ordermonth, qty, cumulativeqty, avgqty FROM @aggtable ORDER BY empid, ordermonth, qty; GO -------------------------------------------------------------------------------- 
Рисунок 1-4: План выполнения для запроса, использующего курсор для вычисления множественных агрегатов с накоплением Если мы посмотрим план исполнения на рисунке 1-4, то данные сканируются один раз; после этого используется построчный цикл обхода курсора для вычисления агрегатов. Однако код длинный и сложный, и обслуживание его обходится дорого. Производительность решения с курсором равна P*N*O, где добавка O связана с построчной обработкой, (напомним, что P - это число разбиений, N - количество строк на разбиение). Сказанное означает, что курсоры имеют линейное падение производительности. Так, если число строк N достаточно велико, то курсоры дают значительный выигрыш по сравнению с решениями на основе множеств. На рисунке 1-5 показаны сравнительные графики оценок производительности двух рассмотренных решений - с помощью курсоров и без них. 
Рисунок 1-5: Агрегаты с накоплением: оценки производительности запроса, основанного на теории множеств, и запроса, использующего курсор Следующим примером задач, где используется последовательность и где можно получить выигрыш за счет упорядоченных вычислений, является задача сравнения соседних строк. Здесь вам будет продемонстрирован очень простой пример, но заметим, что сравнение сосодних строк необходимо для многих задач бизнеса (например, вычисление трендов, поиск пропуска в последовательностях, отчеты о наличии, и так далее). Давайте сравним значения в каком-нибудь столбце текущей строки со значениями для того же столбца в следующей строке таблицы (принимая некоторый порядок последовательности строк).
------------------------------------------------------------
-- Сравнение соседних строк
------------------------------------------------------------
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
GO
INSERT INTO dbo.T1 VALUES(1);
INSERT INTO dbo.T1 VALUES(2);
INSERT INTO dbo.T1 VALUES(3);
INSERT INTO dbo.T1 VALUES(100);
INSERT INTO dbo.T1 VALUES(101);
INSERT INTO dbo.T1 VALUES(102);
INSERT INTO dbo.T1 VALUES(103);
INSERT INTO dbo.T1 VALUES(500);
INSERT INTO dbo.T1 VALUES(997);
INSERT INTO dbo.T1 VALUES(998);
INSERT INTO dbo.T1 VALUES(999);
INSERT INTO dbo.T1 VALUES(1000);
GO
SELECT * FROM T1
GO
Термины следующая или предыдущая строки характерны для курсоров, но не для наборов данных. Таких понятий не существуют для наборов данных. Поэтому мы должны "перевести" эти понятия в нечто, имеющее смысл для наборов данных, т.е. следующий = минимум, больший чем текущее значение
SELECT col1 AS cur, (SELECT MIN(col1) FROM dbo.T1 AS B WHERE B.col1 > A.col1) AS nxt FROM dbo.T1 AS A; Cur nxt 1 2 2 3 3 100 100 101 101 102 102 103 103 500 500 997 997 998 998 999 999 1000 1000 NULL 
Рисунок 1-6: План выполнения запроса для сравнения строк при теоретико-множественном подходе Этот способ мышления не является интуитивно понятным и увеличивает сложность понимания. Но что более важно, если мы рассматрим план выполнения на рисунке 1-6, то мы увидим, что оптимизатор выполняет поиск в индексе для каждой строки; это говорит о том, что оптимизатор не учитывает порядок и просто повторяет поиск для каждой строки. Поэтому вместо того, чтобы один раз выполнить проход по упорядоченному набору данных в индексе, мы поднимаем стоимость запроса до N + N*S, где N - число строк в таблице, а S - стоимость операции поиска записи. Например, если мы имеем 1.000.000 строк в таблице размещенной на нескольких тысячах страниц, то стоимость операции составила бы 3.000.000 операций прямого чтения (при 3 уровнях в индексе). Это ещё упрощенный сценарий; в вычислениях тренда, задачах учета и т.д. вам необходим доступ к атрибутам соседней строки, которые не зависят от атрибута, определяющего порядок последовательности. В этих случаях все становится сложнее, что потребует использования TOP в подзапросах. Итак, в этом разделе показаны некоторые "пропуски" или недостающие элементы в SQL (до введения конструкции OVER), из-за которых отсутствует эффективная поддержка таких типов распространенных задач как вычисление нарастающих агрегатных значений или сравнения соседних строк. Имеющиеся варианты длинны, сложны или интуитивно не очевидны, и мало эффективны. Теперь давайте перейдем к введению в SQL конструкции OVER, её неполной реализции в SQL Server 2005 и к некоторым элементам конструкции все ещё не реализованным в SQL Server. (Продолжение следует...) Полезная информация
§
Мы принимаем участие в конкурсе Ростовских сайтов.
Голосовать можно один раз в день, если, конечно, есть желание :-). § Онлайновый выпуск рассылки можно почитать на сайте.
§
Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL.
§
Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях: КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
