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

تلميح

Microsoft Fabric Data Warehouse هو مستودع علائقي على نطاق مؤسسي قائم على أساس بحيرة البيانات، مع بنية جاهزة للمستقبل، وذكاء اصطناعي مدمج، وميزات جديدة. إذا كنت جديدا في مستودع البيانات، ابدأ ب Fabric Data Warehouse. يمكن لأحمال عمل تجمع SQL المخصصة الحالية الترقية إلى Fabric للوصول إلى قدرات جديدة في علوم البيانات، والتحليلات اللحظية، والتقارير.

• ابدأ تجربة مجانية Fabric.
• Migration Assistant ل Fabric Data Warehouse.

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

ماذا تفعل GROUP BY؟

جملة GROUP BY T-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;

تظهر الصورة التالية نتائج 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
;

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

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

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