Подзапросы ms sql: MS SQL Server и T-SQL

Содержание

MS SQL Server и T-SQL

Последнее обновление: 20.07.2017

Подзапросы в SELECT

В выражении SELECT мы можем вводить подзапросы четырьмя способами:

  1. Использовать в условии в выражении WHERE

  2. Использовать в условии в выражении HAVING

  3. Использовать в качестве таблицы для выборки в выражении FROM

  4. Использовать в качестве спецификации столбца в выражении SELECT

Рассмотрим некоторые из этих случаев. Например, получим все товары, у которых цена выше средней:


SELECT *
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)

Чтобы получить нужные товары, нам вначале надо выполнить подзапрос на получение средней цены товара: SELECT AVG(Price) FROM Products.

Или выберем всех покупателей из таблицы Customers, у которых нет заказов в таблице Orders:


SELECT * FROM CUSTOMERS
WHERE Id NOT IN (SELECT CustomerId FROM Orders)

Хотя в данном случае подзапросы прекрасно справляются со своей задачей, стоит отметить, что это не самый эффективный способ для извлечения данных из
других таблиц, так как в рамках T-SQL для сведения данных из разных таблиц можно использовать оператор JOIN, который рассматривается в следующей теме.

Получение набора значений

При использовании в операторах сравнения подзапросы должны возвращать одно скалярное значение. Но иногда возникает необходимость получить
набор значений. Чтобы при использовании в операторах сравнения подзапрос мог возвращать набор значений, перед ним необходимо использовать один из операторов: ALL, SOME или
ANY.

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


SELECT * FROM Products
WHERE Price < ALL(SELECT Price FROM Products WHERE Manufacturer='Apple')

Если бы мы в данном случае опустили бы ключевое слово ALL, то мы бы столкнулись с ошибкой.

Допустим, если подзапрос возвращает значения vl1, val2 и val3, то условие фильтрации фактически было бы аналогично
объединению этих значений через оператор AND:


WHERE Price < val1 AND Price < val2 AND Price < val3

В тоже время подобный запрос гораздо проще переписать другим образом:


SELECT * FROM Products
WHERE Price < (SELECT MIN(Price) FROM Products WHERE Manufacturer='Apple')

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


SELECT * FROM Products
WHERE Price < ANY(SELECT Price FROM Products WHERE Manufacturer='Apple')

И также стоит отметить, что данный запрос можно сделать проще, переписав следующим образом:


SELECT * FROM Products
WHERE Price < (SELECT MAX(Price) FROM Products WHERE Manufacturer='Apple')
Подзапрос как спецификация столбца

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


SELECT *, 
(SELECT ProductName FROM Products WHERE Id=Orders.ProductId) AS Product 
FROM Orders

Подзапросы в команде INSERT

В команде INSERT подзапросы могут применяться для определения значения, которое вставляется в один из столбцов:


