Н О В О С Т И

IEEE  COMPUTER  SOCIETY

The world's leading membership organization for computing professionals

English facebook twitter Новости IEEE События Программы Архив The Day Вопросы


Анализ таблиц с помощью SQL

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

3.4. … … найти …

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

Приведет фрагменты этих таблиц.

Таблица Страны. В ней хранится информация о странах, которые находятся на интересующих нас параллелях и меридианах.

Страны

страна

долгота

широта

Terra Incognito

 

 

Австралия

 

-37

Аргентина

 

-37

Египет

31

 

Заир

31

-5

Новая Зеландия

 

-37

остров Табор

 

-37

Судан

31

 

Танзания

31

 

Уганда

31

 

Чили

 

-37

Таблица Спонсоры. В этой таблице перечислены потенциальные спонсоры, а также проекты, которые могут их заинтересовать. Понятно, что спонсоров может быть несколько. Спонсоров может не быть совсем. Тогда надо их где-то находить. Спонсоры могут быть готовы профинансировать только какую-то часть экспедиции, но не всю. Все это отражено в таблице, которая упорядочена по именам спонсоров.

Спонсоры

спонсор

ищут

  

Атлантида

адмиралтейство

капитан Грант

газета "Нью-Йорк Геральд"

Ливингстон

Генуя

новый путь в Индию

королева

капитан Грант

король Бельгии

алмазы центральной Африки

король Испании

новый путь в Индию

король Португалии

новый путь в Индию

лорд Гленарван

капитан Грант

простодушные люди

клад на островах Карибского моря

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

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

Хорошо было бы также посмотреть, кто может выступить нашим конкурентом. Кто еще намерен организовать экспедицию, кто может выступить нашим соперником в деле получения спонсорской поддержки. Информация об этом хранится в двух таблицах – Экспедиция и Спонсоры. Стало быть в предложении SELECT эти таблицы надо уметь правильно связывать. Связывать – это означает правильно соединять информацию, хранящуюся в одной таблице с информацией, хранящейся в другой таблицей.

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

SELECT Экспедиция.кто, Экспедиция.ищут, Спонсоры.спонсор
FROM Экспедиция INNER JOIN Спонсоры ON Экспедиция.ищут = Спонсоры.ищут
ORDER BY Экспедиция.кто;

кто

ищут

спонсор

дети капитана

капитан Грант

королева

дети капитана

капитан Грант

адмиралтейство

дети капитана

капитан Грант

лорд Гленарван

Колумб

новый путь в Индию

король Испании

Колумб

новый путь в Индию

король Португалии

Колумб

новый путь в Индию

Генуя

пираты

клад на островах Карибского моря

простодушные люди

Стэнли

алмазы центральной Африки

король Бельгии

Стэнли

Ливингстон

газета "Нью-Йорк Геральд"

Что изменилось в предложении SELECT? Прежде всего, имена столбцов теперь увязываются с именами таблиц, которым они принадлежат. Вначале идет имя таблицы, затем имя столбца. Сделано это потому, что имена столбцов в разных таблицах могут совпадать. Далее, после знакомого нам ключевого слова FROM расположено имя столбца, за которым появилось новое выражение INNER JOIN, название второй таблицы, ключевое слово ON и выражение, в котором столбцу их первой таблицы ставится в соответствие столбец из второй таблицы.

В общем виде выражение, объединяющее таблицы, выглядит так:

таблица1 INNER JOIN таблица2 ON таблица1.имястолбца1 = таблица2.имя столба2

А работает это так. Из обеих  таблиц выбираются только те строки, у которых значения в поле имястолбца1 в первой таблице равно значению поля имястолбца2 во второй таблице. В результате получается таблица, в которой строки из первой таблицы дополняются строками из второй таблицы у которых значение поля имястолбца1 равно значению имястолбца2. В результирующую таблицу выбираются только те столбцы, которые перечислены в после ключевого слова SELECT.

Поэтому по нашему запросу

SELECT Экспедиция.кто, Экспедиция.ищут, Спонсоры.спонсор
FROM Экспедиция INNER JOIN Спонсоры ON Экспедиция.ищут = Спонсоры.ищут
ORDER BY Экспедиция.кто;


в результирующую таблицу попадут только значения  полей «кто» и «ищут» из таблицы Экспедиция и значения поля «спонсор» из таблицы Спонсоры. Потенциальными спонсорами экспедиции, собирающимися открыть новый путь в Индию, могли выступить Генуя, а также короли Испании, Португалии. Поэтому в результирующей таблицы появилось три строки, касающиеся экспедиции Колумба. То же самое касается экспедиции, которая должна отправиться на поиски капитана Гранта. Герои Жюль Верна полагали, что спонсорами экспедиции, помимо лорда Гленарвана, могли бы стать королева и адмиралтейство.

Посмотрим еще раз на результат

кто

ищут

спонсор

дети капитана

капитан Грант

королева

дети капитана

капитан Грант

адмиралтейство

дети капитана

капитан Грант

лорд Гленарван

Колумб

новый путь в Индию

король Испании

Колумб

новый путь в Индию

король Португалии

