sql накопленная сумма с нулевыми значениями при определенных условиях

Question

Мне нужно создать таблицу с кумулятивной суммой, что для: - сумма значения и ноль дает ноль, если в строке нет дальнейшего значения

Моя исходная таблица, из которой я хочу создать эту накопленную сумму, выглядит следующим образом:

+--------------------+---------------+------+-----+---------+-------+
| First_mob          | r2009         | r2010|r2011| r2012   | r2013 |
+--------------------+---------------+------+-----+---------+-------+
| 0                  | 1             | NULL |NULL | NULL    |NULL   |
| 1                  | 3             | 1    | 2   | 3       |3      |
| 2                  | 6             | 6    | 3   | NULL    |NULL   |
| 3                  | 10            | 17   | NULL| NULL    |5      |
| 4                  | 61            | 23   | NULL| 4       |NULL   | 
+--------------------+---------------+------+-----+---------+-------+

Стол, который я хочу получить выглядит

+--------------------+---------------+------+-----+---------+-------+
| First_mob          | r2009         | r2010|r2011| r2012   | r2013 |
+--------------------+---------------+------+-----+---------+-------+
| 0                  | 1             | NULL |NULL | NULL    |NULL   |
| 1                  | 4             | 1    | 2   | 3       |3      |
| 2                  | 10            | 7    | 5   | 3       |3      |
| 3                  | 20            | 24   | NULL| 3       |8      |
| 4                  | 81            | 47   | NULL| 7       |NULL   | 
+--------------------+---------------+------+-----+---------+-------+

Мой SQL-код для накопленной суммы выглядит так:

if OBJECT_ID('tempdb..#suma_risk_prestige_nbr') IS NOT NULL  drop table             #suma_risk_prestige_nbr
select tp1.first_mob_InDef,
       SUM(tp2.r2007) as r2007,
   SUM(tp2.r2008) as r2008,
   SUM(tp2.r2009) as r2009,
   SUM(tp2.r2010) as r2010,
   SUM(tp2.r2011) as r2011,
   SUM(tp2.r2012) as r2012,
   SUM(tp2.r2013) as r2013


into #suma_risk_prestige_nbr
from #risk_prestige_nbr tp1
inner join #risk_prestige_nbr tp2 on tp1.first_mob_InDef>=tp2.first_mob_InDef
group by tp1.first_mob_InDef,tp1.r2007,tp1.r2008,tp1.r2009,tp1.r2010,
tp1.r2011,tp1.r2012,tp1.r2013
order by tp1.first_mob_InDef,tp1.r2007,tp1.r2008,tp1.r2009,tp1.r2010,
tp1.r2011,tp1.r2012,tp1.r2013

Спасибо

Answer 1

Я вижу, что вы используете SQL Server, прочитайте эту ссылку о подсчете скользящего итога - Расчет промежуточного итога в SQL Server.

Самый быстрый способ в 2012 году - использовать сумму (...) сверх (упорядочить по...), в 2008 году - использовать CTE с последовательными номерами row_numbers

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

;with cte as (
  select
     T1.First_mob,
     sum(T2.[r2009]) as [r2009],
     sum(T2.[r2010]) as [r2010],
     sum(T2.[r2011]) as [r2011],
     sum(T2.[r2012]) as [r2012],
     sum(T2.[r2013]) as [r2013]
  from Table1 as T1
     left outer join Table1 as T2 on T2.First_mob <= T1.First_mob
  group by T1.First_mob
)
select
    c1.First_mob,
    c1.[r2009] as [r2009],
    c1.[r2010] as [r2010],
    c1.[r2011] as [r2011],
    c1.[r2012] as [r2012],
    c1.[r2013] as [r2013]
from cte as c1

см. пример с sql fiddle

обновить запрос немного странно, но это потому, что я сделал unpivot, поэтому я не мог указать все имена столбцов везде. Может быть, было бы возможно сделать это более эффективным, но теперь у меня есть это:

;with cte1 as (
  -- unpivoting columns to rows so we could write general queries
   select
       T1.First_mob,
       C.Name, C.Value
   from Table1 as T1
       cross apply (
           select 'r2009', [r2009] union all
           select 'r2010', [r2010] union all
           select 'r2011', [r2011] union all
           select 'r2012', [r2012] union all
           select 'r2013', [r2013]
       ) as C(Name, Value)
), cte2 as (
  -- counting running total
   select
      c1.First_mob, c1.Name, c1.Value, sum(c2.Value) as Total_Value
   from cte1 as c1
       inner join cte1 as c2 on c2.First_mob <= c1.First_mob and c2.Name = c1.Name
   group by c1.First_mob, c1.Name, c1.Value
), cte3 as (
  -- counting total sums (need later)
   select
      c1.Name, sum(c1.Value) as Value
   from cte1 as c1
   group by c1.Name
), cte4 as (
  -- removing all unnecessary values
   select
       c2.First_mob,
       c2.Name,
       case when c3.Value = c2.Total_Value and c2.Value is null then null else c2.Total_Value end as Value
    from cte2 as c2
       inner join cte3 as c3 on c3.Name = c2.Name
)
-- pivoting rows to columns
select
    c4.First_mob,
    max(case when C4.Name = 'r2009' then C4.Value end) as [r2009],
    max(case when C4.Name = 'r2010' then C4.Value end) as [r2010],
    max(case when C4.Name = 'r2011' then C4.Value end) as [r2011],
    max(case when C4.Name = 'r2012' then C4.Value end) as [r2012],
    max(case when C4.Name = 'r2013' then C4.Value end) as [r2013]
from cte4 as c4
group by c4.First_mob

см. пример с sql fiddle