| ← Сентябрь 2008 → | ||||||
|
1
|
2
|
3
|
4
|
5
|
7
|
|
|---|---|---|---|---|---|---|
|
8
|
9
|
10
|
11
|
12
|
14
|
|
|
15
|
16
|
17
|
18
|
19
|
21
|
|
|
22
|
23
|
24
|
25
|
26
|
28
|
|
|
29
|
30
|
|||||
За последние 60 дней ни разу не выходила
Сайт рассылки:
http://www.sql-ex.ru
Открыта:
15-09-2004
Статистика
0 за неделю
Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 207
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 207 (13 сентября 2008 г.)Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 51-й задачи первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Устранена избыточность формулировки задачи 54 (рейтинговый этап). § С результатом (задач 138, время 6.009) к третьему этапу подошла Umrikhina. § Новые лица в ТОР 100 и вернувшиеся туда: § Изменения среди лидеров (решенные за неделю задачи третьего этапа): § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3837 Число участников рейтинга - 17541 Число участников второго этапа - 1531 Число участников третьего этапа - 38 Сертифицировано на сайте - 316 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLТребования к SQL Server по усилению поддержки: предложение OVER() и упорядоченные вычисления (продолжение, начало в вып.195-199, 202-206)Itzik Ben-Gan и Sujata Mehta (оригинал: SQL Server Feature Enhancement Request - OVER Clause and Ordered Calculations )Перевод: Карасева Н.В. Агрегатные функции - реализация в SQL Server 2005Что касается скалярных агрегатных функций, таких как COUNT, SUM, MAX и MIN, то в SQL Server 2005 для них есть только конструкция PARTITION BY в предложении OVER. SQL Server 2005 пока не поддерживает ORDER BY и оконные конструкции (ROWS и RANGE). Когда указано предложение PARTITION BY, агрегатная функция применяется к окну записей, которые имеют одинаковые значения в столбцах, перечисленных в предложении PARTITION BY. Если PARTITION BY не задано, то агрегатная функция применяется ко всему результирующему набору. Цель использования предложения OVER со скалярными агрегатными функциями - вычисление для каждой строки результирующего набора агрегата на группе значений окна, которое простирается за пределы строки, без использования предложение GROUP BY. Другими словами, предложение OVER позволяет нам добавить агрегированные значения к результатам негруппированного запроса так, что и атрибуты строки, и агрегированные значения могут присутствовать совместно в результате; при этом агрегатные значения могут вычисляться на подмножестве данных, определяемых окном. Общая форма аналитических агрегатных функций, поддерживаемых в SQL Server 2005, имеет вид: <агрегатная функция> OVER ([PARTITION BY <список столбцов>]) Давайте рассмотрим пример, демонстрирующий концепцию вычисления агрегатов на основе окна. Мы будем использовать данные учебной базы AdventureWorks для наших запросов (http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004 - прим. перев.). Выполним следующий запрос, чтобы увидеть результирующий набор данных. --------------------------------------------------------------------------------
-- Скалярные агрегатные функции
-- Пример данных
--------------------------------------------------------------------------------
USE AdventureWorks;
SELECT SalesOrderID AS orderid, SalesOrderDetailID AS line,
ProductID AS productid, OrderQty AS qty, LineTotal AS val
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659, 43660);
GO
--------------------------------------------------------------------------------
Orderid Line Productid qty val
43659 1 776 1 2024.994
43659 2 777 3 6074.982
43659 3 778 1 2024.994
43659 4 771 1 2039.994
43659 5 772 1 2039.994
43659 6 773 2 4079.988
43659 7 774 1 2039.994
43659 8 714 3 86.5212
43659 9 716 1 28.8404
43659 10 709 6 34.2
43659 11 712 2 10.373
43659 12 711 4 80.746
43660 13 762 1 419.4589
43660 14 758 1 874.794
--------------------------------------------------------------------------------
Таблица 3-8. Данные из таблицы SalesOrderDetail базы данных AdventureWorks
для агрегатных оконных функций
Пусть нам нужно вывести агрегирующие значения и одновременно атрибуты записей. Например, для каждой строки заказа из таблицы 3-8 нам получить все атрибуты и следующие агрегированные значения: слово First, если это первая строка в заказе (т.е. минимальный номер строки), Last, если это последняя строка (т.е. максимальный номер строки), и mid, если ни то, и ни другое. Наконец, нам нужно получить процентное отношение количества к полному количеству заказа и процент относительно общего объема заказа. Таблица 3-9 показывает требуемые результаты. orderid line Pos productid qty qtyper val valper -------------------------------------------------------------------- 43659 1 First 776 1 3.85 2024.994000 9.85 43659 2 Mid 777 3 11.54 6074.982000 29.54 43659 3 Mid 778 1 3.85 2024.994000 9.85 43659 4 Mid 771 1 3.85 2039.994000 9.92 43659 5 Mid 772 1 3.85 2039.994000 9.92 43659 6 Mid 773 2 7.69 4079.988000 19.84 43659 7 Mid 774 1 3.85 2039.994000 9.92 43659 8 Mid 714 3 11.54 86.521200 0.42 43659 9 Mid 716 1 3.85 28.840400 0.14 43659 10 Mid 709 6 23.08 34.200000 0.17 43659 11 Mid 712 2 7.69 10.373000 0.05 43659 12 Last 711 4 15.38 80.746000 0.39 43660 13 First 762 1 50 419.458900 32.41 43660 14 Last 758 1 50 874.794000 67.59 Таблица 3-9. Оконные скалярные агрегаты с атрибутами записей Если на минуту отставить в сторону предложение OVER, то первое, что приходит на ум, - вычисление агрегированных значений по отдельности в подзапросах, вызов их из внешнего запроса, возвращающего атрибуты базовой строки, и их корреляция. Решим задачу коррелированными подзапросами. -------------------------------------------------------------------------------------
-- Скалярные агрегатные функции
-- Коррелированный подзапрос
-------------------------------------------------------------------------------------
USE AdventureWorks;
SELECT orderid, line,
CASE line WHEN first THEN 'first' WHEN last THEN 'last'
ELSE 'mid' END AS pos, productid,
qty, CAST(1.*qty / totalqty * 100 AS DECIMAL(5, 2)) AS qtyper,
val, CAST(val / totalval * 100 AS DECIMAL(5, 2)) AS valper
FROM (SELECT SalesOrderID AS orderid, SalesOrderDetailID AS line,
ProductID AS productid, OrderQty AS qty, LineTotal AS val,
(SELECT SUM(OrderQty)
FROM Sales.SalesOrderDetail AS I
WHERE I.SalesOrderID = O.SalesOrderID) AS totalqty,
(SELECT SUM(LineTotal)
FROM Sales.SalesOrderDetail AS I
WHERE I.SalesOrderID = O.SalesOrderID) AS totalval,
(SELECT MIN(SalesOrderDetailID)
FROM Sales.SalesOrderDetail AS I
WHERE I.SalesOrderID = O.SalesOrderID) AS first,
(SELECT MAX(SalesOrderDetailID)
FROM Sales.SalesOrderDetail AS I
WHERE I.SalesOrderID = O.SalesOrderID) AS last
FROM Sales.SalesOrderDetail AS O
WHERE SalesOrderID IN(43659, 43660)) AS D
ORDER BY orderid, line;
GO
Запрос, генерирующий производную таблицу D, использует коррелированный подзапрос для каждого агрегированного значения, которое нам нужно -SUM(OrderQty), SUM(LineTotal), MIN(SalesOrderDetailID) и MAX(SalesOrderDetailID). Теперь внешний запрос для таблицы D может выполнить вычисления, включая базовые атрибуты и агрегированные значения. Это решение имеет два главных недостатка. Во-первых, код повторяется и он длинный из-за использования коррелированных подзапросов. Но что более важно, так это то, что, как в примере о нарастающих итогах из 1 раздела, каждый подзапрос требует отдельного сканирования базовых данных, что дает плохую производительность. Теперь посмотрим, как эта задача решается с помощью агрегатных функций и предложения OVER в SQL Server 2005. --------------------------------------------------------------------------------
---Скалярные агрегатные функции
-- Вычисления с использованием оконных агрегатных функций
-- SQL Sever 2005
--------------------------------------------------------------------------------
USE AdventureWorks;
SELECT SalesOrderID AS orderid, SalesOrderDetailID AS line,
CASE SalesOrderDetailID
WHEN MIN(SalesOrderDetailID) OVER(PARTITION BY SalesOrderID) THEN 'first'
WHEN MAX(SalesOrderDetailID) OVER(PARTITION BY SalesOrderID) THEN 'last'
ELSE 'mid'
END AS pos, ProductID AS productid, OrderQty AS qty,
CAST(1.*OrderQty/SUM(OrderQty) OVER(PARTITION BY SalesOrderID)*100
AS DECIMAL(5, 2)) AS qtyper,
LineTotal AS val,
CAST(LineTotal/SUM(LineTotal) OVER(PARTITION BY SalesOrderID)*100
AS DECIMAL(5, 2)) AS valper
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659, 43660)
ORDER BY orderid, line;
GO
В этом решении, мы просто вставили агрегатные функции с предложением OVER в список предложения SELECT вместе с базовыми атрибутами. Мы написали PARTITION BY SalesOrderID, потому что мы хотим получить окно со всеми строками заказа, имеющими тот же SalesOrderID, что и в текущей базовой строке. Это решение вычисляет значения всех агрегатных функций, которые логически разделяют одно и то же окно (строки заказа, которые имеют одно и то же значение SalesOrderID), получаемое при том же самом сканировании данных, что обеспечивает лучшую производительность, чем решение подзапросами. Мы легко увидим эту эффективность, если рассмотрим план выполнения запроса: одно сканирование для наполнения окна и единственный оператор агрегации, вычисляющий все агрегатные значения. Предложение OVER дает нам хорошую производительность, а также простой код. (Продолжение следует...) Полезная информация§ Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях: КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
