Наростаючі підсумки в запитах 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 вказати потрібний рахунок.


Ви можете також перетворити цей приклад, щоб отримати наростаючі різниці (як в інвентарних рахунках). Ви починаєте з опису 1000, і потім віднімаєте різні витрати і надходження.


Такі запити мають дві важливі переваги:


Arthur Fuller (Оригінал: Running totals in SQL Server queries)
Переклад: Моісеєнко С.І.
Оригінал перекладу


Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*