Внутренний запрос sql: Вложенные запросы (SQL Server) — SQL Server

Эволюция моих SQL запросов / Хабр

Всем привет! Я тимлид и Senior Oracle Developer, 12 лет работаю с OeBS и в основном пишу SQL запросы. Хотел бы рассказать, как за это время менялся мой подход в написании SQL запросов.

Вначале было слово, а точнее запрос. Скажем

select name from user where id = 1

Написать такой запрос как-то не так практически невозможно. Он одинаково хорошо работает во всех известных мне базах данных. А знаю я только oracle :З Но подозреваю что и в других реляционных тоже всё будет ок.

Так что же произошло? Проблемы начались, когда таблиц стало две:

select u.name from user u, rest r where u.id = 1 and u.id = r.user_id

Этот код вызывал у меня больше вопросов. Например, как должны быть соединены эти таблицы? Казалось бы что проще  id = user_id, но мне что-то не нравилось. В блоке where мне не хватало четкого разделения между условиями фильтрации и соединениями таблицам. Когда запрос содержал 2 таблицы всё ещё было норм, но когда кол-во таблиц доходило до 5 — всё рассыпалось.

select u.name from user u inner join rest r on u.id = r.user_id where u.id = 1

букв стало немного больше, но я намного лучше стал понимать, как связаны таблицы в моих SQL выражениях. Мир запросов расцвёл для меня новыми красками, и я больше не писал запросы как-то иначе. А ещё распространял эту весть среди других джунов. Это был мой первый шаг в эволюции SQL. Я вырвался от привычных шаблонов легаси кода и сделал что-то своё. Но была одна проблема. Когда используется скажем левостороннее соединение ANSI синтаксис заставляет переносить в связки, и все прочие ограничения для таблицы.

select u.name, r.resp_name 
from user u 
left join resp r on u.id = r.user_id  and r.end_date > sysdate 
where id = 1

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

with resp_q as (
  select resp_name, userid  
  from resp where r.end_date > sysdate)
 ,main_q as (
   select u.name, r.respname
   from user u 
   left join resp_q r on u.id = r.userid
   where id = 1)
 select * from main_q

Кода стало опять больше, но запросы в with позволили мне разбить монолитный запрос и группировать разные кусочки запроса по “историям”, а потом сплетать их вместе. Я мог рассказать про свой запрос так: “Получаем список пользователей. Список ролей. Объединяем их в одну выборку и отсекаем тех кто нам не нравится. С оставшимися идём дальше, взявшись за руки.”  И за каждый шаг отвечала свой небольшой именованный запрос. Это также помогло мне бороться с моим злейшим врагом

Но чего-то не хватало. По своей природе я люблю кодить, но, когда приходит время тестировать, весь мой энтузиазм куда-то пропадает. Как итог, я часто отдавал не до конца протестированный код. Мне регулярно приходилось слышать про unit тесты, автотесты и прочее. Но сложно было это применить к БД. Сегодня сумма за период равна 100р, а завтра 120р. И как ты тут напишешь тест? Так и жил… Но, уже став тимлидом, мне попалась задача, в которой надо было найти отмененные документы. Условие отмены было достаточно сложным и собиралось из множества нюансов (спрятал под функцию).

select * from document where xxstorno(id) = 'Y'

У меня было порядка 10 примеров документов. И завершая условие для одного документа, что-то ломалось в другом. А так как тестировал руками и глазами, времени уходило просто море.  Я уже думал этому не будет конца. Пока не понял, что вокруг моего запроса можно написать обертку, которая будет за меня проверять все мои кейсы и говорить какие документы прошли проверку, а какие нет. Потратив на обертку несколько минут, я сократил время тестирования с 5-7 минут, до нескольких секунд.

with test_case as (
  select 10 id, 'Y' storno from dual 
  union all 
  select 5 id, 'N' storno from dual)
  , run_test as (
    select tc.id, decode(xxstorno(d.id), tc.storno, 'OK', 'Error') result
    from test_case  tc
    left join document d on d.
  id = tc.id)
 select * from run_test

После правки функции, я просто запускал тест-запрос и смотрел сколько документов прошло тестирование, а сколько нет. В процессе тестирования я накидывал туда ещё кейсов, при том что про старые тесты тоже не забывались. И тогда я понял, как же это здорово! Как можно легко тестировать свой запрос, повышать надёжность и при этом не нужно ничего делать руками. Это может показаться элементарным, но до этого мне не встречались подобные конструкции. Обычно я видел конструкции типа and

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

Подзапросы SQL

Подзапрос представляет собой оператор SELECT, вложенный в тело другого оператора.

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

По количеству возвращаемых значений подзапросы разделяются на два типа:

• скалярные подзапросы, которые возвращают единственное значение;
• табличные подзапросы, которые возвращают множество значений.

По способу выполнения выделяют два типа подзапросов:

• простые подзапросы;
• сложные подзапросы.

Подзапрос называется простым, если он может рассматриваться независимо от внешнего запроса. СУБД выполняет такой подзапрос один раз и затем помещает его результат во внешний запрос.

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

Спонсор поста

Простые скалярные подзапросы

Приведем примеры простых скалярных подзапросов.

База данных, используемая в примерах, находится в этом посте.

Пример 1.
Определить наименования деталей, цена которых больше цены детали ‘болт’.

SELECT dname
FROM D
WHERE dprice > (SELECT dprice
                FROM D
                WHERE dname = ’болт’)

Данный подзапрос относится к скалярным, так как возвращает единственное значение — цену детали ‘болт’.

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

Пример 2.
Определить номера деталей, цена которых меньше средней цены деталей.

