مثال های گزارشات پیشرفته در SQL Server با استفاده از Grouping Sets و Pivot

بدون ابزارهای گزارش دهی جامع (یا اکسل)، تولید خروجی گزارش کامل با دستور SELECT یا Stored Procedureها می تواند دست و پا گیر و خسته کننده باشد. اما ما به عملکرد T-SQL دسترسی داریم که بسیار فراتر از ROLLUP و CUBE است، مانند PIVOT، UNPIVOT، و GROUPING SET. بیایید نگاهی به نحوه تولید خروجی بیندازیم که می‌توانیم به راحتی گزارش‌هایی با ظاهری عالی تولید کنیم.

فرض کنید اطلاعات مربوط به مراکز داده و سرورهای خود را ذخیره می‌کنید، و یک جدول مرکزی دارید که استثناها و رویدادهای مربوط به هر سرور را ذخیره می‌کند (برای اختصار، من نمی‌خواهم روابط را در اینجا اعمال کنم، خودتان بسته به کسب و کار خود در مورد نام‌گذاری و اعمال محدودیت‌ها یا پشتیبانی از یونیکد یا اضافه کردن ستون‌های دیگر اقدام نمایید.):

— ایجاد جدول دیتاسنتر

CREATE TABLE dbo.DataCenters

)

    DataCenterID int PRIMARY KEY,

    Name         varchar(32) NOT NULL UNIQUE

;(

INSERT dbo.DataCenters(DataCenterID, Name) VALUES

    (1,’Tatooine’), (2,’Endor’);

— ایجاد جدول سرورها

CREATE TABLE dbo.Servers

)

    ServerID     int IDENTITY(1,1) PRIMARY KEY,

    Name         varchar(16) NOT NULL UNIQUE,

    DataCenterID int NOT NULL

;(

INSERT dbo.Servers(Name, DataCenterID) VALUES

    (‘R2-D2’,1), (‘C3-P0’,1), (‘BB-8’,1), (‘K2-SO’,1),

    (‘Luke’,2),  (‘Leia’,2),  (‘Han’,2),  (‘Chewie’,2);

— رویدادها و استثنائات در این جدول ذحیره میشوند

CREATE TABLE dbo.ServerExceptions

)

    EventTimeUTC datetime2(3) NOT NULL DEFAULT sysutcdatetime(),

    ServerID     int NOT NULL,

    ErrorDetails varchar(256) NULL,

    INDEX CIX_ServerExceptions CLUSTERED(EventTimeUTC, ServerID)

;(

و فرض کنید شما گزارشی می‌خواهید که نشان دهد چند استثنا از هر سرور در سال 2022 آمده است. بخش مورد علاقه من از این نمونه‌ها ارائه یک کوئری است که مجموعه‌ای از داده‌های مرتبط و تصادفی را در یک مرحله تولید می‌کند:

) WITH n(n) AS;

  SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY @@SPID)    FROM sys.all_columns),

s AS (   SELECT ServerID, DataCenterID,       NumRows = ABS(CHECKSUM(NEWID())) % 1000,      MinuteDiff = ABS(CHECKSUM(NEWID())) % 1000 FROM dbo.Servers)

INSERT dbo.ServerExceptions(EventTimeUTC, ServerID)

   SELECT DATEADD(MINUTE, n.n * MinuteDiff, ‘20220101’), ServerID   FROM s   INNER JOIN n

ON n.n >= s.NumRows

   ‘WHERE DATEADD(MINUTE, n.n * MinuteDiff, ‘20220101’) < ‘20230101

به عنوان یک نکته جانبی جالب، اگر دقت کنید، خواهید دید که من چگونه استثنائات را در ابتدای سال وزن کردم، به این معنی که با گذشت سال، در پیشگیری یا حل و فصل استثناها رویدادها بهتر میشوید.

