Отправляет email-рассылки с помощью сервиса Sendsay

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru)

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 2.594 RSS
←   Май 2009   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


За последние 60 дней ни разу не выходила


Сайт рассылки: http://www.sql-ex.ru
Открыта: 15-09-2004

Автор
Serge I

Статистика

2.594 подписчиков
0 за неделю
←   Все выпуски   →

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 243


Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)"

Выпуск 243 от 23 мая 2009 г.

Новым посетителям сайта

SQL Exercises Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.

Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 49-ти задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений.

Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.

Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий.


Новости сайта

§ Изменения среди лидеров (решенные за неделю задачи третьего этапа):
3. Ozzy (156)
4. Umrikhina (154)
18. Gavrila (150)
48. B o r i s (142)

§ Продвинулись в рейтинге:
51. Vasilko (задач 138, время 10.452)
55. Nariman Kurbanoff (136, 122.115)
62. Gendalf (131, 134.421)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
102. GrImago (123, 26.236)
133. zjor (118, 133.847)
158. amg__ (115, 12.493)
163. uon (115, 200.715)

§ На этой неделе сертифицированы:
_irina_ (B09039663) [AR] - г.Минск, Беларусь
Blob (A09002296) [BK] - г.Харьков, Украина

§ Число подписчиков - 3887

Число участников рейтинга - 20972

Число участников второго этапа - 1829

Число участников третьего этапа - 49

Сертифицировано на сайте - 371

Лучшие результаты (ТОР 20)

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1Креславский О.М. (Arcan)11561562137883574.69555.7945714 May 2009 22 May 2009
2Карасёва Н.В. (vlksm)115615621378108091.11161.9845720 Apr 2009 22 May 2009
3Зотов П.Г. (Ozzy)315215521367426157.578174.4475510 May 2009 22 May 2009
4Умрихина Е.В. (Umrikhina)41541542137438727.31929.6675316 May 2009 22 May 2009
5Сальников С.А. ($erges)1153153213704233.8674.1914924 Apr 2009 22 May 2009
6Дроздков А.Н. (anddros)6152148213673436.7097.3454605 May 2009 18 May 2009
7Сенкевич С.В. (GreyC)11481522136335857.86027.1024626 Jan 2009 28 Apr 2009
8Селезнёв А.С. (Артём С.)11461522135744447.24837.5244625 Jan 2009 03 Mar 2009
9Никотин В.М. (@Nikotin)11485213592608.3713.7514014 May 2009 22 May 2009
10Мурашкин И.В. (lepton)1142421343113969.49059.0053805 May 2009 05 May 2009
11Печатнов В.В. (pvv)11421492134435730.84917.4903610 Oct 2008 20 May 2009
12Муллаханов Р.Х. (rem)914642135360714.42720.0563509 May 2009 09 May 2009
13Шиндин А.В. (AlShin)10143150213477920.4587.2033005 Jan 2009 28 Jan 2009
14Держальцев В.А. (MadVet)713314621325125760.78328.4822824 Sep 2008 16 May 2009
15Любченко В.А. (IAS56)613214621324615403.343373.6172811 May 2008 01 Dec 2008
16Голубин Р.С. (Roman S. Golubin)313714521330126093.16258.8222529 Jan 2009 08 Feb 2009
17Nikolaenko A.V. (Shadow77)121381472133143677.45114.0102322 Oct 2008 11 Dec 2008
18Тарасов Д.Б. (Gavrila)1814415021342136138.9548.7012118 May 2009 22 May 2009
19Солдатенков Ю.С. (SolYUtor)21341462132381922.6156.1022014 Aug 2008 01 Apr 2009
20Кудряков А.И. (AKudrakov)19145702134148394.01552.9092007 May 2009 07 May 2009

