代码之家 › 专栏 › 技术社区 › Gork. O

sql动态累计和-sql Server

cumulative-sum moving-average sql-server sql

Gork. O · 技术社区 · 8 年前

我有一张这样的桌子

+------+--------+
|   ID | Salary | 
+------+--------+
| 1    |    100 |
| 2    |     40 |
| 3    |     30 |
| 4    |     40 |
| 5    |     90 |
| 6    |    160 |
| 7    |     70 |
| 8    |     40 |
| 9    |     20 |
| 10   |     10 |
| 11   |    200 | 
| 12   |     50 |
+------+--------+

我可以做正常的累计和,但我需要一些不同于正常的东西 CUMULATIVE SUM . 如果累计总和为 %50 高于上一个总和,对于下一个累积总和,从该值开始

+------+--------+---------------+
|   ID | Salary | Running Total |
+------+--------+---------------+
| 1    |    100 |           100 |
| 2    |     40 |           140 |
| 3    |     30 |           170 |
| 4    |     40 |           210 |
| 5    |     90 |           300 |
| 6    |    160 |           460 |
| 7    |     70 |           230 |
| 8    |     40 |           270 |
| 9    |     20 |           290 |
| 10   |     10 |           300 |
| 11   |    200 |           500 |
| 12   |     50 |           250 |
+------+--------+---------------+

我想要这样的输出。

4 回复 | 直到 8 年前

DineshDB 8 年前

尝试以下操作:

SELECT ID , Salary,SUM(Salary) OVER(ORDER BY ID)Running Total
FROM Your_Table

更新答案:

我花了一整天的时间来得到这个:

DECLARE @TAB TABLE(ID INT, SALARY INT)

INSERT INTO @TAB VALUES(1 ,100)
INSERT INTO @TAB VALUES(2 , 40)
INSERT INTO @TAB VALUES(3 , 30)
INSERT INTO @TAB VALUES(4 , 40)
INSERT INTO @TAB VALUES(5 , 90)
INSERT INTO @TAB VALUES(6 ,160)
INSERT INTO @TAB VALUES(7 , 70)
INSERT INTO @TAB VALUES(8 , 40)
INSERT INTO @TAB VALUES(9 , 20)
INSERT INTO @TAB VALUES(10, 10)
INSERT INTO @TAB VALUES(11,200)
INSERT INTO @TAB VALUES(12, 50)


DECLARE @MIN INT, @MAX INT, @PreVal INT, @CurVal INT, @OldVal INT,@NextVal,  INT, @ExistVal INT
DECLARE @TABLE1 TABLE(ID INT, Sal INT, RunTotal INT)

INSERT INTO @TABLE1 (Id, Sal)
SELECT * FROM @TAB

SELECT @MIN=MIN(ID),@MAX=MAX(ID) FROM @TAB

WHILE(@MIN<=@MAX)
BEGIN
    SELECT @NextVal=SALARY FROM @TAB WHERE ID=@MIN
    SELECT @ExistVal=SALARY FROM @TAB WHERE ID=@MIN-1
    SELECT @OldVal=RunTotal FROM @TABLE1 WHERE ID=@MIN-1
    SELECT @PreVal=RunTotal FROM @TABLE1 WHERE ID=@MIN-2

    IF(@OldVal>@PreVal+(@PreVal/2))
    BEGIN
        SELECT @CurVal = @NextVal+@ExistVal
    END
    ELSE
    BEGIN
        SELECT @CurVal=ISNULL(@OldVal,0)+@NextVal-- FROM @TABLE1 WHERE ID=@MIN
    END

    UPDATE @TABLE1 SET RunTotal=@CurVal WHERE ID=@MIN
    SELECT @MIN=@MIN+1
END

SELECT * FROM @TABLE1

结果:

ID  Sal RunTotal
1   100 100
2   40  140
3   30  170
4   40  210
5   90  300
6   160 460
7   70  230
8   40  270
9   20  290
10  10  300
11  200 500
12  50  250

如果有人有更好的答案,请在评论中提及。

DineshDB 8 年前

回答第二个问题:

DECLARE @MIN INT, @MAX INT, @PreVal INT, @CurVal INT, @OldVal INT,@NextVal INT, @ExistVal INT
DECLARE @TABLE1 TABLE(ID INT, Sal INT, RunTotal INT,TimesGen INT)

INSERT INTO @TABLE1 (Id, Sal)
SELECT * FROM @TAB

SELECT @MIN=MIN(ID),@MAX=MAX(ID) FROM @TAB

