Вам поручают создать запрос в SQL Server, содержащий нарастающие итоги на момент наступления события. Классическим примером является счет в банке.
Для каждого заданного счета вы суммируете дебиты (депозиты) и кредиты (снятие средств) на данный момент времени. После каждой транзакции Вы хотите знать текущий баланс. В листинге А приводится простой пример создания такой таблицы.
Листинг А
CREATE TABLE [dbo].[BankAccount](
[TransactionID] [int] IDENTITY(1,1) NOT NULL,
[TransactionDateTime] [datetime] NOT NULL CONSTRAINT [DF_BankAccount_TransactionDateTime] DEFAULT(getdate()),
[Amount] [money] NOT NULL CONSTRAINT [DF_BankAccount_Amount] DEFAULT((0)),
[TransactionType] [char](1)COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AccountNumber] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_BankAccount] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF) ON [PRIMARY]
) ON [PRIMARY]
Вот типичные строки:
1 2006-11-03 02:33:42.340 10000.00
2 2006-11-03 02:34:50.467 -500.00
3 2006-11-03 02:35:04.857 250.00
4 2006-11-03 02:42:19.763 -124.25
Так как дата имеет значение по умолчанию, все, что Вы должны сделать, - это только добавить несколько сумм. Для простоты в примере приводится информация только об одном банковском счете.
Теперь Вы можете создать запрос, который содержит текущий баланс. Так как Вы делаете запись депозитов и изъятий в одном и том же столбце, используя положительные и отрицательные значения, достаточно использовать сумму. Чтобы получить текущий баланс, Вы суммируете все предыдущие транзакции и добавляете полученную сумму к величине текущей транзакции.
Следующий запрос реализует этот алгоритм:
SELECT transactionid, transactiondatetime, amount,
(SELECT SUM(amount)
FROM dbo.bankaccount as D1
WHERE D1.transactiondatetime <= D0.transactiondatetime) AS balance
FROM dbo.bankaccount AS D0
В результате получим следующий набор строк:
1 2006-11-03 02:33:42.340 10000.00 10000.00
2 2006-11-03 02:34:50.467 -500.00 9500.00
3 2006-11-03 02:35:04.857 250.00 9750.00
4 2006-11-03 02:42:19.763 -124.25 9625.75
Как показывает этот пример, нарастающие итоги легко получить, если вы понимаете, что от вас требуется. В данном примере предполагается, что таблица содержит только один счет, но легко обобщить его на случай со многими счетами: Вам нужно лишь добавить в таблицу еще столбец с номером счета - BankAccountNumber, а в запросе в предикате предложения WHERE указать нужный счет.
Вы можете также преобразовать этот пример, чтобы получить нарастающие разности (как в инвентарных счетах). Вы начинаете с описи 1 000, и затем вычитаете различные расходы и поступления.
Такие запросы имеют два важных преимущества:
- Вам не потребуется хранить результаты. При отборе по номеру счета или подобного ему внешнему ключу запрос будет выполняться очень быстро.
- Упрощается работа с журналом транзакций, который может просматриваться построчно. Если возникает ошибка, то Вы будете в состоянии изолировать конкретную транзакцию, которая ее вызвала.
Arthur Fuller (оригинал: Running totals in SQL Server queries)
Перевод: Моисеенко С.И.
Оригинал перевода