将列总和到 SQL 中的当前行?

2022-01-09 00:00:00 sql sum partitioning sql-server

我正在尝试将一列汇总到当前行(在 SQL Server 中).我该怎么做?

I'm trying to sum a column up to the current row (in SQL Server). How do I do this?

select t1.CounterTime,
       t1.StartTime,
       t1.EndTime,
       isNull(t1.value, 0) as value1,

       -- How do I make Total1 the sum of t1.value over all previous rows?
       sum( isNull(t1.value, 0) ) over (partition by t1.CounterTime order by t1.CounterTime) as Total1

from   SomeTable t1
order by t1.CounterTime

但是我弄错了 partition by...

╔═══╦═════════════════════════╦═════════════════════════╦═════════════════════════╦════════╦════════╗
║   ║      CounterTime        ║        StartTime        ║         EndTime         ║ value1 ║ Total1 ║
╠═══╬═════════════════════════╬═════════════════════════╬═════════════════════════╬════════╬════════╣
║ 1 ║ 2015-03-17 12:00:00.000 ║ 2015-03-17 00:00:00.000 ║ 2015-03-18 00:00:00.000 ║     0  ║     0  ║
║ 2 ║ 2015-03-18 12:00:00.000 ║ 2015-03-18 00:00:00.000 ║ 2015-03-19 00:00:00.000 ║     0  ║     0  ║
║ 3 ║ 2015-03-19 12:00:00.000 ║ 2015-03-19 00:00:00.000 ║ 2015-03-20 00:00:00.000 ║   422  ║   422  ║
║ 4 ║ 2015-03-20 12:00:00.000 ║ 2015-03-20 00:00:00.000 ║ 2015-03-21 00:00:00.000 ║   989  ║   989  ║
║ 5 ║ 2015-03-21 12:00:00.000 ║ 2015-03-21 00:00:00.000 ║ 2015-03-22 00:00:00.000 ║  1162  ║  1162  ║
║ 6 ║ 2015-03-22 12:00:00.000 ║ 2015-03-22 00:00:00.000 ║ 2015-03-23 00:00:00.000 ║   524  ║   524  ║
╚═══╩═════════════════════════╩═════════════════════════╩═════════════════════════╩════════╩════════╝

应该是:

╔════════╗
║ Total1 ║
╠════════╣
║     0  ║
║     0  ║
║   422  ║
║  1411  ║
║  2573  ║
║  3097  ║
╚════════╝

推荐答案

试试这个
删除partition by"只使用order by"

Try this
Remove the "partition by" use only "order by"

select t1.CounterTime,
           t1.StartTime,
           t1.EndTime,
           isNull(t1.value, 0) as value1,

           -- How do I make Total1 the sum of t1.value over all previous rows?
           sum( isNull(t1.value, 0) ) over (order by t1.CounterTime) as Total1

    from   SomeTable t1
    order by t1.CounterTime

相关文章