SQL Добавить столбец текущей суммы для каждого элемента в гигантской таблице

Я хочу рассчитать промежуточный итог на основе 5 типов транзакций (скажем, транзакции A, B, C, D, E). Но у меня в этой таблице более тысячи различных продуктов, и каждый продукт может иметь миллионы записей транзакций в разные дни.

Итак, таблица выглядит так:

ProductID   A   B   C   D   E   Running Total

   1       10   0   5   0   5       20
   2       15   0   0   0   0       15
   3       20   5   0   10  0       35
   1       10   0   0   0   0       30 (20 for product 1, plus 10 for product 1 again)
   3       12   0   33  0   0       80 (35 for product 3, plus 45 for product 3 again)

sql cumulative-sum
person user3775316    schedule 04.09.2014    source источник
comment
Что вы пытаетесь достичь? Вам нужно рассчитать промежуточный итог при вставке новой записи? Вы контролируете код? Вы должны реализовать в SQL? Можете ли вы использовать триггер или полевую процедуру?   -  person KingOfAllTrades    schedule 05.09.2014
comment
Какая СУБД? MySQL? СЕРВЕР SQL? оракул? Синтаксис для промежуточных сумм различается. Независимо от того, какой из них, я готов поспорить, что могу найти на этом сайте общий пример для вашей СУБД... наборы окон, использующие синтаксис over, скорее всего, вам нужны в Oracle, DB2, SQL-сервере, пользовательские переменные в mySQL.   -  person xQbert    schedule 05.09.2014
comment
Привет, ребята, большое спасибо за ваши комментарии. Я использую SQL Server 2008 и несколько лет назад пытаюсь подсчитать промежуточную сумму количества каждого продукта. Исходя из нарастающей суммы, я мог получить количество в наличии на нашем складе и контролировать все визуально. Я надеюсь, что это сделает проблему более ясной. Еще раз спасибо, я ценю время каждого и доброту.   -  person user3775316    schedule 05.09.2014

Ответы (2)


arrow_upward
1
arrow_downward

Стандартный метод ANSI заключается в использовании sum() в качестве оконной функции:

select t.*,
       sum(a + b + c + d + e) over (partition by productid order by <datetimecol>) as RunningTotal
from table t;

Таблицы SQL представляют неупорядоченные наборы, поэтому вам нужен столбец, определяющий порядок. Я предполагаю, что для этой цели где-то есть столбец даты/времени.

Большинство баз данных поддерживают этот стандартный синтаксис: Oracle, SQL Server 2012+, Postgres, Teradata и DB2.

person Gordon Linoff    schedule 04.09.2014

arrow_upward
0
arrow_downward

Тестовые данные

DECLARE @TABLE TABLE (ProductID INT, A INT,  B INT,  C INT,  D INT,  E INT)  
INSERT INTO @TABLE VALUES
(1 ,10,   0,   5 ,  0 ,  5),  --     20
(2 ,15,   0,   0 ,  0 ,  0),  --     15
(3 ,20,   5,   0 ,  10,  0),  --     35
(1 ,10,   0,   0 ,  0 ,  0),  --     30 (20 for product 1, plus 10 for product 1 again)
(3 ,12,   0,   33,  0 ,  0)   --    80

Запрос

;WITH CTE AS
(
    select *
          ,ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductID ASC) rn 
    from @TABLE  
)
SELECT ProductID 
      ,A 
      ,B
      ,C
      ,D
      ,E
      ,runningTotal
FROM CTE c
 cross apply (select sum(A+B+C+D+E) as runningTotal
                from CTE
                where rn <= c.rn
                  and ProductID = c.ProductID
              ) as rt

Результат

╔═══════════╦════╦═══╦════╦════╦═══╦══════════════╗
║ ProductID ║ A  ║ B ║ C  ║ D  ║ E ║ runningTotal ║
╠═══════════╬════╬═══╬════╬════╬═══╬══════════════╣
║         1 ║ 10 ║ 0 ║  5 ║  0 ║ 5 ║           20 ║
║         1 ║ 10 ║ 0 ║  0 ║  0 ║ 0 ║           30 ║
║         2 ║ 15 ║ 0 ║  0 ║  0 ║ 0 ║           15 ║
║         3 ║ 20 ║ 5 ║  0 ║ 10 ║ 0 ║           35 ║
║         3 ║ 12 ║ 0 ║ 33 ║  0 ║ 0 ║           80 ║
╚═══════════╩════╩═══╩════╩════╩═══╩══════════════╝
person M.Ali    schedule 04.09.2014