SQL Server: cumulative percentage with "group by" on many fields

I have this table, and I want to calculate the cumulative percentage for each TYPE_MATERIAL/YEARS

declare @mytable table (TYPE_MATERIAL int, YEARS int, ROW_NUM int, PERCENTUAL_PRICE numeric(6,2))
insert @mytable
select 1,2010,1,54.5
union all select 1,2010,2,37.5
union all select 1,2010,3,8.0

union all select 1,2009,1,72.8
union all select 1,2009,2,21.0
union all select 1,2009,3,6.2

union all select 2,2010,1,61.0
union all select 2,2010,2,36.0
union all select 2,2010,3,3.0

Результат должен быть примерно таким:

TYPE_MATERIAL YEARS       ROW_NUM     PERCENTUAL_PRICE    PERCENTUAL_PRICE_cumulative
1             2010        1           54.50               54.5 (=54.5)
1             2010        2           37.50               92.0 (=54.5+37.5)
1             2010        3           8.00                100.0 (=54.5+37.5+8)
1             2009        1           72.80               72.8
1             2009        2           21.00               93.8
1             2009        3           6.20                100.0
2             2010        1           61.00               61.0
2             2010        2           36.00               97.0
2             2010        3           3.00                100.0

I found this query on internet, but it's not good for this case because it calculate the cumulative percentage only if I have one TYPE_MATERIAL and one YEARS

select TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE,
PERCENTUAL_PRICE_cumulative=
(
select SUM(PERCENTUAL_PRICE) from @mytable b
where b.ROW_NUM<=a.ROW_NUM
)
from @mytable a

Какие-либо предложения? СПАСИБО

Источник

2 ответа

Решение
select TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE,
PERCENTUAL_PRICE_cumulative=
(
select SUM(PERCENTUAL_PRICE) from @mytable b
where b.ROW_NUM<=a.ROW_NUM AND b.YEARS = a.YEARS AND b.TYPE_MATERIAL = a.TYPE_MATERIAL
)
from @mytable a
Источник

Ваша проблема вызвана тем, что ваш внутренний запрос не ограничивается type_material и годами.

select TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE,
PERCENTUAL_PRICE_cumulative=
(
select SUM(PERCENTUAL_PRICE) from @mytable b
where b.ROW_NUM<=a.ROW_NUM AND b.type_material = a.type_material AND b.years = a.years
)
from @mytable a

Вы также можете сделать это с помощью рекурсивного CTE, который хорошо работает для очень больших данных.

with myCte (TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE, PERCENTUAL_PRICE_cumulative)
as
(
    select TYPE_MATERIAL, YEARS, ROW_NUM, PERCENTUAL_PRICE, PERCENTUAL_PRICE AS PERCENTUAL_PRICE_cumulative
    FROM @mytable
    WHERE row_num = 1
    UNION ALL
    select t.TYPE_MATERIAL, t.YEARS, t.ROW_NUM, t.PERCENTUAL_PRICE, CAST(t.PERCENTUAL_PRICE + myCte.PERCENTUAL_PRICE_cumulative AS  numeric(6,2)) AS PERCENTUAL_PRICE_cumulative
    FROM @mytable t
        INNER JOIN myCte ON myCte.type_material = t.type_material AND myCte.years = t.years AND mycte.row_num = t.row_num-1
    WHERE t.row_num > 1
)
SELECT * FROM myCte
ORDER BY  TYPE_MATERIAL, YEARS, ROW_NUM
Источник

Это должно быть решено с помощью оконных функций, а не рекурсии или подзапросов:

SELECT
  type_material, years, row_num, percentual_price
  SUM (percentual_price) OVER (PARTITION BY type_material, years ORDER BY row_num) cumulative
FROM @mytable
Источник
Другие вопросы по тегам