การใช้การรวมค่าทางสถิติกับข้อมูลอนุกรมเวลา

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

Jasmin Ludolf

Content Developer, DataCamp

การรวมค่าทางสถิติ

ฟังก์ชันรวมค่า: MIN(), MAX(), SUM(), AVG(), COUNT()
การรวมค่าทางสถิติ: ค่าเฉลี่ยและค่ามัธยฐาน

ภาพประกอบแสดงผู้คนสี่คนรอบกระดานที่มีการแสดงผลการวิเคราะห์และสถิติ

การคำนวณค่าเฉลี่ย

SELECT
  AVG(views)::INTEGER as avg_views
  title
FROM dc_news_fact
JOIN dc_news_dim USING(id)
GROUP BY title
ORDER BY avg_views DESC;

|avg_views|title                      |
|---------|---------------------------|
|      207|For the Wealthiest, a Pr...|
|       12|Pet surrenders on rise a...|
|       12|Argentina's New Presiden...|
|        7|These 5 charts prove tha...|
|        7|How Is the Economy Doing...|

จำนวนการดูเฉลี่ยต่อวัน

WITH day_views AS (
  SELECT id, ts::DATE AS date, SUM(views) AS views
  FROM dc_news_fact
  GROUP BY id, date
)
SELECT
  AVG(views)::INTEGER AS avg
  title
FROM day_views JOIN dc_news_dim USING(id)
GROUP BY title
ORDER BY avg DESC;

จำนวนการดูเฉลี่ยต่อวัน

| avg|title                                                            |
|----|-----------------------------------------------------------------|
|9855|For the Wealthiest, a Private Tax System That Saves Them Billions|
| 579|Pet surrenders on rise as Fort McMurray's economy falls          |
| 574|Argentina's New President Moves Swiftly to Shake Up the Economy  |
| 352|How Is the Economy Doing? Politics May Decide Your Answer        |
| 348|These 5 charts prove that the economy does better under ...      |

จำนวนการดูเฉลี่ยต่อวัน

SELECT
  (SUM(views)/COUNT(DISTINCT ts::DATE))::INTEGER as avg,
  title
FROM dc_news_fact JOIN dc_news_dim USING(id)
GROUP BY title 
ORDER BY avg DESC;

|avg |title                                                            |
|----|-----------------------------------------------------------------|
|9855|For the Wealthiest, a Private Tax System That Saves Them Billions|
| 579|Pet surrenders on rise as Fort McMurray's economy falls          |
| 574|Argentina's New President Moves Swiftly to Shake Up the Economy  |
...

ค่ามัธยฐานแบบดิสครีตและแบบต่อเนื่อง

ค่ามัธยฐานแบบดิสครีต: ค่าแรกที่ใกล้กับค่ากลางที่สุด
ค่ามัธยฐานแบบต่อเนื่อง: ค่าที่แบ่งชุดข้อมูลออกเป็นสองส่วนเท่ากัน

จำนวนสมาชิกคี่

ชุด = (1, 2, 3, 4, 5)
ค่ามัธยฐานแบบดิสครีต = 3
ค่ามัธยฐานแบบต่อเนื่อง = 3

จำนวนสมาชิกคู่

ชุด = (1, 2, 3, 4)
ค่ามัธยฐานแบบดิสครีต = 2
ค่ามัธยฐานแบบต่อเนื่อง = 2.5

ฟังก์ชันรวมค่าแบบ ordered-set

PERCENTILE_DISC()
PERCENTILE_CONT()
ฟังก์ชันรวมค่าแบบ ordered-set: PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY field)

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP
  (ORDER BY value) AS median_cont,
  PERCENTILE_DISC(0.5) WITHIN GROUP
  (ORDER BY value) AS median_disc
FROM
(
  VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,5), (2,7), (2,11), (2,11)
) AS t (id, value)
GROUP BY id;

ฟังก์ชันรวมค่าแบบ ordered-set

|median_cont|median_disc|
|-----------|-----------|
|        3.0|          3|
|        7.0|          7|

ค่ามัธยฐาน ควอนไทล์ เปอร์เซ็นไทล์ ควอร์ไทล์

ค่ามัธยฐานเป็นประเภทหนึ่งของเปอร์เซ็นไทล์
เปอร์เซ็นไทล์เป็นประเภทหนึ่งของควอนไทล์

ควอนไทล์: แบ่งข้อมูลออกเป็นกลุ่มย่อยที่เกือบเท่ากัน
- ควอร์ไทล์ (สี่กลุ่ม)
- เดไซล์ (สิบกลุ่ม)

การคำนวณควอร์ไทล์

SELECT
  PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY value) AS ptile_25,
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY value) AS ptile_50,
  PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY value) AS ptile_75
FROM
(
  VALUES 
  (1,1), (1,2), (1,3), (1,4), (1,5)
) AS t (id, value)
GROUP BY id;

|ptile_25|ptile_50|ptile_75|
|--------|--------|--------|
|       2|       3|       4|

การคำนวณอาร์เรย์ของควอร์ไทล์แบบดิสครีต

SELECT
  PERCENTILE_DISC(ARRAY[0.25, 0.5, 0.75]) 
  WITHIN GROUP (ORDER BY value) AS median_disc
FROM (
  VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5) )
  AS t (id, value)
GROUP BY id;

|median_disc|
|-----------|
|{2,3,4}    |

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

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