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

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

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 2.592 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


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


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

Автор
Serge I

Статистика

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

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


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

Выпуск 246 от 13 июня 2009 г.

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

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

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

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

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


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

§ Уточнена формулировка задачи 16 (SELECT) и написана подсказка.

§ Изменения среди лидеров (решенные за неделю задачи третьего этапа):
4. $erges (154)
39. glassman (141)
43. Faust_zp (140)

§ Продвинулись в рейтинге:
52. Vasilko (задач 138, время 10.480)
53. lemon (136, 8.929)
54. Nariman Kurbanoff (136, 122.215)
57. Fomichev (134, 20.502)
59. nebiros (133, 423.763)
68. Bulldozer (129, 335.511)
75. Балуткин (125, 401.552)
77. GrImago (124, 26.664)

§ Новые лица в ТОР 100 и вернувшиеся туда:
70. Lohmatyi (126, 3.030)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
102. lexaNRJ (119, 92.519)
104. vitaliiS (121, 109.470)
105. Sergey79 (121, 566.328)
145. Wiedzmen (115, 8.358)
147. alx_ol (115, 80.181)
149. _Allex_ (115, 59.472)
151. Stasca (115, 211.900)
153. BW (113, 58.108)
163. Baumanec (113, 5.746)
179. IrinkaR (111, 9.198)

§ На этой неделе сертифицированы:
max506 (A09034082) [BK] - г.Одинцово, Россия

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

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

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

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

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

