| ← Ноябрь 2008 → | ||||||
|
2
|
||||||
|---|---|---|---|---|---|---|
|
5
|
6
|
7
|
9
|
|||
|
10
|
11
|
12
|
13
|
14
|
16
|
|
|
17
|
18
|
19
|
20
|
21
|
23
|
|
|
24
|
25
|
26
|
27
|
28
|
30
|
|
За последние 60 дней ни разу не выходила
Сайт рассылки:
http://www.sql-ex.ru
Открыта:
15-09-2004
Статистика
0 за неделю
Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 214
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)"Выпуск 214 от 01 ноября 2008 г.Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 51-й задачи первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Новыми данными от автора усилена проверка задачи 89. § В блоке новостей на главной странице сайта разместил картинку географии посетителей сайта. Статистика еще не накоплена, поэтому карта пока не отражает реального положения, которое я оцениваю по статистике LiveInternet. § Рекомендую почитать форум. На этой неделе опубликована масса классных решений многих задач, вызванная желанием orange перерешать все задачи перед 3 этапом. Приглашаю всех поучиться писать запросы у orange, anddros, @Nikotin - главных авторов постов прошедшей недели. § На этой неделе дважды сменился лидер. Незначительная разница в баллах обещает нам захватывающее соперничество между $erges и @Nikotin при появлении на 3 этапе новых задач. Одна уже скоро появится. Автор - @Nikotin. :-) § Одна задача до третьего этапа осталась: § Новые лица в ТОР 100 и вернувшиеся туда: § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3834 Число участников рейтинга - 19015 Число участников второго этапа - 1588 Число участников третьего этапа - 41 Сертифицировано на сайте - 335 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLТребования к SQL Server по усилению поддержки: предложение OVER() и упорядоченные вычисления (продолжение, начало в вып.195-199, 202-213)Itzik Ben-Gan и Sujata Mehta (оригинал: SQL Server Feature Enhancement Request - OVER Clause and Ordered Calculations )Перевод: Карасева Н.В. v. Ограничение окон (ROWS, RANGE)Подчиненные предложения ROWS и RANGE предложения OVER применимы к некоторым аналитическим функциям - главным образом агрегатным, но также и другим, и они позволяют вам определить окно строк, на которых будет вычисляться функция. ROWS позволяет определить окно относительно текущей строки, и полезно для вычислений со скользящим окном. Синтаксис для конструкции ROWS следующий: ROWS BETWEEN <выражение_от> AND <выражение_до> <выражение_от> может быть одним из: <выражение_до> может быть одним из: <целочисленное_выражение> представляет смещение в терминах числа строк, находящихся перед (PRECEDING) или после (FOLLOWING) текущей строки. Другие варианты понятны без объяснений. Как пример для использования конструкции ROWS, рассмотрим таблицу EmpOrders, приведенную ранее. Предположим, что требуется вычислить агрегированные значения (суммарное и среднее значение qty) для работника (empid) за 2 месяца, предшествующих текущему месяцу, и текущий месяц. Без предложения OVER вы использовали бы или самосоединения, или подзапросы. Вот решение, использующее самосоединение: -- Скользящие агрегаты для служащего за три месяца перед текущим
SELECT O1.empid,
CONVERT(VARCHAR(7), O1.ordermonth, 121) AS tomonth, O1.qty AS qtythismonth,
SUM(O2.qty) AS totalqty,
AVG(1.*O2.qty) AS avgqty
FROM dbo.EmpOrders AS O1
JOIN dbo.EmpOrders AS O2
ON O2.empid = O1.empid
AND (O2.ordermonth > DATEADD(month, -3, O1.ordermonth)
AND O2.ordermonth <= O1.ordermonth)
GROUP BY O1.empid, O1.ordermonth, O1.qty
ORDER BY O1.empid, O1.ordermonth;
Решение с предложением OVER могло бы выглядеть так: SELECT empid,
CONVERT(VARCHAR(7), ordermonth, 121) AS tomonth, qty AS qtythismonth,
SUM(qty) OVER(PARTITION BY empid
ORDER BY ordermonth
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS totalqty,
AVG(1.*qty) OVER(PARTITION BY empid
ORDER BY ordermonth
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avgqty
FROM dbo.EmpOrders
ORDER BY empid, ordermonth;
Видно, что последнее решение проще и интуитивно понятнее. Более того, в его оценку стоимости не входит join и отсутствует повторное многократное сканирование одних и тех же строк. Если предложение BETWEEN отсутствует и никакой верхний предел не определен, то предполагается CURRENT ROW (текущая строка) в качестве верхнего предела. Например, вместо ROWS BETWEEN 2 PRECEDING AND CURRENT ROW вы можете указать ROWS 2 PRECEDING. Если конструкция окна вообще отсутствует, то предполагается ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Конструкция окна RANGE позволяет вам определить ранг, основанный на логическом смещении (в терминах упорядоченных значений) в отличие от ROWS, где вы определяете физическое смещение (в терминах числа строк). Логическое смещение основано на значении в столбце текущей строки, по которому выполняется сортировка, и оно может быть либо числом, либо датой. Важным элементом подчиненного предложения RANGE является возможность использования отсутствующей в SQL Server поддержки временных (datetime) интервалов. Конструкция RANGE оказывается полезной, когда число строк в окне может меняться. Например, предположим, что в таблице EmpOrders не гарантируется отсутствие пропусков в деятельности работника. Работник может работать в январе и марте, но не работать в феврале. Понятно, что в этом случае последнее представленное решение с использованием ROWS логически не эквивалентно решению с использованием самосоединения. Решение с ROWS определяет окно, основанное на количестве строк, вместо рассмотрения периода времени, который начался 2 месяца назад и заканчивается текущим месяцем. Конструкция RANGE с поддержкой интервалов разрешает эту проблему: SELECT empid,
CONVERT(VARCHAR(7), ordermonth, 121) AS tomonth, qty AS qtythismonth,
SUM(qty) OVER(PARTITION BY empid
ORDER BY ordermonth
RANGE BETWEEN INTERVAL '2' MONTH PRECEDING
AND CURRENT ROW) AS totalqty,
AVG(1.*qty) OVER(PARTITION BY empid
ORDER BY ordermonth
RANGE BETWEEN INTERVAL '2' MONTH PRECEDING
AND CURRENT ROW) AS avgqty
FROM dbo.EmpOrders
ORDER BY empid, ordermonth;
Без поддержки для интервалов решение этой задачи возможно, но оно сложно и имеет худшую стоимость, т.к. не может полагаться на индекс во избежание сортировки: SELECT empid,
CONVERT(VARCHAR(7), ordermonth, 121) AS tomonth, qty AS qtythismonth,
SUM(qty) OVER(PARTITION BY empid
ORDER BY YEAR(ordermonth) * 100 + MONTH(ordermonth)
RANGE BETWEEN 2 PRECEDING
AND CURRENT ROW) AS totalqty,
AVG(1.*qty) OVER(PARTITION BY empid
ORDER BY YEAR(ordermonth) * 100 + MONTH(ordermonth)
RANGE BETWEEN 2 PRECEDING
AND CURRENT ROW) AS avgqty
FROM dbo.EmpOrders
ORDER BY empid, ordermonth;
Здесь сортировка основана на целочисленном значении, полученном конкатенацией года и месяца (YEAR (ordermonth) * 100 + MONTH (ordermonth)). Например, июль 1996 представлен значением 199607. В виду того, что упорядочение идет по этим целочисленным значениям, выражение 2 PRECEDING представляет две единицы до текущего значения в порядке сортировки - другими словами, 2 месяца тому назад. Но снова, это добавляет сложность и ухудшает производительность. Заметим, что вы можете также ссылаться на выражение сортировки в выражениях границы конструкции RANGE. Например, предположим, что имеется выражение сортировки ORDER BY col1, тогда RANGE BETWEEN 2 PRECEDING эквивалентно ROWS BETWEEN (col1 - 2) PRECEDING. Однако некоторые выражения просто должны включать выражение сортировки, например, col1/2. Ввиду того, что конструкция RANGE основана на логическом смещении относительно значения сортировки в текущей строке, включение подчиненного предложения ORDER BY является обязательным и ограничивается одним выражением. Когда ключевое слово BETWEEN отсутствует и <выражение_до> не задано, по умолчанию этой границей окна является текущая строка (CURRENT ROW). Как вы уже поняли, конструкции ROWS и RANGE очень удобны для вычислений на скользящих окнах. Мы надеемся, что кроме расширения поддержки предложения OVER, Server SQL также введет поддержку интервалов, позволяющих получать простые, естественные и эффективные решения этих общих задач. (Продолжение следует...) Полезная информация§ Приглашаем вас посетить новый проект - Интерактивный учебник по SQL. § Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Хотите поддержать проект? Вот инструкция по применению. :-) КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
