สำรวจฟังก์ชันที่มีประโยชน์

PostgreSQL Summary Stats and Window Functions

Michel Semaan

Data Scientist

ระวัง null!

คิวรี

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM summer_medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY ROLLUP(Country, Medal)
ORDER BY Country ASC, Medal ASC;

null หมายถึงยอดรวมของกลุ่ม

ผลลัพธ์

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | null   | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | null   | 143    |
| null    | null   | 327    |

แนะนำ COALESCE

COALESCE() รับรายการค่าและคืนค่าที่ไม่ใช่ null ตัวแรก โดยอ่านจากซ้ายไปขวา
COALESCE(null, null, 1, null, 2) → 1
มีประโยชน์เมื่อใช้คำสั่ง SQL ที่อาจคืนค่า null
- ROLLUP และ CUBE
- การ Pivot
- LAG และ LEAD

กำจัด null

คิวรี

SELECT
  COALESCE(Country, 'Both countries') AS Country,
  COALESCE(Medal, 'All medals') AS Medal,
  COUNT(*) AS Awards
FROM summer_medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY ROLLUP(Country, Medal)
ORDER BY Country ASC, Medal ASC;

ผลลัพธ์

| Country        | Medal      | Awards |
|----------------|------------|--------|
| Both countries | All medals | 327    |
| CHN            | All medals | 184    |
| CHN            | Bronze     | 57     |
| CHN            | Gold       | 74     |
| CHN            | Silver     | 53     |
| RUS            | All medals | 143    |
| RUS            | Bronze     | 56     |
| RUS            | Gold       | 43     |
| RUS            | Silver     | 44     |

บีบอัดข้อมูล

ก่อน

| Country | Rank |
|---------|------|
| CHN     | 1    |
| RUS     | 2    |
| USA     | 3    |

คอลัมน์ Rank ซ้ำซ้อน เพราะอันดับสื่อถึงลำดับอยู่แล้ว

หลัง

CHN, RUS, USA

กระชับและครบถ้วน เพราะลำดับสื่อถึงอันดับอยู่แล้ว

แนะนำ STRING_AGG

STRING_AGG(column, separator) รวมค่าทั้งหมดในคอลัมน์เข้าด้วยกัน โดยคั่นแต่ละค่าด้วย separator

STRING_AGG(Letter, ', ') แปลงข้อมูลนี้...

| Letter |
|--------|
| A      |
| B      |
| C      |

...ให้เป็นแบบนี้

A, B, C

คิวรีและผลลัพธ์

ก่อน

WITH Country_Medals AS (
  SELECT
    Country, COUNT(*) AS Medals
  FROM Summer_Medals
  WHERE Year = 2012
    AND Country IN ('CHN', 'RUS', 'USA')
    AND Medal = 'Gold'
    AND Sport = 'Gymnastics'
  GROUP BY Country),

  SELECT
    Country,
    RANK() OVER (ORDER BY Medals DESC) AS Rank
  FROM Country_Medals
  ORDER BY Rank ASC;

หลัง

WITH Country_Medals AS (...),

  Country_Ranks AS (...)

  SELECT STRING_AGG(Country, ', ')
  FROM Country_Medals;

ผลลัพธ์

CHN, RUS, USA

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

PostgreSQL Summary Stats and Window Functions