← Каталог
Общие табличные выражения (CTE) — Возможности операций выборки
Фрагмент из «Общие табличные выражения (CTE)»: Возможности операций выборки.
WITH заказы_клиентов AS (
SELECT
c.customer_id,
c.customer_name,
o.order_id,
o.order_date,
o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
)
SELECT
customer_name,
COUNT(order_id) AS количество_заказов,
SUM(total_amount) AS общая_сумма
FROM заказы_клиентов
GROUP BY customer_name
ORDER BY общая_сумма DESC;WITH заказы_клиентов AS (
SELECT
c.customer_id,
c.customer_name,
o.order_id,
o.order_date,
o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
)
SELECT
customer_name,
COUNT(order_id) AS количество_заказов,
SUM(total_amount) AS общая_сумма
FROM заказы_клиентов
GROUP BY customer_name
ORDER BY общая_сумма DESC;WITH продажи_по_регионам AS (
SELECT
region,
SUM(sales_amount) AS total_sales,
AVG(sales_amount) AS avg_sales,
COUNT(*) AS transaction_count
FROM sales_data
GROUP BY region
)
SELECT
region,
total_sales,
avg_sales,
transaction_count,
total_sales / transaction_count AS средний_чек
FROM продажи_по_регионам
WHERE total_sales > 100000;WITH продажи_по_регионам AS (
SELECT
region,
SUM(sales_amount) AS total_sales,
AVG(sales_amount) AS avg_sales,
COUNT(*) AS transaction_count
FROM sales_data
GROUP BY region
)
SELECT
region,
total_sales,
avg_sales,
transaction_count,
total_sales / transaction_count AS средний_чек
FROM продажи_по_регионам
WHERE total_sales > 100000;WITH рейтинги_продуктов AS (
SELECT
product_id,
product_name,
category,
sales_amount,
RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS category_rank,
ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS global_rank
FROM products
WHERE active = 1
)
SELECT
product_name,
category,
sales_amount,
category_rank,
global_rank
FROM рейтинги_продуктов
WHERE category_rank <= 3;WITH рейтинги_продуктов AS (
SELECT
product_id,
product_name,
category,
sales_amount,
RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS category_rank,
ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS global_rank
FROM products
WHERE active = 1
)
SELECT
product_name,
category,
sales_amount,
category_rank,
global_rank
FROM рейтинги_продуктов
WHERE category_rank <= 3;