INSERT INTO Orders (ProductId, CustomerId, CreatedAt, ProductCount, Price)
VALUES
( 
	(SELECT Id FROM Products WHERE ProductName='Galaxy S8'), 
	(SELECT Id FROM Customers WHERE FirstName='Tom'),
	'2017-07-11',  
	2, 
	(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
)

Подзапросы в команде UPDATE

В команде UPDATE подзапросы могут применяться:

  1. В качестве устанавливаемого значения после оператора SET

  2. Как часть условия в выражении WHERE

Так, увеличим количество купленных товаров на 2 в тех заказах, где покупатель Тоm:


UPDATE Orders
SET ProductCount = ProductCount + 2
WHERE CustomerId=(SELECT Id FROM Customers WHERE FirstName='Tom')

Или установим для заказа цену товара, полученную в результате подзапроса:


UPDATE Orders
SET Price = (SELECT Price FROM Products WHERE Id=Orders. ProductId) + 2000
WHERE Id=1

Подзапросы в команде DELETE

В команде DELETE подзапросы также применяются как часть условия. Так, удалим все заказы на Galaxy S8, которые сделал Bob:


DELETE FROM Orders
WHERE ProductId=(SELECT Id FROM Products WHERE ProductName='Galaxy S8')
AND CustomerId=(SELECT Id FROM Customers WHERE FirstName='Bob')

НазадСодержаниеВперед

T-SQL — подзапросы — CoderLessons.com

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

Подзапросы могут использоваться с операторами SELECT, INSERT, UPDATE и DELETE вместе с такими операторами, как =, <,>,> =, <=, IN, BETWEEN и т. Д.

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

  • Вы должны заключить подзапрос в скобки.

  • Подзапрос должен включать в себя предложение SELECT и предложение FROM.

  • Подзапрос может включать необязательные предложения WHERE, GROUP BY и HAVING.

  • Подзапрос не может включать предложения COMPUTE или FOR BROWSE.

  • Вы можете включить предложение ORDER BY, только если включено предложение TOP.

  • Вы можете вкладывать подзапросы до 32 уровней.

Вы должны заключить подзапрос в скобки.

Подзапрос должен включать в себя предложение SELECT и предложение FROM.

Подзапрос может включать необязательные предложения WHERE, GROUP BY и HAVING.

Подзапрос не может включать предложения COMPUTE или FOR BROWSE.

Вы можете включить предложение ORDER BY, только если включено предложение TOP.

Вы можете вкладывать подзапросы до 32 уровней.

Подзапросы с оператором SELECT

Синтаксис

Подзапросы чаще всего используются с оператором SELECT. Ниже приведен основной синтаксис.

SELECT column_name [, column_name ] 
FROM   table1 [, table2 ] 
WHERE  column_name OPERATOR 
   (SELECT column_name [, column_name ] 
   FROM table1 [, table2 ] 
   [WHERE])

пример

Рассмотрим таблицу CUSTOMERS, имеющую следующие записи.

ID  NAME       AGE       ADDRESS            SALARY 
1   Ramesh     32        Ahmedabad          2000.00 
2   Khilan     25        Delhi              1500.00 
3   kaushik    23        Kota               2000.00 
4   Chaitali   25        Mumbai             6500.00 
5   Hardik     27        Bhopal             8500.00 
6   Komal      22        MP                 4500.00 
7   Muffy      24        Indore             10000.00

Давайте применим следующий подзапрос с оператором SELECT.

SELECT *  
   FROM CUSTOMERS
   WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500)

Приведенная выше команда выдаст следующий вывод.

ID  NAME       AGE       ADDRESS          SALARY 
4   Chaitali   25        Mumbai           6500. 00 
5   Hardik     27        Bhopal           8500.00 
7   Muffy      24        Indore           10000.00

Подзапросы с оператором INSERT

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

Синтаксис

Ниже приведен основной синтаксис.

INSERT INTO table_name [ (column1 [, column2 ]) ] 
   SELECT [ *|column1 [, column2 ] 
   FROM table1 [, table2 ] 
   [ WHERE VALUE OPERATOR ]

пример

Рассмотрим таблицу CUSTOMERS_BKP с такой же структурой, что и таблица CUSTOMERS. Ниже приведен синтаксис для копирования полной таблицы CUSTOMERS в CUSTOMERS_BKP.

INSERT INTO CUSTOMERS_BKP 
   SELECT * FROM CUSTOMERS  
   WHERE ID IN (SELECT ID FROM CUSTOMERS)

Подзапросы с оператором UPDATE

Подзапрос может использоваться вместе с оператором UPDATE. Можно использовать один или несколько столбцов в таблице при использовании подзапроса с оператором UPDATE.

Синтаксис

Ниже приведен основной синтаксис.

UPDATE table 
SET column_name = new_value 
[ WHERE OPERATOR [ VALUE ] 
   (SELECT COLUMN_NAME 
   FROM TABLE_NAME) 
   [ WHERE) ]

пример

Предположим, у нас есть таблица CUSTOMERS_BKP, которая является резервной копией таблицы CUSTOMERS.

Следующий пример команды обновляет SALARY в таблице CUSTOMERS в 0,25 раза для всех клиентов, чей возраст больше или равен 27.

UPDATE CUSTOMERS 
   SET SALARY = SALARY * 0.25 
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 )

Это повлияет на две строки, и, наконец, таблица CUSTOMERS будет иметь следующие записи.