با داده‌های موجود، فرض کنید می‌خواهید گزارشی شبیه این تهیه کنید، مجموع برای هر دیتاسنتر Roll بشود، و هیچ نامی از دیتاسنتر نیز تکرار نشود:

من توضیح خواهم داد که چگونه می توانید از یک پرس و جوی aggregation ساده به یک پرس و جوی PIVOT پیشرفت کنید که خروجی یکسان را برگرداند:

ابتدا از طریق استفاده از aggregate fumction را با هم میبینیم:

SELECT ServerID,    [Month] = DATEPART(MONTH, EventTimeUTC),    ExceptionCount = COUNT(*)FROM dbo.ServerExceptions

‘WHERE EventTimeUTC >= ‘20210101’  AND EventTimeUTC <  ‘20220101

GROUP BY ServerID, DATEPART(MONTH, EventTimeUTC)

ORDER BY ServerID, [Month];

بخشی از نتیجه:

در مرحله بعد، می‌خواهید Grouping Sets را استفاده کنید، زیرا این به شما امکان می‌دهند تا ردیف‌های هدر/مجموع کل، تولید کنید. برای ساده‌سازی عبارات تکراری، کوئری aggregate شده را درون یک جدول CTE یا derived table قرار دهید:

) WITH src AS  ; 

SELECT ServerID,           [Month] = DATEPART(MONTH, EventTimeUTC),            ExceptionCount = COUNT(*)    FROM dbo.ServerExceptions

   ‘ WHERE EventTimeUTC >= ‘20210101’      AND EventTimeUTC <  ‘20220101   

GROUP BY ServerID, DATEPART(MONTH, EventTimeUTC))SELECT ServerID,       [Month],         ExceptionCount = SUM(ExceptionCount) FROM src  

GROUP BY GROUPING SETS((ServerID, [Month]), ([Month]))

ORDER BY ServerID, [Month];

این 12 ردیف خروجی اضافی تولید می کند. یکی برای هر ماه و یکی برای هر سرور، بنابراین serverID، NULL است. بخشی از نتیجه:

در مرحله بعد، می‌توانید نام‌های Server و DataCenter را با تودرتو کردن یک CTE اضافی که نام‌ها را از جداول مرتبط بیرون می‌آورد، و افزودن DataCenter به ابتدای هر grouping Set با این کوئری به خروجی اضافه کنید:

WITH src AS; 
)
    ,SELECT ServerID
           , Month]=DATEPART(MONTH, EventTimeUTC)]
           (*)ExceptionCount = COUNT
    FROM dbo.ServerExceptions
    'WHERE EventTimeUTC >= '20210101
      'AND EventTimeUTC <  '20220101
    GROUP BY ServerID, DATEPART(MONTH, EventTimeUTC)
,(
srv AS
)
    ,SELECT DataCenter = dc.Name
           ,[Server]   = s.Name 
           ,src.[Month] 
           src.ExceptionCount
    FROM src 
    INNER JOIN dbo.Servers AS s 
            ON src.ServerID = s.ServerID
    INNER JOIN dbo.DataCenters AS dc
            ON s.DataCenterID = dc.DataCenterID
(
,SELECT DataCenter         = COALESCE(DataCenter, '{All}')
, Server]=OALESCE([Server],   '{All}')]
       ,IsDataCenterHeader = GROUPING(DataCenter)
       ,IsServerHeader     = GROUPING([Server])
      , [Month] 
       ExceptionCount     = SUM(ExceptionCount)
 FROM srv
 GROUP BY GROUPING SETS
)
    ,(DataCenter, [Server], [Month])
    ,(DataCenter, [Month])
    ([Month])
