SQL Server - оконные функции

Question

Итак, мне нужна помощь с домашним заданием.

Ниже приведена схема частей базы данных, которые мне нужно использовать.

Я уверен, что есть некоторые избыточности (я был бы признателен за любую помощь в настройке). В любом случае, вот назначение:

• Менеджер по продажам теперь хочет, чтобы вы создали отчет, в котором ее продукты ранжируются как по общим продажам, так и по общему объему продаж (у каждого будет свой собственный столбец). Создайте хранимую процедуру, которая возвращает
  следующие столбцы, но также с добавлением двух новых столбцов ранга.

  Название продукта | Количество заказов | Общая стоимость продаж | Общее количество продаж

• Генеральный директор хотел бы знать продажи на основе продаж в прошлом месяце. Пожалуйста, предоставьте ей запрос, чтобы ранжировать товары по количеству заказов за последний месяц. Там не должно быть пропущенных чисел.

• Создайте инструкцию SELECT, которая генерирует список заказов с идентификатором клиента, датой заказа и т. Д. *** Используйте функцию номера строки, чтобы сгенерировать уникальный идентификатор для каждой строки, отсортированной по дате заказа. DESC

Answer 1

Попытайся

CREATE PROCEDURE report_salesmanager 
AS 
  WITH cte_main AS 
  ( 
             SELECT     p.NAME, 
                        Isnull(Count(DISTINCT o.orderid), 0)  corderid, 
                        Sum(Isnull(o.ordertotal, 0))          sordertotal, 
                        Sum (Isnull(oi.orderitemquantity, 0)) sorderitemquantity 
             FROM       product p 
             LEFT JOIN  orderitem oi 
             INNER JOIN orders o 
             ON         o.orderid - oi.orderid 
             ON         p.productid = oi.productid 
             GROUP BY   p.NAME), cte_rank_value AS 
  ( 
           SELECT   NAME, 
                    Rank() OVER( ORDER BY sordertotal DESC) rk 
           FROM     cte_main), cte_rank_qtd AS 
  ( 
           SELECT   NAME, 
                    Rank() OVER( ORDER BY sorderitemquantity DESC) rk 
           FROM     cte_main) 
  SELECT     a.NAME [Product Name], 
             a.corderid, 
             [Orders Count] a.sordertotal [Total Sales Value], 
             b.rk [Rank Total Sales Value], 
             a.sorderitemquantity [Total Sales Quantity], 
             c.rk [Rank Total Sales Quantity] 
  FROM       cte_main a 
  INNER JOIN cte_rank_value b 
  ON         a.NAME = b.NAME 
  INNER JOIN cte_rank_qtd c 
  ON         a.NAME = c.NAME

GO

CREATE PROCEDURE report_ceo 
AS 
  DECLARE @somedateLastMonth DATE = Dateadd(-1,m,Getdate()) WITH cte_main AS 
  ( 
             SELECT     p.NAME, 
                        Isnull(Count(DISTINCT o.orderid), 0)  corderid, 
                        Sum(Isnull(o.ordertotal, 0))          sordertotal, 
                        Sum (Isnull(oi.orderitemquantity, 0)) sorderitemquantity 
             FROM       product p 
             INNER JOIN orderitem oi 
             INNER JOIN orders o 
             ON         o.orderid - oi.orderid 
             ON         p.productid = oi.productid 
             WHERE      Year(o.orderdate) * 100 + Month(o.orderdate) = Year(@somedateLastMonth) * 100 + Month(@somedateLastMonth)
             GROUP BY   p.NAME), 
    cte_rank_value AS 
  ( 
           SELECT   NAME, 
                    Rank() OVER( ORDER BY sordertotal DESC) rk 
           FROM     cte_main), 
    cte_rank_qtd AS 
  ( 
           SELECT   NAME, 
                    Rank() OVER( ORDER BY sorderitemquantity DESC) rk 
           FROM     cte_main) 
  SELECT     a.NAME [Product Name], 
             a.corderid, 
             [Orders Count] a.sordertotal [Total Sales Value], 
             b.rk [Rank Total Sales Value], 
             a.sorderitemquantity [Total Sales Quantity], 
             c.rk [Rank Total Sales Quantity] 
  FROM       cte_main a 
  INNER JOIN cte_rank_value b 
  ON         a.NAME = b.NAME 
  INNER JOIN cte_rank_qtd c 
  ON         a.NAME = c.NAME
go


SELECT     Row_number() OVER (ORDER BY o.orderdate DESC, o.ordertime DESC) rowNumber ,
           * 
FROM       product p 
INNER JOIN orderitem oi 
INNER JOIN orders o 
ON         o.orderid - oi.orderid 
ON         p.productid = oi.productid 
INNER JOIN cart c 
ON         c.orderid = o.orderid