ID  NAME       AGE       ADDRESS             SALARY 
1   Ramesh     32        Ahmedabad           500.00 
2   Khilan     25        Delhi               1500.00 
3   kaushik    23        Kota                2000. 00 
4   Chaitali   25        Mumbai              6500.00 
5   Hardik     27        Bhopal              2125.00 
6   Komal      22        MP                  4500.00 
7   Muffy      24        Indore              10000.00

Подзапросы с оператором DELETE

Подзапрос может использоваться вместе с оператором DELETE, как и любые другие операторы, упомянутые выше.

Синтаксис

Ниже приведен основной синтаксис.

DELETE FROM TABLE_NAME 
[ WHERE OPERATOR [ VALUE ] 
   (SELECT COLUMN_NAME 
   FROM TABLE_NAME) 
   [ WHERE) ]

пример

Предположим, у нас есть таблица CUSTOMERS_BKP, которая является резервной копией таблицы CUSTOMERS.

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

DELETE FROM CUSTOMERS 
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >=27 )

Это повлияет на две строки, и, наконец, таблица CUSTOMERS будет иметь следующие записи.

Как писать подзапросы в SQL

В этой статье кратко объясняется, как написать подзапрос на языке SQL, на примерах.

Введение

SQL-запрос — это команда, используемая для запроса данных из таблиц, хранящихся в реляционных базах данных. Как правило, SQL-запрос содержит как минимум два или более предложений:

  1. Выберите предложение : Это предложение используется для указания метаданных набора результатов (столбцы, фиксированные значения, выражения).
  2. Из пункта : Этот пункт используется для указания запрашиваемых источников данных. Источником данных может быть одна таблица или представление, а также более сложные формы.
  3. Где пункт : Этот пункт используется для указания операций фильтрации данных, необходимых в SQL-запросе.

В следующих разделах объясняется, как написать подзапрос на SQL в предложениях SELECT, FROM и WHERE.

  • Примечание: Все примеры в этой статье сделаны с использованием базы данных Stack Overflow 2013 и SQL Server 2019

Запись подзапросов в предложении SELECT

Сначала мы объясним, как написать подзапрос на SQL в предложении SELECT. Даже если написание подзапроса
поддерживается в предложении SELECT, разработчики должны тщательно написать свой запрос, как только они решат его использовать, поскольку он
снижает производительность запросов.

Предположим, что нам нужно написать SQL-запрос для получения первых десяти пользователей в базе данных переполнения стека и
последний значок, полученный каждым пользователем. Рассмотрим следующий запрос:

SELECT TOP (10) [Id]

    ,[DisplayName]

    ,(SELECT TOP 1 [Name] FROM [dbo].[Значки] badges WHERE badges.UserId = users.Id Order By [Date] Desc) as Latest_Badge

FROM [StackOverflow2013]. [dbo].[Users] пользователи

Рисунок 1. Написание подзапроса в предложении SELECT

Запись подзапроса в виде столбца не означает, что подзапрос выполняется для каждой строки, полученной из таблицы Users.
стол. Если мы отобразим предполагаемый план выполнения, он покажет, что данные значков извлекаются, а затем остаются присоединенными.
с таблицей пользователей.

Рисунок 2 – План выполнения

Запись подзапросов в предложении FROM

В этом разделе мы проиллюстрируем, как написать подзапрос на SQL в предложении FROM.

Вместо использования имени таблицы или представления в предложении FROM мы можем использовать подзапрос SQL в качестве источника данных, отметив, что
требуется присвоение псевдонима. Попробуем написать предыдущий запрос по-другому:

SELECT [Id]

    ,[DisplayName]

    ,(SELECT TOP 1 [Name] FROM [dbo]. [Значки] badges WHERE badges.UserId = users.Id ORDER BY [Date] DESC) as Latest_Badge

3

3 FROM (SELECT TOP 10 * FROM [StackOverflow2013].[dbo].[Users]) пользователей

Вместо того, чтобы писать параметр TOP 10 в предложении SELECT, мы решили заставить оптимизатор запросов SQL Server
выполните операцию извлечения данных TOP 10 перед присоединением к таблице Users с данными Badges, как показано
ранее.