Лучшие результаты (ТОР 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)1 156 156 22 379 857 74.720 55.794 57 05 Jun 2009 12 Jun 2009
2 Карасёва Н.В. (vlksm)1 156 156 22 379 1128 91.137 61.984 57 07 Jun 2009 12 Jun 2009
3 Зотов П.Г. (Ozzy)3 150 156 21 364 438 163.149 180.018 57 22 May 2009 12 Jun 2009
4 Сальников С.А. ($erges)1 155 154 22 377 471 4.374 4.675 55 11 Jun 2009 12 Jun 2009
5 Умрихина Е.В. (Umrikhina)4 154 154 22 375 407 27.334 29.667 53 05 Jun 2009 11 Jun 2009
6 Дроздков А.Н. (anddros)6 153 157 22 371 374 6.709 7.345 49 05 Jun 2009 11 Jun 2009
7 Сенкевич С.В. (GreyC)1 145 152 21 358 358 57.860 27.102 46 26 Jan 2009 28 Apr 2009
8 Селезнёв А.С. (Артём С.)1 143 152 21 352 444 47.248 37.524 46 25 Jan 2009 03 Mar 2009
9 Никотин В.М. (@Nikotin)1 146 150 21 355 283 8.371 3.751 40 06 Jun 2009 12 Jun 2009
10 Мурашкин И.В. (lepton)1 139 156 21 338 1139 69.490 59.005 38 05 May 2009 05 May 2009
11 Печатнов В.В. (pvv)1 142 149 21 342 584 30.849 17.490 36 25 May 2009 12 Jun 2009
12 Муллаханов Р.Х. (rem)9 147 152 21 352 635 14.627 20.056 35 06 Jun 2009 07 Jun 2009
13 Шиндин А.В. (AlShin)10 140 150 21 342 79 20.458 7.203 30 05 Jan 2009 25 May 2009
14 Держальцев В.А. (MadVet)7 132 146 21 322 1509 60.783 28.482 28 03 Jun 2009 03 Jun 2009
15 Любченко В.А. (IAS56)6 129 146 21 319 615 403.343 373.617 28 11 May 2008 01 Dec 2008
16 Голубин Р.С. (Roman S. Golubin)3 134 145 21 325 1260 93.162 58.822 25 29 Jan 2009 08 Feb 2009
17 Nikolaenko A.V. (Shadow77)12 135 147 21 326 436 77.451 14.010 23 22 Oct 2008 11 Dec 2008
18 Тарасов Д.Б. (Gavrila)18 144 150 22 343 1379 38.970 8.701 21 05 Jun 2009 11 Jun 2009
19 Солдатенков Ю.С. (SolYUtor)2 131 146 21 318 819 22.615 6.102 20 14 Aug 2008 01 Apr 2009
20 Кудряков А.И. (AKudrakov)19 142 152 21 336 483 94.015 52.909 20 07 May 2009 07 May 2009

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Порфирьев А.П. (AlexeyKa) 35 39 71 35 106 1262 12 Jun 2009
2 Корягин (shuricus) 39 88 75 0 75 290 12 Jun 2009
3 >Дучинский А.С. (Ducha) 33 33 64 1 65 2906 12 Jun 2009
4 >Гаврилов А.А. (INS@NE) 33 33 64 0 64 2969 12 Jun 2009
5 >Шамсудинов А.Ш. (Artsha) 33 33 64 0 64 2971 12 Jun 2009
6 >Степанова А.А. (Степанова Анна) 32 32 63 0 63 3081 12 Jun 2009
7 >Poltavtsev S.V. (Sergey90) 29 32 60 0 60 3114 12 Jun 2009
8 Голубев Д. (Димоныч) 28 33 57 0 57 3177 11 Jun 2009
9 >Островский А.А. (aleost) 24 33 51 0 51 3035 12 Jun 2009
10 >Емельяненко Р.А. (Roman_SPbPU) 25 25 51 0 51 4231 12 Jun 2009
11 Марадудин В.А. (Vinger) 19 37 40 9 49 2220 11 Jun 2009
12 Караханов В.Н. (karakhan) 24 24 46 0 46 4829 11 Jun 2009
13 Латфуллин А.С. (Replikon) 21 89 45 0 45 282 12 Jun 2009
14 >Ssp (Baumanec) 18 113 44 0 44 163 12 Jun 2009
15 >Пилипенко А.А. (Alexey20) 5 53 7 35 42 876 12 Jun 2009
16 Chunadra M.M. (Miheyjumaric) 19 19 36 6 42 5355 10 Jun 2009
17 Плугин В. (Basilik) 20 25 41 0 41 4897 10 Jun 2009
18 Тимонин И. (2angry) 16 19 40 0 40 4839 08 Jun 2009
19 >Ширшаков Д.Н. (PM_DNS) 18 39 39 0 39 2222 12 Jun 2009
20 Gerasimovich N. (COLE) 19 19 34 5 39 6493 10 Jun 2009
21 >Rakovich A. (Lohmatyi) 14 126 33 1 34 70 12 Jun 2009
22 Соболев А.Ф. (Soba) 17 17 25 8 33 7401 12 Jun 2009
23 >Царевский Н.А. (Nick-Tsa) 12 37 27 4 31 1779 12 Jun 2009
24 Kabanov (kabanov) 16 16 30 0 30 6905 11 Jun 2009

Изучаем SQL

Как удалить дубликаты строк из таблицы?

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

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

Пусть имеется следующая таблица T:

name
John
Smith
John
Smith
Smith
Tom

Для простоты я не включаю сюда другие столбцы, предполагая, что данные в них однозначно определяются значением в столбце name. Требуется сделать столбец name уникальным (скажем, первичным ключом), предварительно удалив дубликаты.

Распространенным решением данной проблемы является создание вспомогательной таблицы требуемой структуры, в которую копируются уникальные строки из таблицы T с последующим удалением таблицы T и переименованием вспомогательной таблицы. Ниже приводится код на языке T-SQL, реализующий данный алгоритм.


CREATE TABLE Ttemp(name VARCHAR(50) NOT NULL PRIMARY KEY);
GO
INSERT INTO Ttemp
SELECT DISTINCT * FROM T;
GO
DROP TABLE T;
GO
EXEC sp_rename 'Ttemp', 'T';
GO
SELECT * FROM T;

В результате получим то, что и требовалось: 

name
John
Smith
Tom

При этом ограничение первичного ключа будет препятствовать появлению дубликатов впоследствии.

