خيارات 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 pool المخصص. هذه الخيارات لها حلول بديلة، وهي كما يلي:

• المجموعة مع رول أب
• مجموعات التجميع
• المجموعة مع المكعب

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

أبسط خيار هنا هو استخدام 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، يطلب المثال السابق التجميعات التالية:

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

لاستبدال 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;

لاستبدال مجموعات التجميع، ينطبق مبدأ العينة. كل ما تحتاجه هو إنشاء جميع أقسام الاتحاد لمستويات التجميع التي تريد رؤيتها.

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

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

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

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;

تظهر الصورة التالية نتائج إنشاء جدول كاختيار:

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

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
;

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

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

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