代码之家 › 专栏 › 技术社区 › Rich.Carpenter

需要有关SQL聚合查询的帮助

data-warehouse sql-server-2005 sql-server sql

Rich.Carpenter · 技术社区 · 15 年前

生成可在SQL Server Reporting Services报表中用于显示以下内容的数据集的最简单方法是什么:

SalesPerson        # Sales        # Gross        Profit
John Doe               100       $140,000       $25,000
Everyone Else (Avg.)  1200     $2,000,000      $250,000


Jane Smith              80       $100,000       $15,000
Everyone Else (Avg.)  1220     $2,040,000      $260,000


...and so on.

这是一个非常简单的例子,说明了我要做的事情(例如,真实的场景包括将“其他人”划分为三个类别行),但它说明了显示每个人的聚合数据的主要目标,并与其他人进行比较(排除)。伪代码就可以了。我对SQL代码的第一次尝试很快就变得非常混乱,我知道一定有更直接的方法。

感谢您的任何建议。

4 回复 | 直到 8 年前

binco 8 年前

如果您以后不介意格式化,那么如果我们假设您有如下内容:

首先,我需要一些辅助变量来计算总计数

一些助手变量*/ 声明@totalquantity int ,@totalamount十进制(19,4) ,@利润总额小数(19,4) ,@everyoneleise国际


   然后我们得到给定时期(2009年)每个人的总数。

在期间内提取总计*/
选择@totalquantity=sum(salesquantity)
,@totalamount=sum(销售额)
,@totalprofit=sum(利润)
,@everyonelse=count(distinct salesonkey)-1
从FactSales AS
在s.datekey=d.datekey上将dimdate作为d联接
式中[年]=2009

/*现在我们有这个时期每个人的总数*/
< /代码> 

现在,针对每个人与其他人,但都是一行。


每个销售人员与每个其他人的合计平均值*/
选择全名
,sum(salesQuantity)as[个人销售计数]
,总和(销售额)为[个人销售额]
,金额(利润)为[个人销售利润]
,(@totalquantity-sum(salesquantity))/@everyonelease as[每个人的平均销售额]
,(@totalamount-sum(salesamount))/@everyoneelse as[everyonelseavgsalesamount]
,(@totalprofit-sum(profit))/@everyoneleise as[everyoneleseavgsaleprofit]
从FactSales AS
在s.datekey=d.datekey上将dimdate作为d联接
右键联接dimsalerson as p on p.salesmeronkey=s.salesmeronkey
式中[年]=2009
按全名分组
< /代码> 

现在,您可以使用日期间隔的参数将所有这些打包到存储过程中。可能仍然需要调整销售人员的数量,以确定在某个时期内哪些人是活跃的,以及如何计算那些没有销售任何东西的人。有了这个,everyonelelse意味着销售某物的销售人员数量-1;因此,如果您有10名销售人员,而只有5名销售人员,那么就比everyonelelse=4多。



首先,我需要一些辅助变量来计算总计数


/* Few helper variables*/
DECLARE @TotalQuantity int
       ,@TotalAmount decimal(19, 4)
       ,@TotalProfit decimal(19, 4)
       ,@EveryoneElse int


然后我们得到给定时期(2009年)每个人的总数。


/* Fetch totals in the period*/
SELECT  @TotalQuantity = sum(SalesQuantity)
       ,@TotalAmount = sum(SalesAmount)
       ,@TotalProfit = sum(Profit)
       ,@EveryoneElse = count(DISTINCT SalesPersonKey) - 1
FROM   factSales AS s
       JOIN dimDate AS d ON s.DateKey = d.DateKey
WHERE   [Year] = 2009

/* Now we have totals for everyone in the period */


现在,对于每个人和其他人,都是一排的。


