| ← Июнь 2008 → | ||||||
|
1
|
||||||
|---|---|---|---|---|---|---|
|
2
|
3
|
4
|
5
|
6
|
8
|
|
|
9
|
10
|
11
|
12
|
13
|
15
|
|
|
16
|
17
|
18
|
19
|
20
|
22
|
|
|
23
|
24
|
25
|
26
|
27
|
29
|
|
|
30
|
||||||
За последние 60 дней ни разу не выходила
Сайт рассылки:
http://www.sql-ex.ru
Открыта:
15-09-2004
Статистика
0 за неделю
Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 196
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 196 (28 июня 2008 г.)Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 54-х задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Автор - _Bkmz_ - усилил проверку задачи 89, добавив данные. § Новые лица в ТОР 100 и вернувшиеся туда: § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3787 Число участников рейтинга - 17507 Число участников второго этапа - 1409 Число участников третьего этапа - 32 Сертифицировано на сайте - 288 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLТребования к SQL Server по усилению поддержки: предложение OVER() и упорядоченные вычисления (продолжение, начало в вып.195)Itzik Ben-Gan и Sujata Mehta (оригинал: SQL Server Feature Enhancement Request - OVER Clause and Ordered Calculations )Перевод Карасева Н.В. Раздел 1: Предпосылки. Недостающие элементы в стандартном SQL до введения в него предложения OVERВ этом разделе мы постараемся показать, что в SQL недоставало элементов, пока не ввели предложение OVER для эффективной поддержки определенных типов запросов широкого назначения как-то агрегатов с накоплением или сравнения соседних строк.Язык SQL является декларативным языком, который предназначен для того, чтобы эффективно извлекать данные и манипулировать ими в реляционных базах данных. Он основан на реляционной модели, которая в свою очередь основана на теории множеств. SQL по своей сути отличается от всех процедурных или объектно-ориентированных языков. В основном он имеет дело с неупорядоченными наборами данных. Многим программистам трудно думать таким же образом, каким SQL обрабатывает данные, т.е. в терминах множеств и неупорядоченных данных. В SQL, когда мы запрашиваем данные без указания конструкции ORDER BY, существенным является то, что возвращаемые данные неупорядочены. Когда мы требуем сортировки данных, используя ORDER BY, SQL полностью обрабатывает данные как неупорядоченный набор, а затем данные упорядочиваются согласно нашим требованиям и возвращаются скорее в виде курсора, а не множества. SQL требует совершенно другого типа мышления. Существуют определенные типы решений в SQL Server 2000, которые интуитивно не понятны и требуют нестандарного мышления. Вот некоторые примеры: ранжирование или агрегирование данных, являющихся логическим подмножеством результирующих данных; вычисление нарастающих итогов, скользящих или на данный год; отображение атрибутов базовой строки и агрегатов для той же строки; сравнение строк и т.д. Рассмотрим пример для вычисления нарастающих агрегатов в следующей постановке: для каждого работника и каждого месяца вывести полную сумму количества (qty) от начала деятельности работника до данного месяца. Это самый тривиальный пример вычисления нарастающих ("накапливающихся") агрегированных значений, но на практике часто возникают задачи, которые могут быть решены с использованием нарастающих агрегатов (например, задачи учета, задачи с временными последовательностями, слияние конкурирующих сессий и.т.д.). Договоримся, что мы принимаем стиль мышления SQL и попытаемся решить задачу в рамках теоретико-множественного подхода на SQL Server 2005 с помощью коррелирующего подзапроса. (Прим. перев.: база данных Northwind для SQL 2000) -- Агрегаты с накоплением
--Создаем таблицу EmpOrders в базе данных tempdb, используя данные из Northwind
-------------------------------------------------------------------------------
USE tempdb;
IF OBJECT_ID('EmpOrders') IS NOT NULL
DROP TABLE EmpOrders
GO
CREATE TABLE EmpOrders
(
empid INT NOT NULL,
ordermonth DATETIME NOT NULL,
qty INT NOT NULL,
PRIMARY KEY(empid, ordermonth)
)
INSERT INTO EmpOrders(empid, ordermonth, qty)
SELECT O.EmployeeID,
CAST(CONVERT(CHAR(6), O.OrderDate, 112) + '01' AS DATETIME) AS ordermonth,
SUM(Quantity) AS qty
FROM Northwind.dbo.Orders AS O
JOIN Northwind.dbo.[Order Details] AS OD
ON O.OrderID = OD.OrderID
GROUP BY EmployeeID,
CAST(CONVERT(CHAR(6), O.OrderDate, 112) + '01' AS DATETIME);
SELECT empid, CONVERT(CHAR(10), ordermonth, 121) AS ordermonth, qty
FROM EmpOrders
ORDER BY empid, ordermonth;
-----------------------------------------------------------------------------
-- Агрегаты с накоплением
-- Решение 1 : Использование коррелирующих подзапросов
-----------------------------------------------------------------------------
SELECT O1.empid, O1.ordermonth, O1.qty,
(SELECT SUM(O2.qty)
FROM EmpOrders AS O2
WHERE O2.empid = O1.empid
AND O2.ordermonth <= O1.ordermonth) AS cumulativeqty
FROM EmpOrders AS O1;
Первое, что мы увидим - способ организации запроса. Большинство людей, вероятно, подумали бы так: требуется вычислить нарастающий итог независимо для каждого работника на основании хронологического упорядочения месяцев в году. Но без возможности выполнить вычисления, основанные на упорядоченном наборе, мы должны принять образ мышления теории множеств и переформулировать запрос следующим образом: для каждой строки (назовем ее текущей строкой) вычислить общее количество по строкам, в которых работник тот же, что и работник в текущей строке, а месяц не больше, чем месяц в текущей строке. Второе, что мы увидим, это то, что запрос этот очень неэффективный. Давайте проанализируем план его выполнения, представленный на рис. 1-1.  
Рисунок 1-1: План выполнения для запроса, использующего коррелированный подзапрос для вычисления единственного агрегата с накоплением Во-первых, выполняется полное сканирование таблицы (Clustered Index Scan - сканирование кластерного индекса). Приняв за P число разбиений (работников), а за N - среднее количество строк для каждого разбиения в таблице, стоимость этого сканирования будет P*N. Для каждой строки, возвращаемой при сканировании таблицы, SQL Server выполняет Seek (поиск)+ Partial Scan (частичное сканирование) в кластерном индексе, созданном на столбцах (empid, ordermonth), для получения строк, для которых выполняется агрегирование (обозначено в плане как Clustered Index Seek). В целях дискуссии и для упрощения вычислений, ограничимся стоимостью набора частичных сканирований на листе и опустим все другие стоимости в этом плане. Число строк, сканированных на листе индекса на каждую внешнюю строку есть число строк, имеющих такое же значение empid, как во внешней строке, и не превышает ordermonth. В среднем оно равно (1+N) /2 строк для каждой внешней строки. В итоге число строк при частичных сканированиях можно оценить так: P*N* (1+N) /2 = P* (N+N^2) /2. Таким образом, алгоритмическая сложность этого плана равна N^2. При большом количестве строк на работника вы получаете просто огромные числа. Например, при 5 работниках и 100.000 строк на работника, вы получаете сканирование 25 000 250 000 строк. Оценка времени выполнения подобного запроса - более часа. С увеличением количества строк на работника падение производительности будет нелинейным - N^2. Например, для одного разбиения и 10 000 000 строк этот запрос исполнялся бы около года! (Продолжение следует...) Полезная информация§ Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях: КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
