sql - 使用 Group By 进行多个运行总计

Question

我正在努力寻找一种好方法来运行带有 group by 的运行总计，或者类似的方法。下面基于游标的运行总计适用于完整的表，但我想扩展它以添加“客户端”维度。因此，我将获得如下创建的运行总计，但对于一张表中的每个公司(即公司 A、公司 B、公司 C 等)

CREATE TABLE test (tag int,  Checks float, AVG_COST float, Check_total float,  Check_amount float, Amount_total float, RunningTotal_Check float,  
 RunningTotal_Amount float)

DECLARE @tag int,
        @Checks float,
        @AVG_COST float,
        @check_total float,
        @Check_amount float,
        @amount_total float,
        @RunningTotal_Check float ,
        @RunningTotal_Check_PCT float,
        @RunningTotal_Amount float



SET @RunningTotal_Check = 0
SET @RunningTotal_Check_PCT = 0
SET @RunningTotal_Amount = 0
DECLARE aa_cursor CURSOR fast_forward
FOR
SELECT tag, Checks, AVG_COST, check_total, check_amount, amount_total
FROM test_3

OPEN aa_cursor
FETCH NEXT FROM aa_cursor INTO @tag,  @Checks, @AVG_COST, @check_total, @Check_amount, @amount_total
WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @RunningTotal_CHeck = @RunningTotal_CHeck + @checks
  set @RunningTotal_Amount = @RunningTotal_Amount + @Check_amount
  INSERT test VALUES (@tag, @Checks, @AVG_COST, @check_total, @Check_amount, @amount_total,  @RunningTotal_check, @RunningTotal_Amount )
  FETCH NEXT FROM aa_cursor INTO @tag, @Checks, @AVG_COST, @check_total, @Check_amount, @amount_total
 END

CLOSE aa_cursor
DEALLOCATE aa_cursor

SELECT *, RunningTotal_Check/Check_total as CHECK_RUN_PCT, round((RunningTotal_Check/Check_total *100),0) as CHECK_PCT_BIN,  RunningTotal_Amount/Amount_total as Amount_RUN_PCT,  round((RunningTotal_Amount/Amount_total * 100),0) as Amount_PCT_BIN
into test_4
FROM test ORDER BY tag
create clustered index IX_TESTsdsdds3 on test_4(tag)

DROP TABLE test

----------------------------------

我可以计算任何 1 家公司的运行总计，但我想对多家公司执行此操作，以产生类似下面的结果。

CLIENT  COUNT   Running Total
Company A   1   6.7%
Company A   2   20.0%
Company A   3   40.0%
Company A   4   66.7%
Company A   5   100.0%
Company B   1   3.6%
Company B   2   10.7%
Company B   3   21.4%
Company B   4   35.7%
Company B   5   53.6%
Company B   6   75.0%
Company B   7   100.0%
Company C   1   3.6%
Company C   2   10.7%
Company C   3   21.4%
Company C   4   35.7%
Company C   5   53.6%
Company C   6   75.0%
Company C   7   100.0%

Answer 1

这在 SQL Server 2012 中终于变得简单了，其中 SUM 和 COUNT 支持包含 ORDER BY 的 OVER 子句。使用 Cris 的 #Checks 表定义:

SELECT
  CompanyID,
  count(*) over (
    partition by CompanyID
    order by Cleared, ID
  ) as cnt,
  str(100.0*sum(Amount) over (
    partition by CompanyID
    order by Cleared, ID
  )/
  sum(Amount) over (
    partition by CompanyID
  ),5,1)+'%' as RunningTotalForThisCompany
FROM #Checks;

SQL fiddle here .