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

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

به اشتراک بگذاریم برای یادگیری، یاد بگیریم برای به اشتراک گذاری
هوش تجاری (Business Intelligence)

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

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

Running Total

مساله:

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


01

تصویر 1


راه حل:

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

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


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

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


تصویر 2



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


  1. USE    Test;--DataBase Name
  3. IF      OBJECT_ID('dbo.TransactTable', 'U') IS NOT NULL DROP TABLE dbo.TransactTable;
  5. CREATE TABLE  dbo.TransactTable
  6. (
  7.    UserID INT NOT NULL
  8. ,  TransactionID INT NOT NULL
  9. ,  Quantity INT NULL
  10. ,  CONSTRAINT PK_TransactTable PRIMARY KEY CLUSTERED
  11.          (
  12.       UserID ASC
  13.    ,  TransactionID ASC
  14.    )
  15. );
  17. INSERT INTO dbo.TransactTable (UserID, TransactionID, Quantity) VALUES
  18.               (1, 1, 50),( 2, -7),(1, 3, 10),(1, 4, 3), (1, 5, -2),(1, 6, 3),(1, 7, -1)
  19.      ,(1, 8, 17),(1, 9, -6),(1, 10, 1),(2, 1, 10),(2, 2, 5),(2, 3, 4),(2, 4, 7),
  20.      (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 نمایش می دهد. این عملیات به ازای هر یک از رکوردهای کوئری بیرونی، یک بار انجام می شود.

  1. SELECT     UserID
  2. ,          TransactionID
  3. ,          Quantity
  4. ,          (
  5.               SELECT     SUM(T2.Quantity)
  6.               FROM       dbo.TransactTable    AS     T2
  7.               WHERE      T2.TransactionID     =      T1.TransactionID
  8.                          AND
  9.                          T2.TransactionID     <=     T1.TransactionID
  10.            )  AS         Balance
  11. 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) می شود.


  1. SELECT        T1.UserID
  2. ,             T1.TransactionID
  3. ,             T1.Quantity
  4. ,             SUM(T2.Quantity)     AS     Balance
  5. FROM          dbo.TransactTable    AS     T1
  6. INNER JOIN    dbo.TransactTable    AS     T2
  7.         ON    T2.UserID            =      T1.UserID
  8.               AND
  9.               T2.TransactionID     <=     T1.TransactionID
  10. GROUP BY      T1.UserID
  11. ,             T1.TransactionID
  12. ,             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 است، انجام می شود.

  1. SELECT     UserID
  2. ,          TransactionID
  3. ,          Quantity
  4. ,          SUM(Quantity) OVER(PARTITION BY UserID ORDER BY TransactionID
  5.               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)             AS     Balance
  6. FROM       dbo.TransactTable ;


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

 

 

مقایسه Execution Planها:

در جدولی که کوئری های فوق بر روی آن اجرا شده است، ستون های UserID و TransactionID کلید اصلی می باشند.


 

لازم به ذکر است که هزینه اجرای کوئری های فوق، می تواند با افزایش تعداد تراکنش ها نسبت به تعداد کاربران و یا بالعکس، متغیر باشد.

 





منبع: برگرفته از کتاب Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions نوشته Itzik Ben-Gan

هوش تجاری تحلیل داده انبار داده ETL هوشمندی کسب و کار SQL OLAP داده کاوی پالایش داده ها Running Total چیست
علی سیدزاده سه‌شنبه 10 بهمن 1396 ساعت 03:41 ب.ظ
0 نظر