خيارات GROUP BY في Synapse SQL

يسمح Synapse SQL بتطوير الحلول من خلال تنفيذ خيارات GROUP BY المختلفة.

ما تقوم به GROUP BY

تقوم عبارة GROUP BY T-SQL بتجميع البيانات إلى مجموعة ملخصة من الصفوف.

تجمع SQL بلا خادم لا يدعم خيارات GROUP BY. تجمع SQL المخصص يدعم عددًا محدودًا من خيارات GROUP BY.

خيارات GROUP BY المدعومة في تجمع 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;

الصورة التالية تعرض نتائج CREATE TABLE AS SELECT:

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

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
;

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

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

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