| ← Август 2008 → | ||||||
|
1
|
3
|
|||||
|---|---|---|---|---|---|---|
|
4
|
5
|
6
|
7
|
8
|
10
|
|
|
11
|
12
|
13
|
14
|
15
|
17
|
|
|
18
|
19
|
20
|
21
|
22
|
24
|
|
|
25
|
26
|
27
|
28
|
29
|
31
|
|
За последние 60 дней ни разу не выходила
Сайт рассылки:
http://www.sql-ex.ru
Открыта:
15-09-2004
Статистика
0 за неделю
Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 203
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 203 (16 августа 2008 г.)Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 54-х задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Реализовали раскраску скриптов на форуме. За основу взят базовый стиль Management Studio SS2005. § Изменения среди лидеров (решенные за неделю задачи третьего этапа): § Одна задача до третьего этапа осталась: § Новые лица в ТОР 100 и вернувшиеся туда: § Продвинулись в рейтинге: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3812 Число участников рейтинга - 16865 Число участников второго этапа - 1489 Число участников третьего этапа - 35 Сертифицировано на сайте - 310 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLТребования к SQL Server по усилению поддержки: предложение OVER() и упорядоченные вычисления (продолжение, начало в вып.195-199, 202)Itzik Ben-Gan и Sujata Mehta (оригинал: SQL Server Feature Enhancement Request - OVER Clause and Ordered Calculations )Перевод Карасева Н.В. Функция ROW_NUMBER:Для нумерации записей в SQL Server 2005 используется функция ROW_NUMBER. ROW_NUMBER выдает последовательные целые числа для строк результирующего набора запроса согласно порядку, определяющему позицию строки по отношению к другим строкам результирующего набора (или дополнительно для разбиений). Нумерация всегда начинается с 1 и увеличивается на единицу для каждой следующей строки в соответствии с заданной сортировкой. Функция ROW_NUMBER имеет широкое практическое применение, выходящее далеко за пределы классического разбиения на страницы, вывода n первых строк на основе критерия сортировки в пределах разбиений, подсчета существующих и отсутствующих диапазонов данных, вычисления медианных значений, сортировки иерархических структур и т.д. Функции RANK() и DENSE_RANK:Функции RANK() и DENSE_RANK в SQL Server 2005 похожи на функцию ROW_NUMBER тем, что их значения - целочисленные ранги строк результирующего набора, зависящие от заданного списка сортировки. Однако функция ROW_NUMBER дает для каждой строки отличные значения. Значения же функций RANK и DENSE_RANK одинаковы для всех строк, имеющих совпадающие значения в списке столбцов, по которым выполняется сортировка. Значение функции RANK основано на количестве строк, имеющих меньшие значения по указанным в списке ORDER BY полям, +1. Строки с равными значениями в столбцах сортировки получают одно и то же значение RANK, и значения функции RANK могут иметь пропуски. Значение DENSE_RANK основано на количестве различных строк, имеющих меньшие значения по столбцам сортировки, +1. Записи с равными значениями в полях сортировки получают одно и то же значение; значения функции DENSE_RANK не имеют пропусков. Функция NTILE:Функция NTILE разделяет набор данных (возможно, внутри разбиений) на указанное количество групп (n), назначая номер группе от 1 до n согласно указанной сортировке. Число строк в группе определяется следующим образом: (число строк)/n (целочисленное деление). Если остаток от деления равен r, то дополнительная строка добавляется к первым r группам. Теперь давайте рассмотрим несколько примеров, чтобы увидеть, насколько просто и интуитивно понятно использование аналитических ранжирующих функций. Для этой цели, создадим и заполним таблицу заказов Orders. -----------------------------------------------------------------------
--Аналитические ранжирующие функции
-- Создание и заполнение таблицы Orders
-----------------------------------------------------------------------
USE tempdb;
GO
IF OBJECT_ID('dbo.Orders') IS NOT NULL
DROP TABLE dbo.Orders;
GO
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
orderdate DATETIME NOT NULL,
empid INT NOT NULL,
custid VARCHAR(5) NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED(orderid)
);
CREATE UNIQUE CLUSTERED INDEX idx_UC_orderdate_orderid
ON dbo.Orders(orderdate, orderid);
SET NOCOUNT ON;
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30001, '20030802', 3, 'B', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10001, '20031224', 1, 'C', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10005, '20031224', 1, 'A', 30);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(40001, '20040109', 4, 'A', 40);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10006, '20040118', 1, 'C', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(20001, '20040212', 2, 'B', 20);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(40005, '20040212', 4, 'A', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(20002, '20040216', 2, 'C', 20);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30003, '20040418', 3, 'B', 15);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30004, '20040418', 3, 'B', 20);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30007, '20040907', 3, 'C', 30);
GO
SELECT * FROM Orders;
-----------------------------------------------------------------------
Результат запроса показан в таблице 3-2. Orderid orderdate empid custid qty 30001 8/2/2003 3 B 10 10001 12/24/2003 1 C 10 10005 12/24/2003 1 A 30 40001 1/9/2004 4 A 40 10006 1/18/2004 1 C 10 20001 2/12/2004 2 B 20 40005 2/12/2004 4 A 10 20002 2/16/2004 2 C 20 30003 4/18/2004 3 B 15 30004 4/18/2004 3 B 20 30007 9/7/2004 3 C 30 Таблица 3-2. Содержимое таблицы Orders Давайте применим предложение OVER для всех 4 функций ранжирования как с конструкцией PARTITION BY, так и без неё, для демонстрации их использования и уяснения разницы между ними. ----------------------------------------------------------------- --Аналитические ранжирующие функции -- без PARTITION BY ----------------------------------------------------------------- SELECT orderid, qty, ROW_NUMBER() OVER(ORDER BY qty) AS rownum, RANK() OVER(ORDER BY qty) AS rnk, DENSE_RANK() OVER(ORDER BY qty) AS densernk, NTILE(4) OVER(ORDER BY qty) AS ntile4 FROM dbo.Orders ORDER BY qty; GO ----------------------------------------------------------------- orderid qty rownum rnk densernk ntile4 30001 10 1 1 1 1 10001 10 2 1 1 1 10006 10 3 1 1 1 40005 10 4 1 1 2 30003 15 5 5 2 2 30004 20 6 6 3 2 20002 20 7 6 3 3 20001 20 8 6 3 3 10005 30 9 9 4 3 30007 30 10 9 4 4 40001 40 11 11 5 4 Таблица 3-3 Аналитические ранжирующие функции без разбиений Результаты приведены в таблице 3-3, и по ним видны отличия функций ранжирования. Если мы посмотрим на результаты, то номера строк (rownum) последовательно возрастают и не зависят от того, меняется значение сортировки или нет, т.е. значения столбца rownum получают свои значения на основе упорядочения по столбцу qty и сохраняют приращение 1, даже если значение qty не изменяется. RANK дает одинаковые значения в столбце rnk, если значения столбца qty одинаковы, но при изменении значения qty значения rnk подскакивают. Значения DENSE_RANK одинаковы для совпадающих значений qty, но когда qty увеличивается, приращение DENSE_RANK равно 1 и значения этой функции не имеют пропусков. NTILE разделяет результирующий набор на заданное число групп, в нашем случае - 4 (NTILE (4)). Строки результирующего набора равномерно распределяется по количеству разбиений (11/4 = 2). Оставшиеся строки (остаток равен 3), распределяются по одной, начиная с первой группы, пока строки не закончатся (поэтому число строк в первых трех группах равно 2+1 = 3). (Продолжение следует...) Полезная информация§ Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях: КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
