Как вернуть самый заказанный товар за каждый месяц

Я пытаюсь вернуть товар, заказываемый чаще всего в месяц, 2007 года. Я хотел бы увидеть название продукта, сколько из них было заказано в этом месяце и месяц. Я использую базу данных AdventureWorks2012. Я пробовал несколько разных способов, но каждый раз возвращались несколько заказов на один и тот же месяц, а не один продукт, у которого было наибольшее количество заказов в этом месяце. Извините, если это не ясно. Я пытаюсь проверить себя, поэтому я придумываю свои собственные вопросы и пытаюсь ответить на них. Если кто-нибудь знает сайт, на котором есть такие вопросы и ответы, чтобы я мог проверить, это было бы очень полезно! Спасибо за любую помощь. Вот самое дальнее, что я смог получить с запросом.

WITH     Ord2007Sum
AS       (SELECT   sum(od.orderqty) AS sorder,
                   od.productid,
                   oh.orderdate,
                   od.SalesOrderID
          FROM     Sales.SalesOrderDetail AS od
                   INNER JOIN
                   sales.SalesOrderHeader AS oh
                   ON od.SalesOrderID = oh.SalesOrderID
          WHERE    year(oh.OrderDate) = 2007
          GROUP BY ProductID, oh.OrderDate, od.SalesOrderID)
SELECT   max(sorder),
         s.productid,
         month(h.orderdate) AS morder --, s.salesorderid
FROM     Ord2007Sum AS s
         INNER JOIN
         sales.SalesOrderheader AS h
         ON s.OrderDate = h.OrderDate
GROUP BY s.ProductID, month(h.orderdate)
ORDER BY morder;

sql sql-server adventureworks
person Lucas    schedule 28.07.2014    source источник
comment
Проверьте функцию ROW_NUMBER(), множество примеров на SO.   -  person Andrew    schedule 29.07.2014

Ответы (2)


arrow_upward
0
arrow_downward

Создайте CTE, который группирует наши продукты по месяцам и создает сумму

;WITH OrderRows AS
(
    SELECT
        od.ProductId, 
        MONTH(oh.OrderDate) SalesMonth,
        SUM(od.orderqty) OVER (PARTITION BY od.ProductId, MONTH(oh.OrderDate) ORDER BY oh.OrderDate) ProdMonthSum
    FROM SalesOrderDetail AS od
    INNER JOIN SalesOrderHeader AS oh
        ON od.SalesOrderID = oh.SalesOrderID
    WHERE year(oh.OrderDate) = 2007
),

Составьте простую таблицу чисел, чтобы разбивать каждый месяц года.

Months AS
(
    SELECT 1 AS MonthNum UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 
    UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
)

Мы запрашиваем нашу таблицу месяцев по данным и выбираем лучший продукт для каждого месяца на основе суммы

SELECT 
    m.MonthNum,
    d.ProductID,
    d.ProdMonthSum
FROM Months m
OUTER APPLY
(
    SELECT TOP 1 r.ProductID, r.ProdMonthSum 
    FROM OrderRows r
    WHERE r.SalesMonth = m.MonthNum
    ORDER BY ProdMonthSum DESC
) d
person Kevin Cook    schedule 28.07.2014

arrow_upward
0
arrow_downward

Оператор group by не должен включать oh.OrderDate, od.SalesOrderID, потому что это приведет к агрегированию ваших данных на неправильном уровне. Вам нужен ProductID, который чаще всего продавался в месяц, чтобы группа по условиям стала ProductID, datepart(mm,oh.OrderDate). Как предположил Эндрю, в этом случае полезна функция Row_Number, поскольку она позволяет создать ключ, упорядоченный по месяцам и порядку, который сбрасывается каждый месяц. Наконец, во внешнем запросе результаты ограничиваются первым экземпляром (что является наибольшим количеством) за каждый месяц.

WITH     Ord2007Sum 
AS(
        SELECT  sum(od.orderqty) AS sorder,
                od.productid,
                datepart(mm,oh.OrderDate) AS 'Month'
                row_number() over (partition by datepart(mm,oh.OrderDate)
                                    Order by datepart(mm,oh.OrderDate)desc, sorder desc) row
        FROM    Sales.SalesOrderDetail AS od
                INNER JOIN
                sales.SalesOrderHeader AS oh
             ON od.SalesOrderID = oh.SalesOrderID
        WHERE    datepart(yyyy,oh.OrderDate) = 2007
        GROUP BY ProductID, datepart(mm,oh.OrderDate)
    )

SELECT  productid,
        sorder,
        [month]
FROM    Ord2007Sum 
WHERE   row =1
person Oliver    schedule 28.07.2014