SQL Server 中的动态“每周销售额”过程
4.70/5 (22投票s)
2005 年 9 月 23 日
5分钟阅读
143556
1711
一种创建基于日期的、具有一组动态列的汇总报告的方法。
引言
本文介绍了一种在 SQL Server 中创建分类“按周销售额”报告的方法，其中周表示为列，类别表示为行，并按类别汇总值。在描述存储过程（我们将参数化此数据请求并允许动态创建列）之前，了解通用查询的工作原理非常有用。一个详细的内部查询用于将销售额根据其在特定周内的位置放入适当的列。然后，该查询被一个聚合外部查询包装，该查询按类别折叠每个销售总额。出于演示目的，Northwind 是目标数据库。
详细内部查询
内部查询的目的是提供我们将需要的表连接，并建立每周使用的列。代码大致如下：
SELECT CategoryName
, <<column defining sales data for the first week>>
, <<column defining sales data for the second week>>
, ...
, <<column defining sales data for the last week>>
FROM Orders o
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
WHERE <<criteria limiting the selection to those dates
between the first and last weeks>>
FROM 子句只是连接从 Northwind 类别名称到单个订单（在 [Order Details] 中找到）的销售额所需的所有表。 WHERE 子句也将是直接的 - 一个简单的日期范围比较。
SELECT 子句值得关注。如果我们希望在结果集中，每一周都表示为自己的列，那么我们需要在 SELECT 子句中为每一列单独定义。幸运的是，它们都遵循相同的模式，并且可以在存储过程中用循环包装。
为了定义每一列，我们将使用 SQL Server 的 CASE 语句。如果给定的 OrderDate 落在所讨论的周内，我们将把销售总额作为正值输出在该周的列中。如果给定的 OrderDate 不落在所讨论的周内，我们将输出零值。例如，要定义 1998 年 3 月 1 日这一周的列，我们可以使用以下 CASE 语句：
CASE
WHEN OrderDate >= '03/01/1998' and OrderDate < '03/08/1998'
THEN (od.Quantity * od.UnitPrice) - od.Discount
ELSE 0
END AS [Week0]
计算 (od.Quantity * od.UnitPrice) – od.Discount 只是对该详细记录的销售额进行求和。在此示例中，我们将列的别名命名为 [Week0]。在存储过程中进行循环时，我们将看到 [Week1]、[Week2] 等。
如果从 1998 年 3 月 1 日开始报告三周的销售额，则此详细查询的 SQL 如下所示：
SELECT CategoryName
, CASE
WHEN OrderDate >= '03/01/1998' and OrderDate < '03/08/1998'
THEN (od.Quantity * od.UnitPrice) - od.Discount
ELSE 0
END AS [Week0]
, CASE
WHEN OrderDate >= '03/08/1998' and OrderDate < '03/15/1998'
THEN (od.Quantity * od.UnitPrice) - od.Discount
ELSE 0
END AS [Week1]
, CASE
WHEN OrderDate >= '03/15/1998' and OrderDate < '03/22/1998'
THEN (od.Quantity * od.UnitPrice) - od.Discount
ELSE 0
END AS [Week2]
FROM Orders o
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
WHERE (OrderDate >= '03/01/1998' AND OrderDate < '03/22/1998')
数据看起来像这样（如果给定的详细记录不在此周内，请注意零值）
CategoryName Week0 Week1 Week2
--------------- ------------ ------------ ------------
Seafood 25.889999 0.0 0.0
Dairy Products 340.0 0.0 0.0
Beverages 1079.75 0.0 0.0
Dairy Products 849.75 0.0 0.0
Confections 418.79999 0.0 0.0
...(etc.) ... ... ...
Condiments 0.0 500.0 0.0
Beverages 0.0 378.0 0.0
Seafood 0.0 249.75 0.0
Grains/Cereals 0.0 71.75 0.0
Seafood 0.0 569.79999 0.0
...(etc.) ... ... ...
Condiments 0.0 0.0 110.0
Dairy Products 0.0 0.0 37.450001
Seafood 0.0 0.0 57.850002
Beverages 0.0 0.0 387.45001
Condiments 0.0 0.0 399.95001
再次说明，这里的每一行代表一个单独的详细行，其值仅针对一个订单项计算；类别名称被重复，并且值未被聚合。
聚合外部查询
为了达到每个类别每周的总销售额的期望格式，我们将详细查询嵌套在一个聚合值的外部查询中，并按类别名称进行分组。这个外部查询是不复杂地使用 SUM() 函数和 GROUP BY 子句。
SELECT CategoryName
,Sum(Week0) AS [Week of 1 Mar]
,Sum(Week1) AS [Week of 8 Mar]
,Sum(Week2) AS [Week of 15 Mar]
FROM (
<< the inner query as defined above >>
) AS InnerDetail
GROUP BY CategoryName
由于我们为不落在给定列中的记录应用了零值，因此 SUM() 函数有效地仅为其各自的周产生总和。列别名 [Week of xxx] 提供了一个方便的标题来标识每一周。再次以 1998 年 3 月 1 日作为起始周执行完整的查询，结果如下：
CategoryName Week of 1 Mar Week of 8 Mar Week of 15 Mar
--------------- ------------------- ------------------- -------------------
Beverages 3243.9999694824219 2623.9499816894531 4589.3500213623047
Condiments 2806.0 1669.8500061035156 936.95001220703125
Confections 4932.7000503540039 6142.75 5382.8499145507812
Dairy Products 2136.5499877929687 5157.5 2716.4499931335449
Grains/Cereals 843.65000152587891 1196.75 763.80000686645508
Meat/Poultry 1250.3999862670898 330.0 656.0
Produce 1367.9000244140625 2724.7999877929687 2893.1999969482422
Seafood 2197.7900238037109 3154.2999877929687 2396.1500205993652
创建存储过程
将此聚合查询构建成存储过程的价值在于参数化。如果我们可以传入一个起始日期和我们感兴趣的周数数据，这种查询将更有用。我们从存储过程开始，声明一些变量。
CREATE PROCEDURE DynamicCategorySalesByWeek
@startingDate datetime
,@numWeeks int = 4
as
begin
declare @selectClause varchar(8000)
declare @fromClause varchar(8000)
declare @whereClause varchar(8000)
declare @groupByClause varchar(8000)
declare @i int;
declare @sDate datetime
declare @colHead varchar(255)
declare @case varchar(1000)
declare @cases varchar(8000)
declare @sqlInner varchar(8000)
. . .
end
我们将把起始日期和周数设置为参数，默认持续时间为四周。该过程的第一部分建立一个 while 循环，该循环在内部详细的 SELECT 语句中定义每一列。
-- determine columns to appear in the select clause of the inner detail set
set @i = 0
set @cases = ''
while (@i < @numWeeks)
begin
set @sDate = @startingDate + (@i * 7)
set @colHead = '[Week' + CONVERT(varchar(2), @i) + ']'
set @case = ', CASE WHEN OrderDate >= '''
+ CONVERT(varchar(30),@sDate,101)
+ ''' and OrderDate < '''
+ CONVERT(varchar(30), @sDate + 7, 101)
+ ''' THEN (od.Quantity * od.UnitPrice) - od.Discount ELSE 0 END'
set @cases = @cases + '
' +@case + ' as ' + @colHead
set @i = @i + 1
end
set @selectClause = 'select CategoryName' + @cases
除了使用基于 @startingDate 参数和循环计数器的计算来定义每一列的 CASE 语句外，此代码还创建了我们的列标题 [Week0]、[Week1]、[Week2] 等。内部详细集合的 FROM 子句在代码中如下：
-- the from clause of the inner detail set
set @fromClause = '
from Orders o Inner Join [Order Details] od on o.OrderID = od.OrderID
Inner Join Products p on od.ProductID = p.ProductID
Inner Join Categories c on p.CategoryID = c.CategoryID
'
@fromClause 字符串中嵌入的换行符纯粹是为了调试的可读性（可以嵌入 print 语句来测试正在编译的 SQL）。然后，WHERE 子句根据 @startingDate 和 @numWeeks 应用适当的日期范围。
-- the where clause, based on @startingDate and @numWeeks
set @whereClause = ' where (OrderDate >= '''
+ CONVERT(varchar(30), @startingDate, 101)
+ ''' and OrderDate < '''
+ CONVERT(varchar(30), @startingDate + (@numWeeks * 7), 101)
+ ''')'
--remember this "inner" detail query
set @sqlInner = @selectClause + @fromClause + @whereClause
为了创建外部聚合查询，我们使用另一个 while 循环将 SUM() 应用于每个 [WeekX] 列。我们还将为每一列定义一个友好的标题。
-- now we have the detail; create an outer query that aggregates the detail,
-- grouping by our CategoryName
set @i = 0;
set @cases = ''
while (@i < @numWeeks)
begin
set @sDate = @startingDate + (@i * 7)
set @colHead = '[Week of ' + CONVERT(varchar(255), @sDate, 6) + ']'
set @case = ', Sum([Week' + CONVERT(varchar(2), @i) + '])'
set @cases = @cases + '
' +@case + ' as ' + @colHead
set @i = @i + 1
end
最后，我们编译完整的聚合查询（包装内部详细查询）并执行它。
set @selectClause = 'select CategoryName as Category' + @cases
set @fromClause = ' from (' + @sqlInner + ') z'
set @groupByClause = ' group by CategoryName order by CategoryName'
-- finally, execute the aggregating query
execute(@selectClause + @fromClause + @groupByClause)
可以通过单击本文顶部的链接下载完整的存储过程。
关于演示项目
演示项目是一个简单的 ASP.NET 应用程序，它使用动态构建的查询。页面 default.aspx 包含一个非常简单的 DataGrid 控件，只有从存储过程检索数据源并应用表示格式化所必需的代码。要使用演示项目，请在 Northwind 数据库中执行文件 DynamicCategorySalesByWeek.sql，为 ASP.NET 用户设置其 EXECUTE 权限，并修改 web.config 以包含适当的连接字符串。
摘要
本文介绍的 DynamicCategorySalesByWeek 存储过程演示了一种创建具有基于日期的动态列的汇总报告的方法。在此情况下，我们选择周作为我们的列，但该技术可以轻松地应用于月、季度或年。该过程构建了一个聚合外部查询，该查询包装了一个内部详细查询，使用 while 循环来组合使用 CASE 语句定义的列。CASE 语句确保只有来自周内详细记录的值才会在该周的列中求和。通过将这些语句封装在动态的、参数化的过程中，开发人员可以获得此数据请求的灵活性，并可以在应用程序界面中包含这种灵活性。