需要 SQL 聚合查询帮助

Question

生成可用于 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 代码很快就陷入了困境，我知道必须有一个更直接的方法。

任何提示表示赞赏。

Answer 1

如果您不介意稍后格式化，那么如果我们假设您有类似的内容：

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

/* 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。

Answer 2

几乎可以肯定性能不是很好，但声明式清晰：（

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 的第二部分肯定可以改进，但已经晚了，我无法直接思考..）

Answer 3

在 SSRS 中，在表中添加额外的详细信息行。然后在聚合函数上使用 Scope 参数，并根据第一原理进行平均。

例如：

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

Answer 4

我在这里做了一些假设，但是如果您有一个像这样的表

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