Версия для печати

На главную страницу

Нарастающие итоги в запросах SQL Server

Вам поручают создать запрос в 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

На главную страницу

  Версия для печати