Сбросить промежуточный итог на основе другого столбца

Пытаюсь подсчитать промежуточный итог. Но он должен сбрасываться, когда совокупная сумма больше, чем значение другого столбца.

create table #reset_runn_total
(
id int identity(1,1),
val int, 
reset_val int
)

insert into #reset_runn_total
values 
(1,10),
(8,12),(6,14),(5,10),(6,13),(3,11),(9,8),(10,12)

образец данных

+----+-----+-----------+
| id | val | reset_val |
+----+-----+-----------+
|  1 |   1 |        10 |
|  2 |   8 |        12 |
|  3 |   6 |        14 |
|  4 |   5 |        10 |
|  5 |   6 |        13 |
|  6 |   3 |        11 |
|  7 |   9 |         8 |
|  8 |  10 |        12 |
+----+-----+-----------+

Ожидаемый результат

+----+-----+-----------------+-------------+
| id | val |    reset_val    | Running_tot |
+----+-----+-----------------+-------------+
|  1 |   1 | 10              |       1     |  
|  2 |   8 | 12              |       9     |  --1+8
|  3 |   6 | 14              |       15    |  --1+8+6 -- greater than reset val
|  4 |   5 | 10              |       5     |  --reset 
|  5 |   6 | 13              |       11    |  --5+6
|  6 |   3 | 11              |       14    |  --5+6+3 -- greater than reset val
|  7 |   9 | 8               |       9     |  --reset -- greater than reset val 
|  8 |  10 | 12              |      10     |  --reset
+----+-----+-----------------+-------------+

Запрос:

;WITH cte
     AS (SELECT id,
                val,
                reset_val,
                val AS running_total
         FROM   #reset_runn_total
         WHERE  id = 1
         UNION ALL
         SELECT r.*,
                CASE
                  WHEN lag(c.running_total + r.val) over(order by r.id) > lag(r.reset_val) over(order by r.id) THEN r.reset_val 
                  ELSE c.running_total + r.val
                END
         FROM   cte c
                JOIN #reset_runn_total r
                  ON r.id = c.id + 1)
SELECT *
FROM   cte

явно отставание не получит прежние значения есть идеи?


sql sql-server tsql sql-server-2012 cumulative-sum
person Pரதீப்    schedule 07.02.2017    source источник

Ответы (2)


arrow_upward
10
arrow_downward

Попробуйте пометить предыдущую строку

WITH cte
     AS (SELECT id,
                val,
                reset_val,
                val AS running_total,
                CASE WHEN val > reset_val THEN 1 ELSE 0 END as flag
         FROM   #reset_runn_total
         WHERE  id = 1
         UNION ALL
         SELECT r.*,
                CASE c.flag
                  WHEN 1 then r.val
                  ELSE c.running_total + r.val
                END,
                CASE WHEN CASE c.flag
                      WHEN 1 then r.val
                      ELSE c.running_total + r.val
                    END > r.reset_val 
                    THEN 1 ELSE 0 END
         FROM   cte c
                JOIN #reset_runn_total r
                  ON r.id = c.id + 1)
SELECT *
FROM   cte
person Serg    schedule 07.02.2017
comment
Вау.. Можно ли этого добиться с помощью оконного агрегата Sum() Over()? - person Pரதீப்; 07.02.2017
comment
Или без использования Recursive CTE ? - person Pரதீப்; 07.02.2017
comment
К вашему сведению, первое 0 as flag неверно, должно быть что-то вроде CASE WHEN val > reset_val THEN 1 ELSE 0 END as flag - person wqw; 07.02.2017
comment
Это братан.. Это было очень полезно. - person Pரதீப்; 09.02.2017

arrow_upward
1
arrow_downward

Вы можете попробовать использовать причудливое обновление, подобное этому

--- setup
IF OBJECT_ID('tempdb..#reset_runn_total') IS NOT NULL DROP TABLE #reset_runn_total
create table #reset_runn_total(id int identity(1,1) PRIMARY KEY, val int, reset_val int, running_sum int)

insert into #reset_runn_total(val, reset_val) values (1,10),(8,12),(6,14),(5,10),(6,13),(3,11),(9,8),(10,12)

--- use quirky update
DECLARE     @running_sum    INT
            , @temp         INT

UPDATE      #reset_runn_total
SET         @temp = running_sum = COALESCE(@running_sum, 0) + val
            , @running_sum = CASE WHEN @temp < reset_val THEN @temp ELSE 0 END
OPTION      (FORCE ORDER)

--- dump result
SELECT * FROM #reset_runn_total

Обратите внимание, что CLUSTERED INDEX во временной таблице требуется (тип PK по умолчанию), чтобы OPTION (FORCE ORDER) имело смысл.

person wqw    schedule 07.02.2017
comment
Спасибо за ответ. Я должен сделать это для более чем одной группы. Для упрощения я добавил образцы данных только для одной группы. Это не будет работать для более чем одной группы - person Pரதீப்; 07.02.2017
comment
Убедитесь, что вы проверили все предварительные условия (не менее 10) для причудливого обновления. sqlservercentral.com/articles/T-SQL/68467 - person Serg; 07.02.2017