Лучшие результаты за неделю

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1>Вишневский П.С. (Вишня)5252963413097222 May 2009
2Bolos S.A. (Sorin)4050833311699122 May 2009
3Козлов В.В. (Mazep)4553882511395922 May 2009
4>Рудских И.П. (Shredder)49499111102152122 May 2009
5Иванов И.И. (sas__71)2748602686122521 May 2009
6>Belyi (Dam1989)353569978237522 May 2009
7Shavlyugin E.A. (Jonick)2326481967284616 May 2009
8>Колетвинова А. (Поганка)1833441458186622 May 2009
9Иванов Д.М. (Dmitrii)223047047409021 May 2009
10>Horoshko R. (SoMaL1986)242447047497122 May 2009
11>Ларионов А.Ф. (DataStream)19904104128722 May 2009
12Khurtsilava G. (gigi.dba)212138341571718 May 2009
13Здвижков (AlxLucky)163532032301522 May 2009
14Соискатель КХВ (Соискатель КХВ)141731031659817 May 2009
15Леонов А.А. (ЛеоновАА)191931031700121 May 2009
16Nogai (mdg_)3332629727820 May 2009
17Slastihin A.V. (СГСЭУ)102827027372416 May 2009
18>navitski A.S. (Indrikis)151527027776622 May 2009
19>Андреева Л.М. (Vinny)1313161127776922 May 2009
20Горбунов А.В. (risp)13862602632222 May 2009
21Левин Ю. (Юрий Левин)161626026792520 May 2009
22asd (qweasdqwe)151524024839018 May 2009
23>Евтисов С.С. (Minimal)13972302326422 May 2009
24>Орлов М.В. (Eagleoff)10812302336022 May 2009
25Саурин (Delchef)11672022259422 May 2009
26>Карпенко Н.В. (Netaly)82422022427322 May 2009
27>Похмелкин А.Ю. (Corleone)10972002026122 May 2009
28>Nowakowski M. (mnowakow(PL))9782002038422 May 2009
29Салин В.С. (twisting_soul)91720020719021 May 2009

Изучаем SQL

Как вывести по N строк из каждой группы?

Моисеенко С.И.

Такой вопрос возникает, например, когда на сайте требуется вывести по 3 самых свежих анонса в каждой новостной группе, или рекламу 5 самых популярных товаров в каждой категории.

Чтобы решить эту задачу, нужно выполнить разбиение всего набора строк на группы, произвести сортировку по требуемому критерию (по дате или количеству продаж) в пределах каждой группы и последовательно выбрать требуемое количество строк, начиная с первой строки в каждой группе.

Подобную задачу можно решать процедурно, используя временные таблицы и/или курсоры. Здесь же я хочу предложить два решения в стиле тех задач, которые мы решаем на сайте, т.е. одним запросом SELECT. Первое решение - "классическое", которое должно работать на большинстве СУБД; второе решение использует новые конструкции, которые появились в стандарте SQL:1999 и поддерживаются еще не так широко.

Рассмотрим следующую задачу:

Вывести из таблицы Product по три модели с наименьшими номерами из каждой группы, характеризуемой типом продукции.

Т.е. требует получить 3 компьютера, 3 ноутбука и 3 принтера, номера которых меньше номеров остальных моделей в своей группе. Поскольку номер модели является уникальным в таблице Product, то тут не возникает проблем с дубликатами. Заметим, что проблема дубликатов не является принципиальной, однако потребует уточнения формулировки.

"Классическое" решение

Это решение опирается на алгоритм нумерации строк, возвращаемых запросом. Т.е. мы нумеруем строки, а потом выбираем те их них, которые имеют номера, меньшие заданного числа. Следуя упомянутому алгоритму, запрос, который нумерует упорядоченный по возрастанию номера модели весь набор строк в таблице, можно записать так:


SELECT Pr1.model, COUNT(*) num
FROM Product Pr1 JOIN Product Pr2
 ON Pr1.model >= Pr2.model
GROUP BY Pr1.model

Только для решения нашей задачи нужно пронумеровать не весь набор, а каждую группу в отдельности. Этого легко добиться, если в условие соединения таблиц добавить условие совпадения типов продукции, а также добавить группировку по типу:


SELECT Pr1.model, Pr1.type, COUNT(*) num
FROM Product Pr1 JOIN Product Pr2
 ON Pr1.type = Pr2.type AND Pr1.model >= Pr2.model
GROUP BY Pr1.type, Pr1.model
HAVING COUNT (*) <= 3
ORDER BY type, model

