На главную страницу
Нарастающие итоги в запросах SQL Server
Arthur Fuller (оригинал:
Running totals in SQL Server queries)
Перевод Моисеенко С.И.
Вам поручают создать запрос в 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, и затем вычитаете различные расходы
и поступления.
Такие запросы имеют два важных преимущества:
· Вам не потребуется хранить результаты. При отборе по номеру счета или подобного
ему внешнему ключу запрос будет выполняться очень быстро.
· Упрощается работа с журналом транзакций, который может просматриваться
построчно. Если возникает ошибка, то Вы будете в состоянии изолировать конкретную
транзакцию, которая ее вызвала.
12-02-07
На главную страницу