Рисунок 3. Запись подзапроса в предложении FROM

На скриншоте ниже вы можете заметить, как выполняется оператор TOP сразу после сканирования кластера Users.
index, тогда как в предыдущем разделе он выполнялся как последний шаг.

Рисунок 4 – План выполнения

  • Примечание : Это не означает, что второй подход лучше первого. Пример используется только для иллюстрации влияния перемещения оператора TOP в подзапрос

Написание подзапросов в JOINS

Кроме того, мы можем добавлять соединения в предложении FROM при использовании подзапросов. Давайте используем следующий пример, чтобы проиллюстрировать, как написать подзапрос на SQL в предложении FROM, когда необходимы соединения.

SELECT users.[Id]

    ,[DisplayName]

    ,latest_posts.[CreationDate]

ИЗ [StackOverflow2013].[dbo].[Пользователи] пользователи INNER JOIN

(ВЫБЕРИТЕ ТОП-10 [OwnerUserId],[CreationDate] FROM [dbo].[Posts] ORDER BY [CreationDate] DESC)

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

Рисунок 5. Использование подзапроса SQL в предложении FROM с соединениями

Написание подзапросов в предложении WHERE

Чтобы проиллюстрировать, как написать подзапрос в SQL в предложении WHERE, мы отредактируем предыдущий запрос, чтобы получить
пользователей, опубликовавших последние десять сообщений в базе данных переполнения стека. Давайте воспользуемся следующим запросом:

SELECT [Id]

    ,[DisplayName]

FROM [StackOverflow2013].[dbo].[Users] users

WHERE  [Id] IN (SELECT TOP 10 [OwnerUserId] FROM [dbo].[Posts] ORDER ПО [Дата Создания] DESC)

В этом запросе мы переместили подзапрос из предложения FROM в предложение WHERE и использовали оператор IN для
отфильтровать идентификатор пользователя на основе результата подзапроса.

Рисунок 6. Запись подзапроса SQL в предложении WHERE

Альтернативы

Существует множество альтернатив использования подзапросов в SQL:

  1. Использование просмотров: в некоторых случаях представления могут заменять подзапросы, чтобы запрос выглядел проще. Этот параметр не влияет на производительность запросов и не улучшает ее, за исключением индексированных представлений. Вы можете узнать больше о представлениях в следующей статье: Изучение SQL: Представления SQL
  2. Использование общих табличных выражений (CTE): Общие табличные выражения являются альтернативой подзапросам. Вы можете узнать больше об этой функции в следующей статье: CTE в SQL Server; Запрос общих табличных выражений

Сводка

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

  • Автор
  • Последние сообщения

Хади Фадлаллах

Хади — профессионал SQL Server с более чем 10-летним опытом. Его основная специализация — интеграция данных. Он является одним из ведущих участников ETL и SQL Server Integration Services на Stackoverflow.com. Кроме того, он опубликовал несколько серий статей о Biml, функциях SSIS, поисковых системах, Hadoop и многих других технологиях.

Помимо работы с SQL Server, он работал с различными технологиями обработки данных, такими как базы данных NoSQL, Hadoop, Apache Spark. Он сертифицированный профессионал MongoDB, Neo4j и ArangoDB.

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

Хади действительно любит узнавать что-то новое каждый день и делиться своими знаниями. Вы можете связаться с ним на его личном сайте.

Просмотреть все сообщения от Hadi Fadlallah

Последние сообщения от Hadi Fadlallah (посмотреть все)

Написание подзапросов в SQL | Расширенный SQL

Начиная отсюда? Этот урок является частью полного учебника по использованию SQL для анализа данных. Проверьте начало.

В этом уроке мы рассмотрим:

  • Основы подзапросов
  • Использование подзапросов для агрегирования в несколько этапов
  • Подзапросы в условной логике
  • Объединение подзапросов
  • Подзапросы и UNION

На этом уроке вы продолжите работать с теми же данными о преступности в Сан-Франциско, что и на предыдущем уроке.

