Пример сложного запроса

Схема данных электронного магазина: Поставщик, Товар, Прайс-лист, Покупатель, Корзина, Заказ

Заказ

это множестов товаров, которые покупаются за один раз у одного поставщика.

Корзина

несколько заказов, которые оформляются одновременно, то есть в одной корзине есть товары разных поставщиков.

Схема в компактной записи: первичный ключ таблицы 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

План решения

1. Создадим выборку с товарами из одной корзины с указанием полной стоимости товара (цена*количество).
2. Создадим выборку с суммарной стоимостью корзины.
3. На основании 1 и 2 построи выборку с полями basket_id, product_id, weight (weight - это доля довара в корзине)
4. Из 3 с применением аналитических функцй получаем выборку basket_id, product_id, weight, rank
5. Оставляем только те товары, которые имеют rank <= 5, то есть "топ-5" товаров в каждой корзине.
6. Строим произведение выборки 5 "на себя" и оставляем нужные пары товаров.

Запросы

Запрос 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