Колумб

новый путь в Индию

Генуя

пираты

клад на островах Карибского моря

простодушные люди

Стэнли

алмазы центральной Африки

король Бельгии

Стэнли

Ливингстон

газета "Нью-Йорк Геральд"

В принципе этот результат можно было получить и другим способом. Предложение

SELECT Экспедиция.кто, Экспедиция.ищут, Спонсоры.спонсор
FROM Экспедиция, Спонсоры WHERE (Экспедиция.ищут=Спонсоры.ищут)
ORDER BY Экспедиция.кто;

также даст тот же результат. 



Пока Колумб объяснял испанскому королю, что Земля имеет форму груши и попасть в Индию можно обойдя «грушу» с Запада, в Португалии не сидели сложа руки. В 1497 году португальская экспедиция отправилась на поиски восточного пути в Индию. Экспедицией руководил великий португальский мореплаватель - Васко да Гама. Когда через 2 года экспедиция вернулась на родину, трюмы кораблей были полны пряностей и других экзотических товаров. Это был успех, т.к. привезенный товар окупил все расходы на экспедицию. В косвенной форме это повлияло на судьбу Колумба. Он попал в опалу, т.к. затраты Испании на экспедиции в Новую Индии поначалу не окупались.

Более того, в старых версиях SQL (с которыми работали еще в прошлом веке) это был единственный способ получить нужный нам результат. Но первый способ предпочтительнее. В этом случае в явном указывается какие таблицы связываются и в каком порядке. А это существенно, если идет связывание трех или более таблиц. Кроме того, снижается возможность по невнимательности допустить ошибку в перечне условий, следующих за ключевым словом WHERE. (Громоздкие конструкции следует, по возможности, избегать).

Но вернемся к нашему бизнесу. Полученная таблица, несомненно, хороша. Видны и конкуренты, и цели экспедиции, и возможные спонсоры. Но все же … «огласите весь список, пожалуйста». Возможно, есть еще возможные цели, которые не заинтересовали пока еще спонсоров, но вы знаете ключик как убедить спонсора раскошелиться на вашу экспедицию. Сумел же Колумб убедить короля Испании, что земля имеет округлую форму. Точнее, форму грушу. И не такая уж она (груша) большая. А потому океан не безграничен и Индия достижима!

Огласить весь список ? – пожалуйста! Вот как это можно сделать. 

SELECT Экспедиция.кто, Экспедиция.ищут, Спонсоры.спонсор
FROM Экспедиция LEFT JOIN Спонсоры ON Экспедиция.ищут = Спонсоры.ищут
ORDER BY Экспедиция.кто;

А вот что получается в результате выполнения этого предложения

кто

ищут

спонсор

  

Aтлантида

  

дети капитана

капитан Грант

королева

дети капитана

капитан Грант

адмиралтейство

дети капитана

капитан Грант

лорд Гленарван

Колумб

новый путь в Индию

король Испании

Колумб

новый путь в Индию

король Португалии

Колумб

новый путь в Индию

Генуя

Ливингстон

истоки Нила

  

пираты

клад на островах Карибского моря

простодушные люди

Стэнли

алмазы центральной Африки

король Бельгии

Стэнли

истоки Нила

  

Стэнли

Ливингстон

газета "Нью-Йорк Геральд"

Появились 3 новые строки. Общее для них – нет спонсора для финансирования некоторых экспедиций. Не интересует спонсоров ни Атлантида, ни истоки Нила.

А что изменилось в предложение SELECT. Немного, выражение INNER JOIN заменено на LEFT JOIN. Работает оно похоже на INNER JOIN, но есть небольшое отличие. Выбираются все строки из первой таблицы, даже если для них нет подходящей пары во второй таблице. В этом случае все поля, в которых должны быть помещены значения из второй таблицы, неопределенны (получают значение NULL).

Еще один пример. Какие страны, возможно, придется посетить экспедиции по спасению капитана Гранта?

SELECT Страны.страна
FROM Экспедиция LEFT JOIN Страны ON Экспедиция.широта=Страны.широта
WHERE (((Экспедиция.ищут)="капитан Грант"));

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

страна

остров Табор

Новая Зеландия

Австралия

Чили

Аргентина

Аналогично работает RIGHT JOIN. Но только в этом случае выбираются все строки из второй таблицы, даже если для них нет подходящей пары в первой таблице. В этом случае все поля, в которых должны быть помещены значения из первой таблицы, и которые неопределенны (получают значение NULL).

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

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

Повторить достижение Колумба – убедить короля Испании финансировать экспедицию для поиска новых путей в Индию, очень трудно. Пожалуй, в те времена обещание, найти новые пути в Индию, было столь же оптимистично как обещание достигнуть Луны и привести с нее в подарок лунный камень. Вряд ли вам удастся найти спонсора для поддержки проекта «пойду туда, не знаю куда, принесу то, не знаю что».
Нельзя также забывать и о коммерческих рисках. В бизнес - плане, который Колумб представил испанскому королю, было две существенные ошибки. Первая, что Земля имеет форму груши, а не шара. Эта ошибка не была роковой, хотя затрудняла прокладывание правильного курса. Однако, вторая оказала существенное влияние на его судьбу. Он плохо рассчитал  бизнес - риски. Он полагал, что западный путь в Индию короче и безопаснее.  Когда же Васка да Гама нашел восточный путь в Индию и привез полные трюмы восточных пряностей, Колумб был отозван их "Западной Индии" и посажен в кандалы. В "Западной Индии" восточных пряностей не было, золото и серебро ацтеков в испанскую казну еще не поступало, а есть картофель, курить табак и пить кофе еще не вошло в моду.

