Running average และ Moving Average ของอุณหภูมิรายปี

ในฐานะนักวิทยาศาสตร์ข้อมูลประจำศูนย์วิจัยอุตุนิยมวิทยา คุณต้องการคำนวณค่าเฉลี่ยอุณหภูมิรายปีแบบ time-smoothed ของแต่ละรัฐตามที่อุณหภูมิเปลี่ยนแปลงไปในแต่ละปี จากนั้นเปรียบเทียบค่าเฉลี่ยอุณหภูมิของปีปัจจุบันกับ moving average ของอุณหภูมิรายปีในช่วง 5 ปีที่ผ่านมา

นิพจน์ตารางร่วม (common table expression) จะให้ค่าเฉลี่ยรายปีของค่าเฉลี่ยอุณหภูมิรายเดือนสำหรับแต่ละรัฐ เนื่องจากแต่ละรัฐมีสถานีตรวจอากาศเพียงแห่งเดียว การจัดกลุ่มตามรัฐจึงเทียบเท่ากับการจัดกลุ่มตามสถานี

แบบฝึกหัดนี้เป็นส่วนหนึ่งของหลักสูตร

การวิเคราะห์อนุกรมเวลาใน PostgreSQL

ดูคอร์ส

แบบฝึกหัดเชิงโต้ตอบแบบลงมือทำ

ลองทำแบบฝึกหัดนี้โดยเติมโค้ดตัวอย่างนี้ให้สมบูรณ์

-- Running average of the yearly avg temperature
WITH yearly AS(
	SELECT
		state, 
	    DATE_PART('year', year_month)::INTEGER AS year, 
	    AVG(t_monthly_avg) AS yearly_avg
	FROM temperatures_monthly JOIN temperature_stations USING(station_id)
    WHERE t_monthly_avg > -9999
	GROUP BY state, year
)
SELECT
	state,
	year,
	yearly_avg,
	___ OVER (
		___ 
		___ 
		ROWS BETWEEN ___)
        AS running_yearly_avg
FROM yearly
ORDER BY state, year;

แก้ไขและรันโค้ด