بيانات الاستعلام
بمجرد أن تملأ جداول الأبعاد والحقائق في مستودع البيانات بالبيانات، يمكنك استخدام T-SQL للاستعلام وتحليلها. يدعم SELECTT-SQL دوال ، JOIN، ، دوال تجميع ، دوال النوافذ والمزيد — مما يمنحك الأدوات لاستخراج وتصفية وتجميع وتلخيص بياناتك.
تجميع المقاييس حسب سمات البعد
نمط شائع هو ربط جدول حقائق بجدول أو أكثر من جداول الأبعاد، ثم تجميع مقياس رقمي مجمع بواسطة سمة بعدية.
يجمع الاستعلام التالي مبالغ المبيعات حسب السنة والربع من جداول FactSalesوDimDate :
SELECT dates.CalendarYear,
dates.CalendarQuarter,
SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter
ORDER BY dates.CalendarYear, dates.CalendarQuarter;
النتائج تبدو مشابهة للجدول التالي:
| CalendarYear | CalendarQuarter | إجمالي المبيعات |
|---|---|---|
| 2024 | 1 | 25980.16 |
| 2024 | 2 | 27453.87 |
| 2024 | 3 | 28527.15 |
| 2024 | 4 | 31083.45 |
| 2025 | 1 | 34562.96 |
| 2025 | 2 | 36162.27 |
| ... | ... | ... |
يمكنك ربط جداول متعددة الأبعاد لتقسيم النتائج بسمات إضافية. الاستفسار التالي يوسع المثال السابق لتقسيم المبيعات الفصلية حسب المدينة، باستخدام جدول DimCustomer :
SELECT dates.CalendarYear,
dates.CalendarQuarter,
custs.City,
SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
JOIN dbo.DimCustomer AS custs ON sales.CustomerKey = custs.CustomerKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter, custs.City
ORDER BY dates.CalendarYear, dates.CalendarQuarter, custs.City;
هذه المرة، تتضمن النتائج إجمالي مبيعات ربع سنوي لكل مدينة.
| CalendarYear | CalendarQuarter | المدينة | إجمالي المبيعات |
|---|---|---|---|
| 2024 | 1 | أمستردام | 5982.53 |
| 2024 | 1 | برلين | 2826.98 |
| 2024 | 1 | شيكاغو | 5372.72 |
| ... | ... | ... | .. |
| 2024 | 2 | أمستردام | 7163.93 |
| 2024 | 2 | برلين | 8191.12 |
| 2024 | 2 | شيكاغو | 2428.72 |
| ... | ... | ... | .. |
| 2024 | 3 | أمستردام | 7261.92 |
| 2024 | 3 | برلين | 4202.65 |
| 2024 | 3 | شيكاغو | 2287.87 |
| ... | ... | ... | .. |
| 2024 | 4 | أمستردام | 8262.73 |
| 2024 | 4 | برلين | 5373.61 |
| 2024 | 4 | شيكاغو | 7726.23 |
| ... | ... | ... | .. |
| 2025 | 1 | أمستردام | 7261.28 |
| 2025 | 1 | برلين | 3648.28 |
| 2025 | 1 | شيكاغو | 1027.27 |
| ... | ... | ... | .. |
الانضمام في مخطط بلورة ثلجية
عند استخدام مخطط رقاقات الثلج، قد يتم تطبيع الأبعاد جزئيا، مما يتطلب عدة وصلات لربط جداول الحقائق بأبعاد ندفة الثلج. على سبيل المثال، افترض أن مستودع البيانات يتضمن جدول أبعاد DimProduct تم تسوية فئات المنتج منه في جدول DimCategory منفصل. قد يبدو استعلام تجميع العناصر المباعة حسب فئة المنتج مشابهاً للمثال التالي:
SELECT cat.ProductCategory,
SUM(sales.OrderQuantity) AS ItemsSold
FROM dbo.FactSales AS sales
JOIN dbo.DimProduct AS prod ON sales.ProductKey = prod.ProductKey
JOIN dbo.DimCategory AS cat ON prod.CategoryKey = cat.CategoryKey
GROUP BY cat.ProductCategory
ORDER BY cat.ProductCategory;
تتضمن النتائج من هذا الاستعلام عدد العناصر المباعة لكل فئة منتج:
| ProductCategory | ItemsSold |
|---|---|
| مُكَمِّلات | 28271 |
| الملابس | 5368 |
| ... | ... |
إشعار
كلا JOIN الجملتين مطلوبتان لعبور سلسلة العلاقات من FactSales إلى DimCategory عبر DimProduct، رغم عدم ظهور أي حقول من DimProduct في النتائج.
استخدام دالات الترتيب
استعلام تحليلي شائع آخر يقسم النتائج بناء على سمة بعد ويرتبها داخل كل قسم. على سبيل المثال، قد ترغب في ترتيب المتاجر كل عام حسب إيرادات المبيعات الخاصة بها. لتحقيق هذا الهدف، يمكنك استخدام وظائف الترتيب Transact-SQL مثل ROW_NUMBERو RANKوDENSE_RANK وNTILE. تمكنك هذه الدوال من تقسيم البيانات عبر الفئات، حيث تعطي كل فئة قيمة تشير إلى الموقع النسبي لكل صف داخل التقسيم:
- ROW_NUMBER تُرجع الموضع الترتيبي للصف داخل القسم. على سبيل المثال، يتم ترقيم الصف الأول 1 والثاني 2 وهكذا.
- تُرجع الدالة RANK الموضع المصنف لكل صف في النتائج المرتبة. على سبيل المثال، في قسم من المتاجر مرتبة حسب حجم المبيعات، يحتل المتجر الذي يحتوي على أكبر حجم مبيعات في المرتبة 1. إذا كان لدى العديد من المتاجر نفس أحجام المبيعات، فسيتم تصنيفها بنفس الترتيب، ويعكس التصنيف المخصص للمخازن اللاحقة عدد المتاجر التي لديها أحجام مبيعات أعلى - بما في ذلك الروابط.
- DENSE_RANK يصنف الصفوف في تقسيم بنفس طريقة RANK، ولكن عندما يكون لعدة صفوف نفس الرتبة، يتم ترتيب الصفوف التالية بدون فراغات — التعادل لا يستهلك مراكز إضافية.
- تُرجع NTILE القيمة المئوية المحددة التي يقع فيها الصف. على سبيل المثال، في قسم من المتاجر مرتبة حسب حجم المبيعات،
NTILE(4)ترجع الربع الذي يضعه فيه حجم مبيعات المتجر.
على سبيل المثال، خذ بعين الاعتبار الاستعلام الآتي:
SELECT ProductCategory,
ProductName,
ListPrice,
ROW_NUMBER() OVER
(PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS RowNumber,
RANK() OVER
(PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Rank,
DENSE_RANK() OVER
(PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS DenseRank,
NTILE(4) OVER
(PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Quartile
FROM dbo.DimProduct
ORDER BY ProductCategory;
يقوم الاستعلام بتقسيم المنتجات حسب الفئة، ويصنف كل منتج ضمن تقسيمه حسب سعر القائمة. النتائج تبدو مشابهة للجدول التالي:
| ProductCategory | ProductName | ListPrice | RowNumber | تصنيف | DenseRank | الربع |
|---|---|---|---|---|---|---|
| مُكَمِّلات | زجاجة الماء | 8.99 | 1 | 1 | 1 | 1 |
| مُكَمِّلات | عصابة الرأس | 8.49 | 2 | 2 | 2 | 1 |
| مُكَمِّلات | دفايات الذراع | 5.99 | 3 | 3 | 3 | 2 |
| مُكَمِّلات | حزام الكاحل | 5.99 | 4 | 3 | 3 | 2 |
| مُكَمِّلات | النعل الداخلي | 2.99 | 5 | 5 | 4 | 3 |
| مُكَمِّلات | رباط الحذاء | 0.25 | 6 | 6 | 5 | 4 |
| الملابس | قبعة الجري | 7.49 | 1 | 1 | 1 | 1 |
| الملابس | غلاف الضغط | 6.99 | 2 | 2 | 2 | 1 |
| الملابس | حقيبة الصالة الرياضية | 4.25 | 3 | 3 | 3 | 2 |
| ... | ... | ... | ... | ... | ... | ... |
إشعار
توضح نتائج العينة الفرق بين RANK و DENSE_RANK. لاحظ أنه في فئة الإكسسوارات ، فإن منتجات دفايات الذراعوحزام الكاحل لها نفس سعر القائمة، وكلاهما مصنف كثالث أعلى منتج سعرا. المنتج الأعلى سعراً التالي له RANK من 5 (هناك أربعة منتجات أكثر تكلفة منه) وDENSE_RANK من 4 (هناك ثلاثة أسعار أعلى).
لمعرفة المزيد حول دالات الترتيب، راجع الوحدة النمطية Use built-in functions وGROUP BY في Transact-SQL .
استرداد عدد تقريبي
بينما الغرض الأساسي من مستودع البيانات هو دعم نماذج البيانات التحليلية والتقارير للمؤسسة، غالبا ما يحتاج محللو البيانات وعلماء البيانات إلى إجراء بعض الاستكشاف الأولي للبيانات لتحديد الحجم الأساسي وتوزيع البيانات.
على سبيل المثال، يستخدم الاستعلام التالي الدالة COUNT لاسترجاع عدد المبيعات لكل سنة:
SELECT dates.CalendarYear AS CalendarYear,
COUNT(DISTINCT sales.OrderNumber) AS Orders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;
قد تبدو نتائج هذا الاستعلام مشابهة لما يلي:
| CalendarYear | الأوامر |
|---|---|
| 2023 | 239870 |
| 2024 | 284741 |
| 2025 | 309272 |
| ... | ... |
حجم البيانات في مستودع البيانات قد يعني أن حتى الاستعلامات البسيطة لعدد السجلات التي تلبي المعايير المحددة تستغرق وقتا طويلا للتشغيل. في كثير من الحالات، لا يطلب وجود عدد دقيق — فتقدير تقريبي يكفي. في مثل هذه الحالات، يمكنك استخدام APPROX_COUNT_DISTINCT الدالة كما هو موضح في المثال التالي:
SELECT dates.CalendarYear AS CalendarYear,
APPROX_COUNT_DISTINCT(sales.OrderNumber) AS ApproxOrders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;
APPROX_COUNT_DISTINCT تستخدم الدالة خوارزمية HyperLogLog لاسترداد عدد تقريبي. النتيجة مضمونة أن يكون الحد الأقصى لمعدل خطأ 2% مع احتمال 97%، لذا قد تبدو نتائج هذا الاستعلام مشابهة للجدول التالي:
| CalendarYear | عوامل التقريب |
|---|---|
| 2023 | 235552 |
| 2024 | 290436 |
| 2025 | 304633 |
| ... | ... |
تعد العد أقل دقة، ولكنها لا تزال كافية للمقارنة التقريبية للمبيعات السنوية. مع حجم كبير من البيانات، يكتمل الاستعلام باستخدام APPROX_COUNT_DISTINCT الدالة بسرعة أكبر، وقد تكون الدقة المنخفضة مفاضلة مقبولة أثناء استكشاف البيانات الأساسية.
إشعار
راجع وثائق الدالة APPROX_COUNT_DISTINCT لمزيد من التفاصيل.