Как написать сложные запросы в Apache Spark SQL с использованием CTE (с пунктом)
30 июня 2025 г.Apache Spark SQL использует возможности SQL для обработки крупномасштабных структурированных данных. Одной мощной особенностью в современном SQL являетсяWITHпункт, поддерживаемый в Spark SQL как общие выражения таблицы (CTE) CTE предлагает более организованный, читаемый и часто более эффективный способ построения сложных запросов. Эта статья объяснит, что такое CTE, почему она ценна в Spark SQL, и изучить его синтаксис с практическими примерами.
Что такое общее выражение таблицы (CTE)?
АОбщее выражение таблицы, или CTE, является именованным временным набором результатов, который вы определяете в одном операторе SQL. Это как временная виртуальная таблица, которая существует только во время запуска запроса. CTE начинается сWITHПункт, за которым следует один или несколько названных подраздел.
Основным примером синтаксиса является:
WITH expression_name [ ( column_name [ , ... ] ) ] [ AS ] ( query ) [ , ... ]
expression_name: Уникальное имя, которое вы назначаете своему временному набору результатов.(column_name, ...): Дополнительный список псевдоним столбцов для вывода CTE. Если не предоставлено, Spark SQL выведет имена столбцов изSELECTЗаявление в CTE.AS (query):SELECTоператор, который определяет логику для вашего CTE.
Зачем использовать CTE в Spark SQL?
Хотя вы часто можете достичь аналогичных результатов, используя вложенные подзадачи, CTE приносит несколько значительных преимуществ для развития разработки SQL:
Улучшает читабельность:Сложные запросы могут быстро следовать и изменить из-за вложенных подразделений. CTE позволяют вам разбить общую логику на меньшие, названные и более управляемые части. Каждый CTE действует как логическая единица работы, облегчая понимание всего запроса, отладка и поддержания.
Повышает удобство использования:Ключевым преимуществом CTE является то, что вы можете ссылаться на них несколько раз в одном и том же
WITHпункт или финалSELECTзаявление. Это помогает избежать дублирования кода и обеспечивает согласованность в ваших промежуточных расчетах.Упрощает отладку:Разбивая логику на отдельные блоки, вы можете легко отлаживать каждую часть CTE независимо. Это поможет вам найти проблемы намного быстрее, чем пытаться отладить один, сложный запрос.
Потенциал для оптимизации:В то время как CTE определяются как временные наборы результатов, Spark часто рассматривает их как логические представления. Это позволяет оптимизатору катализатора Spark применять оптимизацию, такие как продвижение предикатов по границам CTE. Это может привести к более эффективным планам выполнения, особенно когда CTE используется несколько раз. Spark может материализировать результат или оптимизировать его выполнение только один раз.
Практический пример
Предположим, у нас естьsalesТаблица и хочу найти общие продажи для каждой категории продукта.
-- Sample Data Setup (for demonstration purposes)
-- This would typically be a pre-existing table or DataFrame
CREATE OR REPLACE TEMPORARY VIEW sales AS
SELECT * FROM VALUES
('Electronics', 'Laptop', 1200.00, '2024-01-15'),
('Electronics', 'Mouse', 25.00, '2024-01-15'),
('Clothing', 'T-Shirt', 20.00, '2024-01-16'),
('Electronics', 'Keyboard', 75.00, '2024-01-16'),
('Clothing', 'Jeans', 50.00, '2024-01-17'),
('Electronics', 'Monitor', 300.00, '2024-01-17')
AS sales_data(category, product, amount, sale_date);
-- Using a CTE to calculate total sales per category
WITH CategorySales AS (
SELECT category, SUM(amount) AS total_category_sales
FROM sales
GROUP BY category
)
SELECT category, total_category_sales
FROM CategorySales
ORDER BY total_category_sales DESC;
Electronics 1600.00
Clothing 70.00
Time taken: 0.157 seconds, Fetched 2 row(s)
В этом примере,CategorySalesэто наш CTE. Он рассчитывает суммуamountсгруппированоcategoryПолем ФиналSELECTЗаявление затем просто запрашивает этот временныйCategorySalesнабор результатов.
Цепочка CTE
Одной из самых мощных особенностей CTE является способность их цепорить. Это означает, что более поздний CTE может относиться к более раннему CTE в том жеWITHпункт. Этот подход позволяет вам построить сложную логику шаг за шагом.
Рассмотрим расширение предыдущего примера, чтобы найти средние продажи по всем категориям, а затем определить категории, продажи которых выше этого среднего.
WITH CategorySales AS (
SELECT category, SUM(amount) AS total_category_sales
FROM sales
GROUP BY category
), AverageOverallSales AS (
SELECT AVG(total_category_sales) AS overall_avg_sales
FROM CategorySales -- Referencing the first CTE
)
SELECT
cs.category,
cs.total_category_sales,
aos.overall_avg_sales
FROM CategorySales cs
CROSS JOIN AverageOverallSales aos
WHERE cs.total_category_sales > aos.overall_avg_sales
ORDER BY cs.total_category_sales DESC;
Electronics 1600.00 835.000000
Time taken: 0.321 seconds, Fetched 1 row(s)
Здесь,CategorySalesРассчитывает общие продажи для каждой категории. Затем,AverageOverallSalesИспользованиеCategorySalesЧтобы найти общее среднее. Наконец, основной запрос объединяет эти два CTE, чтобы отфильтровать категории с продажами выше среднего.
Наиболее подходящие варианты использования
CTE очень полезны в различных сценариях реального мира:
- Пошаговое преобразование данных:Когда вам нужно применить серию преобразований, таких как фильтрация, агрегация и соединение с вашими данными, CTE позволяют четко определить каждый шаг.
- Сложные агрегации и аналитика:Для многоуровневых агрегаций или расчетов, включающих оконные функции, где необходимы промежуточные результаты, CTE предлагают четкую структуру.
- Суб-кверовой факторизация: If you find yourself writing the same sub-query multiple times, extract it into a CTE for usability.
- Обнаружение аномалии и проверки качества:Вы можете определить CTE для обнаружения аномалий или конкретных шаблонов данных, а затем использовать эти CTE в вашем основном запросе, чтобы пометить или исключить проблемные записи.
- Повышение производительности для повторных вычислений:Если в большом запросе рассчитывается сложный подпрограмма несколько раз, превращение его в CTE иногда может помочь зажечь оптимизировать его выполнение, что потенциально избегает повторяющихся расчетов.
Заключение
Общие выражения таблицы являются ключевой особенностью в современном SQL, которая значительно улучшает опыт разработчика. Позволяя модульности, улучшая читаемость и способствуя повторному использованию, CTE помогают специалистам по данным писать более чистые, более обслуживаемые и часто более эффективные запросы Squar Sql. Они превращают сложные задачи данных в четкие, управляемые шаги.
Первоначально опубликованоздесь
Оригинал