Оптимизация SQL query

Question

Нашел интересную задачу из ЕГЭ по работе с базами данных через sqlite3

Я, недолго думая, написал вот такую query, которая выдала правильный ответ 30%

SELECT SUM(products_moving.quantity * products_moving.cost) * 100 / (
    SELECT SUM(products_moving.quantity * products_moving.cost) FROM products_moving
    JOIN shops ON products_moving.shop_id = shops.id
    WHERE products_moving.op_type = 'Продажа'
        AND shops.region = 'Октябрьский'
) FROM products_moving
JOIN products ON products_moving.article = products.article
JOIN shops ON products_moving.shop_id = shops.id
WHERE products_moving.op_type = 'Продажа'
    AND shops.region = 'Октябрьский'
    AND products.type = 'Молоко'

Но это далеко не самое красивое решение, ведь я, по идее, целых два раза ищу в базе данных все продажи в Октябрьском регионе. Мне стало очень интересно, можно ли как-то более красиво и изящно расписать эту query?

Первое что мне приходит на ум - это сначала найти все продажи в Октябрьском регионе, сохранить куда-то их, посчитать их сумму в рублях, сохранить эту сумму куда-то, потом уже из сохранённого ранее набора всех продаж в Октябрьском районе посчитать только все те, у которых была категория "Молоко", найти уже их сумму в рублях и посчитать процент

т.е. сделать что-то типа такого, но в SQL:

sales_in_region = filter(
    lambda row: row["op_type"] == 'Продажа' and row["region"] == 'Октябрьский',
    all_rows
)

sales_in_region_sum = sum(map(
    lambda row: row["quantity"] * row["cost"],
    sales_in_region
))

sales_in_region_milk = filter(
    lambda row: row["type"] == 'Молоко',
    sales_in_region
)

sales_in_region_milk_sum = sum(map(
    lambda row: row["quantity"] * row["cost"],
    sales_in_region_milk
))

print(sales_in_region_milk_sum // sales_in_region_sum * 100)

В рамках ЕГЭ конечно этот запрос оптимизировать нет никакого смысла, т.к. более одного раза я его не выполню и можно не париться, даже если он с десяток секунд занимает, но хочется знать, есть ли возможность как-то красиво обрабатывать такие кейсы?

UPD: В ответах ниже подробно разобрали идею с оператором WITH AS, на который я намекал выше, но взглянув на задачку под другим углом я придумал другое по смыслу решение, которое уже заметно быстрее выполняется

SELECT 
    SUM(CASE WHEN products.type = 'Молоко' THEN products_moving.quantity * products_moving.cost END) 
    * 100 /  SUM(products_moving.quantity * products_moving.cost)
FROM products_moving
JOIN products ON products_moving.article = products.article
JOIN shops ON products_moving.shop_id = shops.id
WHERE products_moving.op_type = 'Продажа'
    AND shops.region = 'Октябрьский'

идея в том, чтобы с помощью оператора CASE фильтровать нужные строки внутри SUM

Answer 1

Да, можно сделать так:

WITH total_sales AS (
    SELECT SUM(products_moving.quantity * products_moving.cost) AS total
    FROM products_moving
    JOIN shops ON products_moving.shop_id = shops.id
    WHERE products_moving.op_type = 'Продажа'
        AND shops.region = 'Октябрьский'
)
SELECT SUM(products_moving.quantity * products_moving.cost) * 100 / total_sales.total
FROM products_moving
JOIN products ON products_moving.article = products.article
JOIN shops ON products_moving.shop_id = shops.id
CROSS JOIN total_sales
WHERE products_moving.op_type = 'Продажа'
    AND shops.region = 'Октябрьский'
    AND products.type = 'Молоко';

Запросы WITH (Общие табличные выражения)

Дополнила вариант с использованием CASE внутри SUM

SELECT 
    SUM(CASE 
            WHEN products.type = 'Молоко' THEN products_moving.quantity * products_moving.cost 
            ELSE 0 
        END) * 100 / NULLIF(SUM(CASE 
                                    WHEN shops.region = 'Октябрьский' AND products_moving.op_type = 'Продажа' THEN products_moving.quantity * products_moving.cost 
                                    ELSE 0 
                                END), 0) AS milk_sales_percentage
FROM products_moving
JOIN products ON products_moving.article = products.article
JOIN shops ON products_moving.shop_id = shops.id
WHERE products_moving.op_type = 'Продажа'
    AND shops.region = 'Октябрьский';

Условные выражения CASE (Условная агрегация)

Answer 2

Учитывая все моменты из комментариев получилась вот такая query, возвращающая целое число процентов или null если подходящих записей не найдено

SELECT 
    SUM((products.type = 'Молоко') * products_moving.quantity * products_moving.cost) 
    * 100 / NULLIF(SUM(products_moving.quantity * products_moving.cost), 0)
FROM products_moving
JOIN products ON products_moving.article = products.article
JOIN shops ON products_moving.shop_id = shops.id
WHERE products_moving.op_type = 'Продажа'
    AND shops.region = 'Октябрьский'

БЛОГ НА HUSL

Оптимизация SQL query

Ответы (2 шт):