التجميعات ومقاييس الاكتمال

WITH readings AS (SELECT * FROM VALUES
 ('north',CAST(2 AS DECIMAL(10,3))),
 ('north',CAST(3 AS DECIMAL(10,3))),
 ('north',CAST(NULL AS DECIMAL(10,3))) AS r(site_id,kwh))
SELECT site_id, COUNT(*) AS received, COUNT(kwh) AS known,
 SUM(kwh) AS total_kwh, AVG(kwh) AS mean_known_kwh
FROM readings GROUP BY site_id ORDER BY site_id;

site_id | received | known | total_kwh | mean_known_kwh
north   | 3        | 2     | 5         | 2.5

الأعداد المنطقية received=3 وknown=2، والمجموع total_kwh=5 والمتوسط mean_known_kwh=2.5. قد يختلف مقياس العرض.

SELECT site_id, SUM(kwh) AS total_kwh
FROM curated_readings
WHERE quality = 'accepted'
GROUP BY site_id
HAVING SUM(kwh) > 100
ORDER BY site_id;

يفترض المثال علاقة منقحة أُعدّت صراحة. تظل القراءات دون100 مساهمة، لأن الحد يطبق على إجمالي كل موقع.