SELECT dname
FROM D
WHERE dprice < (SELECT AVG(dprice)
                FROM D)

Пример 3.
Определить номер поставщика, выполнившего поставку с минимальным объемом.

SELECT pnum
FROM PD
WHERE volume = (SELECT min(volume)
                FROM PD)

Пример 4.
Определить номера деталей, которых поставляется больше, чем деталей с номером 2.

SELECT pnum
FROM PD
GROUP BY dnum
HAVING sum(volume) > (SELECT sum(volume)
                      FROM PD
                      WHERE dnum = 2)

Подзапросы можно использовать не только в предложении WHERE, но и в других предложениях оператора SELECT, например, в самом предложении SELECT.

Пример 5.
Вывести следующую информацию о деталях: наименование, цена, отклонение от средней цены.

SELECT dname, dprice, dprice - (SELECT AVG(dprice) FROM PD) AS dif
FROM PD

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

Простые табличные подзапросы

Если подзапрос возвращает множество значений, то его результат следует обрабатывать специальным образом. Для этого предназначены операции IN, ANY, SOME и ALL.

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

Операция

Операция IN осуществляет проверку на принадлежность значения множеству, которое получается после выполнения подзапроса.

Пример 6.
Определить наименования поставщиков, которые поставляют детали.

SELECT pname
FROM P
WHERE pnum in (SELECT pnum
               FROM PD)

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

СУБД сначала выполняет подзапрос, в результате чего получает множество номеров поставщиков, которые поставляют детали. Затем СУБД проверяет номер каждого поставщика из таблицы P на принадлежность полученному множеству. При вхождении в множество наименование поставщика помещается в результирующую таблицу.

Пример 7.
Определить наименования поставщиков, которые не поставляют деталь с номером 2.

SELECT pname
FROM P
WHERE pnum not in (SELECT pnum
                   FROM PD
                   WHERE dnum = 2)

Пример 8.
Определить наименования поставщиков, которые поставляют только деталь с номером 1.

SELECT pname
FROM PD
WHERE pnum in (SELECT pnum
               FROM PD
               WHERE dnum = 1) AND pnum not in (SELECT pnum
                                                FROM PD
                                                WHERE dnum <> 1)

Операции

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

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

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

Ключевое слово SOME является синонимом ANY и используется для повышения наглядности текстов запросов.

Пример 9.
Определить наименования поставщиков, которые поставляют детали.

SELECT pname
FROM P
WHERE pnum = ANY(SELECT pnum
                 FROM PD)

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

СУБД сначала выполняет подзапрос, в результате чего получает множество номеров поставщиков, которые поставляют детали. Затем СУБД проверяет номер каждого поставщика из таблицы P на равенство хотя бы одному из номеров из полученного множества. При выполнении условия наименование поставщика помещается в результирующую таблицу.

Пример 10.
Определить наименование детали с максимальной ценой.

SELECT dname
FROM D
WHERE dprice >= ALL(SELECT dprice
                    FROM PD)

Последний пример можно решить следующим способом:

SELECT dname
FROM D
WHERE dprice = (SELECT max(dprice)
               FROM PD)

Сложные табличные подзапросы

Операция EXISTS

Результат выполнения таких операций представляет собой значения TRUE или FALSE.

Для операции EXISTS результат равен TRUE, если в возвращаемой подзапросом таблице присутствует хотя бы одна строка.

Если в результирующей таблице подзапроса пуста, то операция EXISTS возвращает значение FALSE. Для операции NOT EXISTS используются обратные правила обработки.

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

Пример 11.
Определить наименования поставщиков, которые поставляют детали.

SELECT pname
FROM P
WHERE EXISTS(SELECT *
             FROM PD
             WHERE PD.pnum = P.pnum)

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

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

Первой выбирается строка с информацией о поставщике Иванов. В подзапрос вместо P.pnum подставляется значение 1 (номер поставщика Иванова), после чего подзапрос выполняется.

Подзапрос возвращает три первых строки из таблицы PD, соответствующие поставкам Иванова, поэтому результат операции EXISTS равен TRUE, и наименование Иванов помещается в результирующую таблицу.

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

Создание самосоединений

Самосоединение это обычное соединение языка SQL, которое соединяет таблицу саму с собой. Такое соединение позволяет сравнивать значения, хранящиеся в одном столбце таблицы.

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

Пример 12.
Определить наименования поставщиков, которые поставляют и деталь с номером 1, и деталь с номером 2.

Один из вариантов решения задачи можно записать с помощью подзапроса следующим образом.

SELECT pnum
FROM PD
WHERE dnum = 1 AND pnum in (SELECT pnum
                            FROM PD
                            WHERE dnum = 2)

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

SELECT PD. pnum
FROM PD INNER JOIN PD AS PD1 ON PD.pnum = PD1.pnum
WHERE PD.dnum = 1 AND PD1.dnum = 2

Пример 13.
Определить наименования поставщиков, которые поставляют и деталь с номером 1, и деталь с номером 2, и деталь с номером 3.

SELECT PD.pnum
FROM (PD INNER JOIN PD AS PD1 ON PD.pnum=PD1.pnum)
INNER JOIN PD AS PD2 ON PD1.pnum=PD2.pnum
WHERE PD.dnum=1 AND PD1.dnum=2 AND PD2.dnum=3

Резюмирую

Из этой статьи вы узнали что такое подзапрос в SQL. Теперь вы легко отличите скалярный запрос от табличного, и простой запрос от сложного.

Также мы рассмотрели на примерах такие операции, как IN, ANY, SOME и ALL.

Написание подзапросов в 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. crunchbase_companies 9.0030 .

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

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

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

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

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

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

Основы работы с подзапросами

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

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

 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.
No related posts.