时间戳

首页 > 解决方案 > 最佳销售表现者在指定年份的每个月

sql - 最佳销售表现者在指定年份的每个月

问题描述

使用 Adventure Works 2017 测试数据库，我需要查看指定年份的每个月的最佳销售人员是谁。管理层只对自行车“零部件”的销售感兴趣。创建一个存储过程来获取此信息。

  1. 年份必须是输入参数。
  2. 在一个字段中显示名字和姓氏。
  3. 显示每个表现最好的人的销售额和月份的总价值。
  4. 使用单个语句将分配额外的分数

到目前为止，我有这个：

CREATE PROCEDURE getTopSalesByYear (@Year int)
AS
BEGIN 
SET NOCOUNT ON
SELECT  FirstName + ' ' + LastName AS SalesPerson,
    sp.BusinessEntityID,
    DATENAME(MONTH,SOH.OrderDate) as SalesMonth,
    SUM(SOH.SubTotal) AS TotalSales FROM sales.SalesOrderHeader SOH
INNER JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
INNER JOIN sales.SalesPerson sp on soh.SalesPersonID = sp.BusinessEntityID
INNER JOIN Person.Person p on p.BusinessEntityID = sp.BusinessEntityID
INNER JOIN Production.Product Pr on sod.ProductID = pr.ProductID
INNER JOIN Production.ProductCategory pc on pc.ProductCategoryID = pr.ProductSubcategoryID
INNER JOIN Production.ProductSubcategory psc on pc.ProductCategoryID = pc.ProductcategoryID
WHERE psc.ProductCategoryID = 2
GROUP BY p.FirstName,p.LastName,sp.BusinessEntityID, DATENAME(MONTH,SOH.OrderDate)
ORDER BY TotalSales desc

这是我到目前为止所拥有的，但它需要成为一个程序，随着年份的过去。还要注意我不知道在哪里将参数传递给什么值。

标签： sqlsql-servertsql

解决方案

您需要ROW_NUMBER每月使用以获得最佳性能。

  • 要检查日期是否在特定年份内，而不是使用YEAR函数进行映射，最好计算起点和终点。结束应该是独占的，这称为半开区间。

  • 您还可以分组EOMONTH（月末），这可能比分组更有效DATENAME(MONTH，您可以在之后计算实际名称。

CREATE PROCEDURE getTopSalesByYear (@Year int)
AS

SET NOCOUNT ON;

SELECT
  s.FirstName + ' ' + s.LastName AS SalesPerson,
  DATENAME(MONTH, s.SalesMonth) AS SalesMonth,
  s.TotalSales
FROM (
    SELECT
      p.FirstName,
      p.LastName,
      p.BusinessEntityID,
      EOMONTH(SOH.OrderDate) AS SalesMonth,
      SUM(SOH.SubTotal) AS TotalSales,
      ROW_NUMBER() OVER (PARTITION BY EOMONTH(SOH.OrderDate) ORDER BY SUM(SOH.SubTotal) DESC) AS rn
    FROM sales.SalesOrderHeader SOH
    INNER JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
    INNER JOIN sales.SalesPerson sp on soh.SalesPersonID = sp.BusinessEntityID
    INNER JOIN Person.Person p on p.BusinessEntityID = sp.BusinessEntityID
    INNER JOIN Production.Product Pr on sod.ProductID = pr.ProductID
    WHERE pr.ProductCategoryID = 2
      AND SOH.OrderDate >= DATEFROMPARTS(@Year    , 1, 1)
      AND SOH.OrderDate <  DATEFROMPARTS(@Year + 1, 1, 1)
    GROUP BY
      p.FirstName,
      p.LastName,
      p.BusinessEntityID,
      EOMONTH(SOH.OrderDate)
) s
WHERE s.rn = 1
ORDER BY SalesMonth;

GO

请注意，如果该月确实有销售，这只会为您提供一个月的结果。如果没有销售，您将不会获得0，当月将没有行。

推荐阅读