А можно ли обойтись без создания новой таблицы? Можно. Например, с помощью такого алгоритма:
- добавить новый столбец типа счетчик (IDENTITY), который перенумерует все имеющиеся строки в таблице;
- из каждой группы строк с одинаковым значением в столбце name удалить все строки за исключением строки с максимальным номером (или минимальным - это все равно, т.к. мы имеем дело с дубликатами);
- удалить вспомогательный столбец;
- наложить ограничение.

Вот пример реализации такого подхода:


ALTER TABLE T
ADD id INT IDENTITY(1,1);
GO
DELETE FROM T
WHERE id < (SELECT MAX(id)
    FROM T AS T1
                WHERE T.name = T1.name
              );
GO
ALTER TABLE T
DROP COLUMN id;
GO
ALTER TABLE T
ALTER COLUMN name VARCHAR(50) NOT NULL;
GO
ALTER TABLE T
ADD CONSTRAINT T_PK PRIMARY KEY(name);
GO

А если без создания дополнительного столбца? Опять ответ утвердительный, но тут нам потребуются новые возможности языка, специфицированные в стандарте ANSI SQL-99. Идея состоит в том, чтобы создавать не постоянный столбец в таблице, который потом потребуется удалять, а виртуальный (вычисляемый). Этот столбец мы создадим с помощью оконных функций, присвоив ранг каждой строке внутри окна, определяемого равенством значений в столбце name. Наконец, мы удалим все строки с рангом выше 1.

Давайте подробно рассмотрим построение запроса на удаление дубликатов этим методом.

1. Нумерация строк

Мы не можем сразу ранжировать строки просто потому, что их не по чем ранжировать. Дело в том, что одинаковые строки будут иметь одинаковый ранг. Поэтому сначала пронумеруем их, упорядочив по столбцу name.


SELECT *, ROW_NUMBER() OVER(ORDER BY name) num
FROM T

В результате получим

name num
John 1
John 2
Smith 3
Smith 4
Smith 5
Tom 6

2. Ранжирование строк внутри групп дубликатов

К сожалению, запрещено (MS SQL Server) использовать оконные функции внутри оконных функций. Т.е. мы не можем написать так:


SELECT name,
RANK() OVER (PARTITION BY name ORDER BY ROW_NUMBER() OVER(ORDER BY name)) rnk
FROM T;

а потому используем подзапрос:


SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) num
   FROM T
   ) X;

Ниже представлен результат этого запроса.

name rnk
John 1
John 2
Smith 1
Smith 2
Smith 3
Tom 1

3. Удаление дубликатов из виртуальной таблицы

Недопустимо удалять записи из запроса, т.е. мы не можем воспользоваться таким вариантом: 


DELETE FROM (SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk
    FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) num
          FROM T
      ) X
WHERE rnk > 1;

т.к. в операторе DELETE допускается использовать только базовую таблицу или представление. Поэтому мы могли бы создать представление и удалить записи уже из него. Конечно, на самом деле записи удаляются из базовой таблицы, на которой создано представление. Итак, мы можем поступить следующим образом:


CREATE VIEW Tview
AS
SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk
FROM(SELECT *, ROW_NUMBER() OVER(ORDER BY name) num
  FROM T
 ) X
GO
DELETE FROM Tview
WHERE rnk > 1;

"Опять что-то создавать", - скажете вы. Не обязательно, и, чтобы доказать это, нам помогут общие табличные выражения (CTE), которые можно назвать виртуальными представлениями. CTE, хотя и не являются сохраняемыми в базе данных объектами, могут использоваться с операторами обновления. В результате все сводится к одному запросу:


WITH CTE AS
 (SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk
  FROM(SELECT *, ROW_NUMBER() OVER(ORDER BY name) num
        FROM T
    ) X
 )
DELETE FROM CTE
WHERE rnk > 1;
GO

Не забудьте только создать первичный ключ. :-)

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

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

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

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

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

Контакты

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