SQL Server: условный агрегат;

Ответ 1

DECLARE @t TABLE ( [Year] INT, Value MONEY )

INSERT  INTO @t
VALUES  ( 2013, -0.0016 ),
        ( 2014, -0.0001 ),
        ( 2015, 0.0025 ),
        ( 2016, -0.0003 ),
        ( 2017, 0.0023 ),
        ( 2018, 0.0002 )

SELECT  t1.Year ,
        t1.Value ,
        oa.AggCol
FROM    @t t1
        OUTER APPLY ( SELECT    SUM(Value) AS AggCol
                      FROM      @t t2
                      WHERE     Year <= t1.Year
                                AND Year > ( SELECT ISNULL(MAX(Year), 0)
                                             FROM   @t
                                             WHERE  Year < t1.Year AND Value > 0)
                    ) oa

Вывод:

Year    Value    AggCol
2013    -0.0016  -0.0016
2014    -0.0001  -0.0017
2015    0.0025   0.0008
2016    -0.0003  -0.0003
2017    0.0023   0.002
2018    0.0002   0.0002

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

Ответ 2

Вы также можете сделать это с помощью оконных функций:

;WITH PrevValues AS (
   SELECT Year, Value,
          LAG(Value) OVER (ORDER BY Year) AS prevValue
   FROM Table1
), Flags AS (
  SELECT Year, Value,
         CASE 
            WHEN Value < 0 AND prevValue > 0 THEN 2  -- next slice
            WHEN Value < 0 OR prevValue < 0  THEN 1  -- same slice
            WHEN Value > 0 AND prevValue > 0 THEN -1 -- not in a slice
         END AS flag
  FROM PrevValues
), Islands AS (
  SELECT Year, Value,    
       CASE 
          WHEN flag = -1 THEN -1   
          ELSE SUM(flag) OVER (ORDER BY Year)      
               -
               ROW_NUMBER() OVER (ORDER BY Year) 
       END AS grp
  FROM Flags
)
SELECT Year, Value,
       CASE 
          WHEN grp = -1 THEN Value
          ELSE SUM(Value) OVER (PARTITION BY grp ORDER BY Year) 
       END AS AggCol
FROM Islands
ORDER BY Year

Идея состоит в том, чтобы идентифицировать острова строк, в которых применяется текущая сумма.

Демо здесь

Ответ 3

DECLARE @t TABLE ( [Year] INT, Value MONEY )
INSERT  INTO @t
VALUES (2013,-0.0016),(2014,0.0001),(2015,0.0025),(2016,-0.0003),(2017,0.0023),(2018,0.0002)

;WITH cteRowNum AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Year) as RowNum
    FROM
       @t
)
, cteRecursive AS (
    SELECT
       Year
       ,Value
       ,Value as AggCol
       ,RowNum
    FROM
       cteRowNum
    WHERe
       RowNum = 1

    UNION ALL

    SELECT
       c.Year
       ,c.Value
       ,CASE
          WHEN AggCol >= 0 THEN c.Value
          ELSE AggCol + c.Value
       END
       ,c.RowNum
    FROM
       cteRecursive r
       INNER JOIN cteRowNum c
       ON r.RowNum + 1 = c.RowNum
)

SELECT Year, Value, AggCol
FROM
    cteRecursive

ПРИМЕЧАНИЕ. ЭТО РАЗЛИЧНЫЕ ДАННЫЕ, ЧЕМ ЧТО ВЫ ПРЕДОСТАВЛЯЕТЕ! вот результаты

Year    Value        AggCol
2013    -0.0016     -0.0016
2014     0.0001     -0.0015
2015     0.0025      0.001
2016    -0.0003     -0.0003
2017     0.0023      0.002
2018     0.0002      0.0002

Проблема с вашими исходными тестовыми данными заключается в том, что она не учитывает ситуацию, когда для обеспечения текущей суммы положительных записей потребуется несколько последовательных положительных записей. Впоследствии ОБА другие ответы в то время, когда я отправляю свой ответ, ошибочны. Поэтому я изменил только запись 2014 на положительный .0001, и вы можете увидеть, как это решение работает, а другие - нет.

Вероятно, есть способы сделать это с помощью оконных функций, но рекурсивный cte довольно прямой, поэтому я пошел по этому маршруту:

• Сначала создайте row_number в наборе данных для использования в соединениях для учета ситуации, если в вашем наборе данных отсутствует что-то из года.
• Затем создайте рекурсивную строку cte и step 1 за раз, используя номер строки, и определите, должно ли агрегированное значение быть reset или добавлено в зависимости от того, является ли предыдущее значение строк положительным или отрицательным.

Вот результаты ответов Giorgos и Giorgi, если вы внесете изменения в тестовые данные:

Year    Value        AggCol
2013    -0.0016     -0.0016
2014     0.0001     -0.0015
2015     0.0025      0.0025
2016    -0.0003     -0.0003
2017     0.0023      0.002
2018     0.0002      0.0002

Вы можете увидеть, что проблема с AggCol для 2015 года неверна.

Обратите внимание: я думаю, что ответы - это отличные попытки и показать некоторые реальные навыки/код, когда дело доходит до пробелов/островов. Я не пытаюсь атаковать, просто повышаю качество сообщения.