Предложение 


HAVING COUNT (*) < = 3

в соответствии с условием задачи ограничивает тремя количество строк в каждой группе. Фактически мы уже решили задачу. Осталось лишь добавить производителя (maker), что также можно сделать разными способами. Например, еще раз соединить по номеру модели приведенный выше запрос с таблицей Product, или использовать коррелирующий подзапрос в предложении SELECT. В учебных целях приведу оба подхода.

1. Соединение


SELECT maker, X.model, X.type
FROM product JOIN (
  SELECT Pr1.model, Pr1.type
  FROM Product Pr1 JOIN Product Pr2
   ON Pr1.type = Pr2.type AND Pr1.model >= Pr2.model
  GROUP BY Pr1.type, Pr1.model
  HAVING COUNT (*) <= 3
    ) X on X.model = product.model
ORDER BY type,model

Здесь мы исключили лишний столбец num, который использовался в демонстрационных целях, поскольку нам не требуется выводить номер строки.

2. Подзапрос в предложении SELECT 


SELECT (SELECT maker
  FROM Product
  WHERE Product.model = Pr1.model) maker,
 Pr1.model, Pr1.type
FROM product Pr1 JOIN product Pr2
 ON Pr1.type = Pr2.type AND Pr1.model >= Pr2.model
GROUP BY Pr1.type, Pr1.model
HAVING COUNT (*) <= 3
ORDER BY type,model

Использование подзапроса в предложении SELECT допускается, если он возвращает всего одно значение для каждой строки основного запроса. Это условие у нас выполняется, т.к. мы выбираем производителя модели, которая передается из основного запроса и является уникальной (первичный ключ в таблице Product).

Решение на основе ранжирующих функций

Ранжирующие функции - ROW_NUMBER, RANK, DENSE_RANK и NTILE появились в составе SQL Server, начиная с версии 2005. Их появление в языке SQL было вызвано потребностью выполнять упорядоченные вычисления. Собственно, наше упражнение как раз и относится к этому классу задач. И теперь у нас есть возможность оценить данное приобретение. :

Для решения нашей задачи воспользуемся функцией RANK. Эта функция позволяет разбить все строки, возвращаемые запросом, на группы и вычислить ранг каждой строки в группе в соответствии заданной сортировкой. Поскольку мы будем сортировать по уникальному номеру модели, то ранг фактически будет совпадать с номером строки в группе. Итак, решение


SELECT maker, model, type FROM
(
SELECT maker, model, type, RANK() OVER(PARTITION BY type ORDER BY model) num
FROM Product
) X
WHERE num <= 3

Собственно, все делается в подзапросе. Внешний запрос служит лишь для того, чтобы ограничить выборку тремя моделями по каждой группе. Говоря другими словами, мы оставляем только те строки, у которых ранг не превышает трех.

Экономно, не так ли. Однако давайте разберем более детально конструкцию


RANK() OVER(PARTITION BY type ORDER BY model)

Предложение PARTITION BY type формирует группы; в одну группу у нас попадают строки, имеющий один и тот же тип продукции (одно и то же значение в столбце type).

Предложение ORDER BY model задает сортировку строк в группе (по возрастанию номера модели).

Наконец, RANK() присваивает ранг каждой строке в группе на основе заданной сортировки, т.е. первая строка в группе получает ранг 1, следующая, если она имеет отличный номер модели, ранг 2 и т.д. Как я уже сказал, поскольку номер модели уникальный, то каждая строка в группе будет иметь отличный ранг. В противном случае, строки с одинаковым номером модели имели бы одинаковый ранг.

Подробное описание функций ранжирования выходит за рамки данной статьи, но, возможно, я напишу нечто подобное для Учебника по SQL.

Полезная информация

§ Приглашаем вас посетить новый проект - Интерактивный учебник по SQL.
   Ресурс позиционируется как "справочное обеспечение" для сайта SQL-EX.RU, но может использоваться и независимо от него.

§ Онлайновый выпуск рассылки можно почитать на сайте.

§ Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL.

§ Хотите поддержать проект? Вот инструкция по применению.

Контакты

По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.
Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"
В избранное