| ← Октябрь 2008 → | ||||||
|
2
|
3
|
5
|
||||
|---|---|---|---|---|---|---|
|
6
|
7
|
8
|
9
|
10
|
12
|
|
|
13
|
14
|
15
|
16
|
17
|
19
|
|
|
20
|
21
|
22
|
23
|
24
|
26
|
|
|
27
|
28
|
29
|
30
|
31
|
||
За последние 60 дней ни разу не выходила
Сайт рассылки:
http://www.sql-ex.ru
Открыта:
15-09-2004
Статистика
0 за неделю
Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 211
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 211 (11 октября 2008 г.)Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 51-й задачи первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Продолжается народное голосование в рамках Премии Рунета. Поддержите сайт своими голосами! Голосовать можно раз в сутки одновременно за <=50 проектов. § Добавил проверочные данные в ответ на сообщение о прохождении неверных решений, сделанное Bless на форуме 66 задачи. Принял его же версию формулировки этой задачи. § Запущен новый проект - интерактивный учебник по SQL по материалам книги "SQL. Задачи и решения". § С лучшим результатом на 138-ой задаче (не считая автора) закончил второй этап @Nikotin (задач 138, время 4.885) § Изменения среди лидеров (решенные за неделю задачи третьего этапа): § Новые лица в ТОР 100 и вернувшиеся туда: § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3843 Число участников рейтинга - 18415 Число участников второго этапа - 1567 Число участников третьего этапа - 40 Сертифицировано на сайте - 327 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLТребования к SQL Server по усилению поддержки: предложение OVER() и упорядоченные вычисления (продолжение, начало в вып.195-199, 202-210)Itzik Ben-Gan и Sujata Mehta (оригинал: SQL Server Feature Enhancement Request - OVER Clause and Ordered Calculations )Перевод: Карасева Н.В. ii. Функции LAG и LEADФункции LAG и LEAD позволяют вам получать значения записей с заданным смещением (в терминах числа строк) относительно к текущей строки. Эти функции могут быть очень полезны для решения задач бизнеса, таких как расчет трендов, сравнение значений в соседних строках, а также для определения пропусков в последовательностях. Синтаксис для этих функций: {LAG | LEAD}(<выражение>, <смещение>, <значение_по_умолчанию>) OVER(…)
LAG возвращает <выражение> из строки, сдвинутой на число строк, равного <смещению> вверх по отношению к текущей, и если такая строка не найдена, то возвращается <значение_по_умолчанию>. LEAD работает так же, только <смещение> будет выполнено в сторону последующих строк, а не предшествующих. Классическое применение этих функций - сопоставление значений из предшествующих или последующих строк со значениями в текущей строке. Например, (используется таблица EmpOrders), предположим, что вам нужно вычислить тренды сбыта продукции каждым работником путем сопоставления деятельности работника в текущем месяце с деятельностью за предыдущие месяцы. Без функции LAG вам пришлось бы использовать развернутые подзапросы или оператор APPLY. С функцией LAG все гораздо проще, да оптимальность такого решения будет потенциально выше за счет единственного сканирования данных. Если существует индекс (для столбцов разбиения, сортировки и окна), то возможен один проход уже упорядоченного индекса, что позволяет избежать дополнительной сортировки. Вот как выглядело бы такое решение: SELECT empid, ordermonth, qty as qtythismonth,
qty - LAG(qty, 1, NULL) OVER(PARTITION BY empid
ORDER BY ordermonth) AS qtydiff
FROM dbo.EmpOrders;
Заметим, что функции LAG и LEAD являются не стандартными, а предложенными в качестве расширения стандарта, подобного сделанному в Oracle. Выражение: LAG(qty, 1, NULL) OVER(PARTITION BY empid ORDER BY ordermonth) может быть выражено стандартными средствами посредством вычислений на базе OVER: MIN(qty) OVER (PARTITION BY empid ORDER BY ordermonth
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
Однако использование LAG и LEAD в подобных случаях выглядят заметно проще и интуитивно понятней. Такие вычисления уместны также и для транзакционных данных. Для примера, взяв таблицу Orders в базе данных Northwind, предположим, что вам нужно сравнить каждый текущий заказ работника с предыдущим заказом с тем, чтобы вычислить, сколько дней прошло между датами текущего и предыдущего заказов. Предшествование заказов работника основано на OrderDate, OrderID. OrderID используется как разрыв цепочки (tiebreaker). Без использования функции LAG возможны несколько решений. Например, вы можете использовать подзапрос с TOP 1 и соединения: SELECT Cur.EmployeeID,
Cur.OrderID AS CurOrderID, Prv.OrderID AS PrvOrderID,
Cur.OrderDate AS CurOrderDate, Prv.OrderDate AS PrvOrderDate,
Cur.RequiredDate AS CurReqDate, Prv.RequiredDate AS PrvReqDate
FROM (SELECT EmployeeID, OrderID, OrderDate, RequiredDate,
(SELECT TOP(1) OrderID
FROM dbo.Orders AS O2
WHERE O2.EmployeeID = O1.EmployeeID
AND (O2.OrderDate < O1.OrderDate
OR O2.OrderDate = O1.OrderDate
AND O2.OrderID < O1.OrderID)
ORDER BY OrderDate DESC, OrderID DESC) AS PrvOrderID
FROM dbo.Orders AS O1) AS Cur
LEFT OUTER JOIN dbo.Orders AS Prv
ON Cur.PrvOrderID = Prv.OrderID
ORDER BY Cur.EmployeeID, Cur.OrderDate, Cur.OrderID;
Этот запрос сопоставляет текущий заказ с предшествующим заказом. Конечно, теперь вы можете рассчитать разности для значений атрибутов текущей и предшествующей строки. Это решение весьма неэффективно. Вы можете несколько оптимизировать его путем использования оператора APPLY: SELECT Cur.EmployeeID,
Cur.OrderID AS CurOrderID, Prv.OrderID AS PrvOrderID,
Cur.OrderDate AS CurOrderDate, Prv.OrderDate AS PrvOrderDate,
Cur.RequiredDate AS CurReqDate, Prv.RequiredDate AS PrvReqDate
FROM dbo.Orders AS Cur
OUTER APPLY
(SELECT TOP(1) OrderID, OrderDate, RequiredDate
FROM dbo.Orders AS O
WHERE O.EmployeeID = Cur.EmployeeID
AND (O.OrderDate < Cur.OrderDate
OR (O.OrderDate = Cur.OrderDate
AND O.OrderID < Cur.OrderID))
ORDER BY OrderDate DESC, OrderID DESC) AS Prv
ORDER BY Cur.EmployeeID, Cur.OrderDate, Cur.OrderID;
При существовании индекса на EmployeeID, OrderDate, OrderID с добавлением RequiredDate для покрытия запрос будет выполнять операцию поиска (seek) в индексе для каждой внешней строки. Это решение, хоть и эффективнее предыдущего, но весьма незначительно. Если у вас N записей в таблице, и стоимость операции поиска составляет S чтений, то стоимость данного решения N + N*S. Например, для таблицы с 1.000.000 записями и 3-мя уровнями в индексе, этот запрос потребует свыше 3.000.000 чтений. Вы можете оптимизировать решение (как и решение предыдущей задачи определения тренда) путем использования функции ROW_NUMBER, которая имеется в SQL Server 2005: WITH OrdersRN AS
(
SELECT EmployeeID, OrderID, OrderDate, RequiredDate,
ROW_NUMBER() OVER(PARTITION BY EmployeeID
ORDER BY OrderDate, OrderID) AS rn
FROM dbo.Orders
)
SELECT Cur.EmployeeID,
Cur.OrderID AS CurOrderID, Prv.OrderID AS PrvOrderID,
Cur.OrderDate AS CurOrderDate, Prv.OrderDate AS PrvOrderDate,
Cur.RequiredDate AS CurReqDate, Prv.RequiredDate AS PrvReqDate
FROM OrdersRN AS Cur
LEFT OUTER JOIN OrdersRN AS Prv
ON Cur.EmployeeID = Prv.EmployeeID
AND Cur.rn = Prv.rn + 1
ORDER BY Cur.EmployeeID, Cur.OrderDate, Cur.OrderID;
Это решение потребует 2-х операций сканирования (scan) индекса плюс, конечно, стоимость соединения. С функцией LAG потенциально существует возможность решения с единственной операцией упорядоченного сканирования индекса. Решение могло бы выглядеть так: SELECT EmployeeID,
OrderID AS CurOrderID,
LAG(OrderID, 1, NULL)
OVER(PARTITION BY EmployeeID
ORDER BY OrderDate, OrderID) AS PrvOrderID,
OrderDate AS CurOrderDate,
LAG(OrderDate, 1, NULL)
OVER(PARTITION BY EmployeeID
ORDER BY OrderDate, OrderID) AS PrvOrderDate,
RequiredDate AS CurReqDate,
LAG(RequiredDate, 1, NULL)
OVER(PARTITION BY EmployeeID
ORDER BY OrderDate, OrderID) AS PrvReqDate
FROM Orders;
Видно, что это решение проще, интуитивно понятнее и потенциально имеет лучшую производительность. Функцию LAG можно также использовать как средство достижения цели в других задачах. Например, в широко распространенной классической задаче, упомянутой ранее, когда вам нужно определить пропуски в последовательности. Дана следующая таблица T1: USE tempdb;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1
GO
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.T1(col1) VALUES(1);
INSERT INTO dbo.T1(col1) VALUES(2);
INSERT INTO dbo.T1(col1) VALUES(3);
INSERT INTO dbo.T1(col1) VALUES(100);
INSERT INTO dbo.T1(col1) VALUES(101);
INSERT INTO dbo.T1(col1) VALUES(103);
INSERT INTO dbo.T1(col1) VALUES(104);
INSERT INTO dbo.T1(col1) VALUES(105);
INSERT INTO dbo.T1(col1) VALUES(106);
Вам нужно определить пропуски в значениях col1. Покажем, как можно использовать функцию LEAD для решения этой задачи: WITH C AS ( SELECT col1 AS cur, LEAD(col1, 1, NULL) OVER(ORDER BY col1) AS nxt FROM dbo.T1 ) SELECT cur + 1 AS start_gap, nxt - 1 AS end_gap FROM C WHERE nxt - cur > 1; Как вы можете видеть, решение просто, интуитивно понятно и может быть оптимизировано с помощью выполнения единственной операции упорядоченного сканирования индекса, определенного на col1. Выводы: функции LAG и LEAD дают возможность решить общие проблемы, когда нужно получить значения из строки, находящейся на некотором расстоянии от текущей строки. Примеры таких задач, рассмотренных в этом разделе, включают: вычисления трендов, сопоставление значений текущей и предыдущей транзакций, определение пропусков в последовательности и, конечно, многие другие. (Продолжение следует...) Полезная информация§ Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Хотите поддержать проект? Вот инструкция по применению. :-) КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
