ค่าเฉลี่ยเคลื่อนที่และผลรวมเคลื่อนที่

PostgreSQL Summary Stats and Window Functions

Michel Semaan

Moving averages

ภาพรวม

ค่าเฉลี่ยเคลื่อนที่ (MA): ค่าเฉลี่ยของ n ช่วงล่าสุด
- ตัวอย่าง: MA 10 วันของยอดขาย คือค่าเฉลี่ยของยอดขาย 10 วันล่าสุด
- ใช้บ่งชี้แนวโน้มและโมเมนตัม
- ช่วยกำจัดความผันผวนตามฤดูกาลได้ด้วย
ผลรวมเคลื่อนที่: ผลรวมของ n ช่วงล่าสุด
- ตัวอย่าง: ผลรวมเหรียญรางวัลจากโอลิมปิก 3 ครั้งล่าสุด
- ใช้วัดประสิทธิภาพ หากผลรวมลดลง แสดงว่าประสิทธิภาพโดยรวมลดลง

ตารางต้นทาง

คิวรี

SELECT
  Year, COUNT(*) AS Medals
FROM Summer_Medals
WHERE
  Country = 'USA'
  AND Medal = 'Gold'
  AND Year >= 1980
GROUP BY Year
ORDER BY Year ASC;

ผลลัพธ์

| Year | Medals |
|------|--------|
| 1984 | 168    |
| 1988 | 77     |
| 1992 | 89     |
| 1996 | 160    |
| 2000 | 130    |
| 2004 | 116    |
| 2008 | 125    |
| 2012 | 147    |

ค่าเฉลี่ยเคลื่อนที่

คิวรี

WITH US_Medals AS (...)

SELECT
  Year, Medals,
  AVG(Medals) OVER
    (ORDER BY Year ASC
     ROWS BETWEEN
     2 PRECEDING AND CURRENT ROW) AS Medals_MA
FROM US_Medals
ORDER BY Year ASC;

ผลลัพธ์

| Year | Medals | Medals_MA |
|------|--------|-----------|
| 1984 | 168    | 168.00    |
| 1988 | 77     | 122.50    |
| 1992 | 89     | 111.33    |
| 1996 | 160    | 108.67    |
| 2000 | 130    | 126.33    |
| 2004 | 116    | 135.33    |
| 2008 | 125    | 123.67    |
| 2012 | 147    | 129.33    |

ผลรวมเคลื่อนที่

คิวรี

WITH US_Medals AS (...)

SELECT
  Year, Medals,
  SUM(Medals) OVER
    (ORDER BY Year ASC
     ROWS BETWEEN
     2 PRECEDING AND CURRENT ROW) AS Medals_MT
FROM US_Medals
ORDER BY Year ASC;

ผลลัพธ์

| Year | Medals | Medals_MT |
|------|--------|-----------|
| 1984 | 168    | 168       |
| 1988 | 77     | 245       |
| 1992 | 89     | 334       |
| 1996 | 160    | 326       |
| 2000 | 130    | 379       |
| 2004 | 116    | 406       |
| 2008 | 125    | 371       |
| 2012 | 147    | 388       |

ROWS vs RANGE

RANGE BETWEEN [START] AND [FINISH]
- ทำงานคล้ายกับ ROWS BETWEEN
- RANGE จะถือว่าค่าซ้ำใน ORDER BY ของ OVER เป็นกลุ่มเดียวกัน

ตาราง

| Year | Medals | Rows_RT | Range_RT |
|------|--------|---------|----------|
| 1992 | 10     | 10      | 10       |
| 1996 | 50     | 60      | 110      |
| 2000 | 50     | 110     | 110      |
| 2004 | 60     | 170     | 230      |
| 2008 | 60     | 230     | 230      |
| 2012 | 70     | 300     | 300      |

ROWS BETWEEN นิยมใช้มากกว่า RANGE BETWEEN

มาฝึกกันเถอะ!

PostgreSQL Summary Stats and Window Functions