WHILE(@MIN<=@MAX)
BEGIN
    SELECT @NextVal=SALARY FROM @TAB WHERE ID=@MIN
    SELECT @ExistVal=SALARY FROM @TAB WHERE ID=@MIN-1
    SELECT @OldVal=RunTotal FROM @TABLE1 WHERE ID=@MIN-1
    SELECT @PreVal=RunTotal FROM @TABLE1 WHERE ID=@MIN-2

    IF(@OldVal>@PreVal+(@PreVal/2))
    BEGIN
        SELECT @CurVal = @NextVal--+@ExistVal
        UPDATE @TABLE1 SET TimesGen=1 WHERE ID=@MIN 
    END
    ELSE
    BEGIN
        SELECT @CurVal=ISNULL(@OldVal,0)+@NextVal-- FROM @TABLE1 WHERE ID=@MIN
        UPDATE @TABLE1 SET TimesGen=ISNULL((SELECT TimesGen FROM @TABLE1 WHERE ID=@MIN-1),0)+1 WHERE ID=@MIN
    END

    UPDATE @TABLE1 SET RunTotal=@CurVal WHERE ID=@MIN
    SELECT @MIN=@MIN+1
END

SELECT ID,Sal Salary
    ,'('+CAST(RunTotal AS VARCHAR)+'/'+CAST(TimesGen AS VARCHAR)+') '+CAST(CAST(RunTotal*1.0/TimesGen AS NUMERIC(8,1)) AS VARCHAR) MovingAvg
    ,CAST(RunTotal*1.0/TimesGen AS NUMERIC(8,1))MovingAvg1
FROM @TABLE1

输出:

ID  Salary  MovingAvg
1   100     (100/1) 100.0
2   40      (140/2) 70.0
3   30      (170/3) 56.7
4   40      (210/4) 52.5
5   90      (300/5) 60.0
6   160     (460/6) 76.7
7   70      (230/2) 115.0
8   40      (270/3) 90.0
9   20      (290/4) 72.5
10  10      (300/5) 60.0
11  200     (500/6) 83.3
12  50      (250/2) 125.0

Gork. O 8 年前

顺便问一下,如何将RunTotal列更改为移动平均列。我想计算那条线到那条线的总和的平均值

以相同值为例;

+------+--------+----------+-----+
|   ID | Salary |MovingAvg | Flag|/* if the movavg increasing rate higher */
+------+--------+----------+-----+/* than %50 get 1 to flag, else 0 */
| 1    |    100 |      100 |  0  |
| 2    |     40 |       70 |  0  |/* (100+40)/2=70 */
| 3    |     30 |     56.6 |  0  |/* (100+40+30)/3=56.6 */
| 4    |     40 |     52.5 |  0  |/* (100+40+30+40)/4=52.5 */
| 5    |     90 |       60 |  0  |/* (100+40+30+40+90)/5=60 */
| 6    |    270 |       95 |  1  |/*MovAvg increasing rate is more than%50*/
| 7    |     70 |       70 |  0  |/* starting from this line*/
| 8    |     40 |       55 |  0  |/* (70+40)/2 */
| 9    |     10 |       40 |  0  |
| 10   |     60 |       45 |  0  |
| 11   |    300 |       96 |  1  |/*MovAvg increasing rate is more than%50*/
| 12   |     50 |       50 |  0  |
| 13   |     60 |       55 |  0  |/* (50+60)/2 */
+------+--------+----------+-----+

paparazzo 8 年前

基于递归CTE的答案

select * 
from @T t
order by t.id;
with cteRN as 
( select t.*, ROW_NUMBER() over (order by t.id) as rn 
  from @T t
)
, cte as 
( select t.id, t.salary, t.salary as running, flag = 0, t.rn
  from cteRN t
  where t.rn = 1
  union all 
  select t.id, t.salary
       , case when c.flag = 1 then t.salary + c.salary
              else                 c.running + t.salary
         end 
       , case when t.salary > c.running / 2 then 1
              else 0
         end
       , t.rn
  from cte c 
  join cteRN t
    on t.rn = c.rn + 1
)
select t.id, t.salary, t.running 
from cte t
order by t.id;

id          salary
----------- -----------
1           100
2           40
3           30
4           40
5           90
6           160
7           70
8           40
9           20
10          10
11          200
12          50

id          running     running
----------- ----------- -----------
1           100         100
2           140         140
3           170         170
4           210         210
5           300         300
6           460         460
7           230         230
8           270         270
9           290         290
10          300         300
11          500         500
12          250         250