| ← Ноябрь 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) 215
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)"Выпуск 215 от 08 ноября 2008 г.Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 51-й задачи первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Выполнил компенсацию времени в связи с недоступностью сайта со 2 по 4 ноября. § Изменения среди лидеров (решенные за неделю задачи третьего этапа): § Новые лица в ТОР 100 и вернувшиеся туда: § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3841 Число участников рейтинга - 19157 Число участников второго этапа - 1590 Число участников третьего этапа - 41 Сертифицировано на сайте - 338 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLТребования к SQL Server по усилению поддержки: предложение OVER() и упорядоченные вычисления (продолжение, начало в вып.195-199, 202-214)Itzik Ben-Gan и Sujata Mehta (оригинал: SQL Server Feature Enhancement Request - OVER Clause and Ordered Calculations )Перевод: Карасева Н.В. vi. Предложение DISTINCT для агрегатных функцийВ настоящее время агрегатные функции, основанные на предложении OVER, не поддерживают опцию DISTINCT. Также как для обычных агрегатных функций было бы полезно определить предложение DISTINCT для агрегатных функций, основанных на предложении OVER, особенно для функции COUNT: COUNT(DISTINCT <выражение>) OVER(<спецификация_over>) vii. FIRST_VALUE, LAST_VALUEFIRST_VALUE и LAST_VALUE - примеры двух других полезных функций, основанных на предложении OVER. Однако из всех функций, представленных до сих пор, эти будут, вероятно, наименее важными. Эти функции возвращают требуемое значение из первой/последней строки в группе (partition), где первая/последняя строка в группе, определяется предложением ORDER BY. Например, используя таблицу EmpOrders, предположим, что мы хотим вычислить разницу между количеством(qty) в текущей строке и количеством для работника за первый и последний месяц его трудовой деятельности. Простое использование MIN и MAX не хорошо здесь в виду того, что первый/последний здесь нужны в порядке ordermonth, а не в порядке qty(количества). Количество за первой месяц работы сотрудника не будет обязательно наименьшей величиной для него. Без функций FIRST_VALUE и LAST_VALUE, вы можете решить проблему, используя подзапросы с TOP (1): SELECT empid, ordermonth, qty as qtythismonth,
qty - (SELECT TOP(1) qty
FROM dbo.EmpOrders AS I
WHERE I.empid = O.empid
ORDER BY I.ordermonth) AS diff_first,
qty - (SELECT TOP(1) qty
FROM dbo.EmpOrders AS I
WHERE I.empid = O.empid
ORDER BY I.ordermonth DESC) AS diff_last
FROM dbo.EmpOrders AS O;
Однако, каждое такой подзапрос потребовал бы поиска по индексу (считаем, что такой индекс создан). С функциями FIRST_VALUE и LAST_VALUE вы можете упростить решение, и, кроме того, вычисления могли бы выполняться один раз на группу (partition): SELECT empid, ordermonth, qty as qtythismonth,
qty - FIRST_VALUE(qty) OVER(PARTITION BY empid ORDER BY ordermonth)
AS diff_first,
qty - LAST_VALUE(qty) OVER(PARTITION BY empid ORDER BY ordermonth)
AS diff_last
FROM dbo.EmpOrders;
Другими примерами использования этих функций служат сравнение текущих остатков с начальными остатками и конечными, текущий баланс счета в банке с первым и последним за период времени, и так далее. viii. Последовательные вычисления на упорядоченных данныхПоддержка всех аспектов предложения OVER, основанного на стандарте SQL, и включенных в другие платформы баз данных, было бы крупным шагом вперед. Однако существуют некоторые типы задач, требующие вычислений на упорядоченных данных с дополнительной накруткой. Мы назовем вычисления этого типа "прогрессивным". Такие вычисления выполняются на строках в окне, упорядоченных требуемым образом, и, в отличие от стандартных вычислений с предложением OVER, используют результат вычисления в предыдущей строке. Пример такой задачи представлен Marcello Poletti, SQL Server MVP. Дана следующая таблица T1: USE tempdb;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
sort_col INT NOT NULL PRIMARY KEY,
data_col INT NOT NULL
);
GO
INSERT INTO dbo.T1 VALUES(1, 10);
INSERT INTO dbo.T1 VALUES(4, -15);
INSERT INTO dbo.T1 VALUES(5, 5);
INSERT INTO dbo.T1 VALUES(6, -10);
INSERT INTO dbo.T1 VALUES(8, -15);
INSERT INTO dbo.T1 VALUES(10, 20);
INSERT INTO dbo.T1 VALUES(17, 10);
INSERT INTO dbo.T1 VALUES(18, -10);
INSERT INTO dbo.T1 VALUES(20, -30);
INSERT INTO dbo.T1 VALUES(31, 20);
Предположим, вам нужно вычислить неотрицательные суммы data_col при упорядочении по sort_col. Сумма должна быть неотрицательной; если это не так, то вместо суммы ставим 0 и используем его для вычислений в следующей строке. Вот требуемый результат: sort_col data_col non_negative_sum 1 10 10 4 -15 0 5 5 5 6 -10 0 8 -15 0 10 20 20 17 10 30 18 -10 20 20 -30 0 31 20 20 Существуют и другие задачи, требующие прогрессивных вычислений на упорядоченных данных, включая основанные на FIFO/LIFO условно-накопительные вычисления, некоторые типы запросов на базе временных (temporal) данных, и другие. В настоящее время единственный разумный способ в плане производительности - это решать подобные задачи в курсорах. У нас нет синтаксиса для элементов запроса, поддерживающих подобные вычисления, но мы заявляем об их необходимости. Представляется естественным, чтобы такие вычисления поддерживались предложением OVER, поскольку для них нужны определения группы (partitions), упорядочения и варианта окна. Проблема состоит в том, что должен быть способ доступа к результату вычисления в предыдущей строке. Решение могло бы пойти по пути разрешения вычислений, ссылающихся на алиас, обеспечиваемый вычислением (представляющим результат для предыдущей строки, который можно было бы рассматривать как рекурсивную ссылку). Вот пример такого предполагаемого синтаксиса: SELECT sort_col, data_col,
CASE WHEN PREVIOUS(non_negative_sum, 0)
OVER(ORDER BY sort_col) + data_col < 0
THEN 0
ELSE PREVIOUS(non_negative_sum, 0)
OVER(ORDER BY sort_col) + data_col
END AS non_negative_sum
FROM dbo.T1;
(Окончание следует...) Полезная информация§ Приглашаем вас посетить новый проект - Интерактивный учебник по SQL. § Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Хотите поддержать проект? Вот инструкция по применению. :-) КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
