تجميع حسب الخيارات لتجمعات SQL المخصصة في تحليلات Azure Synapse

في هذه المقالة، ستجد تلميحات لتنفيذ المجموعة حسب الخيارات في تجمعات SQL المخصصة.

ماذا تفعل GROUP BY؟

تجمع عبارة GROUP BY T-SQL البيانات إلى مجموعة ملخصة من الصفوف. لدى GROUP BY بعض الخيارات التي لا يدعمها تجمع SQL المخصص. تحتوي هذه الخيارات على حلول بديلة، وهي كما يلي:

• GROUP BY باستخدام ROLLUP
• GROUPING SETS
• GROUP BY باستخدام CUBE

خيارات مجموعات التجميع والمجموعة

أبسط خيار هنا هو استخدام UNION ALL لتنفيذ مجموعة التحديثات بدلاً من الاعتماد على بناء الجملة الصريح. والنتيجة هي نفسها تمامًا.

المثال التالي باستخدام عبارة GROUP BY مع خيار ROLLUP:

SELECT [SalesTerritoryCountry]
,      [SalesTerritoryRegion]
,      SUM(SalesAmount)             AS TotalSalesAmount
FROM  dbo.factInternetSales s
JOIN  dbo.DimSalesTerritory t       ON s.SalesTerritoryKey       = t.SalesTerritoryKey
GROUP BY ROLLUP (
                        [SalesTerritoryCountry]
                ,       [SalesTerritoryRegion]
                )
;

باستخدام ROLLUP، يطلب المثال السابق التجميعات التالية:

• البلد والمنطقة
• الدولة
• المجموع الكلي

لاستبدال ROLLUP وإرجاع نفس النتائج، يمكنك استخدام UNION ALL وتحديد التجميعات المطلوبة بشكل صريح:

SELECT [SalesTerritoryCountry]
,      [SalesTerritoryRegion]
,      SUM(SalesAmount) AS TotalSalesAmount
FROM  dbo.factInternetSales s
JOIN  dbo.DimSalesTerritory t     ON s.SalesTerritoryKey       = t.SalesTerritoryKey
GROUP BY
       [SalesTerritoryCountry]
,      [SalesTerritoryRegion]
UNION ALL
SELECT [SalesTerritoryCountry]
,      NULL
,      SUM(SalesAmount) AS TotalSalesAmount
FROM  dbo.factInternetSales s
JOIN  dbo.DimSalesTerritory t     ON s.SalesTerritoryKey       = t.SalesTerritoryKey
GROUP BY
       [SalesTerritoryCountry]
UNION ALL
SELECT NULL
,      NULL
,      SUM(SalesAmount) AS TotalSalesAmount
FROM  dbo.factInternetSales s
JOIN  dbo.DimSalesTerritory t     ON s.SalesTerritoryKey       = t.SalesTerritoryKey;

لاستبدال GROUPING SETS، ينطبق مبدأ النموذج. تحتاج فقط إلى إنشاء مقاطع UNION ALL لمستويات التجميع التي تريد رؤيتها.

خيارات المكعب

من الممكن إنشاء GROUP BY WITH CUBE باستخدام نهج UNION ALL. المشكلة هي أن التعليمات البرمجية يمكن أن تصبح مرهقة وغير عملية بسرعة. للتخفيف من هذه المشكلة، يمكنك استخدام هذا النهج الأكثر تقدمًا.

باستخدام المثال السابق، الخطوة الأولى هي تعريف "المكعب" الذي يحدد جميع مستويات التجميع التي نريد إنشاءها.

لاحظ CROSS JOIN للجدولين المشتقين لأن هذا ينشئ جميع المستويات بالنسبة إلينا. بقية التعليمات البرمجية موجودة للتنسيق:

CREATE TABLE #Cube
WITH
(   DISTRIBUTION = ROUND_ROBIN
,   LOCATION = USER_DB
)
AS
WITH GrpCube AS
(SELECT    CAST(ISNULL(Country,'NULL')+','+ISNULL(Region,'NULL') AS NVARCHAR(50)) as 'Cols'
,          CAST(ISNULL(Country+',','')+ISNULL(Region,'') AS NVARCHAR(50))  as 'GroupBy'
,          ROW_NUMBER() OVER (ORDER BY Country) as 'Seq'
FROM       ( SELECT 'SalesTerritoryCountry' as Country
             UNION ALL
             SELECT NULL
           ) c
CROSS JOIN ( SELECT 'SalesTerritoryRegion' as Region
             UNION ALL
             SELECT NULL
           ) r
)
SELECT Cols
,      CASE WHEN SUBSTRING(GroupBy,LEN(GroupBy),1) = ','
            THEN SUBSTRING(GroupBy,1,LEN(GroupBy)-1)
            ELSE GroupBy
       END AS GroupBy  --Remove Trailing Comma
,Seq
FROM GrpCube;

تعرض الصورة التالية نتائج CTAS:

الخطوة الثانية هي تحديد جدول هدف لتخزين النتائج المؤقتة:

DECLARE
 @SQL NVARCHAR(4000)
,@Columns NVARCHAR(4000)
,@GroupBy NVARCHAR(4000)
,@i INT = 1
,@nbr INT = 0
;
CREATE TABLE #Results
(
 [SalesTerritoryCountry] NVARCHAR(50)
,[SalesTerritoryRegion]  NVARCHAR(50)
,[TotalSalesAmount]      MONEY
)
WITH
(   DISTRIBUTION = ROUND_ROBIN
,   LOCATION = USER_DB
)
;

الخطوة الثالثة هي التكرار الحلقي فوق مكعب الأعمدة الذي ينفذ التجميع. سيتم تشغيل الاستعلام مرة واحدة لكل صف في الجدول المؤقت #Cube. يتم تخزين النتائج في الجدول المؤقت #Results:

SET @nbr =(SELECT MAX(Seq) FROM #Cube);

WHILE @i<=@nbr
BEGIN
    SET @Columns = (SELECT Cols    FROM #Cube where seq = @i);
    SET @GroupBy = (SELECT GroupBy FROM #Cube where seq = @i);

    SET @SQL ='INSERT INTO #Results
              SELECT '+@Columns+'
              ,      SUM(SalesAmount) AS TotalSalesAmount
              FROM  dbo.factInternetSales s
              JOIN  dbo.DimSalesTerritory t  
              ON s.SalesTerritoryKey = t.SalesTerritoryKey
              '+CASE WHEN @GroupBy <>''
                     THEN 'GROUP BY '+@GroupBy ELSE '' END

    EXEC sp_executesql @SQL;
    SET @i +=1;
END

وأخيرًا، يمكنك إرجاع النتائج عن طريق القراءة من الجدول المؤقت #Results:

SELECT *
FROM #Results
ORDER BY 1,2,3
;

من خلال تقسيم التعليمات البرمجية إلى أقسام وإنشاء بنية تكرار حلقي، تصبح التعليمات البرمجية أكثر قابلية للإدارة والصيانة.

الخطوات التالية

لمزيد من نصائح التطوير، راجع نظرة عامة على التطوير.