摘要資料

使用 AI 進行 SQL 查詢進階

Jasmin Ludolf

Senior Data Science Content Developer

彙總函式

SUM()
AVG()
MIN()
MAX()

影片的平均投票數是多少？

含摘要統計的報表

欄位摘要

彙總函式將多筆資料列總結為單一數值

用來從整體或分組了解資料

Prompt: 顯示所有影片的投票總數

SELECT SUM(num_votes) AS total_votes
FROM reviews;

|total_votes|
|-----------|
|419507814  |

計算欄位平均值

Prompt: 影片的平均投票數是多少？

SELECT AVG(num_votes) AS average_votes_per_film
FROM reviews;

|average_votes_per_film|
|----------------------|
|84441.991545893720    |

結果取整

Prompt: 影片的平均投票數（四捨五入）是多少？

SELECT ROUND(AVG(num_votes)) AS avg_votes
FROM reviews;

|avg_votes|
|---------|
|84442    |

ROUND(AVG(num_votes), 1)

84442.0

ROUND(AVG(num_votes), 2)

84441.99

ROUND(AVG(num_votes), -2)

結果取整

Prompt: …四捨五入到小數點後 1 位

SELECT ROUND(AVG(num_votes), 1) AS avg_votes
FROM reviews;

|avg_votes|
|---------|
|84442.0  |

Prompt: …四捨五入到最接近的百位

SELECT ROUND(AVG(num_votes), -2) AS avg_votes
FROM reviews;

|avg_votes|
|---------|
|84400    |

分組平均

Prompt: 依語言顯示四捨五入後的平均預算，並由高到低排序

SELECT language, ROUND(AVG(budget)) AS average_budget
FROM films
GROUP BY language
ORDER BY average_budget DESC;

|language |average_budget|
|---------|--------------|
|Greek    |              |
|Kannada  |              |    
|Polish   |              |
|Korean   |2741550000    |
...

最小值與最大值

Prompt: 顯示各國的最低與最高預算，依字母順序排列

SELECT country, 
       MIN(budget) AS lowest_budget, 
       MAX(budget) AS highest_budget
FROM films
GROUP BY country
ORDER BY country;

|country    |lowest_budget|highest_budget|
|-----------|-------------|--------------|
|Afghanistan|46000        |46000         |
|Argentina  |800000       |2000000       |
|Aruba      |35000000     |35000000      |
...

辨識範圍
了解資料涵蓋面
掌握典型值

彙總函式與資料型別

僅限數值欄位

SUM()
AVG()

多種資料型別

MIN()
MAX()
COUNT()

Minimum <-> Maximum

最小 <-> 最大
A <-> Z
最早 <-> 最近

最小值與最大值

SELECT MIN(language) AS min_language
FROM films;

|min_language|
|------------|
|Aboriginal  |

SELECT MAX(language) AS max_language
FROM films;

|max_language|
|------------|
|Zulu        |

SQL 最佳實務：別名（alias）

SELECT SUM(budget) AS total_budget
...

SELECT AVG(budget) AS average_budget
...

SELECT country, 
       MIN(budget) AS lowest_budget, 
       MAX(budget) AS highest_budget
...

SELECT AVG(num_user), AVG(num_critic)
FROM reviews;

|avg     |avg     |
|275.6...|141.9...|

SQL 最佳實務：格式化

查詢語句分成多行
使用縮排

SELECT country, 
       MIN(budget) AS lowest_budget, 
       MAX(budget) AS highest_budget
FROM films
GROUP BY country
ORDER BY country;

紙上有眼鏡

SQL 圖示

準備開始分析了嗎？

使用 AI 進行 SQL 查詢進階