Получить наиболее продаваемый продукт для каждой страны из базы данных NORTHWIND

Question

Добрый день, ребята, я боролся с этим в течение прошлого дня, и я просто не могу понять это.

Моя задача - извлечь наиболее продаваемый продукт для каждой страны из популярной базы данных с открытым исходным кодом NORTHWIND: https://northwinddatabase.codeplex.com/

Я смог добраться до этого этапа, вот мой код в SQL Server:

--Get most sold product for each country
WITH TotalProductsSold AS 
(
    SELECT od.ProductID, SUM(od.Quantity) AS TotalSold
        FROM [Order Details] AS od
        GROUP BY od.ProductID
)
SELECT MAX(TotalProductsSold.TotalSold) AS MostSoldQuantity, s.Country --,p.ProductName
    FROM Products AS p
    INNER JOIN TotalProductsSold
    ON TotalProductsSold.ProductID = p.ProductID
    INNER JOIN Suppliers AS s
    ON s.SupplierID = p.SupplierID
    GROUP BY s.Country
    ORDER BY MostSoldQuantity DESC

Это дает мне следующий результат:

Это все хорошо, но я хочу узнать название продукта для MostSoldQuantity.

Большое спасибо!

PS Я положил комментарий -p.ProductName, где я думал, что это будет работать, но это не сработало, и если кто-то может объяснить мне, почему GROUP BY не позволяет автоматически выводить название продукта для строки, которая была бы отличной

Answer 1

Во-первых, начните с подсчета проданных товаров по стране, а не только по продукту. Тогда оцените их и выберите только что-нибудь в RANK = 1. Что-то вроде...

WITH
    ProductQuantityByCountry AS 
(
    SELECT
       s.CountryID,
       p.ProductID,
       SUM(od.Quantity)   AS Quantity
    FROM
        [Order Details]   AS od
    INNER JOIN
        Products          AS p
            ON  p.ProductID = od.ProductID
    INNER JOIN
        Suppliers         AS s
            ON  s.SupplierID = p.SupplierID
    GROUP BY
       s.CountryID,
       p.ProductID
),
    RankedProductQuantityByCountry
AS
(
    SELECT
        RANK() OVER (PARTITION BY CountryID ORDER BY Quantity DESC)  AS countryRank,
        *
    FROM
        ProductQuantityByCountry
)
SELECT
    *
FROM
    RankedProductQuantityByCountry
WHERE
    countryRank = 1

Обратите внимание, что одна страна может поставлять идентичное количество различных продуктов, и поэтому два продукта могут иметь ранг = 1. ROW_NUMER() и / или DENSE_RANK() для других, но похожих поведений RANK(),

РЕДАКТИРОВАТЬ: простое, хотя упражнение, чтобы объяснить, почему SQL не позволяет поставить Product.Name в вашем последнем запросе стоит задать вопрос.

Что должен делать SQL в этом случае?

SELECT
    MAX(TotalProductsSold.TotalSold) AS MostSoldQuantity,
    MIN(TotalProductsSold.TotalSold) AS LeastSoldQuantity,
    s.Country,
    p.ProductName
FROM
    blahblahblah
GROUP BY
    s.Country
ORDER BY
    MostSoldQuantity DESC

Наличие MIN и MAX делает вещи двусмысленными.

Вам может быть ясно, что вы хотите выполнить операцию by country и эта операция, чтобы выбрать продукт с наибольшим объемом продаж из этой страны. Но это на самом деле не является явным, и небольшие изменения в запросе могут иметь очень запутанные последствия для любого предполагаемого поведения. Вместо этого декларативный синтаксис SQL обеспечивает очень четкое / явное / детерминированное описание проблемы, которая должна быть решена.

Если выражение не упомянуто в GROUP BY оговорка, ты не можешь SELECT это, не агрегируя это. Это сделано для того, чтобы не было двусмысленности относительно того, что имеется в виду или что должен делать механизм SQL.

Требуя от вас оговорить get the total sales per country per product на одном уровне запроса вы можете and then pick the highest ranked per country на другом уровне запроса.

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