(
; ORDER BY DataCenter, [Server], [Month]

همچنین توجه داشته باشید که چگونه از تابع GROUPING برای تعیین اینکه آیا ردیف واقعاً یک ردیف “هدر” است که همه فرزندان خود را جمع می کند یا خیر، استفاده می کنید. بخشی از نتیجه:

در مرحله بعد، می‌توانید پس از اضافه کردن CTE دیگر، یک PIVOT روی این خروجی اعمال کنید (می‌توانید این کار را بدون CTE انجام دهید، اما گام برداشتن و بررسی نتایج میانی بسیار آسان‌تر خواهد بود):

WITH src AS; 
)
    ,SELECT ServerID
         ,Mount]=DATEPART(MONTH, EventTimeUTC)]
       ( * )ExceptionCount = COUNT
    FROM dbo.ServerExceptions
    'WHERE EventTimeUTC >= '20210101
      'AND EventTimeUTC <  '20220101
    GROUP BY ServerID, DATEPART(MONTH, EventTimeUTC)
,(
srv AS
)
   , SELECT DataCenter = dc.Name
          , [Server]   = s.Name 
         ,  src.[Month]
           src.ExceptionCount
    FROM src 
    INNER JOIN dbo.Servers AS s 
            ON src.ServerID = s.ServerID
    INNER JOIN dbo.DataCenters AS dc
            ON s.DataCenterID = dc.DataCenterID
,(
agg AS
)
    SELECT DataCenterName    = COALESCE(DataCenter, '{All}'),
          ,Server]=COALESCE([Server],   '{All}')]
          , IsDataCenterHeader = GROUPING(DataCenter)
          , IsServerHeader     = GROUPING([Server]) 
           ,[Month] 
           ExceptionCount     = SUM(ExceptionCount)
    FROM srv
    GROUP BY GROUPING SETS
    )
       ,(DataCenter, [Server], [Month])
      , (DataCenter, [Month])
       ([Month])
   (
(
SELECT * FROM agg 
PIVOT
)
    MAX(ExceptionCount) FOR [Month] IN 
   )
       [1],[2],[3],[4], [5], [6],
       [7],[8],[9],[10],[11],[12]
   (
 AS p(
,ORDER BY IsDataCenterHeader DESC
        , DataCenterName 
         ,IsServerHeader DESC
         ;[Server]

این خروجی مانند زیر تولید می کند:

نتایج کامل برای اولین پرس و جو PIVOT

با این حال، استفاده از SELECT * از مطابقت خروجی با خروجی ایده‌آل ما جلوگیری می‌کند (اما من همیشه در حین ساخت پرس و جو، این روش را با PIVOT شروع می‌کنم). ما باید برخی از مقادیر DataCenterName را برای رشته های خالی تنظیم کنیم، ستون های IsHeader را حذف کنیم و هدرهای شماره ماه را به اختصارات نام ماه تبدیل کنیم.

برای رفع این موارد، فقط کافیست تنظیماتی را در کوئری SELECT نهایی انجام دهید:

…
SELECT DataCenter = CASE WHEN IsDataCenterHeader + IsServerHeader > 0
                         THEN DataCenterName ELSE '' END, 
       [Server], 
       Jan = [1], Feb = [2],  Mar = [3],  Apr   = [4], 
       May = [5], Jun = [6],  Jul = [7],  Aug   = [8],
       Sep = [9], Oct = [10], Nov = [11], [Dec] = [12]
FROM agg 
PIVOT
)
   MAX(ExceptionCount) FOR [Month] IN 
   )
      [1],[2],[3],[4], [5], [6],
    (  [7],[8],[9],[10],[11],[12]
 AS p(
,ORDER BY IsDataCenterHeader DESC
        , DataCenterName
         ,IsServerHeader DESC
      ; [Server]

حالا خوب بنظر می رسد:

نتایج نهایی

البته می توانید نتیجه را متفاوت کنید، اگر می خواهید مجموع ها پایین بیایند، فقط ORDER BY نهایی را تغییر دهید تا DESC ها حذف شوند:

…

ORDER BY IsDataCenterHeader,   DataCenterName,   IsServerHeader,     [Server];

که حالا نتیجه این چنین خواهد بود: