代码之家  ›  专栏  ›  技术社区  ›  Ryan

SUM()OVER,条件基于当前和值

  •  1
  • Ryan  · 技术社区  · 12 年前

    我有一张桌子(TableA),有点像这样:

    Id  Type    TimeBlock   Value
    1   1       1           100
    2   1       1           150
    3   2       1           100
    4   1       1           1000
    5   1       1           100
    6   1       2           50
    6   1       2           50
    

    我希望能够 SUM 但是,我只想 总和 该行基于某些条件。条件是: 总和 如果当前和值 大于或等于“值”列

    我假设查询将从以下内容开始:

    DECLARE @Amount int = 500;
    SELECT *, @Amount - SUM (Value) OVER (PARTITION BY TimeBlock ORDER BY Id) AS Sum
    FROM TableA
    

    我希望结果如下:

    Id  Type    TimeBlock   Value   Sum
    1   1       1           100     400     (500 - 100)
    2   1       1           150     250     (400 - 150)
    3   2       1           1000    NULL    (This does not count since its of type = 2 and 250 < 1000)
    4   1       1           1000    0       (250 - 1000 = -750 there its filled the @Amount so we'll leave it at 0)
    5   1       1           100     NULL    (Since @Amount has been filled anything that comes after in this time block is NULL)
    6   1       2           50      450     (500 - 50)
    6   1       2           50      400     (450 - 50)
    

    如您所见,我需要能够获得 总和 函数,以便能够将其与“值”列进行比较,我不太确定该如何做。我希望能够执行以下操作

    DECLARE @Amount int = 500;
    SELECT *, @Amount - SUM (CASE WHEN Type = 2 AND SUM(Value) OVER (PARTITION BY TimeBlock ORDER BY Id) < Value THEN 0 ELSE Value END) OVER (PARTITION BY TimeBlock ORDER BY Id) AS Sum
    FROM TableA
    

    编辑 类型=1-无论发生什么情况,对“值”列求和 类型=2-如果当前和值大于或等于值,则仅对行中的“值”列求和

    1 回复  |  直到 12 年前
        1
  •  0
  •   Pieter Geerkens    12 年前

    尽管这是基于有争议的 运行Sum Quirky更新 (Jeff Moden详细描述 here ),以下代码将起作用:

    -- prepare temporary table with required clustered index to control processing order
    create table #t (
         Id         int not null primary key nonclustered
        ,Type       int not null
        ,TimeBlock  int not null
        ,Value      int not null
    
        ,RunningSum int null
    
        ,unique clustered (TimeBlock,Id)
    );
    
    -- load temporary table from data source, 
    -- hard-coded data used for this example.
    insert #t(Id, Type,TimeBlock,Value)
    values
     (1, 1,  1,    100)
    ,(2, 1,  1,    150)
    ,(3, 2,  1,    100)
    ,(4, 1,  1,   1000)
    ,(5, 1,  1,    100)
    ,(6, 1,  2,     50)
    ,(7, 1,  2,     50);
    
    go
    
    declare @sum        int = 0
           ,@TimeBlock  int = -1;
    
    update #t
        set @sum = case when TimeBlock <> @TimeBlock then 500 - Value
                        when Value > @sum            then @sum
    
                        else @sum - Value
                    end
           ,@TimeBlock = TimeBlock
           ,RunningSum = @sum
    from #t with (TABLOCKX)
    where Type = 1
    option (MAXDOP 1)
    ;
    
    -- display results
    select * from #t
    
    drop table #t;
    go
    

    输出:

    Id          Type        TimeBlock   Value       RunningSum
    ----------- ----------- ----------- ----------- -----------
    1           1           1           100         400
    2           1           1           150         250
    3           2           1           100         NULL
    4           1           1           1000        250
    5           1           1           100         150
    6           1           2           50          450
    7           1           2           50          400
    

    请特别注意,这要求聚集索引与排序相同,并且为了确保其他管理员不会修改该排序,将数据复制到具有所需聚集索引的临时表中。此外 选项(MAXDOP 1) 设置为确保优化器不尝试对查询进行并行化,并设置EXCLUSIVE锁,以便(如果不需要临时表)在处理过程中不会对表进行修改。

    使现代化 : Quirky更新 这是有争议的,因为尽管它自Sybase SQL Server早期就开始工作,但它依赖于SQL Server的未记录功能,这些功能在未来的版本中不一定会得到支持。