SUM values in SQL starting from a specific point i

问题:

I have a table that lists the index/order, the name, and the value. For example, it looks like this:

TABLE1:
ID | NAME | VALUE
1  | A    | 2
2  | B    | 5
3  | C    | 2
4  | D    | 7
5  | E    | 0

Now, I have another table that has a random list of NAMEs. It'll just show either A, B, C, D, or E. Depending on what the NAME is, I wanted to calculate the SUM of all the values that it will take to get to E. Does that make sense?

So if for example, my table looks like this:

TABLE2:
NAME
D
B
A

I'd want another column next to NAME that'll show the sum. So D would have 7 because the next event is E. B would have to be the sum of 5, 2, and 7 because B is 5, and C is 2, and D is 7. And A would have the sum of 2, 5, 3, and 7 and so on.

Hopefully this is easy to understand.

I actually don't have much at all aside from joining the two tables and getting the current value of the NAME. But I wasn't sure how to increment and so on and keep adding?

SELECT T2.NAME, T1.VALUE
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.NAME = T2.NAME

Is doing this even possible? Or am I wasting my time? Should I be referring to actual code to do this? Or should I make a function?

I wasn't sure where to start and I was hoping someone could help me out.

Thank you in advance!

回答1:

As gregory suggests, you can do this with a simple windowed function, which (in this case) will sum up all the rows after and including the current one based on the ID value. Obviously there are a number of different ways in which you can slice your data, though I'll leave that up to you to explore :)

---

declare @t table(ID int,Name nvarchar(50),Val int);
insert into @t values(1,'A',2),(2,'B',5),(3,'C',2),(4,'D',7),(5,'E',0);

select ID      -- The desc makes the preceding work the right way. This is 
        ,Name  -- essentially shorthand for "sum(Val) over (order by ID rows between current row and unbounded following)"
        ,Val   -- which is functionally the same, but a lot more typing...
        ,sum(Val) over (order by ID desc rows unbounded preceding) as s 
from @t
order by ID;

Which will output:

+----+------+-----+----+
| ID | Name | Val | s  |
+----+------+-----+----+
|  1 | A    |   2 | 16 |
|  2 | B    |   5 | 14 |
|  3 | C    |   2 |  9 |
|  4 | D    |   7 |  7 |
|  5 | E    |   0 |  0 |
+----+------+-----+----+

回答2:

The query is in two parts; this is hard to see at first, so I'll walk through each step.

Step 1: Obtain the rolling sum

Join table1 to itself for any letters greater than itself:

select *
    from table1 t1
    inner join table1 t2 on t2.name >= t1.name
    order by t1.name

This produces the following table

+ -- + ---- + ----- + -- + ---- + ----- +
| id | name | value | id | name | value |
+ -- + ---- + ----- + -- + ---- + ----- +
| 1  | A    | 2     | 1  | A    | 2     |
| 1  | A    | 2     | 2  | B    | 5     |
| 1  | A    | 2     | 3  | C    | 2     |
| 1  | A    | 2     | 4  | D    | 7     |
| 1  | A    | 2     | 5  | E    | 0     |
| 2  | B    | 5     | 2  | B    | 5     |
| 2  | B    | 5     | 3  | C    | 2     |
| 2  | B    | 5     | 4  | D    | 7     |
| 2  | B    | 5     | 5  | E    | 0     |
| 3  | C    | 2     | 3  | C    | 2     |
| 3  | C    | 2     | 4  | D    | 7     |
| 3  | C    | 2     | 5  | E    | 0     |
| 4  | D    | 7     | 4  | D    | 7     |
| 4  | D    | 7     | 5  | E    | 0     |
| 5  | E    | 0     | 5  | E    | 0     |
+ -- + ---- + ----- + -- + ---- + ----- +

Notice that if we group by the name from t1, we can get the rolling sum by summing the values from t2. This query

select  t1.name,
        SUM(t2.value) as SumToE
    from table1 t1
    inner join table1 t2
        on t2.name >= t1.name
    group by t1.name

gives us the rolling sums we want

+ ---- + ------ +
| name | sumToE |
+ ---- + ------ +
| A    | 16     |
| B    | 14     |
| C    | 9      |
| D    | 7      |
| E    | 0      |
+ ---- + ------ +

Note: This is equivalent to using a windowed function that sums over a set, but it is much easier to visually see what you're doing via this joining technique.

Step 2: Join the rolling sum

Now that you have this rolling sum for each letter, you simply join it to table2 for the letters you want

select t1.*
    from table2 t2
    inner join (
        select  t1.name,
                SUM(t2.value) as SumToE
            from table1 t1
            inner join table1 t2
                on t2.name >= t1.name
            group by t1.name
    ) t1 on t1.name = t2.name

Result:

+ ---- + ------ +
| name | sumToE |
+ ---- + ------ +
| A    | 16     |
| B    | 14     |
| D    | 7      |
+ ---- + ------ +

回答3:

CREATE TABLE #tempTable2(name VARCHAR(1))
INSERT INTO #tempTable2(name)
VALUES('D')
INSERT INTO #tempTable2(name)
VALUES('B')
INSERT INTO #tempTable2(name)
VALUES('A')


CREATE TABLE #tempTable(id INT, name VARCHAR(1), value INT)
INSERT INTO #temptable(id,name,value)
VALUES(1,'A',2)
INSERT INTO #temptable(id,name,value)
VALUES(2,'B',5)
INSERT INTO #temptable(id,name,value)
VALUES(3,'C',2)
INSERT INTO #temptable(id,name,value)
VALUES(4,'D',7)
INSERT INTO #temptable(id,name,value)
VALUES(5,'E',0)



;WITH x AS
(
    SELECT id, value, name, RunningTotal = value
    FROM dbo.#temptable
    WHERE id = (SELECT MAX(id) FROM #temptable)

  UNION ALL

    SELECT y.id, y.value, y.name, x.RunningTotal + y.value
    FROM x 
        INNER JOIN dbo.#temptable AS y ON 
            y.id = x.id - 1

)
SELECT x.id, x.value, x.name, x.RunningTotal
  FROM x
    JOIN #tempTable2 t2 ON
        x.name = t2.name
  ORDER BY x.id



DROP TABLE #tempTable
DROP TABLE #tempTable2