/* Totals for each sales person vs everyone else Average */
SELECT  FullName
       ,SUM(SalesQuantity) AS [PersonSalesCount]
       ,SUM(SalesAmount) AS [PersonSalesAmount]
       ,SUM(Profit) AS [PersonSalesProfit]
       ,( @TotalQuantity - SUM(SalesQuantity) ) / @EveryoneElse AS [EveryoneElseAvgSalesCount]
       ,( @TotalAmount - SUM(SalesAmount) ) / @EveryoneElse AS [EveryoneElseAvgSalesAmount]
       ,( @TotalProfit - SUM(Profit) ) / @EveryoneElse AS [EveryoneElseAvgSalesProfit]
FROM    factSales AS s
        JOIN dimDate AS d ON s.DateKey = d.DateKey
        RIGHT JOIN dimSalesPerson AS p ON p.SalesPersonKey = s.SalesPersonKey
WHERE   [Year] = 2009
GROUP BY FullName


现在,您可以使用日期间隔的参数将所有这些打包到存储过程中。可能仍然需要调整销售人员的数量,以确定在某个时期内哪些人是活跃的,以及如何计算那些没有销售任何东西的人。有了这个,EveryoneElse指销售人员的数量-1;因此,如果您有10名销售人员,只有5名销售人员,那么EveryoneElse = 4.

davek 15 年前

几乎可以肯定不是很有表现力,但声明性地清楚:

declare @i int = 0
declare @j int = 1

select * from
(
select (@i = @i + 2) as order_col, SalesPerson, sales, gross, profit
from myTable order by SalesPerson

union all

select (@j = @j + 2) as order_col, 'Everybody else'
, (select sum(sales) from myTable i where i.SalesPerson <> o.Salesperson)
, (select sum(gross) from myTable i where i.SalesPerson <> o.Salesperson)
, (select sum(profit) from myTable i where i.SalesPerson <> o.Salesperson)
from myTable o
order by SalesPerson
) x order by order_col

(第二部分 UNION 当然可以改进,但是已经晚了,我想不清楚了……)

Rob Farley 15 年前

在SSR中,在表中添加一个额外的细节行。然后在聚合函数上使用范围参数,并根据第一个原则进行平均。

如:

(Sum(Fields!Sales.Value, "table1") - Fields!Sales.Value) 
/
(Sum(Fields!NumSales.Value, "table1") - Fields!NumSales.Value)

etoisarobot 15 年前

我在这里做一些假设,但是如果你有这样的桌子

If object_id('Sales') is not null 
  Drop table Sales

CREATE TABLE [dbo].[Sales]
(
 [Salesperson] [nvarchar](50) NULL,
 [Sales] [int] NULL,
 [Gross] [money] NULL,
 [Profit] [money] NULL,
)

像这样的数据填充

Insert into Sales values ('John Doe', 100, 200.00, 100.00)
Insert into Sales values ('John Doe', 125, 300.00, 100.00)
Insert into Sales values ('Jane Smith', 100, 200.00, 100.00)
Insert into Sales values ('Jane Smith', 125, 1.00, 0.50)
Insert into Sales values ('Joel Spolsky', 100, 2.00, 1.00)
Insert into Sales values ('Joel Spolsky', 125, 3.00, 1.00)

这样的存储过程可以为您提供所需的

If object_id('usp_SalesReport') is not null 
Drop procedure usp_SalesReport

Go


Create Procedure usp_SalesReport
as
Declare @results as table
(
 SalesPerson nvarchar(50),
 Sales int,
 Gross money,
 Profit money
)

Declare  @SalesPerson nvarchar(50)
Declare SalesSums CURSOR FOR

Select  distinct SalesPerson from Sales

Open SalesSums

Fetch SalesSums INTO @SalesPerson

While @@Fetch_Status = 0

Begin
 Insert into @results Select Sales.Salesperson, sum(sales), sum(Gross), sum(profit) from Sales group by Sales.Salesperson having Sales.Salesperson = @SalesPerson
 Insert into @results Select 'EveryoneElse', avg(sales), avg(Gross), avg(profit) from Sales where Salesperson <> @SalesPerson

Fetch SalesSums INTO @SalesPerson          
End
Select * from @results
Close SalesSums
Deallocate SalesSums
Return