Схема данных электронного магазина: Поставщик, Товар, Прайс-лист, Покупатель, Корзина, Заказ
Схема в компактной записи: первичный ключ таблицы X - X_id, если поле Y_id встречается в таблице X, то это ссылочный ключ. Типы данных - целые числа и строки. Запись ниже означает Таблица(поля)
supplier(supplier_id, supplier_name) product(product_id, product_name) pricelist(pricelist_id, supplier_id, product_id, pricelist_price) client(client_id, client_name) basket(basket_id, client_id) basketorder(basketorder_id, basket_id, supplier_id, product_id, basketorder_amount)
Вывести пары пяти "наиболее важных" товаров в группе
заказа. Наиболее важные - товары с максимальной долей (в рублях) от
общей стоимости корзины. В результате требуется получить выборку вида
basket_id, product_1_id, product_2_id
Запрос 3.
WITH subtotals(basket_id, product_id, subtotal)
AS (
SELECT basketorder.basket_id,
basketorder.product_id,
basketorder.basketorder_amount * pricelist.pricelist_price AS subtotal
FROM basketorder
JOIN pricelist ON basketorder.product_id = pricelist.product_id
AND basketorder.supplier_id = pricelist.supplier_id
)
SELECT subtotals.basket_id,
subtotals.product_id,
subtotals.subtotal / totals.total AS weight
FROM subtotals
JOIN (
SELECT basket_id, sum(subtotal) AS total
FROM subtotals
GROUP BY basket_id
) totals ON subtotals.basket_id = totals.basket_id
Запрос 5. Строим на основании 3. Основная часть запроса 3 становится подзапросом в разделе FROM с именем query3. Запрос 4 - это подзапрос ranket_basket.
WITH subtotals(basket_id, product_id, subtotal)
AS (
SELECT basketorder.basket_id,
basketorder.product_id,
basketorder.basketorder_amount * pricelist.pricelist_price AS subtotal
FROM basketorder
JOIN pricelist ON basketorder.product_id = pricelist.product_id
AND basketorder.supplier_id = pricelist.supplier_id
)
SELECT ranked_basket.basket_id,
ranked_basket.product_id,
ranked_basket.weight,
ranked_basket.product_rank
FROM (
SELECT query3.basket_id,
query3.product_id,
query3.weight,
RANK() OVER (
PARTITION BY basket_id
ORDER BY weight DESC
) AS product_rank
FROM (
SELECT subtotals.basket_id,
subtotals.product_id,
subtotals.subtotal / totals.total AS weight
FROM subtotals
JOIN (
SELECT basket_id, sum(subtotal) AS total
FROM subtotals
GROUP BY basket_id
) totals ON subtotals.basket_id = totals.basket_id
) query3
) ranked_basket
WHERE ranked_basket.product_rank <= 5
Итоговый запрос получается переносом предыдущего в WITH и выборкой из него с соединением по совпадению корзины, но не товара.
WITH
subtotals(basket_id, product_id, subtotal) AS (
SELECT basketorder.basket_id,
basketorder.product_id,
basketorder.basketorder_amount * pricelist.pricelist_price AS subtotal
FROM basketorder
JOIN pricelist ON basketorder.product_id = pricelist.product_id
AND basketorder.supplier_id = pricelist.supplier_id
),
top5_products (basket_id, product_id, weight, product_rank)
AS (
SELECT ranked_basket.basket_id,
ranked_basket.product_id,
ranked_basket.weight,
ranked_basket.product_rank
FROM (
SELECT query3.basket_id,
query3.product_id,
query3.weight,
RANK() OVER (
PARTITION BY basket_id
ORDER BY weight DESC
) AS product_rank
FROM (
SELECT subtotals.basket_id,
subtotals.product_id,
subtotals.subtotal / totals.total AS weight
FROM subtotals
JOIN (
SELECT basket_id, sum(subtotal) AS total
FROM subtotals
GROUP BY basket_id
) totals ON subtotals.basket_id = totals.basket_id
) query3
) ranked_basket
WHERE ranked_basket.product_rank <= 5
)
SELECT product_1.product_id AS product_1_id,
product_2.product_id AS product_2_id,
product_1.basket_id
FROM top5_products AS product_1
JOIN top5_products AS product_2 ON product_2.basket_id = product_1.basket_id
AND product_2.product_id <> product_1.product_id