Основы подзапросов

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

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

 SELECT sub.*
  ОТ (
        ВЫБИРАТЬ *
          ИЗ tutorial.sf_crime_incidents_2014_01
         ГДЕ day_of_week = 'Пятница'
       ) суб
 ГДЕ подразрешение = 'НЕТ'

Давайте разберем, что происходит, когда вы запускаете приведенный выше запрос:

Сначала база данных выполняет «внутренний запрос» — часть в скобках:

 SELECT *
  ИЗ tutorial. sf_crime_incidents_2014_01
 ГДЕ day_of_week = 'Пятница'

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

 SELECT sub.*
  ОТ (
       <<результаты внутреннего запроса идут сюда>>
       ) суб
 ГДЕ подразрешение = 'НЕТ'

Подзапросы должны иметь имена, которые добавляются после круглых скобок так же, как вы добавляете псевдоним к обычной таблице. В данном случае мы использовали имя «sub».

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

Практическая задача

Напишите запрос, который выбирает все ордерные аресты из набора данных tutorial.sf_crime_incidents_2014_01 , а затем заключайте его во внешний запрос, который отображает только неразрешенные инциденты.

Попробуйте См. ответ

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

Использование подзапросов для агрегирования в несколько этапов

Что делать, если вы хотите выяснить, сколько инцидентов сообщается в каждый день недели? А что, если вы хотите узнать, сколько инцидентов происходит в среднем в пятницу декабря? В январе? Этот процесс состоит из двух шагов: подсчет количества инцидентов каждый день (внутренний запрос), затем определение среднемесячного значения (внешний запрос):
суб. день_недели,
AVG(sub.incidents) КАК среднее_происшествие
ОТ (
ВЫБЕРИТЕ день_недели,
дата,
COUNT(incidnt_num) инцидентов AS
ИЗ tutorial.sf_crime_incidents_2014_01
СГРУППИРОВАТЬ НА 1,2
) суб
СГРУППИРОВАТЬ НА 1,2
ЗАКАЗАТЬ ПО 1,2

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

Практическая задача

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

Попробуйте См. ответ

Подзапросы в условной логике

Вы можете использовать подзапросы в условной логике (в сочетании с WHERE , JOIN / ON или CASE ). Следующий запрос возвращает все записи с самой ранней даты в наборе данных (теоретически — плохое форматирование столбца даты на самом деле заставляет возвращать значение, отсортированное первым в алфавитном порядке):

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ Дата = (ВЫБЕРИТЕ МИН (дата)
                 ИЗ tutorial.sf_crime_incidents_2014_01
              )

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

 SELECT *
  ИЗ tutorial.sf_crime_incidents_2014_01
 ГДЕ Дата В (ВЫБЕРИТЕ дату
                 ИЗ tutorial.sf_crime_incidents_2014_01
                ЗАКАЗАТЬ ПО дате
                ПРЕДЕЛ 5
              )

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

Объединение подзапросов

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

 ВЫБОР *
  ИЗ tutorial.sf_crime_incidents_2014_01 происшествий
  ПРИСОЕДИНЯЙТЕСЬ ( ВЫБЕРИТЕ дату
           ИЗ tutorial.sf_crime_incidents_2014_01
          ЗАКАЗАТЬ ПО дате
          ПРЕДЕЛ 5
       ) суб
    ON инциденты.дата = суб.дата

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

 ВЫБЕРИТЕ инциденты.*,
       sub.incidents AS инциденты_этот_день
  ИЗ tutorial.sf_crime_incidents_2014_01 происшествий
  ПРИСОЕДИНЯЙТЕСЬ ( ВЫБЕРИТЕ дату,
          COUNT(incidnt_num) инцидентов AS
           ИЗ tutorial.sf_crime_incidents_2014_01
          СГРУППИРОВАТЬ ПО 1
       ) суб
    ON инциденты.дата = суб.дата
 ORDER BY sub.incidents DESC, время

Практическая задача

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

ПопробуйтеСмотреть ответ

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

 SELECT COALESCE(acquisitions.acquired_month, Investments.funded_month) AS month,
       COUNT(DISTINCT Acquirements.company_permalink) КАК компании_приобретены,
       COUNT(DISTINCT Investments.company_permalink) КАК инвестиции
  ИЗ приобретения tutorial.crunchbase_acquisitions
  FULL JOIN tutorial.crunchbase_investments инвестиции
    ON приобретения.acquired_month = инвестиции.funded_month
 СГРУППИРОВАТЬ ПО 1

Обратите внимание, что для того, чтобы сделать это правильно, вы должны соединить поля даты, что вызывает массовый «взрыв данных». По сути, происходит то, что вы соединяете каждую строку в данном месяце из одной таблицы с каждым месяцем в данной строке в другой таблице, поэтому количество возвращаемых строк невероятно велико. Из-за этого мультипликативного эффекта вы должны использовать COUNT(DISTINCT) вместо COUNT , чтобы получить точные подсчеты. Вы можете увидеть это ниже:

Следующий запрос показывает 7414 строк:

 ВЫБРАТЬ СЧЕТЧИК(*) ИЗ tutorial.crunchbase_acquisitions

Следующий запрос показывает 83 893 строки:

 SELECT COUNT(*) FROM tutorial.crunchbase_investments

Следующий запрос показывает 6 237 396 строк:

 SELECT COUNT(*)
      ИЗ приобретения tutorial.crunchbase_acquisitions
      FULL JOIN tutorial.crunchbase_investments инвестиции
        ON приобретения.acquired_month = инвестиции.funded_month

Если вы хотите понять это немного лучше, вы можете провести дополнительное исследование декартовых произведений. Также стоит отметить, что FULL JOIN и COUNT , приведенные выше, на самом деле работают довольно быстро — это COUNT(DISTINCT) , который занимает вечность. Подробнее об этом в уроке по оптимизации запросов.

Конечно, вы могли бы решить эту проблему намного эффективнее, объединив две таблицы по отдельности, а затем объединив их вместе, чтобы подсчеты выполнялись для гораздо меньших наборов данных:
приобретения.companies_acquired,
Investments.companies_rec_investment
ОТ (
ВЫБЕРИТЕ приобретаете_месяц КАК месяц,
COUNT(DISTINCT company_permalink) AS company_acquired
ИЗ tutorial.crunchbase_acquisitions
СГРУППИРОВАТЬ ПО 1
) приобретения
ПОЛНОЕ СОЕДИНЕНИЕ (
ВЫБЕРИТЕ funded_month AS месяц,
COUNT(DISTINCT company_permalink) КАК company_rec_investment
ИЗ tutorial.crunchbase_investments
СГРУППИРОВАТЬ ПО 1
)вложения
ON приобретения.месяц = ​​инвестиции.месяц
ЗАКАЗАТЬ ПО 1 ДЕСК

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

Практическая задача

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

Попробуйте См. ответ

Подзапросы и ОБЪЕДИНЕНИЯ

В следующем разделе мы возьмем урок, посвященный ОБЪЕДИНЕНИЯМ, снова используя данные Crunchbase:

 SELECT *
  ИЗ tutorial.crunchbase_investments_part1
 СОЮЗ ВСЕХ
 ВЫБИРАТЬ *
   ИЗ tutorial.crunchbase_investments_part2

Набор данных нередко бывает разбит на несколько частей, особенно если данные проходят через Excel в какой-либо момент (Excel может обрабатывать только около 1 млн строк на электронную таблицу). Две использованные выше таблицы можно рассматривать как разные части одного и того же набора данных — почти наверняка вы захотите выполнять операции со всем объединенным набором данных, а не с отдельными его частями. Вы можете сделать это с помощью подзапроса:

 SELECT COUNT(*) AS total_rows
  ОТ (
        ВЫБИРАТЬ *
          ИЗ tutorial.crunchbase_investments_part1
         СОЮЗ ВСЕХ
        ВЫБИРАТЬ *
          ИЗ tutorial.crunchbase_investments_part2
       ) суб

Это довольно просто. Попробуйте сами:

Практическая задача

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

ПопробуйтеСмотреть ответ

Практическая задача

Напишите запрос, который делает то же самое, что и в предыдущей задаче, но только для компаний, которые все еще работают. Подсказка: рабочий статус указан в tutorial.