Menu
CoddyTech

Упрощение запросов, ключевое слово WITH

Часть раздела Основы путешествия по SQL на Coddy. Урок 49 из 72.

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

SELECT * FROM table1
WHERE col2 IN (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN (
		SELECT col5 FROM table3
		WHERE col1 AND col3 OR col2
	)
)

Чтобы упростить задачу, мы можем использовать ключевое слово WITH query_name AS (...). Оно позволяет сохранить запрос под именем и использовать его где угодно:

WITH query1 AS (
	SELECT col5 FROM table3
	WHERE col1 AND col3 OR col2
), query2 AS (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN (SELECT col5 FROM query1)
)
SELECT * FROM table1
WHERE col2 IN (SELECT col1 FROM query2) AND col4 IN (SELECT col5 FROM query1)

Здесь мы повторно использовали query1 в query2, а затем снова — в основном запросе, чтобы добавить ещё одно условие для col4, которого нет во вложенной версии выше. Именованный запрос, созданный с помощью WITH, называется CTE (обобщённое табличное выражение).

challenge icon

Задание

Легко

Доступные таблицы и столбцы:

  • devices_specs: device_id, width, height, num_features, opinion
  • devices_score: device_id, score

Напишите запрос, который:

  1. Сначала определяет «крупные устройства» (устройства, у которых значение width больше 200)
  2. Затем вычисляет среднюю оценку этих крупных устройств. Назовите этот столбец average_score

Используйте конструкцию WITH, чтобы решить эту задачу.

Попробуйте сами

-- Шаг 1: задайте имя временному набору результатов, содержащему только интересующие вас устройства
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- Шаг 2: повторно используйте этот временный набор результатов в основном запросе
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);
quiz iconПроверьте себя

В этом уроке есть небольшой тест. Начните урок, чтобы ответить на вопросы и сохранить прогресс.

Все уроки раздела Основы

Потренируйтесь самостоятельно: Песочница SQL