06_Inner_Joins.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Занятие 7: Объединение таблиц в запросе"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## FROM для выбора нескольких таблиц"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_TODO дополнить про интерфейс и запуск БД_\n",
    "\n",
    "_а пока воспользуйтесь следующей ссылкой_<br>\n",
    "http://84.201.167.29/phpmyadmin<br>\n",
    "пользователь: student<br>\n",
    "пароль: n123456789"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Напомним, что таблицы в реляционных базах данных связаны через первичные ключи и иногда другие поля, которые являются общими для нескольких таблиц (как в случае с нашим набором данных распознавания).  Наша цель, когда мы выполняем ОБЪЕДИНЕНИЕ или создаем объединенную таблицу, состоит в том, чтобы использовать эти общие столбцы, чтобы позволить базе данных определить, какие строки в одной таблице соответствуют каким строкам в другой таблице.  Как только это сопоставление установлено с использованием хотя бы одного общего поля или столбца, база данных может извлекать любые столбцы, которые вы хотите, из сопоставленных или объединенных таблиц и выводить сопоставленные данные в одну общую таблицу.\n",
    "\n",
    "Внутреннее соединение - это соединение, которое выводит только те строки, которые имеют точное совпадение в обеих объединяемых таблицах:\n",
    "\n",
    "<img src=\"https://duke.box.com/shared/static/xazeqtyq6bjo12ojvgxup4bx0e9qcn5d.jpg\" width=700 alt=\"INNER_JOIN\" />\n",
    "\n",
    "Чтобы проиллюстрировать, как это работает, давайте выясним, имеют ли владельцы собак, которые особенно удивлены результатами тестов на распознавание, как правило, похожие породы (или типы пород, или группы пород) собак.  Есть много способов ответить на этот вопрос, но давайте начнем с того, что сосредоточимся на владельцах собак, которые поставили не менее 10 оценок одной или нескольким своим собакам в таблице оценок. Из этих владельцев, какие 200 владельцев сообщили о наибольшей средней степени удивления поведением своей собаки, и какова была порода, breed_type и breed_group собаки каждого из этих владельцев?\n",
    "\n",
    "Неожиданные оценки хранятся в таблице отзывов.  Информация о породе собаки приведена в таблице \"Собаки\".  Есть два столбца, которые являются общими для обеих таблиц: user_guid и dog_guid. Как мы используем общие столбцы для объединения информации из двух таблиц? \n",
    "\n",
    "Чтобы объединить таблицы, вы можете использовать предложение WHERE и добавить пару сведений в предложение FROM, чтобы база данных знала, из какой таблицы берется каждое поле в вашем предложении SELECT.  \n",
    "\n",
    "Во-первых, начните с добавления всех столбцов, которые мы хотим проверить, в инструкцию SELECT:\n",
    "\n",
    "```mySQL\n",
    "SELECT dog_guid AS DogID, user_guid AS UserID, AVG(rating) AS AvgRating, \n",
    "       COUNT(rating) AS NumRatings, breed, breed_group, breed_type\n",
    "FROM dogs, reviews\n",
    "GROUP BY user_guid\n",
    "HAVING NumRatings >= 10\n",
    "ORDER BY AvgRating DESC\n",
    "LIMIT 200\n",
    "```\n",
    "Вы должны получить сообщение об ошибке, в котором говорится, что идентификаторы dog_guid и user_guid в списке полей необозначены. Причина в том, что заголовок столбца существует в обеих таблицах, и MySQL не знает, какой из них нам нужен. Мы должны указать имя таблицы перед указанием имени поля и разделить эти два имени точкой\n",
    "\n",
    "_Внимание!!! Запрос не выполнится, так как не оптимизирован и требует много ресурсов_\n",
    "```mySQL\n",
    "SELECT dogs.dog_guid AS DogID, dogs.user_guid AS UserID, AVG(reviews.rating) AS AvgRating,     \n",
    "       COUNT(reviews.rating) AS NumRatings, dogs.breed, dogs.breed_group, dogs.breed_type\n",
    "FROM dogs, reviews\n",
    "GROUP BY dogs.user_guid\n",
    "HAVING NumRatings >= 10\n",
    "ORDER BY AvgRating DESC\n",
    "LIMIT 200\n",
    "```\n",
    "\n",
    "Вы также можете воспользоваться преимуществами псевдонимов, чтобы вам не приходилось каждый раз записывать названия таблиц.  Здесь я представлю другой синтаксис для псевдонимов, в котором полностью отсутствует AS.  В этом синтаксисе псевдонимом является любое слово (или фраза, если вы используете кавычки), следующее сразу после имени поля или таблицы, разделенное пробелом.  Чтобы мы могли написать:\n",
    "\n",
    "_Внимание!!! Запрос не выполнится, так как не оптимизирован и требует много ресурсов_\n",
    "```MySQL\n",
    "SELECT d.dog_guid AS DogID, d.user_guid AS UserID, AVG(r.rating) AS AvgRating, \n",
    "       COUNT(r.rating) AS NumRatings, d.breed, d.breed_group, d.breed_type\n",
    "FROM dogs d, reviews r\n",
    "GROUP BY d.user_guid\n",
    "HAVING NumRatings >= 10\n",
    "ORDER BY AvgRating DESC\n",
    "LIMIT 200\n",
    "```  \n",
    "      \n",
    "В определения таблиц базы данных не встроено ничего, что могло бы указать серверу, как самостоятельно объединять таблицы (помните, именно так реляционные базы данных экономят место и остаются гибкими).  Кроме того, запрос в том виде, в каком он написан, не сообщает базе данных, как связаны эти две таблицы.  Как следствие, вместо того, чтобы сопоставлять две таблицы в соответствии со значениями в столбце user_id и / или dog_id, база данных будет делать единственное, что она умеет делать, а именно выводить каждую отдельную комбинацию записей в таблице dogs с записями в таблице reviews.  Другими словами, каждая отдельная строка таблицы dogs будет сопоставлена с каждой отдельной строкой таблицы reviews.  Это известно как декартово произведение.  Вывод таблицы, полная длина которой в одной таблице умножена на полную длину другой, не только будет тяжелым бременем для базы данных (и вас это разочарует, потому что выполнение запроса займет очень много времени), но и вывод будет практически бесполезным.\n",
    "\n",
    "Чтобы этого не произошло, укажите базе данных, как связать таблицы в предложении WHERE:\n",
    "\n",
    "```mySQL\n",
    "SELECT d.dog_guid AS DogID, d.user_guid AS UserID, AVG(r.rating) AS AvgRating, \n",
    "       COUNT(r.rating) AS NumRatings, d.breed, d.breed_group, d.breed_type\n",
    "FROM dogs d, reviews r\n",
    "WHERE d.dog_guid=r.dog_guid\n",
    "GROUP BY d.user_guid\n",
    "HAVING NumRatings >= 10\n",
    "ORDER BY AvgRating DESC\n",
    "LIMIT 200\n",
    "```\n",
    "\n",
    "Чтобы быть очень осторожным и исключить любые неправильные записи dog_guide или user_guide, вы можете включить оба общих столбца в предложение WHERE:\n",
    "\n",
    "```mySQL\n",
    "SELECT d.dog_guid AS DogID, d.user_guid AS UserID, AVG(r.rating) AS AvgRating, \n",
    "       COUNT(r.rating) AS NumRatings, d.breed, d.breed_group, d.breed_type\n",
    "FROM dogs d, reviews r\n",
    "WHERE d.dog_guid=r.dog_guid AND d.user_guid=r.user_guid\n",
    "GROUP BY d.user_guid\n",
    "HAVING NumRatings >= 10\n",
    "ORDER BY AvgRating DESC\n",
    "LIMIT 200\n",
    "```\n",
    "\n",
    "Запрос должен выполняться быстро.  Этого не было бы, если бы вы не включили предложение WHERE для объединения двух таблиц. Если вы случайно запросите декартово произведение из наборов данных с миллиардами строк, вам может потребоваться несколько дней для получения результатов запроса (и, вероятно, у вас возникнут проблемы с администратором вашей базы данных). Поэтому всегда не забывайте указывать базе данных, как объединить ваши таблицы!\n",
    "\n",
    "Давайте рассмотрим нашу объединенную таблицу немного подробнее.  Объединенная таблица, выводимая приведенным выше запросом, должна содержать 38 строк, несмотря на то, что мы установили наш ЛИМИТ в 200.  Причина этого в том, что оказывается, что относительно небольшое число клиентов предоставили 10 или более отзывов.  Если вы удалите предложение HAVING и LIMIT BY из запроса, у вас должно получиться 389 строк.\n",
    "\n",
    "```MySQL\n",
    "SELECT d.dog_guid AS DogID, d.user_guid AS UserID, AVG(r.rating) AS AvgRating, \n",
    "       COUNT(r.rating) AS NumRatings, d.breed, d.breed_group, d.breed_type\n",
    "FROM dogs d, reviews r\n",
    "WHERE d.dog_guid=r.dog_guid AND d.user_guid=r.user_guid\n",
    "GROUP BY d.user_guid\n",
    "ORDER BY AvgRating DESC\n",
    "```\n",
    "\n",
    "Из этого вывода становится ясно, что (А) не многие клиенты давали оценки, и (Б) когда они это делали, они ставили не самые высокий рейтинг в отзыве.  Таким образом, эти рейтинги, вероятно, не дадут много полезной информации о том, какие испытания надо улучшать.  Однако таблица рейтингов по-прежнему предоставляет прекрасную возможность проиллюстрировать результаты различных типов объединений.\n",
    "\n",
    "Перед написанием запроса, рассмотрим сколько уникальных dog_guid и user_guid есть в таблице reviews и dogs независимо друг от друга?\n",
    "\n",
    "```MySQL\n",
    "SELECT COUNT(DISTINCT dog_guid)\n",
    "FROM reviews\n",
    "```\n",
    "\n",
    "\n",
    "```MySQL\n",
    "SELECT COUNT(DISTINCT user_guid)\n",
    "FROM reviews\n",
    "```\n",
    "\n",
    "\n",
    "```MySQL\n",
    "SELECT COUNT(DISTINCT dog_guid)\n",
    "FROM dogs\n",
    "```\n",
    "\n",
    "\n",
    "```MySQL\n",
    "SELECT COUNT(DISTINCT user_guid)\n",
    "FROM dogs\n",
    "```\n",
    "\n",
    "Эти подсчеты указывают на некоторые важные вещи:\n",
    "\n",
    "+ У многих клиентов как в отзывах, так и в таблице dogs есть несколько собак\n",
    "+ В таблице dogs гораздо больше уникальных dog_guid и user_guid для пользователей, чем в таблице reviews\n",
    "+ В таблице отзывов гораздо больше уникальных dog_guid и user_guid, чем в выходных данных нашего внутреннего соединения\n",
    "\n",
    "Давайте проверим еще кое-что.  \n",
    "   \n",
    "Попробуем запросить один раз только с помощью dog_guid или один раз только с помощью предложения user_guid в инструкции WHERE:\n",
    "\n",
    "```MySQL\n",
    "SELECT d.dog_guid AS DogID, d.user_guid AS UserID, AVG(r.rating) AS AvgRating, \n",
    "       COUNT(r.rating) AS NumRatings, d.breed, d.breed_group, d.breed_type\n",
    "FROM dogs d, reviews r\n",
    "WHERE d.dog_guid=r.dog_guid\n",
    "GROUP BY d.user_guid\n",
    "ORDER BY AvgRating DESC\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Вопрос 1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Напишите запрос для таблиц `dogs` и `complete_tests`, который позволит получить информацию о колонках `user_guid`, `dog_guid`, `breed`, `breed_type` и `breed_group` для всех питомцев, которые проходили испытание \"Yawn Warm-up\"**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Объединение более двух таблиц"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Теоретически, вы можете объединить столько таблиц, сколько захотите или в чем возникнет необходимость.  Чтобы объединить несколько таблиц, вы применяете тот же подход, который мы использовали при объединении двух таблиц: перечислите все поля, которые вы хотите извлечь, в инструкции SELECT, укажите, из какой таблицы они взяты в инструкции SELECT, перечислите все таблицы, из которых вам нужно будет извлечь поля в инструкции FROM оператор, а затем сообщите базе данных, как подключить таблицы в операторе WHERE.\n",
    "\n",
    "Чтобы извлечь user_guid, штат проживания пользователя, почтовый индекс пользователя, dog_guid, породу, breed_type и breed_group для всех животных, которые завершили игру \"Разминка зевком\", у вас может возникнуть соблазн запросить:\n",
    "\n",
    "\n",
    "```mySQL\n",
    "SELECT c.user_guid AS UserID, u.state, u.zip, d.dog_guid AS DogID, d.breed, d.breed_type, d.breed_group\n",
    "FROM dogs d, complete_tests c, users u\n",
    "WHERE d.dog_guid=c.dog_guid \n",
    "   AND c.user_guid=u.user_guid\n",
    "   AND c.test_name=\"Yawn Warm-up\";\n",
    "```\n",
    "Этот запрос фокусирует отношения в первую очередь на таблице complete_tests.  Однако оказывается, что наш набор данных распознавания имеет только нулевые значения в столбце user_guide таблицы complete_tests.  Если бы вы выполнили приведенный выше запрос, вы бы не получили сообщение об ошибке, но в вашем выводе было бы 0 строк.  Однако здесь пригодятся возможности реляционных баз данных.  Вы можете использовать таблицу dogs для связывания таблиц complete_tests и users (обратите внимание на разницу между оператором WHERE в этом запросе и оператор WHERE в приведенном выше запросе):\n",
    "\n",
    "\n",
    "```mySQL\n",
    "SELECT d.user_guid AS UserID, u.state, u.zip, d.dog_guid AS DogID, d.breed, d.breed_type, d.breed_group\n",
    "FROM dogs d, complete_tests c, users u\n",
    "WHERE d.dog_guid=c.dog_guid \n",
    "   AND d.user_guid=u.user_guid\n",
    "   AND c.test_name=\"Yawn Warm-up\";\n",
    "```\n",
    "\n",
    "Следует отметить, что объединения требуют очень больших ресурсов, поэтому старайтесь не присоединяться без необходимости.  В общем, чем больше соединений вам придется выполнить, тем медленнее будет производительность вашего запроса."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Вопрос 2."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Напишите запрос для таблиц `dogs`, `users`, `complete_tests`, который покажет информацию о колонках `user_guid`, `membership_type`, `dog_guid` всех золотистых ретриверов (значение `breed` _\"Golden retriever\"_), которые прошли хотя бы 1 тест из таблицы `complete_tests`**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Практика темы 6"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Вопрос 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Сколько собак породы _\"Golden Retriever\"_ (колонка `breed`), живущих в Северной Каролине (значение колонки `state` _'NC'_) из таблиц `users`, `dogs`? Проведите подсчет уникальных значений по идентификаторам `dog_guid`.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Вопрос 4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Сколько клиентов из таблицы `reviews` в каждой категории членства `membership_type` оставляли хотя бы один отзыв? Проведите подсчет уникальных значений по идентификаторам `user_guid`.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Вопрос 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Для каких 3 пород собак `breed` в базе данных имеется наибольшая активность `script_detail_id`? Проведите подсчет значений колонки `script_detail_id` из таблиц `dogs` и `site_activities`.**"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}