| ← Сентябрь 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) 206
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 206 (06 сентября 2008 г.)Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 51-й задачи первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Выставил простую задачу на обучающий этап; ее номер 41. § Автор ($erges) усилил проверку задачи 15 в ответ на сообщение от fix4fix. § Произошло обновление задач и изменение сертификационных требований: § Одна задача до 3 этапа осталась: § Новые лица в ТОР 100 и вернувшиеся туда: § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3828 Число участников рейтинга - 17335 Число участников второго этапа - 1525 Число участников третьего этапа - 38 Сертифицировано на сайте - 315 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLТребования к SQL Server по усилению поддержки: предложение OVER() и упорядоченные вычисления (продолжение, начало в вып.195-199, 202-205)Itzik Ben-Gan и Sujata Mehta (оригинал: SQL Server Feature Enhancement Request - OVER Clause and Ordered Calculations )Перевод Карасева Н.В. Вычисления с помощью аналитических ранжирующих функций позволяют решить массу практических задач, таких как разбиение на страницы, поиск наличия и отсутствия диапазонов данных, медианные вычисления, иерархические сортировки и многие другие. Ниже приведены примеры пары решений, демонстрирующих возможности до и после SQL Server 2005. Задачи отсутствующих или имеющихся диапазонов значений возникают во многих производственных системах в разных формах, например, в отчетах о наличии/отсутствии. Это могут быть отсутствующие данные целого типа (integer) или типа даты/времени (datetime) . Решение приводится ниже. ---------------------------------------------------------------------
-- Отсутствующие и присутствующие диапазоны данных
-- или горы и долины
---------------------------------------------------------------------
SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
GO
INSERT INTO dbo.T1 VALUES(1);
INSERT INTO dbo.T1 VALUES(2);
INSERT INTO dbo.T1 VALUES(3);
INSERT INTO dbo.T1 VALUES(100);
INSERT INTO dbo.T1 VALUES(101);
INSERT INTO dbo.T1 VALUES(102);
INSERT INTO dbo.T1 VALUES(103);
INSERT INTO dbo.T1 VALUES(500);
INSERT INTO dbo.T1 VALUES(997);
INSERT INTO dbo.T1 VALUES(998);
INSERT INTO dbo.T1 VALUES(999);
INSERT INTO dbo.T1 VALUES(1000);
GO
--Долины, решение на 2000
SELECT col1 + 1 AS start_range,
(SELECT MIN(col1) FROM dbo.T1 AS B
WHERE B.col1 > A.col1) AS end_range
FROM dbo.T1 AS A
WHERE NOT EXISTS
(SELECT * FROM dbo.T1 AS B
WHERE B.col1 = A.col1 + 1)
AND col1 < (SELECT MAX(col1) FROM dbo.T1);
-- Долины, 2005, решение с номерами строк
WITH C AS
(
SELECT col1, ROW_NUMBER() OVER(ORDER BY col1) AS rn
FROM dbo.T1
)
SELECT Cur.col1 + 1 AS start_range, Nxt.col1 - 1 AS end_range
FROM C AS Cur
JOIN C AS Nxt
ON Nxt.rn = Cur.rn + 1
WHERE Nxt.col1 - Cur.col1 > 1;
-- Горы, решение на 2000
SELECT MIN(col1) AS start_range, MAX(col1) AS end_range
FROM (SELECT col1,
(SELECT MIN(col1) FROM dbo.T1 AS B
WHERE B.col1 >= A.col1
AND NOT EXISTS
(SELECT * FROM dbo.T1 AS C
WHERE C.col1 = B.col1 + 1)) AS grp
FROM dbo.T1 AS A) AS D
GROUP BY grp;
--Горы, 2005, решение с номерами строк
SELECT MIN(col1) AS start_range, MAX(col1) AS end_range
FROM (SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp
FROM dbo.T1) AS D
GROUP BY grp;
GO
---------------------------------------------------------------------
Приведенные выше примеры показывают, что решения на SQL Server 2000 не только не понятны интуитивно, но и демонстрируют неудовлетворительную производительность. Решение для SQLServer 2005 значительно проще и производительней. ---------------------------------------------------------------------
-- Вычисления медианы
---------------------------------------------------------------------
-- Решение на SQL Server 2000
USE pubs;
GO
IF OBJECT_ID('dbo.fn_median') IS NOT NULL
DROP FUNCTION dbo.fn_median;
GO
CREATE FUNCTION dbo.fn_median(@stor_id AS CHAR(4)) RETURNS NUMERIC(11, 1)
AS
BEGIN
RETURN
(
(SELECT MAX(qty) FROM
(SELECT TOP 50 PERCENT qty FROM dbo.sales
WHERE stor_id = @stor_id
ORDER BY qty) AS H1)
+
(SELECT MIN(qty) FROM
(SELECT TOP 50 PERCENT qty FROM dbo.sales
WHERE stor_id = @stor_id
ORDER BY qty DESC) AS H2)
) / 2.
END
GO
SELECT stor_id, dbo.fn_median(stor_id) AS median
FROM dbo.stores;
GO
-- Решение на SQL Server 2005
WITH salesRN AS
(
SELECT stor_id, qty,
ROW_NUMBER() OVER(PARTITION BY stor_id ORDER BY qty) AS rownum,
COUNT(*) OVER(PARTITION BY stor_id) AS cnt
FROM sales
)
SELECT stor_id, CAST(AVG(1.*qty) AS NUMERIC(11, 1)) AS median
FROM salesRN
WHERE rownum IN ((cnt+1)/2, (cnt+2)/2)
GROUP BY stor_id;
GO
Этот пример для вычисления медианы также демонстрирует упрощение кода при использовании предложения OVER. (Продолжение следует...) Полезная информация§ Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях: КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