Поэтому посмотрим, лучше, повнимательнее на Стэнли и его экспедиции.



Генри Стэнли – великий путешественник и удачливый бизнесмен. Однако его жизненный путь отнюдь не был усыпан розами.
Генри Стэнли обычно вспоминают как журналиста, который в 1871 году нашел экспедицию Ливингстона. Но он был не только талантливым журналистом. Он обладал ясным аналитическим умом, что позволяло ему из скудных крупиц информации выбирать важные данные, принимать верные решения.

Жизнь Генри Стэнли не была усыпана розами. Он был родом из Англии, незаконнорожденным. В семилетнем возрасте попал в работный дом, где пробыл до пятнадцати лет. Бежав из работного дома, мальчишка долго скитался, работал сторожем, уборщиком, грузчиком и, в конце концов, попал в Америку. С 17 лет он уже в Новом Орлеане, где также перебивается случайными заработками.

Начавшаяся война между Севером и Югом привела двадцатилетнего Стэнли в лагерь южан. В ходе войны он попал в плен к северянам. Сумев бежать, Стэнли нанимается матросом на корабль, путешествует, затем возвращается в Штаты, где тридцати годам становится известным журналистом, одним из ведущих сотрудников самой популярной в то время газеты США «Нью-Йорк Геральд».

В 1871 году он получает задание отправиться в Африку и отыскать известного английского исследователя Африки Дэвида Ливингстона. Стэнли прекрасно провел подготовительную работу. Умение расшифровывать намеки и недомолвки привело его к убеждению, что великий исследователь находится где-то в районе озера Танганьика. Стэнли собирает караван, отправляется в путь, находит Ливингстона.

Стэнли, как репортер самой известной газеты, знал цену publicity. Его репортаж, а затем и книга «Как я отыскал Ливингстона» наделают немало шума по обе стороны Атлантики. Его несомненная смелость, удачливость, умение анализировать информацию, его организаторские способности привели к тому, что вскоре Генри Стэнли получил предложение возглавить новую весьма необычную экспедицию. На этот раз ее финансировал бельгийский король Леопольд II. Собственно, экспедицией назвать ее было бы не совсем точно. С 1879 по 1884 год Стэнли занимался освоением огромной территории, расположенной в бассейне Конго, организовывал строительство дорог и фортов - опорных пунктов для европейской колонизации страны, получившей название «Свободное государство Конго». Фактически же это была личная колония (небольшое поместье!) короля Леопольда. Она просуществовала 25 лет, после чего получила статус бельгийской колонии. Имя  короля было присвоено ее столице - городу Леопольдвиль. Себя Генри тоже не забыл: в честь него был назван второй по важности город страны - Стэнливиль

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

А теперь посмотрим внимательнее на Стэнли и его экспедиции через призму SQL.

SELECT Спонсоры. спонсор, Экспедиция. ищут, Страны. страна
FROM (Спонсоры INNER JOIN Экспедиция ON Спонсоры. ищут = Экспедиция. ищут) INNER JOIN Страны 
ON Экспедиция. долгота = Страны. долгота
WHERE (Экспедиция. кто = "Стэнли");
 

спонсор

ищут

страна

газета "Нью-Йорк Геральд"

Ливингстон

Танзания

газета "Нью-Йорк Геральд"

Ливингстон

Уганда

газета "Нью-Йорк Геральд"

Ливингстон

Заир

газета "Нью-Йорк Геральд"

Ливингстон

Судан

газета "Нью-Йорк Геральд"

Ливингстон

Египет

король Бельгии

алмазы центральной Африки

Танзания

король Бельгии

алмазы центральной Африки

Уганда

король Бельгии

алмазы центральной Африки

Заир

король Бельгии

алмазы центральной Африки

Судан

король Бельгии

алмазы центральной Африки

Египет

Генри Стэнли обладал ясным аналитическим умом, что позволяло ему из скудных крупиц информации выбирать важные данные, принимать верные решения. Во время экспедиции по поиску Ливингстона умение выяснять подробности, расспрашивать очевидцев, расшифровывать намеки и недомолвки позволили ему существенно сузить область поиска.  Это позволило ему не метаться по Африке (посмотрите еще раз на последнюю таблицу, в принципе, Ливингстон мог быть в любой из пяти перечисленных стран), а направиться в точно определенный, хотя и труднодоступный район - район озера Танганьика.
То же самое можно сказать и о "коммерческой" экспедиции: была освоена огромная территория, гигантская страна, полная полезных ископаемых: золото, алмазы, урановая руда ...

Вернуться

Продолжение ...