هوش تجاری (Business Intelligence)

به اشتراک بگذاریم برای یادگیری، یاد بگیریم برای به اشتراک گذاری

هوش تجاری (Business Intelligence)

به اشتراک بگذاریم برای یادگیری، یاد بگیریم برای به اشتراک گذاری

Running Total

مساله:

جدولی در یک دیتابیس بانکی وجود دارد که اطلاعات تراکنش های مشتریان بانک در آن ذخیره می شود. به گزارشی نیاز داریم که لیست تراکنش های مشتریان به همراه موجودی حساب مشتری را پس از هر تراکنش نمایش دهد.

تصویر 1

راه حل:

تکنیکی به نام Running Total وجود دارد که از آن برای محاسبه سرجمع مقادیر یک ستون از اولین سطر تا سطر جاری استفاده می شود.

استفاده از این تکنیک یکی از بهترین راه کارهای سیستم های مالی، انبارداری و ... می باشد. روش های متفاوتی برای استفاده از تکنیک Running Total وجود دارد که در این مقاله با روش های Sub Query، Join و Window Function به صورت Set Based بررسی می شود.

کوئری های این تمرین برروی جدولی شامل تراکنش های بانکی انجام میشود.

ستون UserID شناسه کاربری، ستون TransactionID شناسه تراکنش و ستونQuantity مبلغ تراکنش انجام شده را در خود ذخیره می کنند. این جدول دارای اطلاعات تراکنش های دو کاربر با شناسه های 1 و 2 است که هر کدام تعداد 10 تراکنش داشته اند.

تصویر 2

دیتابیسی با نام Test (یا هر اسم دلخواه دیگر) ساخته و کد زیر را اجرا کرده تا جدول مورد نظر ساخته شود.

USE Test;--DataBase Name
IF OBJECT_ID('dbo.TransactTable', 'U') IS NOT NULL DROP TABLE dbo.TransactTable;
CREATE TABLE dbo.TransactTable
(
UserID INT NOT NULL
, TransactionID INT NOT NULL
, Quantity INT NULL
, CONSTRAINT PK_TransactTable PRIMARY KEY CLUSTERED
(
UserID ASC
, TransactionID ASC
)
);
INSERT INTO dbo.TransactTable (UserID, TransactionID, Quantity) VALUES
(1, 1, 50),( 2, -7),(1, 3, 10),(1, 4, 3), (1, 5, -2),(1, 6, 3),(1, 7, -1)
,(1, 8, 17),(1, 9, -6),(1, 10, 1),(2, 1, 10),(2, 2, 5),(2, 3, 4),(2, 4, 7),
(2, 5, -9),(2, 6, 90),(2, 7, -10),(2, 8, -5),(2, 9, -7),(2, 10, -50);

کد ساخت جدول

Sub Query:

در این روش کوئری درونی(کوئریی که خروجی آن بوسیله کوئری دیگر استفاده می شود) UserIDهای برابر با UserIDهای کوئری بیرونی(کوئریی که از خروجی کوئری درونی استفاده می کند) و TransactionIDهای کوچکتر از TransactionIDهای کوئری بیرونی را فیلتر کرده، سپس مجموع فیلدهای ستون Quantity را محاسبه می کند و در ستونی به نام Balance نمایش می دهد. این عملیات به ازای هر یک از رکوردهای کوئری بیرونی، یک بار انجام می شود.

SELECT UserID
, TransactionID
, Quantity
, (
SELECT SUM(T2.Quantity)
FROM dbo.TransactTable AS T2
WHERE T2.TransactionID = T1.TransactionID
AND
T2.TransactionID <= T1.TransactionID
) AS Balance
FROM dbo.TransactTable AS T1;

کوئری اجرای Running Total به وسیله Sub Query

Join:

انجام Running Total به وسیله Join مانند روش Sub Query می باشد. در این روش جدول TransactTable با خودش Join زده می شود و شرط Join آن UserIDهای برابر و TransactionIDهای بزرگتر مساوی جدول اول از TransactionIDهای جدول دوم می باشد. حاصل جوین جدول TransactTable با خودش بر اساس شرط های گفته شده، به ازای هر UserID و TransactionID جدول اول، TransactionIDهای کوچکتر مساوی آن UserID تکرار می شود. سپس سرجمع ستون Quantity محاسبه شده و بر اساس ستون های UserID و TransactionID دسته بندی(GROUP BY) می شود.

SELECT T1.UserID
, T1.TransactionID
, T1.Quantity
, SUM(T2.Quantity) AS Balance
FROM dbo.TransactTable AS T1
INNER JOIN dbo.TransactTable AS T2
ON T2.UserID = T1.UserID
AND
T2.TransactionID <= T1.TransactionID
GROUP BY T1.UserID
, T1.TransactionID
, T1.Quantity;

کوئری اجرای Running Total به وسیله Join

Window Function:

با آمدن Window Function به SQL، پیاده سازی Running Total بسیار آسان تر و کم هزینه تر شد. در این روش ستون Quantity را در تابع SUM قرار داده و در OVER، پارتیشن(PARTITION BY) را UserID قرار داده و ترتیب(ORDER BY) را بر اساس TransactionID اعمال میکنیم. محاسبه سرجمع، از اولین رکورد (UNBOUNDED PRECEDING) هر پارتیشن تا سطر جاری(CURRENT ROW) که سرجمع در حال درج در ستون Balance است، انجام می شود.